Sql - Aggregate and GROUP BY - Quizz - Lesson

Sql · Aggregate and GROUP BY

Quizz

The full text of this chapter, to read or revisit. To practise, do the exercises.

▶ Do the exercises

Exercise 1 — Challenge: Choosing the Right Aggregate

You need to find out which Codémon type has the highest average power. Which query would give you this information?

Options

• ```SELECT type, AVG(power) AS avg_power FROM codemon GROUP BY type ORDER BY avg_power DESC LIMIT 1;```

• ```SELECT type, MAX(power) AS max_power FROM codemon GROUP BY type ORDER BY max_power DESC LIMIT 1;```

• ```SELECT type, AVG(power) AS avg_power FROM codemon HAVING MAX(avg_power);```

• ```SELECT type, MAX(AVG(power)) FROM codemon GROUP BY type;```

Hint : Think about which aggregate function calculates an average value, then how to find the highest of those averages. Consider the ordering of results.

Exercise 2 — Challenge: Complete the Query with HAVING

Complete the query to find all Codémon types that have an average level higher than 18.

SELECT type, ____(level) AS avg_level FROM codemon ____ BY type ____ avg_level > 18;

Options: AVG SUM COUNT GROUP ORDER HAVING WHERE

Hint : Think about which function calculates an average, which clause is needed for grouping, and which clause filters grouped results.

Exercise 3 — Challenge: Sales Analysis Query

The Codémon Shop manager wants to know the total sales revenue (price × quantity) for each product category, but only for categories with more than 100 total items sold. Which query will provide this information?

Options

• ```SELECT category, SUM(price * quantity) AS revenue FROM sales GROUP BY category HAVING SUM(quantity) > 100;```

• ```SELECT category, SUM(price * quantity) AS revenue FROM sales WHERE SUM(quantity) > 100 GROUP BY category;```

• ```SELECT category, SUM(price) * SUM(quantity) AS revenue FROM sales GROUP BY category HAVING quantity > 100;```

• ```SELECT category, SUM(price * quantity) AS revenue FROM sales WHERE quantity > 100 GROUP BY category;```

Hint : Consider when to use WHERE versus HAVING, and how to calculate the total quantity sold per category. Remember that aggregate functions can only be used in HAVING, not WHERE.

Exercise 4 — Challenge: Match Queries with Their Purpose

Match each SQL query with what it calculates:

Items to match

• SELECT type, COUNT(*) FROM codemon GROUP BY type;

• SELECT type, AVG( wins/(wins+losses) ) FROM codemon GROUP BY type;

• SELECT COUNT(DISTINCT type) FROM codemon;

• SELECT type, SUM(power)/COUNT(*) FROM codemon GROUP BY type;

Definitions

• Number of Codémons of each type

• Win rate for each Codémon type

• Total number of different Codémon types

• Average power for each Codémon type (alternative calculation)

Exercise 5 — Challenge: Multiple GROUP BY Columns

Complete the query to calculate the total revenue (price × quantity) for each combination of product category and region, showing only combinations with revenue over 100.

SELECT category, region, ____(price * quantity) AS revenue FROM sales ____ BY category, region ____ revenue > 100 ORDER BY revenue DESC;

Options: SUM AVG COUNT GROUP ORDER HAVING WHERE

Hint : Think about which function calculates a total value, how to group by multiple columns, and which clause filters after aggregation.

Exercise 6 — Challenge: Basic Aggregate Functions

Which of these queries correctly shows the total number of Codémons and their average health?

Options

• ```SELECT COUNT(*), AVG(health) FROM codemon;```

• ```SELECT COUNT(health), SUM(health) FROM codemon;```

• ```SELECT health, COUNT(*) FROM codemon;```

• ```SELECT COUNT(*), health FROM codemon GROUP BY health;```

Hint : ```COUNT(*)``` counts all rows, and ```AVG(health)``` calculates the average health (ignoring NULLs). This query gives one summary row with both values.

Exercise 7 — Challenge: Sales by Category

Complete the query to find the total revenue by category, showing only categories with more than 3 different products sold.

SELECT category, ____(price * quantity) AS revenue FROM sales ____ BY category ____ COUNT(DISTINCT product_name) > 3;

Options: SUM AVG COUNT GROUP ORDER HAVING WHERE

Hint : Consider which function calculates total revenue, which clause groups by category, and which clause filters based on the count of distinct products.

Exercise 8 — Challenge: Regional Analysis

Which query shows the total quantity sold per region for Medicine products?

Options

• ```SELECT region, SUM(quantity) FROM sales WHERE category = 'Medicine' GROUP BY region;```

• ```SELECT region, COUNT(*) FROM sales WHERE category = 'Medicine' GROUP BY region;```

• ```SELECT region, AVG(quantity) FROM sales GROUP BY region, category HAVING category = 'Medicine';```

