Question 5 of 8: SQL — Employee/Manages/Department

Question 5: SQL — Employee/Manages/Department (5+7+8=20 marks)

Question text not reproduced: the examination questions are © Engineers and Geoscientists BC. Open the official past paper (linked at the top of this page) to read the question, then follow the worked solution below.

Given. Employee(id, name, salary, deptId); Manages(emp_id, mgr_id) — one row per employee, emp_id is the key, mgr_id is that employee's one direct/immediate manager; Department(id, deptNo). The "transitive manager" sentence is background motivating why Manages need only store the immediate manager (transitive managers are derivable, not a modelling requirement for these three queries).

Find. Three SQL queries: (a) IDs of employees in the department whose deptNo is 10; (b) name+salary of every employee earning more than their own immediate manager; (c) name+ID of the one employee with no row in Manages (the CEO).

Approach. (a) is a simple two-table equi-join filtered on deptNo. (b) is a 3-way self-join through Manages: join Employee to itself via Manages so each row pairs an employee with their manager's own Employee row, then filter on the salary comparison. (c) is an anti-join: an employee is the CEO exactly when their id never appears as an emp_id in Manages (everyone else has exactly one Manages row).

1. (a) Department-10 employees. Join Employee to Department on the (assumed) foreign key and filter on the department's business-facing number:

   SELECT E.id
   FROM   Employee E, Department D
   WHERE  E.deptId = D.id
     AND  D.deptNo = 10;

   Equivalently with explicit JOIN syntax: SELECT E.id FROM Employee E JOIN Department D ON E.deptId = D.id WHERE D.deptNo = 10; — both forms are accepted; the exam-era convention (comma-join + WHERE) is shown first since the source paper is closed-book/pre-ANSI-JOIN style throughout.
2. (b) Out-earning one's own manager. Bring in a SECOND copy of Employee (aliased Mgr) via Manages, so each result row has both the employee's own salary and their direct manager's salary side by side, then keep only rows where the employee's is larger:

   SELECT E.name, E.salary
   FROM   Employee E, Manages M, Employee Mgr
   WHERE  E.id = M.emp_id
     AND  M.mgr_id = Mgr.id
     AND  E.salary > Mgr.salary;

   Only the IMMEDIATE manager (M.mgr_id) is compared, per part (b)'s own wording — the transitive-manager fact from the preamble is irrelevant here.
3. (c) The CEO. Since every non-CEO employee has exactly one row in Manages keyed by their own id as emp_id, the CEO is precisely the employee whose id never appears there:

   SELECT name, id
   FROM   Employee
   WHERE  id NOT IN (SELECT emp_id FROM Manages);

   NOT IN is safe here specifically because emp_id in Manages is declared NOT NULL (it is that table's key) — with a nullable subquery column, NOT IN silently returns no rows if any NULL is present, so a NOT EXISTS correlated form (WHERE NOT EXISTS (SELECT 1 FROM Manages M WHERE M.emp_id = Employee.id)) is the more defensively-written equivalent and would be accepted equally.