OCP 071 View the Exhibit and exa

一、原题

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的员工的工资和提成，其它两个部门的都不需要，所以不正确。

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