SQL Aggregate Functions Practice Questions with Solutions

SQL Aggregate Functions are built-in functions used to perform calculations on multiple rows and return a single summarized value. Instead of displaying every record individually, aggregate functions help generate meaningful insights such as totals, averages, highest values, and record counts. SQL Aggregate Functions practice questions with solutions help to understand the concepts.

Aggregate functions are widely used in:

• Sales Reports
• Student Result Analysis
• Banking Systems
• Payroll Reports
• Business Dashboards
• Inventory Management
• Hospital Databases
• Data Analytics

For example, if you want to calculate the average marks of all students:

SELECT AVG(marks)

FROM students;

If you want to count the total number of students:

SELECT COUNT(*)

FROM students;

Aggregate functions make SQL much more powerful by helping summarize large datasets quickly.

What are SQL Aggregate Functions?

Aggregate functions perform calculations on a group of rows and return one result.

The five most commonly used aggregate functions are:

Why Use Aggregate Functions?

Aggregate functions help you:

• Count records
• Calculate totals
• Find averages
• Identify highest values
• Identify lowest values
• Generate reports
• Build dashboards
• Analyze business data

Sample Table Used Throughout This Chapter

students

1. SQL Query to Count Total Students

Problem Statement

Write an SQL query to count the total number of students in the students table.

SQL Solution

SELECT COUNT(*) AS total_students

FROM students;

Sample Output

Explanation

The COUNT(*) function counts every row in the table, regardless of column values.

It is commonly used to determine the total number of records in a database table.

Concepts Covered

• COUNT()
• Aggregate Functions
• Total Records

2. SQL Query to Count Students from Delhi

Problem Statement

Write an SQL query to count the number of students whose city is Delhi.

SQL Solution

SELECT COUNT(*) AS delhi_students

FROM students

WHERE city = 'Delhi';

Sample Output

Explanation

The WHERE clause filters students from Delhi.

The COUNT(*) function then counts only the filtered rows.

Concepts Covered

• COUNT()
• WHERE
• Filtering Records

3. SQL Query to Calculate Total Marks of All Students

Problem Statement

Write an SQL query to calculate the total marks obtained by all students.

SQL Solution

SELECT SUM(marks) AS total_marks

FROM students;

Sample Output

Explanation

The SUM() function adds together all values in the marks column.

Calculation:

88 + 91 + 95 + 84 + 90 + 82 + 89 = 619

This function is commonly used for:

• Sales Totals
• Salary Totals
• Revenue Reports
• Financial Analysis

Concepts Covered

• SUM()
• Aggregate Functions
• Total Calculation

4. SQL Query to Calculate Total Marks of Python Students

Problem Statement

A Python instructor wants to know the combined marks of all students enrolled in the Python course.

Write an SQL query to calculate the total marks of Python students.

Sample Table

SQL Solution

SELECT SUM(marks) AS python_total_marks

FROM students

WHERE course = 'Python';

Sample Output

Explanation

The query first filters students whose course is Python.

The SUM() function then adds the marks of only those students.

Calculation:

88 + 84 + 82 = 254

This type of query is useful for:

• Course-wise Reports
• Department Analysis
• Performance Tracking

Concepts Covered

• SUM()
• WHERE
• Aggregate Functions

5. SQL Query to Calculate the Average Marks of All Students

Problem Statement

A school principal wants to know the average marks scored by all students.

Write an SQL query to calculate the average marks.

SQL Solution

SELECT AVG(marks) AS average_marks

FROM students;

Sample Output

Explanation

The AVG() function calculates the average value of the marks column.

Calculation:

(88 + 91 + 95 + 84 + 90 + 82 + 89)

÷ 7

= 88.43

The AVG() function is commonly used in:

• Student Performance Reports
• Salary Analysis
• Sales Analytics
• Financial Dashboards

Concepts Covered

• AVG()
• Aggregate Functions
• Average Calculation

6. SQL Query to Calculate the Average Marks of Java Students

Problem Statement

A Java faculty member wants to know the average marks scored by students enrolled in the Java course.

Write an SQL query to calculate the average marks of Java students.