• ```SELECT region, MAX(quantity) FROM sales WHERE category = 'Medicine';```

Hint : Look for a query that filters for Medicine category, groups by region, and uses the right aggregate function to total quantities.

Exercise 9 — Challenge: Identify Query Errors

Match each incorrect query with the reason it will fail:

Items to match

• SELECT product_name, SUM(quantity) FROM sales;

• SELECT category, AVG(price) FROM sales WHERE AVG(price) > 10;

• SELECT region, MAX(price) FROM sales GROUP BY price;

• SELECT COUNT(DISTINCT) FROM sales;

Definitions

• Missing GROUP BY clause with non-aggregated column

• Aggregate function in WHERE clause

• Selected column not in GROUP BY clause

• Missing column name in DISTINCT function

Exercise 10 — Challenge: Predict the Output

Given the Codémon table shown below, what will be the output of this query?

SELECT type, COUNT(*) AS count, ROUND(AVG(power)) AS avg_power FROM codemon GROUP BY type HAVING COUNT(*) > 1;

Options

• No rows (empty result)

• One row for the type 'fire' with count=2 and avg_power=80

• One row for the type 'plant' with count=2 and avg_power=70

• All types, each with their respective counts and average powers

Hint : Analyze the data carefully. Look at how many rows exist for each type in the table, then consider the HAVING clause that filters the results.

Exercise 11 — Challenge: Sales Statistics

Complete the query to show for each product category: the total revenue, average price, and number of items sold. Order results by total revenue (highest first).

SELECT category, ____(price * quantity) AS total_revenue, ____(price) AS avg_price, ____(quantity) AS items_sold FROM sales ____ BY category ORDER BY total_revenue DESC;

Options: SUM AVG COUNT SUM GROUP HAVING ORDER

Hint : Think about which functions calculate total values, averages, and sums, and which clause is needed for grouping by category.

Exercise 12 — Challenge: High-Revenue Categories

Which query finds categories with total revenue over 300?

Options

• ```SELECT category FROM sales GROUP BY category HAVING SUM(price*quantity) > 300;```

• ```SELECT category FROM sales WHERE SUM(price*quantity) > 300 GROUP BY category;```

• ```SELECT category FROM sales WHERE price*quantity > 300;```

• ```SELECT category FROM sales GROUP BY category;```

Hint : Look for the query that uses HAVING to filter groups after aggregation.

Exercise 13 — Challenge: Query Purposes

Match each SQL query with its purpose:

Items to match

• SELECT type, SUM(wins)/SUM(wins+losses) FROM codemon GROUP BY type;

• SELECT type FROM codemon GROUP BY type HAVING MAX(level)-MIN(level)>5;

• SELECT type FROM codemon GROUP BY type HAVING COUNT(*)>=2 AND AVG(health)>100;

• SELECT COUNT(*)-COUNT(health) FROM codemon;

Definitions

• Win rate by type

• Types with wide level range

• Types with multiple high-health Codémons

• Missing health data count

Exercise 14 — Challenge: Debugging an Aggregate Query

A trainee wrote this query to find the product with the highest total revenue in each category, but it's not working. What's wrong with it?

SELECT category, product_name, MAX(SUM(price * quantity)) AS max_revenue FROM sales GROUP BY category, product_name;

Options

• Cannot nest aggregate functions (MAX around SUM)

• Missing WHERE clause to filter results

• GROUP BY should only include category, not product_name

• Should use ORDER BY instead of MAX function

Hint : Think about how aggregate functions work in SQL. Can you nest one aggregate function directly inside another? What might be a better approach?

Exercise 15 — Challenge: Counting NULL Values

Which query correctly counts how many Codémons have NULL health values?

Options

• ```SELECT COUNT(*) - COUNT(health) FROM codemon;```

• ```SELECT COUNT(health IS NULL) FROM codemon;```

• ```SELECT COUNT(*) FROM codemon WHERE health = NULL;```

• ```SELECT COUNT(NULL) FROM codemon;```

Hint : Remember that COUNT(*) counts all rows while COUNT(column) only counts non-NULL values. Think about how to use this difference.

Exercise 16 — Challenge: Type Analysis

Which query finds the average power level for each Codémon type?

Options

• ```SELECT type, AVG(power) FROM codemon GROUP BY type;```

• ```SELECT type, SUM(power)/COUNT(*) FROM codemon;```

• ```SELECT type, AVG(power) FROM codemon;```

• ```SELECT AVG(power) FROM codemon GROUP BY type;```

Hint : Look for a query that groups by type and uses the correct aggregate function to calculate the average power.

Exercise 17 — Challenge: Total Revenue

Complete the query to calculate the total revenue (price × quantity) for each product.

SELECT product_name, ____(price * quantity) AS revenue FROM sales ____ BY product_name ORDER BY revenue DESC;

Options: SUM AVG COUNT GROUP ORDER HAVING

