ITPEC FE Morning October 2020 Question 26
ITPEC FE Morning October 2020 — Question 26 of 80
SQL Self-Join for Manager–Subordinate Relationships — find each manager's ID and average salary of their direct reports using a self-join.
The query uses a self-join on the Employees table:
- Table a = subordinates (whose salary we average)
- Table b = managers (whose Emp_ID we display)
Join condition: b.Emp_ID = a.Manager_ID
This links each subordinate (a) to their manager (b).
SQL structure:
- •
SELECT b.Emp_ID AS "Manager_ID", avg(a.Salary) AS "Average_Salary" - •
FROM Employees a, Employees b - •
WHERE b.Emp_ID = a.Manager_ID - •
GROUP BY b.Emp_ID - •
ORDER BY b.Emp_ID
Blank E = b.Emp_ID (the manager's ID — used in SELECT, GROUP BY, ORDER BY)
Blank F = a.Manager_ID (links subordinate to their manager)
Verification for Manager_ID = 18:
- Subordinates: Amit (50000), Nishi (40000), Pritom (80000), Mohitlal (45000)
- Average: (50000 + 40000 + 80000 + 45000) / 4 = 53750 ✓
Why not others:
- (a) a.DID = b.DID — joins by department, not by manager relationship
- (b) a.Emp_ID = b.Emp_ID — joins employee to itself, meaningless
- (c) a.Manager_ID = b.Manager_ID — groups employees who share the same manager, does not identify the manager
AI-generated — may contain errors
The original exam layout is preserved in the image so diagrams, formulas, tables, and code remain accurate.
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.