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

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?

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.



Leave a Reply 4

Your email address will not be published. Required fields are marked *


Jake

Jake

Why it wouldn’t give the correct result?

Michal

Michal

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 “

Jordan

Jordan

Thanks for clarifying!

Are you preparing for 1z0-061 too?

Ramesh

Ramesh

-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