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.
Why it wouldn’t give the correct result?
Because of this set clause:
(salary, commission) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission)
FROM employees, departments WHERE departments.location_id IN(2900,2700,2100))
So salary and commission would be set to average of those from three locations whereas you need to use average only from their (2100) location:
“-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 “
Thanks for clarifying!
Are you preparing for 1z0-061 too?
-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
These two conditions requires to update in location_id 2100 whereas
-Update only those employees who work in Boston or Seattle (locations 2900 and 2700).
says update only in 2900 and 2700…so it won’t work