Subquery with IN operator in SQL - Mini Project: Build & Apply

Using Subquery with IN Operator in SQL

📖 Scenario: You work at a bookstore that keeps two tables: books and orders. The books table has details about each book, and the orders table records which books customers have ordered.You want to find all books that have been ordered at least once.

🎯 Goal: Build an SQL query using a subquery with the IN operator to list all books that appear in the orders.

📋 What You'll Learn

Create a table called books with columns book_id (integer) and title (text).

Create a table called orders with columns order_id (integer) and book_id (integer).

Insert the exact data into books: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird'), (4, 'Moby Dick').

Insert the exact data into orders: (101, 2), (102, 3), (103, 2).

Write a SELECT query to find all books where book_id is in the list of book_ids from orders using a subquery with the IN operator.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use subqueries with IN to find related records across tables, like finding products that have sales.

💼 Career

Knowing how to write subqueries with IN is a fundamental SQL skill for data analysts, database developers, and backend engineers.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns book_id (integer) and title (text). Then insert these exact rows: (1, 'The Great Gatsby'), (2, '1984'), (3, 'To Kill a Mockingbird'), and (4, 'Moby Dick').

SQL

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

Need a hint?

Use CREATE TABLE books (book_id INTEGER, title TEXT); to create the table. Use one INSERT INTO books statement with multiple rows.

2

Create the orders table and insert data

Write SQL statements to create a table called orders with columns order_id (integer) and book_id (integer). Then insert these exact rows: (101, 2), (102, 3), and (103, 2).

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird'),
(4, 'Moby Dick');
-- Create the orders table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE orders (order_id INTEGER, book_id INTEGER); to create the table. Use one INSERT INTO orders statement with multiple rows.

3

Write the subquery with IN operator

Write a SELECT query to get all columns from books where book_id is in the list of book_ids from orders. Use a subquery with the IN operator.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird'),
(4, 'Moby Dick');

CREATE TABLE orders (order_id INTEGER, book_id INTEGER);
INSERT INTO orders (order_id, book_id) VALUES
(101, 2),
(102, 3),
(103, 2);

-- Write the SELECT query using IN and subquery
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE book_id IN (SELECT book_id FROM orders); to get books that have orders.

4

Complete the query with ordering

Add an ORDER BY clause to the previous SELECT query to sort the results by title in ascending order.

SQL

CREATE TABLE books (book_id INTEGER, title TEXT);
INSERT INTO books (book_id, title) VALUES
(1, 'The Great Gatsby'),
(2, '1984'),
(3, 'To Kill a Mockingbird'),
(4, 'Moby Dick');

CREATE TABLE orders (order_id INTEGER, book_id INTEGER);
INSERT INTO orders (order_id, book_id) VALUES
(101, 2),
(102, 3),
(103, 2);

SELECT * FROM books WHERE book_id IN (SELECT book_id FROM orders)
-- Add ORDER BY title ascending
-- Your code here

Need a hint?

Add ORDER BY title ASC at the end of the SELECT query to sort by title alphabetically.