Sample Table

SQL Solution

SELECT AVG(marks) AS java_average_marks

FROM students

WHERE course = 'Java';

Sample Output

Explanation

The WHERE clause filters only Java students.

The AVG() function calculates the average of their marks.

Calculation:

(91 + 90)

÷ 2

= 90.50

This type of query is useful for comparing course performance.

Concepts Covered

• AVG()
• WHERE
• Aggregate Functions

7. SQL Query to Find the Highest Marks

Problem Statement

A school wants to identify the highest marks scored in the examination.

Write an SQL query to display the highest marks.

SQL Solution

SELECT MAX(marks) AS highest_marks

FROM students;

Sample Output

Explanation

The MAX() function returns the largest value from the specified column.

This is commonly used in:

• Merit Lists
• Sales Reports
• Salary Analysis
• Performance Dashboards

Concepts Covered

• MAX()
• Aggregate Functions

8. SQL Query to Find the Lowest Marks

Problem Statement

A teacher wants to identify the lowest marks obtained by any student.

Write an SQL query to display the lowest marks.

SQL Solution

SELECT MIN(marks) AS lowest_marks

FROM students;

Sample Output

Explanation

The MIN() function returns the smallest value from the specified column.

It helps identify:

• Lowest Marks
• Minimum Salary
• Cheapest Product
• Lowest Price

Concepts Covered

• MIN()
• Aggregate Functions

9. SQL Query to Count Students Scoring Above 90

Problem Statement

The examination committee wants to know how many students scored more than 90 marks.

Write an SQL query to count these students.

SQL Solution

SELECT COUNT(*) AS students_above_90

FROM students

WHERE marks > 90;

Sample Output

Explanation

The WHERE clause filters students whose marks are greater than 90.

The COUNT() function then counts the matching records.

This query is useful for scholarship and merit list analysis.

Concepts Covered

• COUNT()
• WHERE
• Aggregate Functions

10. SQL Query to Calculate Total Marks of Students from Delhi

Problem Statement

A regional coordinator wants to calculate the combined marks of all students from Delhi.

Write an SQL query to calculate the total marks.

SQL Solution

SELECT SUM(marks) AS delhi_total_marks

FROM students

WHERE city = 'Delhi';

Sample Output

Explanation

The query filters students belonging to Delhi.

The SUM() function adds together their marks.

Calculation:

88 + 95 + 82

= 265

This type of report is useful for city-wise performance analysis.

Concepts Covered

• SUM()
• WHERE
• Aggregate Functions

11. SQL Query to Count Students in the Python Course

Problem Statement

The Python department wants to know how many students are enrolled in the Python course.

Write an SQL query to count the total number of Python students.

Sample Table

SQL Solution

SELECT COUNT(*) AS python_students

FROM students

WHERE course = 'Python';

Sample Output

Explanation

The WHERE clause selects only Python students.

The COUNT() function then counts those matching records.

This query is commonly used for:

• Batch Strength
• Admission Reports
• Course Enrollment Analysis

Concepts Covered

• COUNT()
• WHERE
• Aggregate Functions

12. SQL Query to Find the Highest Marks in the SQL Course

Problem Statement

The SQL instructor wants to know the highest marks obtained by a student in the SQL course.

Write an SQL query to display the highest marks.

SQL Solution

SELECT MAX(marks) AS highest_sql_marks

FROM students

WHERE course = 'SQL';

Sample Output

Explanation

The query filters students enrolled in the SQL course.

The MAX() function then returns the highest marks among those students.

Concepts Covered

• MAX()
• WHERE
• Aggregate Functions

13. SQL Query to Find the Lowest Marks in the Java Course

Problem Statement

The Java faculty wants to identify the lowest marks scored in the Java course.

Write an SQL query to display the lowest marks.

SQL Solution

SELECT MIN(marks) AS lowest_java_marks

FROM students

WHERE course = 'Java';

Sample Output

Explanation

The query filters Java students.

The MIN() function then returns the smallest marks value from the filtered records.

This type of report helps identify students who may need additional support.

Concepts Covered

• MIN()
• WHERE
• Aggregate Functions