Hint : Think about which function calculates total values and which clause groups results by product.

Exercise 18 — Challenge: Optimizing an Aggregate Query

Which of these queries is the most efficient way to find types that have at least one Codémon with power above 80?

Options

• ```SELECT DISTINCT type FROM codemon WHERE power > 80;```

• ```SELECT type FROM codemon GROUP BY type HAVING MAX(power) > 80;```

• ```SELECT type FROM codemon WHERE power > 80 GROUP BY type;```

• ```SELECT type FROM codemon GROUP BY type HAVING COUNT(CASE WHEN power > 80 THEN 1 END) > 0;```

Hint : Consider which approach requires the least processing. Some approaches use aggregation when simpler filtering would suffice.

Exercise 19 — Challenge: Business Questions and SQL

Match each business question with the SQL query that answers it:

Items to match

• Which region has the highest average sale amount per transaction?

• How many different products were sold in each category?

• What is our revenue breakdown by month?

• Which categories have more than 3 different products sold in the North region?

Definitions

• SELECT region, AVG(price * quantity) AS avg_sale FROM sales GROUP BY region ORDER BY avg_sale DESC LIMIT 1;

• SELECT category, COUNT(DISTINCT product_name) AS product_count FROM sales GROUP BY category;

• SELECT substr(sale_date, 1, 7) AS month, SUM(price * quantity) AS revenue FROM sales GROUP BY month ORDER BY month;

• SELECT category FROM sales WHERE region = 'North' GROUP BY category HAVING COUNT(DISTINCT product_name) > 3;

Exercise 20 — Challenge: Codémon Statistics Dashboard

Complete the query to create a dashboard showing for each Codémon type: count and average power.

SELECT type, ____(*) AS count, ROUND(____(power), 1) AS avg_power FROM codemon WHERE type IS NOT NULL ____ BY type ORDER BY avg_power DESC;

Options: COUNT AVG SUM MAX GROUP ORDER HAVING

Hint : Think about which functions calculate a count and an average, and which clause is needed for grouping by type.

Exercise 21 — Challenge: Category Revenue

Which query correctly calculates the total revenue by category?

Options

• ```SELECT category, SUM(price * quantity) AS revenue FROM sales GROUP BY category ORDER BY revenue DESC;```

• ```SELECT category, AVG(price * quantity) AS revenue FROM sales GROUP BY category;```

• ```SELECT category, COUNT(*) * AVG(price * quantity) AS revenue FROM sales GROUP BY category;```

• ```SELECT category, MAX(price * quantity) AS revenue FROM sales GROUP BY category;```

Hint : Look for a solution that groups by category and uses the correct function to sum the product of price and quantity.

Exercise 22 — Challenge: Advanced Aggregation

Match each query with its purpose:

Items to match

• SELECT type, MAX(power) - MIN(power) FROM codemon GROUP BY type;

• SELECT COUNT(*) - COUNT(health) FROM codemon;

• SELECT type, ROUND(AVG(power)) FROM codemon GROUP BY type;

• SELECT COUNT(DISTINCT type) FROM codemon;

Definitions

• Power range for each type

• Count of missing health values

• Rounded average power by type

• Number of unique Codémon types

Exercise 23 — Challenge: Regional Sales

Which query shows the total revenue for each region?

Options

• ```SELECT region, SUM(price * quantity) FROM sales GROUP BY region;```

• ```SELECT region, AVG(price * quantity) FROM sales GROUP BY region;```

• ```SELECT region, COUNT(*) FROM sales GROUP BY region;```

• ```SELECT SUM(price * quantity) FROM sales;```

Hint : Look for a query that groups by region and uses the right aggregate function to calculate total revenue.

Exercise 24 — Challenge: Power Variation

Complete the query to find the maximum and minimum power for each type, as well as the difference between them.

SELECT type, ____(power) AS max_power, ____(power) AS min_power, MAX(power) - MIN(power) AS power_range FROM codemon WHERE power IS NOT NULL ____ BY type ORDER BY power_range DESC;

Options: MAX MIN AVG GROUP ORDER HAVING

Hint : Think about which functions find the highest and lowest values, and which clause is needed for grouping results by type.

Exercise 25 — Final Challenge: Product Analysis

Which query shows each product's total quantity sold, ordered from highest to lowest?

Options

• ```SELECT product_name, SUM(quantity) FROM sales GROUP BY product_name ORDER BY SUM(quantity) DESC;```

• ```SELECT product_name, MAX(quantity) FROM sales GROUP BY product_name;```

• ```SELECT product_name, COUNT(quantity) FROM sales GROUP BY product_name;```

• ```SELECT product_name, SUM(quantity) FROM sales;```

Hint : Find a query that groups by product name, uses SUM to total quantities, and orders results in descending order.

← Previous lesson : Having Next lesson : Table relationships →

Back to the course