Why subqueries nest queries in MySQL - See It in Action

Understanding Why Subqueries Nest Queries in MySQL

📖 Scenario: You are managing a small bookstore database. You want to find books that have a price higher than the average price of all books. This requires using a subquery to calculate the average price first, then using that result to filter books.

🎯 Goal: Build a MySQL query using a subquery that nests one query inside another to find books priced above the average price.

📋 What You'll Learn

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

Insert exactly three books with specified prices

Write a subquery to calculate the average price of all books

Write a main query that uses the subquery to find books with price greater than the average

💡 Why This Matters

🌍 Real World

Subqueries are used in real databases to perform complex filtering and calculations in one query, like finding products above average price or customers with orders above average value.

💼 Career

Understanding subqueries is essential for database developers and analysts to write efficient and readable SQL queries for data retrieval and reporting.

1

Create the books table and insert data

Create a table called books with columns id as INT, title as VARCHAR(100), and price as DECIMAL(5,2). Then insert these three rows exactly: (1, 'Book A', 10.00), (2, 'Book B', 15.00), and (3, 'Book C', 20.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 a subquery to calculate average price

Create a subquery that calculates the average price of all books using SELECT AVG(price) FROM books and assign it to a variable called avg_price using a SET statement.

MySQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10.00),
(2, 'Book B', 15.00),
(3, 'Book C', 20.00);

-- Set avg_price variable using a subquery
-- Your code here

Need a hint?

Use SET @avg_price = (SELECT AVG(price) FROM books); to store the average price.

3

Write the main query using the subquery

Write a SELECT query to get id, title, and price from books where price is greater than the subquery (SELECT AVG(price) FROM books). Use the exact subquery inside the WHERE clause.

MySQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10.00),
(2, 'Book B', 15.00),
(3, 'Book C', 20.00);

SET @avg_price = (SELECT AVG(price) FROM books);

-- Select books with price greater than average using a subquery
-- Your code here

Need a hint?

Use the subquery (SELECT AVG(price) FROM books) directly in the WHERE clause.

4

Explain why subqueries nest queries

Add a comment explaining that subqueries nest queries to allow using the result of one query inside another, like using the average price to filter books.

MySQL

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

INSERT INTO books (id, title, price) VALUES
(1, 'Book A', 10.00),
(2, 'Book B', 15.00),
(3, 'Book C', 20.00);

SET @avg_price = (SELECT AVG(price) FROM books);

SELECT id, title, price FROM books WHERE price > (SELECT AVG(price) FROM books);

-- Add a comment explaining why subqueries nest queries
-- Your code here

Need a hint?

Write a comment starting with -- explaining the purpose of nesting queries with subqueries.