HAVING clause for filtering groups in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of HAVING with COUNT filter

Consider a table Orders with columns CustomerID and OrderID. What is the output of this query?

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 2;

Assume the table has these rows:

CustomerID | OrderID
-----------|--------
1          | 101
1          | 102
2          | 103
2          | 104
2          | 105
3          | 106

SQL

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 2;

A [{"CustomerID": 2, "OrderCount": 3}]

B [{"CustomerID": 3, "OrderCount": 1}]

C [{"CustomerID": 1, "OrderCount": 2}, {"CustomerID": 2, "OrderCount": 3}]

D []

💡 Hint

Remember, HAVING filters groups after aggregation.

The query groups orders by CustomerID and counts orders per customer. HAVING filters groups with count > 2. Only CustomerID 2 has 3 orders, so only that row appears.

🧠 Conceptual

intermediate

Purpose of HAVING clause

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

A To filter groups after aggregation

B To filter rows before grouping

C To sort the result set

D To join two tables

💡 Hint

Think about when filtering happens in relation to grouping.

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

📝 Syntax

advanced

Identify the syntax error in HAVING usage

Which option contains a syntax error in using the HAVING clause?

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING Salary > 50000;

A HAVING SUM(Salary) > 100000;

B HAVING AVG(Salary) > 50000;

C HAVING COUNT(*) > 5;

D HAVING Salary > 50000;

💡 Hint

Remember what HAVING can filter on after grouping.

HAVING cannot filter on a column directly unless it is part of GROUP BY or an aggregate function. 'Salary' alone is invalid here.

❓ optimization

advanced

Optimizing HAVING with WHERE

Given a large Sales table with columns Region, Amount, and SaleDate, which query is more efficient to find regions with total sales over 10000 in 2023?

A SELECT Region, SUM(Amount) FROM Sales HAVING SUM(Amount) > 10000;

B SELECT Region, SUM(Amount) FROM Sales GROUP BY Region HAVING SUM(Amount) > 10000 AND YEAR(SaleDate) = 2023;

C SELECT Region, SUM(Amount) FROM Sales WHERE YEAR(SaleDate) = 2023 GROUP BY Region HAVING SUM(Amount) > 10000;

D SELECT Region, SUM(Amount) FROM Sales WHERE SUM(Amount) > 10000 GROUP BY Region;

💡 Hint

Filter rows before grouping when possible.

Filtering rows with WHERE before grouping reduces data processed in aggregation, improving efficiency.

🔧 Debug

expert

Why does this HAVING query return no rows?

Given the table Products with columns Category and Price, why does this query return no rows?

SELECT Category, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Category
HAVING AVG(Price) < 0;

A Because AVG(Price) returns NULL for all groups.

B Because average price cannot be less than zero, so no groups match the condition.

C Because the HAVING clause syntax is incorrect.

D Because the GROUP BY clause is missing a column.

💡 Hint

Think about the possible values of average price.

Average price cannot be negative if prices are non-negative, so the HAVING condition filters out all groups.