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

Using Subquery with EXISTS Operator

📖 Scenario: You work for a bookstore database. The database has two tables: Books and Orders. You want to find all books that have been ordered at least once.

🎯 Goal: Build a SQL query using the EXISTS operator with a subquery to list all books that have orders.

📋 What You'll Learn

Create a Books table with columns BookID (integer) and Title (text).

Create an Orders table with columns OrderID (integer) and BookID (integer).

Write a query using EXISTS with a subquery to select all books that have at least one order.

Use exact table and column names as specified.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use databases to track products and orders. Checking if a product has been ordered is a common task.

💼 Career

Understanding subqueries and EXISTS is important for database querying roles, data analysis, and backend development.

1

Create the Books table with sample data

Create a table called Books with columns BookID (integer) and Title (text). Insert these exact rows: (1, 'Learn SQL'), (2, 'Database Basics'), (3, 'Advanced SQL').

SQL

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

Create the Orders table with sample data

Create a table called Orders with columns OrderID (integer) and BookID (integer). Insert these exact rows: (101, 1), (102, 3).

SQL

CREATE TABLE Books (BookID INTEGER, Title TEXT);
INSERT INTO Books (BookID, Title) VALUES (1, 'Learn SQL'), (2, 'Database Basics'), (3, 'Advanced SQL');
-- Create Orders table and insert data
-- Your code here

Need a hint?

Use CREATE TABLE and INSERT INTO like in Step 1.

3

Write the query using EXISTS subquery

Write a SQL query to select BookID and Title from Books where there exists at least one row in Orders with the same BookID. Use the EXISTS operator with a subquery.

SQL

CREATE TABLE Books (BookID INTEGER, Title TEXT);
INSERT INTO Books (BookID, Title) VALUES (1, 'Learn SQL'), (2, 'Database Basics'), (3, 'Advanced SQL');
CREATE TABLE Orders (OrderID INTEGER, BookID INTEGER);
INSERT INTO Orders (OrderID, BookID) VALUES (101, 1), (102, 3);
-- Write SELECT query using EXISTS
-- Your code here

Need a hint?

Use WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.BookID = Books.BookID) to check for matching orders.

4

Complete the query with ordering

Add an ORDER BY Title clause at the end of the query to sort the results alphabetically by book title.

SQL

CREATE TABLE Books (BookID INTEGER, Title TEXT);
INSERT INTO Books (BookID, Title) VALUES (1, 'Learn SQL'), (2, 'Database Basics'), (3, 'Advanced SQL');
CREATE TABLE Orders (OrderID INTEGER, BookID INTEGER);
INSERT INTO Orders (OrderID, BookID) VALUES (101, 1), (102, 3);
SELECT BookID, Title FROM Books WHERE EXISTS (SELECT 1 FROM Orders WHERE Orders.BookID = Books.BookID)
-- Add ORDER BY clause
-- Your code here

Need a hint?

Use ORDER BY Title at the end of the query to sort results.