OCP 1Z0-051 166

（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博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1172125/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1172125/