SQL HAVING Clause Practice Questions with Solutions

The SQL HAVING clause is used to filter grouped data after the GROUP BY clause has been applied. While the WHERE clause filters individual rows before grouping, the HAVING clause filters the results of each group. SQL HAVING Clause practice questions with solutions help to understand the concepts.

The HAVING clause is always used with aggregate functions such as:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

For example, if you want to display only those courses that have more than two students, you can write:

SELECT course,
       COUNT(*) AS total_students

FROM students

GROUP BY course

HAVING COUNT(*) > 2;

Similarly, if you want to display cities where the average marks are greater than 85, you can write:

SELECT city,
       AVG(marks) AS average_marks

FROM students

GROUP BY city

HAVING AVG(marks) > 85;

The HAVING clause is commonly used in:

• Business Reports
• Sales Dashboards
• Financial Reports
• Banking Systems
• Student Result Analysis
• HR Analytics
• Inventory Reports
• Business Intelligence

What is SQL HAVING?

The HAVING clause filters groups created by the GROUP BY clause.

Unlike the WHERE clause, HAVING works after aggregate functions have been calculated.

Basic Syntax

SELECT column_name,
       aggregate_function(column_name)

FROM table_name

GROUP BY column_name

HAVING condition;

Difference Between WHERE and HAVING

Sample Table Used Throughout This Chapter

students

1. SQL Query to Display Courses Having More Than 2 Students

Problem Statement

A training institute wants to display only those courses where more than two students are enrolled.

Write an SQL query to display these courses.

SQL Solution

SELECT course,
       COUNT(*) AS total_students

FROM students

GROUP BY course

HAVING COUNT(*) > 2;

Sample Output

Explanation

The GROUP BY clause creates separate groups for each course.

The COUNT() function counts students in every course.

The HAVING clause filters only those groups where the student count is greater than 2.

Concepts Covered

• GROUP BY
• HAVING
• COUNT()
• Aggregate Functions

2. SQL Query to Display Cities Having More Than 1 Student

Problem Statement

The administration wants to display only those cities where more than one student lives.

Write an SQL query to display these cities.

SQL Solution

SELECT city,
       COUNT(*) AS total_students

FROM students

GROUP BY city

HAVING COUNT(*) > 1;

Sample Output

Explanation

The query groups students by city.

The HAVING clause removes cities having only one student.

This type of report is useful for:

• Regional Analysis
• Branch Planning
• Admission Reports

Concepts Covered

• GROUP BY
• HAVING
• COUNT()

3. SQL Query to Display Courses Whose Average Marks Are Greater Than 90

Problem Statement

The academic coordinator wants to identify courses where the average marks are greater than 90.

Write an SQL query to display these courses.

SQL Solution

SELECT course,
       AVG(marks) AS average_marks

FROM students

GROUP BY course

HAVING AVG(marks) > 90;

Sample Output

Explanation

The AVG() function calculates the average marks for each course.

The HAVING clause filters only those courses whose average marks exceed 90.

This type of report is useful for:

• Course Performance Analysis
• Academic Reporting
• Faculty Evaluation

Concepts Covered

• GROUP BY
• HAVING
• AVG()

4. SQL Query to Display Cities Having Average Marks Greater Than 85

Problem Statement

The education department wants to identify cities where the average student marks are greater than 85.

Write an SQL query to display these cities along with their average marks.

Sample Table

SQL Solution

SELECT city,
       AVG(marks) AS average_marks

FROM students

GROUP BY city

HAVING AVG(marks) > 85;

Sample Output

Explanation

The query groups students according to their city.

The AVG() function calculates the average marks for each city.

The HAVING clause removes cities where the average marks are 85 or below.

This report is useful for:

• Regional Performance Analysis
• Education Department Reports
• School Performance Dashboards

Concepts Covered

• GROUP BY
• HAVING
• AVG()
• Aggregate Functions

5. SQL Query to Display Courses Having Total Marks Greater Than 180

Problem Statement

The institute wants to identify courses where the combined marks of all enrolled students exceed 180.

Write an SQL query to display these courses.

SQL Solution

SELECT course,
       SUM(marks) AS total_marks

FROM students

GROUP BY course

HAVING SUM(marks) > 180;

Sample Output

Explanation

The SUM() function calculates the total marks for each course.

The HAVING clause filters only those courses whose total marks are greater than 180.

This query is commonly used for:

• Department Performance Reports
• Academic Dashboards
• Business Intelligence Reporting

Concepts Covered

• GROUP BY
• HAVING
• SUM()
• Aggregate Functions

6. SQL Query to Display Cities Having More Than 2 Students

Problem Statement

A university wants to identify cities that have more than two enrolled students for planning regional workshops.

Write an SQL query to display these cities.

