JOIN with aggregate functions in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the total number of orders for each customer.

MySQL

SELECT customers.name, COUNT(orders.id) AS total_orders FROM customers LEFT JOIN orders ON customers.id = orders.customer_id GROUP BY [1];

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

Acustomers.name

Borders.id

Corders.customer_id

Dcustomers.id

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by order ID instead of customer name.

Not using GROUP BY at all.

The GROUP BY clause should group by the customer's unique ID to get the total orders per customer. Grouping by name can cause issues if names are not unique.

2fill in blank

medium

Complete the code to find the average order amount per customer.

MySQL

SELECT customers.name, AVG([1]) AS avg_order_amount FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name;

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

Acustomers.name

Borders.amount

Corders.id

Dcustomers.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using customer ID or name inside AVG function.

Applying AVG to order ID instead of amount.

The AVG function should be applied to the order amount to find the average per customer.

3fill in blank

hard

Fix the error in the code to get the maximum order amount per customer.

MySQL

SELECT customers.name, MAX([1]) AS max_amount FROM customers JOIN orders ON customers.id = orders.customer_id GROUP BY customers.name;

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

Aorders.id

Bcustomers.name

Corders.amount

Dcustomers.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using MAX on customer name or ID.

Using MAX on order ID instead of amount.

The MAX function should be applied to the order amount to find the highest order per customer.

4fill in blank

hard

Fill both blanks to calculate the total quantity ordered per product.

MySQL

SELECT products.name, SUM([1]) AS total_quantity FROM products JOIN order_items ON products.id = order_items.product_id GROUP BY [2];

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

Aorder_items.quantity

Bproducts.id

Cproducts.name

Dorder_items.product_id

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by product ID instead of name.

Summing product ID or name instead of quantity.

SUM should be applied to the quantity column to get total quantity. GROUP BY should be on product name to group results by product.

5fill in blank

hard

Fill all three blanks to find the average price per category.

MySQL

SELECT categories.name AS category_name, AVG([1]) AS avg_price FROM categories JOIN products ON categories.id = products.category_id GROUP BY [2] HAVING AVG([3]) > 50;

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

Aproducts.price

Bcategories.name

Dcategories.id

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by category ID instead of name.

Using HAVING on wrong column or without AVG.

AVG should be applied to products.price to calculate average price. GROUP BY should be on category name. HAVING filters categories with average price greater than 50.