1Z0-047 · Question #215
View the Exhibit and examine the data in EMPLOYEES and DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table…
The correct answer is A. It would execute successfully and update the relevant data. See the full explanation below for the reasoning.
Question
View the Exhibit and examine the data in EMPLOYEES and DEPARTMENTS tables. In the EMPLOYEES table EMPLOYEE_ID is the PRIMARY KEY and DEPARTMENT_ID is the FOREIGN KEY. In the DEPARTMENTS table DEPARTMENT_ID is the PRIMARY KEY. Evaluate the following UPDATE statement:
UPDATE employees a SET department_jd = (SELECT department_id FROM departments WHERE location_id = `2100'), (salary, commission_pct) = (SELECT 1.1AVG(salary), 1.5AVG(commission_pct) FROM employees b WHERE a. department_jd = b. department_id) WHERE first_name|| '||last_name = 'Amit Banda'; What would be the outcome of the above statement?
Exhibit
Options
- AIt would execute successfully and update the relevant data.
- BIt would not execute successfully because there is no LOCATION_ID 2100 in the DEPARTMENTS table.
- CIt would not execute successfully because the condition specified with the concatenation operator is
- DIt would not execute successfully because multiple columns (SALARY,COMMISSION_PCT)cannot be
How the community answered
(61 responses)- A84% (51)
- B2% (1)
- C5% (3)
- D10% (6)
Community Discussion
7
Ingrid P.Jun 9, 2026The answer is A. Oracle SQL fully supports the multi-column subquery syntax shown here, where you set a tuple like (salary, commission_pct) equal to a correlated subquery returning two values, and the WHERE clause concatenation is perfectly valid, so nothing in this statement prevents execution. Card this one under Oracle DML edge cases and pair it with a second card drilling the tuple-update syntax specifically, because the distractor in D trips up a lot of test-takers who confuse Oracle with databases that restrict multi-column SET clauses.
Luis F.Jun 12, 2026Good call on D being the main trap, though I'd add that the subquery itself still has to return exactly one row or you get ORA-01427 at runtime, which is the kind of runtime gotcha they love to hide in the "nothing prevents execution" wording.
Orla P.May 27, 2026D trips up a lot of people because they confuse row-value constructor syntax with something illegal, but Oracle has supported updating multiple columns via a single subquery since 9i, so that is not the issue. The statement runs fine and the answer is A.
Luis F.May 28, 2026Orla is right that the multi-column subquery syntax is valid, but worth pointing out that the subquery in D has to return exactly one row or Oracle throws ORA-01427, so if the source table could produce multiple rows that would be the real disqualifier, not the syntax itself.
Luis F.Jun 15, 2026So A is right, but why does the multi-column subquery syntax work here, is that Oracle-specific?
Wesley A.May 31, 2026D for sure, Oracle flat out rejects multi-column SET like that syntax.
Orla P.Jun 3, 2026Wesley, Oracle actually does support multi-column SET with a subquery on the right side, which is what option A uses, so the syntax is valid and that is why A is the credited answer.