Sample Table

SQL Solution

SELECT city,
       COUNT(*) AS total_students

FROM students

GROUP BY city

HAVING COUNT(*) > 2;

Sample Output

Explanation

The records are grouped by city.

The COUNT() function calculates the total number of students in each city.

The HAVING clause filters only those cities where the student count is greater than 2.

Concepts Covered

• GROUP BY
• HAVING
• COUNT()

7. SQL Query to Display Courses Where Highest Marks Are Above 90

Problem Statement

The academic office wants to identify courses where the highest marks scored are greater than 90.

Write an SQL query to display these courses.

SQL Solution

SELECT course,
       MAX(marks) AS highest_marks

FROM students

GROUP BY course

HAVING MAX(marks) > 90;

Sample Output

Explanation

The MAX() function finds the highest marks in each course.

The HAVING clause displays only those courses whose highest marks exceed 90.

This report helps identify top-performing courses.

Concepts Covered

• GROUP BY
• HAVING
• MAX()

8. SQL Query to Display Cities Where Lowest Marks Are Above 85

Problem Statement

The school wants to identify cities where every student’s minimum marks are above 85.

Write an SQL query to display these cities.

SQL Solution

SELECT city,
       MIN(marks) AS lowest_marks

FROM students

GROUP BY city

HAVING MIN(marks) > 85;

Sample Output

Explanation

The query groups records by city.

The MIN() function returns the lowest marks in each city.

The HAVING clause keeps only cities where the minimum marks are greater than 85.

This type of report highlights consistently high-performing regions.

Concepts Covered

• GROUP BY
• HAVING
• MIN()

9. SQL Query to Display Courses Where Average Age Is Greater Than 21

Problem Statement

The institute wants to identify courses whose average student age is greater than 21 years.

Write an SQL query to display these courses.

SQL Solution

SELECT course,
       AVG(age) AS average_age

FROM students

GROUP BY course

HAVING AVG(age) > 21;

Sample Output

Explanation

The AVG() function calculates the average age for each course.

The HAVING clause filters courses whose average age is greater than 21.

This report is useful for demographic analysis and course planning.

Concepts Covered

• GROUP BY
• HAVING
• AVG()

10. SQL Query to Display Cities Where Total Marks Are Greater Than 200

Problem Statement

The education board wants to identify cities where the combined marks of all students are greater than 200.

Write an SQL query to display these cities.

SQL Solution

SELECT city,
       SUM(marks) AS total_marks

FROM students

GROUP BY city

HAVING SUM(marks) > 200;

Sample Output

Explanation

The SUM() function calculates the total marks for each city.

The HAVING clause filters only those cities where the total marks exceed 200.

This type of report is commonly used in:

• Regional Performance Reports
• Education Analytics
• Government Dashboards

Concepts Covered

• GROUP BY
• HAVING
• SUM()
• Aggregate Functions

11. SQL Query to Display Courses Having More Than One Student from Delhi

Problem Statement

The training institute wants to identify courses where more than one student belongs to Delhi.

Write an SQL query to display these courses.

Sample Table

SQL Solution

SELECT course,
       COUNT(*) AS total_students

FROM students

WHERE city = 'Delhi'

GROUP BY course

HAVING COUNT(*) > 1;

Sample Output

Explanation

The WHERE clause first filters only students from Delhi.

Then GROUP BY creates groups based on the course.

Finally, the HAVING clause displays only those courses where the number of Delhi students is greater than 1.

Concepts Covered

• WHERE
• GROUP BY
• HAVING
• COUNT()

12. SQL Query to Display Cities Offering More Than One Course

Problem Statement

The institute wants to identify cities where more than one course is available.

Write an SQL query to display these cities.

SQL Solution

SELECT city,
       COUNT(DISTINCT course) AS total_courses

FROM students

GROUP BY city

HAVING COUNT(DISTINCT course) > 1;

Sample Output

Explanation

The COUNT(DISTINCT course) function counts unique courses available in each city.

The HAVING clause filters cities having more than one distinct course.

This report is useful for branch expansion and regional planning.

Concepts Covered

• GROUP BY
• HAVING
• DISTINCT
• COUNT()

13. SQL Query to Display Courses Whose Average Marks Are Between 85 and 95

Problem Statement

The academic office wants to identify courses whose average marks fall between 85 and 95.

Write an SQL query to display these courses.

SQL Solution

SELECT course,
       AVG(marks) AS average_marks

FROM students

GROUP BY course

HAVING AVG(marks) BETWEEN 85 AND 95;

Sample Output

Explanation

The query groups students by course.

The AVG() function calculates the average marks.

The HAVING clause filters only those courses whose average marks lie between 85 and 95.

Concepts Covered

• GROUP BY
• HAVING
• AVG()
• BETWEEN

