OCP-1Z0-051 第166题 使用子查询update表中的多列

一、原题

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:
SQL>
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

二、题目翻译

看下面EMPLOYEES和DEPARTMENTS表的结构:
要把EMPLOYEES表做如下更新:
--只更新那些在Boston或Seattle工作的employees（locations为2900和2700)。
--设置这些employees的department_id为London（location_id=2100）对应的department_id。
--设置location_id=2100的employees' salary为他们部门的平均薪水的1.1倍。
--设置location_id=2100的employees' commission为他们部门的平均提成的1.1倍。
执行下面的语句：
结果是什么?
A.执行成功并给出正确结果。
B.执行成功但不给出正确结果。
C.报错，因为UPDATE语句中的子查询不能使用连接条件。
D.报错，因为UPDATE语句中不能一起指定多个列（SALARY, COMMISION）。

三、题目解析

(salary, commission) = (SELECT 1.1 * AVG(salary),
                               1.5 * AVG(commission)
                          FROM employees, departments
                         WHERE departments.location_id IN (2900, 2700, 2100)
这个子查询，查出来的，不是题目要求的记录，
employees, departments两张表应该使用部门ID进行关联
而且，这里只需要改location_id=2100的员工的工资和提成，其它两个部门的都不需要，所以不正确。

所以，这个语句虽然能正常执行，但不是我们题目要求的结果。