Exam 1z0-061 topic 1 question 38 discussion

Actual exam question from Oracle's 1z0-061

Question #: 38
Topic #: 1

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 iocation_id 2100 to 1.1 times the average salary of their department.
-Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department.
You issue the following command:

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, COMMISSION) cannot be specified together in an update statement.

Show Suggested Answer

Suggested Answer: B 🗳️
Not that employees is used both in the first line (UPDATE employees) and later (FROM employees, departments). This would not cause the correct output.
Instead aliases should be use.
The following would be the correct query:

UPDATE employees a -
SET department_id =
(SELECT department_id

FROM departments -
WHERE location_id = '2100'),
(salary, commission_pct) =
(SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)

FROM employees b -
WHERE a.department_id = b.department_id)

WHERE department_id IN -
(SELECT department_id

FROM departments -

WHERE location_id = 2900 -
OR location_id = 2700);
Reference:
http://docs.oracle.com/database/121/SQLRF/statements_10008.htm#SQLRF01708

by Sugar at July 24, 2019, 3:35 p.m.

Comments

Submit Cancel

Sugar

5 years, 11 months ago

I don't agree with your correct output script --this portion will update all the salary for all not for location ID 2100. (salary, commission_pct) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b - WHERE a.department_id = b.department_id) above should have where location_id = 2100

upvoted 2 times

...