Q.Using the Employee table, write an SQL query to display only those departments whose average salary is greater than 29000. Explain why HAVING is used here instead of WHERE.

Filtering on a group's average needs HAVING, not WHERE; this question asks for the query and the reason.

We first group the employees by department, then keep only the groups whose average salary exceeds 29000. Because the condition is on an aggregate value of the group (its average), it must be written in a HAVING clause, which is applied after the grouping:

SELECT Dept, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dept
HAVING AVG(Salary) > 29000;

For the sample data the department averages are Sales 27500, Accounts 30000 and IT 40000, so the result lists Accounts (30000) and IT (40000); Sales is dropped. …

WHERE and HAVING can appear together — e.g. WHERE Salary IS NOT NULL ... GROUP BY Dept HAVING AVG(Salary) > 29000 first drops …

Trying to write WHERE AVG(Salary) > 29000 is an error — aggregates cannot go in WHERE. Use WHERE for row conditions (before gro …