HAVING clause for filtering groups in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to filter groups having more than 3 orders.

SQL

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) [1] 3;

Drag options to blanks, or click blank then click option'

A>

B<

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING for filtering groups

Using < instead of > in the HAVING clause

The HAVING clause filters groups where the count of orders is greater than 3.

2fill in blank

medium

Complete the code to find products with total sales equal to or more than 100.

SQL

SELECT product_id, SUM(quantity) FROM sales GROUP BY product_id HAVING SUM(quantity) [1] 100;

Drag options to blanks, or click blank then click option'

A<

B<=

C>=

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING

Using < instead of >= in HAVING

The HAVING clause filters products where total quantity sold is at least 100.

3fill in blank

hard

Fix the error in the HAVING clause to filter groups with average price less than 50.

SQL

SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) [1] 50;

Drag options to blanks, or click blank then click option'

A>

B<

C=

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING

Using > instead of < in HAVING

The HAVING clause should use < to select groups with average price less than 50.

4fill in blank

hard

Fill both blanks to select customers with more than 5 orders and total amount over 1000.

SQL

SELECT customer_id, COUNT(*), SUM(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) [1] 5 AND SUM(amount) [2] 1000;

Drag options to blanks, or click blank then click option'

A>

B<

C>=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > in conditions

Using WHERE instead of HAVING

Both conditions use > to filter customers with more than 5 orders and total amount over 1000.

5fill in blank

hard

Fill all three blanks to select products with average rating above 4, total reviews at least 50, and total sales over 200.

SQL

SELECT product_id, AVG(rating), COUNT(review_id), SUM(sales) FROM product_reviews GROUP BY product_id HAVING AVG(rating) [1] 4 AND COUNT(review_id) [2] 50 AND SUM(sales) [3] 200;

Drag options to blanks, or click blank then click option'

A>

B>=

C<

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using < or <= instead of > or ≥

Mixing WHERE and HAVING clauses

The HAVING clause filters products with average rating > 4, reviews ≥ 50, and sales ≥ 200.

Practice

1.

What is the main purpose of the HAVING clause in SQL?

easy

A. To filter individual rows before grouping

B. To sort the results of a query

C. To filter groups created by GROUP BY based on aggregate conditions

D. To join two tables together

Solution

1. Step 1: Understand the role of GROUP BY

   The GROUP BY clause groups rows based on column values.

2. Step 2: Identify the purpose of HAVING

   HAVING filters these groups using aggregate functions like SUM or COUNT.

3. Final Answer:

   To filter groups created by GROUP BY based on aggregate conditions -> Option C

4. Quick Check:

   HAVING filters groups, not rows [OK]

Hint: Remember: WHERE filters rows, HAVING filters groups [OK]

Common Mistakes:

• Confusing HAVING with WHERE clause
• Using HAVING without GROUP BY
• Thinking HAVING sorts data

2.

Which of the following is the correct syntax to filter groups with HAVING?

SELECT department, COUNT(*) FROM employees GROUP BY department _______ COUNT(*) > 5;

easy

A. GROUP BY

B. WHERE

C. FILTER

D. HAVING

Solution

1. Step 1: Identify filtering clause after grouping

   After GROUP BY, filtering groups requires HAVING, not WHERE.

2. Step 2: Confirm correct clause usage

   HAVING COUNT(*) > 5 filters groups with more than 5 employees.

3. Final Answer:

   HAVING -> Option D

4. Quick Check:

   Use HAVING after GROUP BY [OK]

Hint: Use HAVING to filter groups, not WHERE [OK]

Common Mistakes:

• Using WHERE instead of HAVING after GROUP BY
• Placing HAVING before GROUP BY
• Using FILTER keyword which is invalid here

3.

Given the table sales with columns region and amount, what will this query return?

SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000;

medium

A. All regions with total sales greater than 1000

B. All regions with total sales less than or equal to 1000

C. All sales records with amount greater than 1000

D. Syntax error due to HAVING clause

Solution

1. Step 1: Group sales by region and sum amounts

   The query groups rows by region and calculates total amount per region.

2. Step 2: Filter groups with total sales > 1000

   The HAVING clause keeps only regions where the sum is greater than 1000.

3. Final Answer:

   All regions with total sales greater than 1000 -> Option A

4. Quick Check:

   HAVING filters groups by aggregate sum [OK]

Hint: HAVING filters groups by aggregate results [OK]

Common Mistakes:

• Thinking HAVING filters individual rows
• Confusing SUM(amount) with amount column
• Assuming syntax error with HAVING

4.

Identify the error in this query:

SELECT category, COUNT(*) FROM products HAVING COUNT(*) > 10 GROUP BY category;

medium

A. Missing WHERE clause before HAVING

B. HAVING clause used before GROUP BY

C. COUNT(*) cannot be used in HAVING

D. GROUP BY should be replaced with ORDER BY

Solution

1. Step 1: Check order of clauses in SQL

   The correct order is GROUP BY first, then HAVING.

2. Step 2: Identify incorrect clause order

   The query places HAVING before GROUP BY, causing syntax error.

3. Final Answer:

   HAVING clause used before GROUP BY -> Option B

4. Quick Check:

   GROUP BY before HAVING [OK]

Hint: GROUP BY must come before HAVING [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Thinking HAVING needs WHERE before it
• Replacing GROUP BY with ORDER BY incorrectly

5.

You have a students table with columns class and score. You want to find classes where the average score is at least 75 and the number of students is more than 10. Which query achieves this?

hard

A. SELECT class, AVG(score), COUNT(*) FROM students GROUP BY class HAVING AVG(score) >= 75 AND COUNT(*) > 10;

B. SELECT class, AVG(score), COUNT(*) FROM students HAVING AVG(score) >= 75 AND COUNT(*) > 10 GROUP BY class;

C. SELECT class, AVG(score), COUNT(*) FROM students WHERE AVG(score) >= 75 AND COUNT(*) > 10 GROUP BY class;

D. SELECT class, AVG(score), COUNT(*) FROM students GROUP BY class WHERE AVG(score) >= 75 AND COUNT(*) > 10;

Solution

1. Step 1: Group students by class

   Use GROUP BY class to group rows by class.

2. Step 2: Filter groups with HAVING using aggregate conditions

   Use HAVING AVG(score) >= 75 AND COUNT(*) > 10 to keep classes meeting both conditions.

3. Final Answer:

   SELECT class, AVG(score), COUNT(*) FROM students GROUP BY class HAVING AVG(score) >= 75 AND COUNT(*) > 10; -> Option A

4. Quick Check:

   HAVING filters groups by multiple aggregates [OK]

Hint: Use HAVING for multiple aggregate filters after GROUP BY [OK]

Common Mistakes:

• Placing HAVING before GROUP BY
• Using WHERE with aggregate functions
• Putting WHERE after GROUP BY