HAVING clause for filtering groups in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the HAVING clause in SQL?

The HAVING clause is used to filter groups created by GROUP BY based on a condition, similar to how WHERE filters rows before grouping.

beginner

How does HAVING differ from WHERE in SQL queries?

WHERE filters individual rows before grouping, while HAVING filters groups after aggregation is done.

beginner

Write a simple SQL query using HAVING to find departments with more than 5 employees.

SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department HAVING COUNT(*) > 5;

intermediate

Can HAVING be used without GROUP BY? Explain.

Yes, HAVING can be used without GROUP BY to filter aggregated results over the entire table, but it is less common.

beginner

Why do aggregate functions like COUNT() or SUM() often appear in HAVING clauses?

Because HAVING filters groups based on aggregated values, aggregate functions are used to specify conditions on those groups.

What does the HAVING clause filter in an SQL query?

A Groups after aggregation

B Columns in the SELECT statement

C Individual rows before grouping

D Database tables

HAVING filters groups after aggregation, unlike WHERE which filters rows before grouping.

Which clause is used to filter rows before grouping in SQL?

A ORDER BY

B GROUP BY

C HAVING

D WHERE

WHERE filters rows before grouping, HAVING filters groups after aggregation.

Which aggregate function is commonly used in HAVING clauses?

A ROUND()

B NOW()

C COUNT()

D SUBSTRING()

COUNT() is an aggregate function often used in HAVING to filter groups by their size.

Can HAVING be used without GROUP BY in SQL?

A No, it always requires GROUP BY

B Yes, but it filters aggregated results over the entire table

C Yes, it filters individual rows

D No, HAVING is only for sorting

HAVING can filter aggregated results without GROUP BY, but this is less common.

Which clause would you use to find groups with SUM(sales) greater than 1000?

A HAVING SUM(sales) > 1000

B WHERE SUM(sales) > 1000

C GROUP BY SUM(sales) > 1000

D ORDER BY SUM(sales) > 1000

HAVING is used to filter groups based on aggregate conditions like SUM(sales) > 1000.

Explain the difference between WHERE and HAVING clauses in SQL.

Think about when filtering happens in the query process.

Write an example SQL query using HAVING to show only groups with an average score above 80.

Use GROUP BY and HAVING with AVG() function.