（166）View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. You want to update the EMPLOYEES table as follows:

-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

-Set department_id for these employees to the department_id corresponding to London (location_id 2100).

-Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.

-Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

You issue the following command:

UPDATE employees SET department_id = (SELECT department_id FROM departments WHERE location_id = 2100),

(salary, commission) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission) FROM employees, departments WHERE departments.location_id IN(2900,2700,2100))

WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700)

What is the outcome?

A. It executes successfully and gives the correct result.

B. It executes successfully but does not give the correct result.

C. It generates an error because a subquery cannot have a join condition in an UPDATE statement.

D. It generates an error because multiple columns (SALARY, COMMISION) cannot be specified together in an UPDATE statement.

答案：（B）

ITPub博客

OCP 1Z0-051 166

最新文章