14. SQL Query to Calculate the Average Marks of Students from Delhi

Problem Statement

The school principal wants to calculate the average marks scored by students from Delhi.

Write an SQL query to calculate the average marks.

SQL Solution

SELECT AVG(marks) AS delhi_average_marks

FROM students

WHERE city = 'Delhi';

Sample Output

Explanation

The query filters students whose city is Delhi.

The AVG() function calculates the average marks of those students.

Calculation:

(88 + 95 + 82)

÷ 3

= 88.33

This report is useful for regional performance analysis.

Concepts Covered

• AVG()
• WHERE
• Aggregate Functions

15. Real-World Example: Monthly Sales Report

Problem Statement

An online store wants to calculate the total monthly sales amount from all completed orders.

Assume the following table:

orders

Write an SQL query to calculate the total sales.

SQL Solution

SELECT SUM(amount) AS total_sales

FROM orders;

Sample Output

Explanation

The SUM() function adds all values from the amount column.

Calculation:

2500 + 1800 + 3200 + 1500 + 4000

= 13000

This query is widely used in:

• Sales Dashboards
• Financial Reports
• E-commerce Analytics
• Business Intelligence Systems

Concepts Covered

• SUM()
• Aggregate Functions
• Business Reporting

Chapter Summary

In this chapter, you learned how to use SQL Aggregate Functions to perform calculations on groups of records and return a single summarized result.

Unlike normal SQL queries that display every row, aggregate functions help you generate reports, dashboards, and business insights by calculating totals, averages, minimum values, maximum values, and record counts.

Throughout this chapter, you practiced using:

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

You also learned how to combine aggregate functions with the WHERE clause to calculate results for specific records, such as students from a particular city or course.

Aggregate functions are widely used in real-world applications like:

• Student Management Systems
• Sales Reports
• Payroll Systems
• Banking Applications
• Inventory Management
• HR Dashboards
• Business Intelligence Reports
• Data Analytics

Mastering aggregate functions is an important step toward writing advanced SQL queries and building analytical reports.

Key Takeaways

• Aggregate functions return one summarized value from multiple rows.
• COUNT() counts the number of records.
• SUM() calculates the total of numeric values.
• AVG() calculates the average value.
• MIN() returns the smallest value.
• MAX() returns the largest value.
• Aggregate functions are often combined with the WHERE clause.
• They help generate reports and business summaries.
• Aggregate functions work on numeric data except COUNT(), which counts rows or non-NULL values.
• These functions are among the most frequently used SQL features in real-world projects.

Frequently Asked Questions (FAQs)

1. What are SQL Aggregate Functions?

Aggregate functions perform calculations on multiple rows and return a single value.

Common aggregate functions include:

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

2. What does COUNT() do?

COUNT() returns the number of rows.

Example:

SELECT COUNT(*)

FROM students;

3. What does SUM() do?

SUM() calculates the total of a numeric column.

Example:

SELECT SUM(marks)

FROM students;

4. What does AVG() do?

AVG() calculates the average value of a numeric column.

Example:

SELECT AVG(marks)

FROM students;

5. What is the difference between MIN() and MAX()?

• MIN() returns the smallest value.
• MAX() returns the largest value.

Example:

SELECT MIN(marks),
       MAX(marks)

FROM students;

6. Can Aggregate Functions be used with WHERE?

Yes.

Example:

SELECT AVG(marks)

FROM students

WHERE city = 'Delhi';

This calculates the average marks of students from Delhi.

7. Can Aggregate Functions be used on text columns?

Generally:

• COUNT() works with text columns.
• SUM(), AVG(), MIN(), and MAX() are mainly used with numeric data.

However, MIN() and MAX() can also return the alphabetically smallest and largest text values in some database systems.

8. Where are Aggregate Functions used in real-world applications?

Aggregate functions are commonly used in:

• Sales Reports
• Employee Salary Reports
• Student Performance Analysis
• Banking Systems
• Inventory Reports
• E-commerce Dashboards
• CRM Applications
• Business Intelligence
• Financial Reporting
• Data Analytics Projects

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

Rishabh Kumar