Why grouping is needed in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of GROUP BY in SQL

Why do we use the GROUP BY clause in SQL queries?

A To filter rows based on a condition

B To sort the rows in ascending order

C To join two tables together

D To combine rows that have the same values in specified columns into summary rows

💡 Hint

Think about how to summarize data by categories.

The GROUP BY clause groups rows that have the same values in specified columns. This allows aggregate functions like COUNT, SUM, AVG to be applied to each group, producing summary results.

❓ query_result

intermediate

Output of GROUP BY with COUNT

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

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

SQL

Sales table data:
Product | Quantity
--------|---------
Apple   | 10
Banana  | 5
Apple   | 7
Banana  | 3
Cherry  | 8

A [{"Product": "Apple", "COUNT(*)": 2}, {"Product": "Banana", "COUNT(*)": 2}, {"Product": "Cherry", "COUNT(*)": 1}]

B [{"Product": "Apple", "COUNT(*)": 17}, {"Product": "Banana", "COUNT(*)": 8}, {"Product": "Cherry", "COUNT(*)": 8}]

C [{"Product": "Apple", "COUNT(*)": 1}, {"Product": "Banana", "COUNT(*)": 1}, {"Product": "Cherry", "COUNT(*)": 1}]

D [{"Product": "Apple", "COUNT(*)": 3}, {"Product": "Banana", "COUNT(*)": 3}, {"Product": "Cherry", "COUNT(*)": 3}]

💡 Hint

COUNT(*) counts rows per group, not sum of Quantity.

The query groups rows by Product and counts how many rows each product has. Apple appears twice, Banana twice, Cherry once.

📝 Syntax

advanced

Identify the syntax error in GROUP BY usage

Which option contains a syntax error in using GROUP BY?

SQL

Table: Employees (Name, Department, Salary)

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

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

C SELECT Name, Department FROM Employees GROUP BY Department;

D SELECT Department, SUM(Salary) FROM Employees GROUP BY Department;

💡 Hint

Check if all selected columns are either grouped or aggregated.

Option C selects Name and Department but groups only by Department. Name is neither aggregated nor grouped, causing a syntax error.

❓ optimization

advanced

Optimizing GROUP BY queries with indexes

Which index will best improve performance of this query?

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

A CREATE INDEX idx_name ON Employees(Name);

B CREATE INDEX idx_department ON Employees(Department);

C CREATE INDEX idx_salary ON Employees(Salary);

D CREATE INDEX idx_department_salary ON Employees(Department, Salary);

💡 Hint

Think about which column is used in GROUP BY.

Indexing the column used in GROUP BY (Department) helps the database group rows faster.

🔧 Debug

expert

Why does this GROUP BY query fail?

Given the table Orders with columns OrderID, CustomerID, and OrderDate, why does this query fail?

SELECT CustomerID, OrderDate, COUNT(*) FROM Orders GROUP BY CustomerID;

A Because OrderDate is neither in GROUP BY nor aggregated

B Because CustomerID is not a valid column

C Because COUNT(*) cannot be used with GROUP BY

D Because GROUP BY requires ORDER BY clause

💡 Hint

Check if all selected columns are grouped or aggregated.

OrderDate is selected but not grouped or aggregated, causing the query to fail.