HAVING clause in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of HAVING with GROUP BY and aggregate

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

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

MySQL

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

A Syntax error because HAVING cannot use alias

B Rows with regions where the average amount is more than 1000

C Rows with regions where the sum of amount is more than 1000

D All rows from sales table without filtering

💡 Hint

HAVING filters groups after aggregation, using the alias defined in SELECT.

The HAVING clause filters groups created by GROUP BY. Here, it filters regions whose total sales exceed 1000. The alias total_sales can be used in HAVING in MySQL.

📝 Syntax

intermediate

Identify the syntax error in HAVING clause

Which option shows a syntax error in using HAVING clause in MySQL?

A SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 5;

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

C SELECT department, COUNT(*) FROM employees GROUP BY department HAVING department = 'Sales';

D SELECT department, COUNT(*) FROM employees HAVING COUNT(*) > 5;

💡 Hint

WHERE cannot filter on aggregate functions.

WHERE filters rows before grouping and cannot use aggregate functions like COUNT(). HAVING filters groups after aggregation.

🧠 Conceptual

advanced

Difference between WHERE and HAVING clauses

Which statement correctly explains the difference between WHERE and HAVING clauses in SQL?

A WHERE filters rows before grouping; HAVING filters groups after aggregation.

B WHERE filters groups after aggregation; HAVING filters rows before grouping.

C WHERE and HAVING both filter rows before grouping but HAVING is slower.

D WHERE filters only numeric columns; HAVING filters only string columns.

💡 Hint

Think about when filtering happens in the query process.

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

❓ query_result

advanced

Output of HAVING with multiple conditions

Consider the table orders with columns customer_id, order_date, and total. What rows will this query return?

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING order_count >= 3 AND total_spent < 500;

MySQL

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING order_count >= 3 AND total_spent < 500;

A All customers regardless of orders or spending

B Customers with at least 3 orders and total spending less than 500

C Customers with exactly 3 orders or total spending less than 500

D Syntax error due to multiple conditions in HAVING

💡 Hint

HAVING can combine multiple conditions with AND/OR.

The HAVING clause filters groups where order_count is 3 or more AND total_spent is less than 500.

🔧 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 avg_price
FROM products
GROUP BY category
HAVING avg_price < 0;

MySQL

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING avg_price < 0;

A Because GROUP BY is missing a WHERE clause to filter rows

B Because HAVING cannot use aliases like avg_price

C Because AVG(price) returns NULL for all categories

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

💡 Hint

Think about the possible values of average price.

Prices are usually non-negative, so average price less than zero is impossible, resulting in no rows.