Why aggregation summarizes data in MySQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this aggregation query?

Consider a table Sales with columns product_id and amount. What does this query return?

SELECT product_id, SUM(amount) FROM Sales GROUP BY product_id;

MySQL

CREATE TABLE Sales (product_id INT, amount INT);
INSERT INTO Sales VALUES (1, 100), (1, 150), (2, 200), (2, 50), (3, 300);

A [{product_id: 1, SUM(amount): 250}, {product_id: 2, SUM(amount): 50}, {product_id: 3, SUM(amount): 300}]

B [{product_id: 1, SUM(amount): 100}, {product_id: 2, SUM(amount): 200}, {product_id: 3, SUM(amount): 300}]

C [{product_id: 1, SUM(amount): 450}, {product_id: 2, SUM(amount): 250}, {product_id: 3, SUM(amount): 300}]

D [{product_id: 1, SUM(amount): 250}, {product_id: 2, SUM(amount): 250}, {product_id: 3, SUM(amount): 300}]

💡 Hint

Aggregation sums all amounts per product_id.

The query groups rows by product_id and sums the amount for each group. For product_id 1, amounts 100 + 150 = 250; for 2, 200 + 50 = 250; for 3, 300 alone.

🧠 Conceptual

intermediate

Why do we use aggregation functions in databases?

Which of the following best explains why aggregation functions like SUM, COUNT, and AVG are used in databases?

A To combine multiple rows of data into a single summary value for easier analysis.

B To delete duplicate rows from a table.

C To create new tables from existing ones.

D To change the data type of columns.

💡 Hint

Think about what happens when you want to know totals or averages.

Aggregation functions summarize many rows into one value, making it easier to understand overall data trends.

📝 Syntax

advanced

Identify the correct aggregation query syntax

Which query correctly calculates the average salary per department from a table Employees(department, salary)?

A SELECT department, salary AVG() FROM Employees GROUP BY department;

B SELECT department, AVG(salary) FROM Employees;

C SELECT department, AVG(salary) FROM Employees GROUP BY department;

D SELECT AVG(salary) FROM Employees GROUP BY department;

💡 Hint

Remember to use GROUP BY when aggregating per group.

Option C uses correct syntax: AVG function with GROUP BY to get average salary per department. Option C has wrong function syntax. Option C misses GROUP BY, so invalid. Option C selects only AVG without department column.

🔧 Debug

advanced

Why does this aggregation query cause an error?

Given a table Orders(order_id, customer_id, amount), why does this query fail?

SELECT order_id, customer_id, SUM(amount) FROM Orders GROUP BY customer_id;

A Because order_id is not included in GROUP BY or an aggregation function.

B Because SUM(amount) cannot be used with GROUP BY.

C Because customer_id should not be in GROUP BY.

D Because amount column does not exist.

💡 Hint

Check which columns appear in SELECT but not in GROUP BY or aggregation.

SQL requires all selected columns to be either aggregated or included in GROUP BY. order_id is neither, causing an error.

❓ optimization

expert

Optimizing aggregation on large datasets

You have a large Transactions table with millions of rows. Which approach best improves performance when calculating total sales per region?

A Use SELECT * with GROUP BY region to get all columns aggregated.

B Create an index on the region column before running the aggregation query.

C Run the aggregation query without any indexes to avoid overhead.

D Drop the region column to speed up aggregation.

💡 Hint

Indexes help queries filter and group data faster.

Indexing the grouping column (region) speeds up grouping and aggregation by allowing quick data access.