Why aggregation is needed in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the total sales from the sales table.

SQL

SELECT [1](amount) FROM sales;

Drag options to blanks, or click blank then click option'

AMIN

BCOUNT

CMAX

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT counts rows, not sums values.

Using MAX or MIN returns only the highest or lowest value.

The SUM function adds all the values in the amount column to give the total sales.

2fill in blank

medium

Complete the code to find how many orders are in the orders table.

SQL

SELECT [1](*) FROM orders;

Drag options to blanks, or click blank then click option'

ASUM

BAVG

CCOUNT

DMAX

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM tries to add values but * is not a column.

Using AVG or MAX does not count rows.

The COUNT function counts the number of rows, which tells how many orders there are.

3fill in blank

hard

Fix the error in the code to find the average price from the products table.

SQL

SELECT [1](price) FROM products;

Drag options to blanks, or click blank then click option'

AAVG

BCOUNT

CSUM

DMIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM adds all prices instead of averaging.

Using COUNT counts rows, not values.

Using MIN finds the smallest price.

The AVG function calculates the average value of the price column.

4fill in blank

hard

Fill both blanks to find the highest and lowest scores from the scores table.

SQL

SELECT [1](score), [2](score) FROM scores;

Drag options to blanks, or click blank then click option'

AMAX

BMIN

CSUM

DCOUNT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM or COUNT does not find highest or lowest values.

Swapping MAX and MIN changes the meaning.

MAX finds the highest score, and MIN finds the lowest score.

5fill in blank

hard

Fill all three blanks to create a query that shows each department, the number of employees, and the average salary from the employees table.

SQL

SELECT [1], [2](employee_id), [3](salary) FROM employees GROUP BY [1];

Drag options to blanks, or click blank then click option'

Adepartment

BCOUNT

CAVG

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of AVG for salary.

Not grouping by department.

Counting salary instead of employee_id.

This query groups employees by department, counts employees with COUNT, and calculates average salary with AVG.

Practice

1. Why do we use aggregation functions like SUM() or COUNT() in SQL?

easy

A. To summarize multiple rows into a single value

B. To delete rows from a table

C. To change the data type of a column

D. To create a new table

Solution

1. Step 1: Understand aggregation functions

   Aggregation functions like SUM and COUNT combine many rows into one summary value.

2. Step 2: Identify the purpose of aggregation

   They help get totals, counts, or averages instead of listing every row.

3. Final Answer:

   To summarize multiple rows into a single value -> Option A

4. Quick Check:

   Aggregation = summarize rows [OK]

Hint: Aggregation combines rows into one summary value [OK]

Common Mistakes:

• Thinking aggregation deletes data
• Confusing aggregation with data type changes
• Assuming aggregation creates new tables

2. Which of the following is the correct syntax to get the total sales from a table named Orders with a column Amount?

easy

A. SELECT COUNT(Amount) FROM Orders;

B. SELECT TOTAL(Amount) FROM Orders;

C. SELECT SUM(Amount) FROM Orders;

D. SELECT ADD(Amount) FROM Orders;

Solution

1. Step 1: Identify the correct aggregation function for total

   The function to add values is SUM(), so SUM(Amount) is correct.

2. Step 2: Check syntax correctness

   SUM(Amount) with SELECT and FROM table is valid SQL syntax.

3. Final Answer:

   SELECT SUM(Amount) FROM Orders; -> Option C

4. Quick Check:

   SUM() sums values [OK]

Hint: Use SUM() to add values in SQL [OK]

Common Mistakes:

• Using TOTAL() which is not standard SQL
• Using COUNT() instead of SUM() for totals
• Using ADD() which is not a SQL function

3. Given the table Sales with columns Region and Amount, what will this query return?

SELECT Region, COUNT(*) FROM Sales GROUP BY Region;

medium

A. The number of sales records per region

B. The total sales amount per region

C. The average sales amount per region

D. All sales records without grouping

Solution

1. Step 1: Understand COUNT(*) with GROUP BY

   COUNT(*) counts rows in each group defined by Region.

2. Step 2: Interpret the query result

   The query returns how many sales records exist for each Region.

3. Final Answer:

   The number of sales records per region -> Option A

4. Quick Check:

   COUNT(*) with GROUP BY = count rows per group [OK]

Hint: COUNT(*) counts rows per group [OK]

Common Mistakes:

• Thinking COUNT(*) sums amounts
• Confusing COUNT(*) with AVG()
• Ignoring GROUP BY effect

4. What is wrong with this SQL query if we want to find the average salary per department?

SELECT Department, AVG(Salary) FROM Employees;

medium

A. AVG() cannot be used on Salary

B. Missing GROUP BY clause for Department

C. SELECT must include only one column

D. Salary column name is incorrect

Solution

1. Step 1: Check aggregation with multiple columns

   When selecting Department and AVG(Salary), Department must be grouped.

2. Step 2: Identify missing GROUP BY

   The query lacks GROUP BY Department, causing error or wrong results.

3. Final Answer:

   Missing GROUP BY clause for Department -> Option B

4. Quick Check:

   Aggregation with columns needs GROUP BY [OK]

Hint: Use GROUP BY when mixing columns with aggregation [OK]

Common Mistakes:

• Forgetting GROUP BY with aggregation
• Thinking AVG() can't be used on numbers
• Assuming SELECT can have unrelated columns

5. You want to find the department with the highest total sales from the Sales table with columns Department and Amount. Which query correctly achieves this?

hard

A. SELECT Department, SUM(Amount) FROM Sales;

B. SELECT Department, MAX(Amount) FROM Sales;

C. SELECT Department FROM Sales WHERE Amount = MAX(Amount);

D. SELECT Department, SUM(Amount) FROM Sales GROUP BY Department ORDER BY SUM(Amount) DESC LIMIT 1;

Solution

1. Step 1: Aggregate total sales per department

   SUM(Amount) with GROUP BY Department calculates total sales per department.

2. Step 2: Order and limit to get highest total

   ORDER BY SUM(Amount) DESC sorts totals from highest to lowest, LIMIT 1 picks top department.

3. Final Answer:

   SELECT Department, SUM(Amount) FROM Sales GROUP BY Department ORDER BY SUM(Amount) DESC LIMIT 1; -> Option D

4. Quick Check:

   Group, sum, order desc, limit 1 = top total [OK]

Hint: Use ORDER BY SUM() DESC LIMIT 1 for top total [OK]

Common Mistakes:

• Using MAX(Amount) instead of SUM(Amount)
• Not grouping by Department
• Trying to filter with WHERE and aggregation