CompTIA DataSys+ (DS0-001)Database FundamentalsEasy
A data analyst needs to retrieve the average salary of employees in each department, but only for departments where the average salary is above $60,000. Which clause should be used after the `GROUP BY` clause to filter these aggregated results?
- AHAVING
- BORDER BY
- CLIMIT
- DWHERE
Show answer & explanationAnswer & explanation
Correct answer: A. HAVING
The `HAVING` clause is used to filter groups based on aggregate functions, which is exactly what is needed here to filter departments by their average salary.
Why the other options are wrong
- B. ORDER BY sorts the results, it does not filter them.
- C. LIMIT restricts the number of rows returned, not for filtering based on aggregate values.
- D. WHERE filters individual rows *before* aggregation, not groups based on aggregate results.
HAVING Clause
A SQL clause used to filter the results of a `GROUP BY` clause based on aggregate function conditions.
- Filters groups, not individual rows.
- Always comes after `GROUP BY`.
- Can use aggregate functions (e.g., SUM, AVG, COUNT) in its condition.
Memory trick: WHERE filters rows, HAVING filters groups.