OCP 1Z0 051 166

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. 

各项叙述都不对，同上题一样，location_id不是唯一列，用=赋值有报错的可能

SQL> SELECT location_id, COUNT(*) AS ct  2    FROM hr.departments  3   GROUP BY location_id  4  HAVING COUNT(*) > 2;LOCATION_ID         CT----------- ----------       1700         211 row selected

SQL> UPDATE hr.employees  2     SET department_id =  3         (SELECT department_id FROM hr.departments WHERE location_id = 2100),  4         (salary, commission_pct) =  5         (SELECT 1.1 * AVG(salary), 1.5 * AVG(commission_pct)  6            FROM hr.employees, hr.departments  7           WHERE departments.location_id IN (2900, 2700, 2100))  8   WHERE department_id IN (SELECT department_id  9                             FROM hr.departments 10                            WHERE location_id = 2900 11                               OR location_id = 2700);1 row updated

UPDATE hr.employees   SET department_id =       (SELECT department_id FROM hr.departments WHERE location_id = 1700),       (salary, commission_pct) =       (SELECT 1.1 * AVG(salary), 1.5 * AVG(commission_pct)          FROM hr.employees, hr.departments         WHERE departments.location_id IN (2900, 2700, 2100)) WHERE department_id IN (SELECT department_id                           FROM hr.departments                          WHERE location_id = 2900                             OR location_id = 2700)ORA-01427: single-row subquery returns more than one row

Answer: B