WHERE vs HAVING mental model in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding WHERE clause filtering

Which statement best describes when the WHERE clause is applied in an SQL query?

A It filters rows before any grouping or aggregation happens.

B It filters groups after aggregation is done.

C It filters columns after the SELECT statement.

D It filters the final result set after ORDER BY.

💡 Hint

Think about when rows are removed in the process: before or after grouping?

The WHERE clause filters individual rows before any grouping or aggregation occurs. It decides which rows enter the grouping stage.

🧠 Conceptual

intermediate

Understanding HAVING clause filtering

Which statement best describes when the HAVING clause is applied in an SQL query?

A It filters individual rows before grouping.

B It filters groups after aggregation is done.

C It filters columns before SELECT.

D It filters rows after ORDER BY.

💡 Hint

Think about filtering groups, not rows.

The HAVING clause filters groups after aggregation. It decides which groups appear in the final result.

❓ query_result

advanced

Output of WHERE vs HAVING filtering

Given the table sales with columns region and amount, what is the output of this query?

SELECT region, SUM(amount) AS total FROM sales WHERE amount > 100 GROUP BY region;

SQL

Table sales:
region | amount
-------|-------
East   | 150
East   | 90
West   | 200
West   | 50
South  | 120
South  | 80

A [{"region": "East", "total": 90}, {"region": "West", "total": 50}, {"region": "South", "total": 80}]

B [{"region": "East", "total": 240}, {"region": "West", "total": 250}, {"region": "South", "total": 200}]

C [{"region": "East", "total": 150}, {"region": "West", "total": 200}, {"region": "South", "total": 120}]

D [{"region": "East", "total": 0}, {"region": "West", "total": 0}, {"region": "South", "total": 0}]

💡 Hint

Remember WHERE filters rows before grouping.

The WHERE clause removes rows where amount ≤ 100 before grouping. So only amounts 150, 200, and 120 remain. The SUM is over these filtered rows.

❓ query_result

advanced

Output of HAVING filtering after aggregation

Given the same sales table, what is the output of this query?

SELECT region, SUM(amount) AS total FROM sales GROUP BY region HAVING SUM(amount) > 200;

SQL

Table sales:
region | amount
-------|-------
East   | 150
East   | 90
West   | 200
West   | 50
South  | 120
South  | 80

A [{"region": "East", "total": 240}, {"region": "West", "total": 250}]

B [{"region": "East", "total": 240}, {"region": "West", "total": 250}, {"region": "South", "total": 200}]

C [{"region": "South", "total": 200}]

D []

💡 Hint

HAVING filters groups after SUM is calculated.

The SUM per region is East=240, West=250, South=200. HAVING keeps groups with total > 200, so South is excluded.

📝 Syntax

expert

Identify the syntax error in combined WHERE and HAVING query

Which option contains a syntax error in this query?

SELECT region, COUNT(*) FROM sales WHERE region = 'East' HAVING COUNT(*) > 1 GROUP BY region;

A The COUNT(*) function cannot be used in HAVING.

B The WHERE clause cannot be used with GROUP BY.

C The GROUP BY clause must come before HAVING, so this query is correct.

D The HAVING clause appears before GROUP BY, causing a syntax error.

💡 Hint

Check the order of clauses in SQL syntax.

In SQL, the GROUP BY clause must come before HAVING. Placing HAVING before GROUP BY causes a syntax error.