166.View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

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:


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
解析：题目重第一条说是只更新工作在Boston or Seattle的人员，那么where条件是正确的，
第二条是更新department_id为工作在London的department_id，那么语句中update department_id也是正确的
第三条说是更新他们的salary为location_id=2100 的平均的1.1倍
第四条说是更新他们的commission为location_id=2100 的平均1.5被，这里对应的update中的WHERE departments.location_id IN(2900,2700,2100)
这个是错误的，应该为WHERE departments.location_id IN(2100)
上面这条语句看是否正确主要问题在department_id =
(SELECT department_id
FROM departments
WHERE location_id = 2100)这条语句，看是否返回单条记录，从题目中没有说明department_id和location_id是唯一值
那么如果location_id = 2100返回多条department_id，那么这条语句就是错误的，但是看给的选项没有这么一个，因此可以
认为它返回单条记录，因此选择B，