ocpÌâ¿âÔõô±³£º11g-051-V9.02-µÚ166Ì⣬ÍêÕûÌâ¿âÇëµã»÷ÕâÀïÁªÏµÀÏʦ×ÉѯÁ˽â

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

You want to update the EMPLOYEES table as follows:4 ? 4;

-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.

Answer: B

ÊÔÌâ½âÎö£º

ÌâÒâÒªÇó¸üÐÂÌõ¼þ£º

1.Ö»¸üÐÂÄÇЩÔÚBoston»òSeattle¹¤×÷µÄemployees(locationsΪ2900»òÕß2700)

2.ÉèÖÃÕâЩemployeesµÄdepartment_idΪLondon(location_id 2100)¶ÔÓ¦µÄdepartment_id

3.ÉèÖÃlocation_id 2100µÄemployees' salaryΪËûÃDz¿ÃŵÄƽ¾ùнˮµÄ1.1±¶

4.ÉèÖÃlocation_id 2100µÄemployees' commissionΪËûÃDz¿ÃŵÄƽ¾ùÌá³ÉµÄ1.5±¶