Nested subqueries in SQL - Mini Project: Build & Apply

Nested Subqueries in SQL

📖 Scenario: You work at a bookstore that keeps track of books and their sales. You want to find out which books have sold more copies than the average number of copies sold across all books.

🎯 Goal: Build an SQL query using nested subqueries to find the titles of books that sold more copies than the average sales.

📋 What You'll Learn

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

Insert the exact data rows provided into the books table

Write a nested subquery to calculate the average copies sold

Use the nested subquery in a WHERE clause to filter books with copies sold greater than the average

💡 Why This Matters

🌍 Real World

Nested subqueries help you compare each row to a calculated value from the whole table, useful in sales analysis, reporting, and decision making.

💼 Career

SQL queries with nested subqueries are common in data analyst and database developer roles to extract meaningful insights from data.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and copies_sold (integer). Then insert these exact rows: (1, 'The Alchemist', 500), (2, '1984', 300), (3, 'To Kill a Mockingbird', 450), (4, 'The Great Gatsby', 200), (5, 'Moby Dick', 150).

SQL

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

Need a hint?

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

2

Calculate the average copies sold

Create a variable or write a subquery that calculates the average of copies_sold from the books table. Use SELECT AVG(copies_sold) FROM books exactly.

SQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    copies_sold INTEGER
);

INSERT INTO books (id, title, copies_sold) VALUES
(1, 'The Alchemist', 500),
(2, '1984', 300),
(3, 'To Kill a Mockingbird', 450),
(4, 'The Great Gatsby', 200),
(5, 'Moby Dick', 150);

-- Write a subquery to calculate average copies_sold
-- Your code here

Need a hint?

Use AVG() function inside a SELECT statement.

3

Write the nested subquery to filter books

Write a query to select title from books where copies_sold is greater than the average copies sold. Use a nested subquery exactly like SELECT title FROM books WHERE copies_sold > (SELECT AVG(copies_sold) FROM books).

SQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    copies_sold INTEGER
);

INSERT INTO books (id, title, copies_sold) VALUES
(1, 'The Alchemist', 500),
(2, '1984', 300),
(3, 'To Kill a Mockingbird', 450),
(4, 'The Great Gatsby', 200),
(5, 'Moby Dick', 150);

-- Select titles where copies_sold is greater than average copies_sold
-- Your code here

Need a hint?

Use a nested subquery inside the WHERE clause to compare copies_sold with the average.

4

Complete the query with ordering

Add an ORDER BY title ASC clause to the query to list the book titles in alphabetical order. The full query should be SELECT title FROM books WHERE copies_sold > (SELECT AVG(copies_sold) FROM books) ORDER BY title ASC.

SQL

CREATE TABLE books (
    id INTEGER,
    title TEXT,
    copies_sold INTEGER
);

INSERT INTO books (id, title, copies_sold) VALUES
(1, 'The Alchemist', 500),
(2, '1984', 300),
(3, 'To Kill a Mockingbird', 450),
(4, 'The Great Gatsby', 200),
(5, 'Moby Dick', 150);

-- Add ORDER BY clause to sort titles alphabetically
-- Your code here

Need a hint?

Use ORDER BY title ASC at the end of the query to sort results alphabetically.