What happens when the statement is executed?

EMPLOYEES and DEPARTMENTS data:
EMPLOYEES

DEPARTMENTS

On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID managers and refers to the
EMPLOYEE_ID.
On the DEPARTMENTS table DEPARTMENT_ID is the primary key.
Evaluate this UPDATE statement.
UPDATE employees
SET mgr_id =
. (SELECT mgr_id
. FROM. employees
. WHERE dept_id=
. (SELECT department_id
. FROM departments
. WHERE department_name = ‘Administration’)),
. Salary = (SELECT salary
. . FROM employees
. . WHERE emp_name = ‘Smith’)
WHERE job_id = ‘IT_ADMIN’;
What happens when the statement is executed?

A.
The statement executes successfully, leaves the manager ID as the existing value, and changes the salary
to 4000 for the employees with ID 103 and 105.

B.
The statement executes successfully, changes the manager ID to NULL, and changes the salary to 4000 forthe employees with ID 103 and 105.

C.
The statement executes successfully, changes the manager ID to NULL, and changes the salary to 3000 for
the employees with ID 103 and 105.

D.
The statement fails because there is more than one row matching the employee name Smith.

E.
The statement fails because there is more than one row matching the IT_ADMIN job ID in the EMPLOYEES
table.

F.
The statement fails because there is no ‘Administration’ department in the DEPARTMENTS table.

Explanation:
‘=’ is use in the statement and sub query will return more than one row.
Employees table has 2 row matching the employee name Smith.
The update statement will fail.
Incorrect Answers :
A:
The Update statement will fail no update was done.
B:
The update statement will fail no update was done.
C:
The update statement will fail no update was done.
E:
The update statement will fail but not due to job_it=’IT_ADMIN’
F:
The update statement will fail but not due to department_id=’Administration’
Refer: Introduction to Oracle9i: SQL, Oracle University Student Guide, Sub queries, p. 6-12