How GROUP BY changes query execution in SQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to group rows by the column 'category'.

SQL

SELECT category, COUNT(*) FROM products [1] category;

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

AWHERE

BORDER BY

CGROUP BY

DHAVING

Attempts:

3 left

💡 Hint

Common Mistakes

Using ORDER BY instead of GROUP BY

Using WHERE to filter groups

The GROUP BY clause groups rows that have the same values in specified columns. Here, it groups rows by 'category'.

2fill in blank

medium

Complete the code to filter groups having more than 5 items.

SQL

SELECT category, COUNT(*) FROM products GROUP BY category [1] COUNT(*) > 5;

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

AHAVING

BWHERE

CORDER BY

DGROUP BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING to filter groups

Using ORDER BY to filter groups

The HAVING clause filters groups after aggregation. It is used to filter groups based on aggregate functions like COUNT.

3fill in blank

hard

Fix the error in the query by completing the blank with the correct clause.

SQL

SELECT category, price FROM products [1] category;

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

AORDER BY

BWHERE

CGROUP BY

DHAVING

Attempts:

3 left

💡 Hint

Common Mistakes

Using GROUP BY without aggregation causing errors

Using HAVING without GROUP BY

This query selects 'category' and 'price' without aggregation. To sort rows by 'category', use ORDER BY. Using GROUP BY without aggregation causes an error.

4fill in blank

hard

Fill both blanks to group by 'department' and filter groups with average salary above 50000.

SQL

SELECT department, AVG(salary) FROM employees [1] department [2] AVG(salary) > 50000;

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

AGROUP BY

BWHERE

CHAVING

DORDER BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE instead of HAVING to filter groups

Not grouping before filtering groups

First, use GROUP BY to group rows by 'department'. Then, use HAVING to filter groups where the average salary is greater than 50000.

5fill in blank

hard

Fill all three blanks to select department names in uppercase, average salary as avg_sal, and filter groups with avg_sal above 60000.

SQL

SELECT [1], AVG(salary) AS [2] FROM employees [3] department HAVING AVG(salary) > 60000;

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

AUPPER(department)

Bavg_sal

CGROUP BY

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Not aliasing the aggregate column

Using HAVING without GROUP BY

Selecting columns not in GROUP BY or aggregated

Use UPPER(department) to show department names in uppercase. Alias the average salary as avg_sal. Group rows by department using GROUP BY before filtering with HAVING.

Practice

1. What does the GROUP BY clause do in an SQL query?

easy

A. It filters rows based on a condition.

B. It groups rows that have the same values in specified columns.

C. It deletes duplicate rows from the result.

D. It sorts the rows in ascending order.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows with the same values in specified columns into groups.

2. Step 2: Compare with other SQL clauses

   Sorting is done by ORDER BY, filtering by WHERE, and removing duplicates by DISTINCT, not GROUP BY.

3. Final Answer:

   It groups rows that have the same values in specified columns. -> Option B

4. Quick Check:

   GROUP BY groups rows by column values [OK]

Hint: GROUP BY groups rows by column values, not sorting or filtering [OK]

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Thinking GROUP BY filters rows
• Assuming GROUP BY removes duplicates

2. Which of the following is the correct syntax to group rows by the column department?

easy

A. SELECT department, COUNT(*) FROM employees WHERE department;

B. SELECT department, COUNT(*) FROM employees ORDER BY department;

C. SELECT department, COUNT(*) FROM employees GROUP BY department;

D. SELECT department, COUNT(*) FROM employees HAVING department;

Solution

1. Step 1: Identify correct GROUP BY usage

   The GROUP BY clause must follow the FROM clause and specify the column to group by, here 'department'.

2. Step 2: Check each option's syntax

   SELECT department, COUNT(*) FROM employees GROUP BY department; uses GROUP BY correctly. SELECT department, COUNT(*) FROM employees ORDER BY department; uses ORDER BY which sorts, not groups. SELECT department, COUNT(*) FROM employees WHERE department; uses WHERE incorrectly. SELECT department, COUNT(*) FROM employees HAVING department; uses HAVING without GROUP BY, which is invalid.

3. Final Answer:

   SELECT department, COUNT(*) FROM employees GROUP BY department; -> Option C

4. Quick Check:

   GROUP BY syntax: SELECT ... FROM ... GROUP BY column [OK]

Hint: GROUP BY follows FROM and lists columns to group by [OK]

Common Mistakes:

• Using ORDER BY instead of GROUP BY
• Using WHERE to group rows
• Using HAVING without GROUP BY

3. 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;

medium

A. A list of regions with the total sales amount per region.

B. A list of all sales amounts without grouping.

C. An error because SUM() cannot be used with GROUP BY.

D. A list of regions sorted by amount.

Solution

1. Step 1: Understand GROUP BY with aggregate functions

   The query groups rows by 'region' and calculates the sum of 'amount' for each group.

2. Step 2: Analyze the output

   The result shows each region once with the total sales amount summed from all rows in that region.

3. Final Answer:

   A list of regions with the total sales amount per region. -> Option A

4. Quick Check:

   GROUP BY + SUM() = totals per group [OK]

Hint: GROUP BY with SUM() gives totals per group [OK]

Common Mistakes:

• Expecting all rows without grouping
• Thinking SUM() causes error with GROUP BY
• Confusing grouping with sorting

4. What is wrong with this query?

SELECT department, employee_name, COUNT(*) FROM employees GROUP BY department;

medium

A. You cannot select employee_name without grouping by it or using an aggregate function.

B. COUNT(*) cannot be used with GROUP BY.

C. GROUP BY must come before SELECT.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check columns in SELECT with GROUP BY

   When using GROUP BY on 'department', all selected columns must be grouped or aggregated.

2. Step 2: Identify the error

   'employee_name' is neither grouped nor aggregated, causing a syntax error.

3. Final Answer:

   You cannot select employee_name without grouping by it or using an aggregate function. -> Option A

4. Quick Check:

   Non-grouped columns must be aggregated [OK]

Hint: All selected columns must be grouped or aggregated [OK]

Common Mistakes:

• Selecting non-grouped columns without aggregation
• Thinking COUNT(*) is invalid with GROUP BY
• Misplacing GROUP BY clause

5. You want to find departments with more than 5 employees. Which query correctly uses GROUP BY and HAVING to achieve this?

hard

A. SELECT department, COUNT(*) FROM employees GROUP BY department WHERE COUNT(*) > 5;

B. SELECT department, COUNT(*) FROM employees HAVING COUNT(*) > 5 GROUP BY department;

C. SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department;

D. SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

Solution

1. Step 1: Understand HAVING clause usage

   HAVING filters groups after aggregation, so it must come after GROUP BY.

2. Step 2: Check query order and syntax

   SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; correctly places HAVING after GROUP BY with condition COUNT(*) > 5. Other options misuse HAVING or WHERE clauses.

3. Final Answer:

   SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; -> Option D

4. Quick Check:

   HAVING filters groups after GROUP BY [OK]

Hint: Use HAVING after GROUP BY to filter groups [OK]

Common Mistakes:

• Using WHERE to filter aggregated results
• Placing HAVING before GROUP BY
• Confusing WHERE and HAVING clauses