Why does SELECT dept FROM Emp WHERE AVG(salary) > 5000 GROUP BY dept fail, while the same condition written in HAVING works?
Correct answer A. WHERE filters individual rows before groups and aggregates exist
Explanation
Logically, SQL processes FROM, then WHERE on single rows, then GROUP BY, then HAVING on groups, then SELECT and ORDER BY. When WHERE runs, there are no groups yet, so an aggregate such as AVG(salary) has nothing to work on and is not allowed there. HAVING runs after grouping, so it can test aggregates. HAVING comes after WHERE, not before.
Answer checked How we write and check questions
Discussion