Why aggregation is needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Aggregation in SQL

Why do we use aggregation functions like SUM(), COUNT(), and AVG() in SQL queries?

A To change the data type of columns in a table.

B To combine multiple rows into a single summary value, helping us understand overall data trends.

C To create new tables from existing ones without any data changes.

D To delete duplicate rows from a table permanently.

💡 Hint

Think about how you find totals or averages from many numbers.

Aggregation functions summarize many rows into one value, like total sales or average score, which helps in analyzing data.

❓ query_result

intermediate

Output of Aggregation Query

Given the table Sales with columns Product and Amount, what is the output of this query?

SELECT Product, SUM(Amount) FROM Sales GROUP BY Product;

SQL

CREATE TABLE Sales (Product VARCHAR(10), Amount INT);
INSERT INTO Sales VALUES ('Pen', 10), ('Pen', 15), ('Book', 20), ('Book', 30), ('Pencil', 5);

A [{'Product': 'Pen', 'SUM(Amount)': 10}, {'Product': 'Pen', 'SUM(Amount)': 15}, {'Product': 'Book', 'SUM(Amount)': 20}, {'Product': 'Book', 'SUM(Amount)': 30}, {'Product': 'Pencil', 'SUM(Amount)': 5}]

B [{'Product': 'Pen', 'SUM(Amount)': 50}, {'Product': 'Book', 'SUM(Amount)': 25}, {'Product': 'Pencil', 'SUM(Amount)': 5}]

C [{'Product': 'Pen', 'SUM(Amount)': 25}, {'Product': 'Book', 'SUM(Amount)': 50}, {'Product': 'Pencil', 'SUM(Amount)': 5}]

D [{'Product': 'Pen', 'SUM(Amount)': 5}, {'Product': 'Book', 'SUM(Amount)': 10}, {'Product': 'Pencil', 'SUM(Amount)': 15}]

💡 Hint

SUM adds all amounts for each product.

The query groups rows by product and sums the amounts for each group.

📝 Syntax

advanced

Identify the Syntax Error in Aggregation Query

Which option contains a syntax error in this aggregation query?

SELECT Department, COUNT(Employee) FROM Employees GROUP BY Department;

A SELECT Department, COUNT(Employee) FROM Employees GROUP BY Department;

B SELECT Department, COUNT(Employee) FROM Employees WHERE Department GROUP BY Department;

C ;tnemtrapeD YB PUORG seeyolpmE MORF )eeyolpmE(TNUOC ,tnemtrapeD TCELES

D SELECT Department, COUNT(Employee) FROM Employees GROUP Department;

💡 Hint

Check the GROUP BY clause syntax carefully.

The correct syntax is GROUP BY, not GROUP alone.

❓ optimization

advanced

Optimizing Aggregation Queries

You want to find the average salary per department from a large Employees table. Which query is more efficient?

A SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

B SELECT Department, Salary FROM Employees WHERE Salary = (SELECT AVG(Salary) FROM Employees);

C SELECT Department, SUM(Salary)/COUNT(*) FROM Employees;

D SELECT Department FROM Employees GROUP BY Department HAVING AVG(Salary) > 0;

💡 Hint

Think about grouping and calculating averages directly.

Option A uses GROUP BY with AVG directly, which is efficient and clear.

🔧 Debug

expert

Debugging Incorrect Aggregation Result

Consider this query:

SELECT Department, COUNT(Employee), Salary FROM Employees GROUP BY Department;

Why does this query cause an error or unexpected result?

A Because Salary is not included in GROUP BY or an aggregate function, causing SQL error.

B Because COUNT(Employee) cannot be used with GROUP BY.

C Because Department is not a valid column in Employees table.

D Because GROUP BY must include all columns in SELECT clause.

💡 Hint

Check which columns must be grouped or aggregated.

Columns in SELECT must be either grouped or aggregated. Salary is neither, causing error.