Why filtering behavior matters in PostgreSQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

Filtering rows with WHERE vs HAVING

Consider a table sales with columns region, product, and amount. Which query correctly returns regions with total sales greater than 1000?

PostgreSQL

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

A Returns regions where each individual sale is over 100 and total sales per region exceed 1000.

B Returns regions where total sales per region exceed 1000, ignoring individual sale amounts.

C Returns all regions regardless of sales amounts.

D Returns an error because WHERE cannot be used with GROUP BY.

💡 Hint

Remember that WHERE filters rows before grouping, and HAVING filters groups after aggregation.

The WHERE amount > 100 filters out sales less than or equal to 100 before grouping. Then HAVING total_sales > 1000 filters groups where the sum is greater than 1000. So only regions with individual sales over 100 and total sales over 1000 are returned.

🧠 Conceptual

intermediate

Effect of filter order on query results

Why does the order of filtering conditions matter in SQL queries involving aggregation?

A Because <code>WHERE</code> filters rows before aggregation, and <code>HAVING</code> filters after aggregation, changing the order changes results.

B Because <code>HAVING</code> always runs before <code>WHERE</code>, so order does not matter.

C Because filters in <code>WHERE</code> and <code>HAVING</code> are combined and run simultaneously.

D Because <code>WHERE</code> filters groups and <code>HAVING</code> filters individual rows.

💡 Hint

Think about when rows are filtered versus when groups are filtered.

WHERE filters rows before aggregation, so it affects which rows are included in groups. HAVING filters groups after aggregation. Changing the order changes which data is aggregated and which groups remain.

📝 Syntax

advanced

Identify the syntax error in filtering query

Which option contains a syntax error in filtering aggregated data?

PostgreSQL

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
WHERE emp_count > 10;

A The query is correct and will run without errors.

B Syntax error because GROUP BY must come after HAVING.

C Syntax error because COUNT(*) cannot be used in HAVING clause.

D Syntax error because WHERE cannot be used after GROUP BY; HAVING should be used instead.

💡 Hint

Check the order of clauses in SQL and which clause filters aggregated results.

WHERE filters rows before grouping and cannot filter aggregated results. To filter groups based on aggregate functions like COUNT, use HAVING after GROUP BY.

❓ optimization

advanced

Optimizing filtering for large datasets

Given a large orders table, which filtering approach is more efficient to find customers with more than 5 orders?

PostgreSQL

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

A Filter rows with WHERE before grouping using COUNT(*) > 5 condition.

B Use a subquery to filter customers with more than 5 orders before grouping.

C Add an index on customer_id to speed up grouping and filtering.

D Use HAVING after GROUP BY as shown; it is efficient for large datasets.

💡 Hint

Think about how indexes affect query performance on large tables.

Indexes on columns used in GROUP BY and filtering can speed up query execution by reducing data scanned. Filtering with WHERE on aggregate functions is not possible. Using HAVING is correct but adding an index improves performance.

🔧 Debug

expert

Debugging unexpected filtering results

A query returns no rows but you expect some. The query is:

SELECT category, AVG(price) AS avg_price
FROM products
WHERE price < 200
GROUP BY category
HAVING AVG(price) > 150;

What is the most likely reason for no results?

A AVG(price) cannot be used in HAVING clause, causing no results.

B The WHERE clause filters out all rows with price ≥ 200 before grouping, so groups have fewer rows, lowering AVG(price).

C HAVING clause is filtering groups incorrectly; it should be in WHERE.

D GROUP BY category is missing, causing aggregation error.

💡 Hint

Consider how WHERE affects which rows are included before aggregation.

The WHERE clause removes all products priced 200 or more before grouping. This reduces the number of rows per category, which can lower the average price below 150, causing HAVING to filter out all groups.

Practice

1. What is the main purpose of the WHERE clause in a PostgreSQL query?

easy

A. To create a new table

B. To sort the results alphabetically

C. To join two tables together

D. To filter rows based on a condition

Solution

1. Step 1: Understand the role of WHERE clause

   The WHERE clause is used to specify conditions that rows must meet to be included in the result.

