Q4: (30 points)
Assume that you have a database which contains the following relations:
Employee(EmpID, Name, DeptID, Salary) (100,000 tuples)
Department(DeptID, DeptName, Location) (500 tuples)
Assume that we want to write SQL query that retrieves employees earning above the average salary in
their department.
Consider the following two answers. Which query is more efficient and why?
Query 1
SELECT E.Name, E.Salary
FROM Employee E
WHERE E.Salary > (SELECT AVG(E2.Salary)
FROM Employee E2
WHERE E2.DeptID = E.DeptID);
Query2
WITH AvgSalaries AS
( SELECT DeptID, AVG(Salary) AS AvgSal
FROM Employee
GROUP BY DeptID )
SELECT E.Name, E.Salary
FROM Employee E JOIN AvgSalaries A
ON E.DeptID = A.DeptID
WHERE E.Salary > A.AvgSal;
3