14. SQL Query to Display Courses Having Fewer Than 3 Students

Problem Statement

The institute wants to identify small batches where fewer than three students are enrolled.

Write an SQL query to display these courses.

SQL Solution

SELECT course,
       COUNT(*) AS total_students

FROM students

GROUP BY course

HAVING COUNT(*) < 3;

Sample Output

Explanation

The query groups students by course.

The COUNT() function calculates the number of students in each course.

The HAVING clause filters courses having fewer than 3 students.

This report is useful for deciding whether to merge or continue small batches.

Concepts Covered

• GROUP BY
• HAVING
• COUNT()

15. Real-World Example: Display Product Categories Having Sales Greater Than ₹30,000

Problem Statement

An e-commerce company wants to identify product categories whose total sales exceed ₹30,000.

sales

Write an SQL query to display these categories.

SQL Solution

SELECT category,
       SUM(amount) AS total_sales

FROM sales

GROUP BY category

HAVING SUM(amount) > 30000;

Sample Output

Explanation

The query groups records according to product category.

The SUM() function calculates the total sales for each category.

The HAVING clause displays only those categories whose total sales exceed ₹30,000.

This type of report is widely used in:

• Sales Dashboards
• Revenue Analysis
• Retail Analytics
• Financial Reporting
• Business Intelligence

Concepts Covered

• GROUP BY
• HAVING
• SUM()
• Sales Analytics

Chapter Summary

In this chapter, you learned how to use the SQL HAVING clause to filter grouped results after the GROUP BY clause has been applied.

Unlike the WHERE clause, which filters individual rows before grouping, the HAVING clause filters entire groups based on aggregate calculations such as COUNT(), SUM(), AVG(), MIN(), and MAX().

Throughout this chapter, you practiced:

• Using HAVING with COUNT()
• Using HAVING with SUM()
• Using HAVING with AVG()
• Using HAVING with MIN()
• Using HAVING with MAX()
• Filtering grouped data based on aggregate values
• Combining WHERE, GROUP BY, and HAVING
• Using DISTINCT with HAVING
• Building real-world sales and reporting queries

The HAVING clause is an essential SQL feature for creating business reports, dashboards, and analytical summaries.

Key Takeaways

• HAVING filters grouped data after aggregation.
• HAVING is generally used with GROUP BY.
• WHERE filters rows before grouping, while HAVING filters groups after grouping.
• COUNT() with HAVING is used to filter groups by record count.
• SUM() with HAVING helps identify groups with large totals.
• AVG() with HAVING is useful for comparing average values.
• MIN() and MAX() can also be filtered using HAVING.
• WHERE and HAVING can be used together in the same query.
• HAVING is frequently used in reporting and analytics.
• Understanding HAVING is important for SQL interviews and real-world database projects.

Frequently Asked Questions (FAQs)

1. What is the SQL HAVING clause?

The HAVING clause filters grouped records after the GROUP BY clause.

Example:

SELECT course,
       COUNT(*) AS total_students

FROM students

GROUP BY course

HAVING COUNT(*) > 2;

2. What is the difference between WHERE and HAVING?

3. Can HAVING be used without GROUP BY?

Yes, although it is uncommon.

Example:

SELECT COUNT(*) AS total_students

FROM students

HAVING COUNT(*) > 5;

This returns the result only if the condition is true.

4. Which aggregate functions work with HAVING?

The most commonly used aggregate functions are:

• COUNT()
• SUM()
• AVG()
• MIN()
• MAX()

5. Can WHERE and HAVING be used together?

Yes.

Example:

SELECT course,
       AVG(marks) AS average_marks

FROM students

WHERE city = 'Delhi'

GROUP BY course

HAVING AVG(marks) > 80;

Here:

• WHERE filters students from Delhi.
• GROUP BY groups them by course.
• HAVING filters courses whose average marks are greater than 80.

6. Can HAVING use multiple conditions?

Yes.

Example:

SELECT course,
       COUNT(*) AS total_students,
       AVG(marks) AS average_marks

FROM students

GROUP BY course

HAVING COUNT(*) >= 2
AND AVG(marks) > 85;

7. Is HAVING slower than WHERE?

Generally, yes.

WHERE filters rows before grouping, reducing the amount of data processed.

HAVING filters results after grouping, which usually requires more processing.

For better performance:

• Use WHERE whenever possible.
• Use HAVING only for aggregate conditions.

8. Where is the HAVING clause used in real-world applications?

The HAVING clause is widely used in:

• Sales Reports
• Student Performance Analysis
• Banking Reports
• Employee Performance Dashboards
• HR Analytics
• Inventory Management
• Business Intelligence
• Financial Reporting
• CRM Systems
• Data Analytics Projects

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar