一、原题
二、题目翻译
三、题目解析
View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.
![](https://img-blog.csdn.net/20140525125842578?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaG9sbHkyMDA4/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
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
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)。
要把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的员工的工资和提成,其它两个部门的都不需要,所以不正确。
1.5 * AVG(commission)
FROM employees, departments
WHERE departments.location_id IN (2900, 2700, 2100)
这个子查询,查出来的,不是题目要求的记录,
employees, departments两张表应该使用部门ID进行关联
而且,这里只需要改location_id=2100的员工的工资和提成,其它两个部门的都不需要,所以不正确。
所以,这个语句虽然能正常执行,但不是我们题目要求的结果。