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的員工的工資和提成，其它兩個部門的都不需要，是以不正确。 是以，這個語句雖然能正常執行，但不是我們題目要求的結果。