Why subqueries are needed in SQL - See It in Action

Understanding Why Subqueries Are Needed in SQL

📖 Scenario: You are managing a small bookstore database. You want to find out which books have a price higher than the average price of all books in the store.

🎯 Goal: Build an SQL query using a subquery to find books priced above the average book price.

📋 What You'll Learn

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

Insert exactly three books with these details: (1, 'Book A', 10), (2, 'Book B', 15), (3, 'Book C', 20)

Write a subquery to calculate the average price of all books

Use the subquery in the WHERE clause to select books with price greater than the average price

💡 Why This Matters

🌍 Real World

Subqueries help answer questions like 'Which items are above average?' or 'Which customers spent more than the average?' in real business databases.

💼 Career

Knowing how to write subqueries is essential for data analysts and database developers to perform complex data filtering and reporting.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), title (text), and price (integer). Then insert these three rows exactly: (1, 'Book A', 10), (2, 'Book B', 15), and (3, 'Book C', 20).

SQL

-- 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

Calculate the average price with a subquery

Write a SQL subquery that calculates the average price of all books in the books table. Assign this subquery to a variable or prepare it to be used in the next step.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES (1, 'Book A', 10);
INSERT INTO books (id, title, price) VALUES (2, 'Book B', 15);
INSERT INTO books (id, title, price) VALUES (3, 'Book C', 20);

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

Need a hint?

Use SELECT AVG(price) FROM books to get the average price.

3

Use the subquery in a WHERE clause

Write a SQL query that selects id, title, and price from books where the price is greater than the average price. Use the subquery (SELECT AVG(price) FROM books) inside the WHERE clause.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES (1, 'Book A', 10);
INSERT INTO books (id, title, price) VALUES (2, 'Book B', 15);
INSERT INTO books (id, title, price) VALUES (3, 'Book C', 20);

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

Need a hint?

Use the subquery inside parentheses in the WHERE clause to compare prices.

4

Complete the query to find books priced above average

Ensure the final SQL query selects id, title, and price from books where the price is greater than the average price calculated by the subquery (SELECT AVG(price) FROM books). This completes the use of subqueries to filter data.

SQL

CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books (id, title, price) VALUES (1, 'Book A', 10);
INSERT INTO books (id, title, price) VALUES (2, 'Book B', 15);
INSERT INTO books (id, title, price) VALUES (3, 'Book C', 20);

-- Finalize the query to find books priced above average
-- Your code here

Need a hint?

Make sure the query uses the subquery correctly in the WHERE clause.