How GROUP BY changes query execution in SQL - Practice Exercises

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY with aggregate function

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

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

SQL

CREATE TABLE Sales (Region VARCHAR(10), Amount INT);
INSERT INTO Sales VALUES ('East', 100), ('West', 200), ('East', 150), ('West', 50);

A SyntaxError

B [{"Region": "East", "SUM(Amount)": 100}, {"Region": "West", "SUM(Amount)": 200}, {"Region": "East", "SUM(Amount)": 150}, {"Region": "West", "SUM(Amount)": 50}]

C [{"Region": "East", "SUM(Amount)": 250}, {"Region": "West", "SUM(Amount)": 250}]

D [{"Region": null, "SUM(Amount)": 500}]

💡 Hint

GROUP BY groups rows by the column values before applying aggregate functions.

The query groups rows by Region and sums Amount for each group. East has 100 + 150 = 250, West has 200 + 50 = 250.

🧠 Conceptual

intermediate

Effect of GROUP BY on non-aggregated columns

What happens if you select a column in a query with GROUP BY that is not included in the GROUP BY clause or an aggregate function?

A The query returns the first value of that column for each group.

B The query returns an error because all selected columns must be grouped or aggregated.

C The query returns the last value of that column for each group.

D The query ignores the column and returns only grouped columns.

💡 Hint

Think about SQL rules for grouping and selecting columns.

SQL requires that all selected columns must be either in the GROUP BY clause or used inside an aggregate function. Otherwise, it raises an error.

📝 Syntax

advanced

Identify the syntax error in GROUP BY usage

Which option contains a syntax error in the GROUP BY clause?

SQL

Table: Employees (Department VARCHAR, Salary INT)

A SELECT Department, Salary FROM Employees GROUP BY Department;

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

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

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

💡 Hint

Check if all selected columns are grouped or aggregated.

Option A selects Salary without aggregation or grouping, causing a syntax error.

❓ optimization

advanced

How GROUP BY affects query performance

Which statement best explains how adding a GROUP BY clause affects query execution?

A GROUP BY has no effect on query execution time.

B GROUP BY always makes queries faster by reducing the number of rows returned.

C GROUP BY eliminates the need for indexes on the grouped columns.

D GROUP BY causes the database to sort or hash rows to group them, which can increase execution time.

💡 Hint

Think about what the database must do to group rows.

Grouping requires sorting or hashing rows, which adds processing overhead and can slow queries.

🔧 Debug

expert

Diagnose the cause of unexpected GROUP BY results

A query:

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

returns lower counts than expected. Which issue is most likely causing this?

A COUNT(EmployeeID) counts only non-NULL EmployeeID, so some rows are excluded.

B Some Department values are NULL, causing those rows to be grouped together.

C The GROUP BY clause is missing a column, causing rows to merge incorrectly.

D The query is missing a WHERE clause to filter rows.

💡 Hint

Consider how COUNT behaves with NULL values.

COUNT(column) counts only rows where the column is not NULL, so rows with NULL EmployeeID are not counted, reducing totals.