2. Step 2: Compare options with WHERE purpose

   Sorting is done by ORDER BY, joining by JOIN, and creating tables by CREATE TABLE, so these are incorrect.

3. Final Answer:

   To filter rows based on a condition -> Option D

4. Quick Check:

   WHERE filters rows = A [OK]

Hint: WHERE filters rows to get only what you want [OK]

Common Mistakes:

• Confusing WHERE with ORDER BY
• Thinking WHERE creates tables
• Mixing WHERE with JOIN

2. Which of the following is the correct syntax to filter rows where the column age is greater than 30?

easy

A. SELECT * FROM users FILTER age > 30;

B. SELECT * FROM users WHERE age > 30;

C. SELECT * FROM users HAVING age > 30;

D. SELECT * FROM users ORDER BY age > 30;

Solution

1. Step 1: Identify correct filtering syntax

   The WHERE clause is used to filter rows based on conditions like age > 30.

2. Step 2: Eliminate incorrect clauses

   FILTER is not valid here, HAVING is for groups, ORDER BY sorts results, so only WHERE is correct.

3. Final Answer:

   SELECT * FROM users WHERE age > 30; -> Option B

4. Quick Check:

   WHERE filters rows correctly = A [OK]

Hint: Use WHERE for row conditions, HAVING for groups [OK]

Common Mistakes:

• Using HAVING without GROUP BY
• Using FILTER instead of WHERE
• Confusing ORDER BY with filtering

3. Given the table employees with columns id, name, and salary, what will this query return?

SELECT name FROM employees WHERE salary >= 50000;

medium

A. All employee names with salary equal or greater than 50000

B. Syntax error due to missing GROUP BY

C. All employee names regardless of salary

D. All employee names with salary less than 50000

Solution

1. Step 1: Analyze the WHERE condition

   The condition salary >= 50000 filters employees with salary 50000 or more.

2. Step 2: Understand SELECT output

   Only the name column is selected for those filtered employees.

3. Final Answer:

   All employee names with salary equal or greater than 50000 -> Option A

4. Quick Check:

   WHERE salary >= 50000 filters names = D [OK]

Hint: WHERE filters rows; SELECT chooses columns [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing >= with <
• Expecting GROUP BY needed here

4. Identify the error in this query:

SELECT * FROM orders WHERE status = 'shipped' AND;

medium

A. Incorrect use of WHERE instead of HAVING

B. Missing ORDER BY clause

C. Missing condition after AND

D. Using * instead of column names

Solution

1. Step 1: Check WHERE clause syntax

   The WHERE clause ends with AND but no condition follows, causing syntax error.

2. Step 2: Validate other parts

   HAVING is not needed here, ORDER BY is optional, and * is valid to select all columns.

3. Final Answer:

   Missing condition after AND -> Option C

4. Quick Check:

   AND must be followed by condition = B [OK]

Hint: AND must always have a condition after it [OK]

Common Mistakes:

• Leaving AND without condition
• Confusing WHERE and HAVING
• Thinking ORDER BY is mandatory

5. You want to find customers who placed orders with total amount over 100 but only if their account is active. Which query correctly filters this?

SELECT customer_id FROM orders WHERE total > 100 AND account_status = 'active';

hard

A. This query correctly filters customers with total > 100 and active accounts

B. The query should use HAVING instead of WHERE for filtering

C. The query needs GROUP BY customer_id to filter correctly

D. Filtering on account_status is invalid in WHERE clause

Solution

1. Step 1: Understand filtering conditions

   Both conditions total > 100 and account_status = 'active' filter rows correctly in WHERE clause.

2. Step 2: Check if GROUP BY or HAVING needed

   Since no aggregation is used, GROUP BY or HAVING is not required here.

3. Final Answer:

   This query correctly filters customers with total > 100 and active accounts -> Option A

4. Quick Check:

   WHERE filters multiple conditions correctly = C [OK]

Hint: Use WHERE for multiple conditions without aggregation [OK]

Common Mistakes:

• Using HAVING without aggregation
• Adding unnecessary GROUP BY
• Thinking account_status can't be filtered in WHERE