Correlated subqueries in MySQL - Mini Project: Build & Apply

Using Correlated Subqueries in MySQL

📖 Scenario: You are managing a small online bookstore database. You want to find books that have a price higher than the average price of books in the same category.

🎯 Goal: Build a MySQL query using a correlated subquery to list all books with prices above the average price of their category.

📋 What You'll Learn

Create a table called books with columns id, title, category, and price.

Insert the exact data rows provided into the books table.

Write a correlated subquery that calculates the average price per category.

Select books where the price is greater than the average price of their category.

💡 Why This Matters

🌍 Real World

Correlated subqueries help compare each row to a group of related rows, useful in sales, inventory, and reporting databases.

💼 Career

Understanding correlated subqueries is important for database analysts and developers to write efficient and meaningful queries.

1

Create the books table and insert data

Create a table called books with columns id as INT, title as VARCHAR(100), category as VARCHAR(50), and price as DECIMAL(5,2). Then insert these exact rows: (1, 'Learn SQL', 'Programming', 25.00), (2, 'Advanced SQL', 'Programming', 40.00), (3, 'Cooking 101', 'Cooking', 15.00), (4, 'Baking Basics', 'Cooking', 20.00), (5, 'Gardening Tips', 'Gardening', 30.00).

MySQL

-- Create the books table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Write the correlated subquery to find average price per category

Write a SELECT statement that uses a correlated subquery to calculate the average price of books in the same category as each book. Use the alias b for the outer query and b2 for the subquery.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  category VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, category, price) VALUES
(1, 'Learn SQL', 'Programming', 25.00),
(2, 'Advanced SQL', 'Programming', 40.00),
(3, 'Cooking 101', 'Cooking', 15.00),
(4, 'Baking Basics', 'Cooking', 20.00),
(5, 'Gardening Tips', 'Gardening', 30.00);

-- Write a SELECT with a correlated subquery to get average price per category
-- Your code here

Need a hint?

Use a subquery inside the SELECT clause that refers to the outer query's b.category.

3

Filter books with price above average category price

Modify the previous SELECT query to include a WHERE clause that selects only books where b.price is greater than the average price of their category using the correlated subquery.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  category VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, category, price) VALUES
(1, 'Learn SQL', 'Programming', 25.00),
(2, 'Advanced SQL', 'Programming', 40.00),
(3, 'Cooking 101', 'Cooking', 15.00),
(4, 'Baking Basics', 'Cooking', 20.00),
(5, 'Gardening Tips', 'Gardening', 30.00);

-- Add a WHERE clause to filter books with price > average category price
-- Your code here

Need a hint?

Use the correlated subquery inside the WHERE clause to compare prices.

4

Complete the query with ordering by category and price

Add an ORDER BY clause to the query to sort the results first by category ascending and then by price descending.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  category VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, category, price) VALUES
(1, 'Learn SQL', 'Programming', 25.00),
(2, 'Advanced SQL', 'Programming', 40.00),
(3, 'Cooking 101', 'Cooking', 15.00),
(4, 'Baking Basics', 'Cooking', 20.00),
(5, 'Gardening Tips', 'Gardening', 30.00);

-- Add ORDER BY category ascending and price descending
-- Your code here

Need a hint?

Use ORDER BY with multiple columns separated by commas.