Table Emp(id, dept, salary) has rows (1,’HR’,3000), (2,’HR’,5000), (3,’IT’,4000), (4,’IT’,6000), (5,’IT’,8000). SELECT id FROM Emp e WHERE salary > (SELECT AVG(salary) FROM Emp WHERE dept = e.dept) ORDER BY id; Which ids are returned?
Correct answer C. 2, 5
Explanation
The subquery is correlated: it refers to e.dept, so it is re-evaluated for each outer row using that row's department. The HR average is 4000, so id 2 (5000) qualifies. The IT average is 6000, so only id 5 (8000) qualifies; id 4 equals the average and fails the strict test. The answer 4, 5 uses the overall average of 5200 instead of a per-department one.
Answer checked How we write and check questions
Discussion