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

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

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

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

Hint

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

Practice

1. What does a nested subquery in SQL do?

easy

A. Runs a query inside another query to filter or compare data

B. Creates a new table from existing data

C. Deletes data from multiple tables at once

D. Updates all rows in a table without conditions

Solution

1. Step 1: Understand the concept of nested subqueries

   A nested subquery is a query inside another query that runs first to provide data for the outer query.

2. Step 2: Identify the correct description

   Runs a query inside another query to filter or compare data correctly describes this behavior as running a query inside another to filter or compare data.

3. Final Answer:

   Runs a query inside another query to filter or compare data -> Option A

4. Quick Check:

   Nested subquery = query inside query [OK]

Hint: Nested means one query inside another [OK]

Common Mistakes:

• Confusing nested subquery with table creation
• Thinking nested subqueries delete data
• Assuming nested subqueries update all rows blindly

2. Which of the following is the correct syntax for a nested subquery in SQL?

easy

A. SELECT * FROM table WHERE id IN SELECT id FROM table2 WHERE value = 10;

B. SELECT * FROM table WHERE id == (SELECT id FROM table2 WHERE value = 10);

C. SELECT * FROM table WHERE id = SELECT id FROM table2 WHERE value = 10;

D. SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10);

Solution

1. Step 1: Review correct nested subquery syntax

   The inner query must be enclosed in parentheses and used with operators like = or IN.

2. Step 2: Check each option

   SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10); uses parentheses correctly and equals operator, making it valid SQL syntax.

3. Final Answer:

   SELECT * FROM table WHERE id = (SELECT id FROM table2 WHERE value = 10); -> Option D

4. Quick Check:

   Nested subquery syntax uses parentheses [OK]

Hint: Always use parentheses around subqueries [OK]

Common Mistakes:

• Missing parentheses around subquery
• Using double equals (==) instead of single =
• Omitting parentheses causing syntax errors

3. Given the tables:
Employees(emp_id, name, dept_id)
Departments(dept_id, dept_name)
What does this query return?

SELECT name FROM Employees WHERE dept_id = (SELECT dept_id FROM Departments WHERE dept_name = 'Sales');

medium

A. Names of employees who work in the Sales department

B. All employee names regardless of department

C. Department names where employees work

D. Employee names who do not work in Sales

Solution

1. Step 1: Understand the inner query

   The inner query finds the dept_id for the 'Sales' department from Departments table.

2. Step 2: Apply the outer query condition

   The outer query selects employee names whose dept_id matches the Sales dept_id found by the inner query.

3. Final Answer:

   Names of employees who work in the Sales department -> Option A

4. Quick Check:

   Inner query finds Sales dept_id, outer filters employees [OK]

Hint: Inner query finds filter value, outer applies it [OK]

Common Mistakes:

• Thinking it returns all employees
• Confusing employee names with department names
• Assuming it returns employees not in Sales

4. Identify the error in this SQL query:

SELECT name FROM Employees WHERE dept_id = SELECT dept_id FROM Departments WHERE dept_name = 'HR';

medium

A. Using = instead of IN for subquery

B. Missing parentheses around the subquery

C. Wrong table name used in subquery

D. Subquery returns multiple columns

Solution

1. Step 1: Check subquery syntax

   The subquery must be enclosed in parentheses to be valid inside WHERE clause.

2. Step 2: Identify the missing parentheses

   The query lacks parentheses around the subquery, causing syntax error.

3. Final Answer:

   Missing parentheses around the subquery -> Option B

4. Quick Check:

   Subqueries need parentheses [OK]

Hint: Always wrap subqueries in parentheses [OK]

Common Mistakes:

• Forgetting parentheses around subquery
• Using wrong operator without parentheses
• Assuming subquery syntax is optional

5. You want to find all customers who placed orders with amounts greater than the average order amount. Which query correctly uses a nested subquery to achieve this?

hard

A. SELECT customer_id FROM Orders WHERE amount < (SELECT AVG(amount) FROM Orders);

B. SELECT customer_id FROM Orders WHERE amount = (SELECT AVG(amount) FROM Orders);

C. SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders);

D. SELECT customer_id FROM Orders WHERE amount IN (SELECT AVG(amount) FROM Orders);

Solution

1. Step 1: Understand the goal

   We want customers with orders greater than the average order amount.

2. Step 2: Analyze each option's condition

   SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders); uses > with a subquery calculating average amount, correctly filtering orders above average.

3. Final Answer:

   SELECT customer_id FROM Orders WHERE amount > (SELECT AVG(amount) FROM Orders); -> Option C

4. Quick Check:

   Use > with AVG subquery to find above-average orders [OK]

Hint: Compare with (SELECT AVG(...)) using > for above average [OK]

Common Mistakes:

• Using = instead of > to find above average
• Using < which finds below average
• Using IN with a single value subquery incorrectly