Table Emp(id, dept, salary) has rows (1,’HR’,3000), (2,’IT’,5000), (3,’IT’,4000), (4,’HR’,NULL), (5,’Ops’,2000). SELECT dept, SUM(salary) FROM Emp GROUP BY dept HAVING SUM(salary) > 3000 ORDER BY dept; Which rows are returned?
Correct answer B. Only (IT, 9000)
Explanation
GROUP BY forms three groups with sums HR = 3000 (the NULL is ignored), IT = 9000 and Ops = 2000. HAVING then filters whole groups, and the condition is strictly greater than 3000, so HR with exactly 3000 is excluded. Only the IT group survives. HAVING is evaluated after aggregation, which is why it can test SUM(salary) while WHERE cannot.
Answer checked How we write and check questions
Discussion