ITPEC FE Morning October 2020 Question 26
The employee’s ID, name, salary, manager ID, and working department are recorded in the Employees table as follows:
Employees
When a query is formulated in SQL to retrieve the manager ID and the average salary of the employees under his/her direct report, and the output is obtained as below, which of the following is the appropriate combination to be inserted in blanks E and F in the SQL statement?
Explanation
E: b.Emp_ID, F: a.Manager_ID — join each employee (a) to the row of his or her manager (b), then group by the manager.
The condition b. pairs every employee row a with the row b of the person he or she reports to. Grouping by b. makes one group per manager, and avg( averages the salaries of that manager's direct reports. Rahman, whose Manager_ID is null, matches no b row and drops out.
This matches the output.
Answer (d)
Why not others:
- •(a) — joining and grouping on DID averages by department, so the first column would show department IDs 1, 2 and 3, not manager IDs
- •(b) — joining on Emp_ID pairs each row with itself, giving nine groups of one employee with his or her own salary
- •(c) — pairs each employee with every colleague who has the same manager; each salary is counted once per colleague, so the averages happen to come out the same for this data, but the join never reaches the manager's own row and multiplies rows for nothing, so it is not the manager–subordinate join the query describes
Key rule: In a self-join, give each alias one role, here a for the subordinate and b for the manager, and join the subordinate's foreign key to the manager's primary key.
Databases · AI-written, may contain errors
This question comes from an official ITPEC past paper. ITPEC Practice is an independent study tool and is not affiliated with ITPEC. See the official FE past-paper collection or Report an issue.