SQL Aggregations & Grouping: Core Concepts

Practice writing SQL queries that summarize and group data using aggregate functions. This quiz covers COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING in real-world scenarios.

Taken by 26 developers

Answered 0/10

Pass 70%

~8 min

00:00

Note: Sign in to save your quiz progress and results.

Which aggregate function returns the total number of rows that match a condition?

About this Quiz

This interactive assessment is designed to test your understanding of SQL Aggregations & Grouping: Core Concepts. Whether you are a beginner looking to grasp the basics or an experienced professional aiming to refine your skills, quizzes are an excellent way to validate your knowledge and identify areas for improvement.

Instructions

Read each question carefully before answering.
Some questions may have multiple correct answers.
There is no time limit, so take your time to think.
You will receive immediate feedback after submission.

Learning Outcomes

Self-assess your current skill level.
Reinforce key concepts through active recall.
Identify knowledge gaps to focus your study.
Gain confidence in your technical expertise.
Reinforce memory and recall.

Topic Overview

Practice writing SQL queries that summarize and group data using aggregate functions. This quiz covers COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING in real-world scenarios.

Questions in this quiz:

1. Which aggregate function returns the total number of rows that match a condition?

SUM()
TOTAL()
COUNT()
AVG()

Hint: <code>COUNT()</code> returns the number of rows matching the query. <code>SUM()</code> adds up numeric values, <code>AVG()</code> returns the average, and <code>TOTAL()</code> is not a standard SQL function.

2. What does the following query return?

The total number of employees across all departments
One row per department showing how many employees are in each
The department with the most employees
A list of all employees grouped alphabetically by department

Hint: <code>GROUP BY department</code> collapses all rows with the same department into one group. <code>COUNT(*)</code> then counts the rows in each group, producing one summary row per department.

3. <code>HAVING</code> is used to filter rows before grouping, while <code>WHERE</code> filters rows after grouping.

True
False

Hint: This is the opposite of the truth. <code>WHERE</code> filters individual rows before grouping, and <code>HAVING</code> filters groups after the <code>GROUP BY</code> has been applied.

4. Which query correctly finds departments that have more than 5 employees?

Hint: Aggregate functions like <code>COUNT()</code> cannot be used in a <code>WHERE</code> clause. You must use <code>HAVING</code> to filter on aggregated values after the <code>GROUP BY</code>.

5. What will <code>SELECT AVG(salary) FROM employees;</code> return if the table has salaries of 40000, 60000, and 80000?

40000
180000
60000
80000

Hint: <code>AVG()</code> sums all values and divides by the count: (40000 + 60000 + 80000) ÷ 3 = 60000.

6. Which of the following aggregate functions would you use to find the highest price in a <code>products</code> table?

TOP(price)
MAX(price)
HIGHEST(price)
UPPER(price)

Hint: <code>MAX(column)</code> returns the largest value in a column. <code>TOP</code>, <code>HIGHEST</code>, and <code>UPPER</code> are not aggregate functions in standard SQL.

7. You can use <code>COUNT(*)</code> and <code>COUNT(column_name)</code> interchangeably — they always return the same result.

True
False

Hint: <code>COUNT(*)</code> counts all rows including those with <code>NULL</code> values. <code>COUNT(column_name)</code> counts only rows where that specific column is not NULL. They differ when the column contains <code>NULL</code>s.

8. What does this query produce?

All categories with their total quantities, sorted from lowest to highest
Only categories where the total quantity sold is at least 100, sorted from highest to lowest
The single category with the most items sold
All categories where any single order has a quantity of 100 or more

Hint: <code>GROUP BY category</code> groups rows per category. <code>HAVING SUM(quantity) >= 100</code> keeps only groups with a total of 100+. <code>ORDER BY total_sold DESC</code> sorts the remaining groups from highest to lowest.

9. Which clause must always be present when using an aggregate function alongside a non-aggregated column in the SELECT list?

ORDER BY
WHERE
GROUP BY
HAVING

Hint: When you mix aggregate functions (e.g., <code>COUNT(*)</code>) with regular columns (e.g., <code>department</code>), SQL requires a <code>GROUP BY</code> clause listing all non-aggregated columns. Without it, the database does not know how to group the rows.

10. What is the result of <code>SELECT MIN(price), MAX(price) FROM products;</code> on a table with prices 5, 12, 7, 3, and 20?

MIN = 5, MAX = 20
MIN = 3, MAX = 20
MIN = 3, MAX = 12
MIN = 5, MAX = 12

Hint: <code>MIN(price)</code> returns the smallest value (3) and <code>MAX(price)</code> returns the largest (20) across all rows in the table.