Subqueries in WHERE with IN in PostgreSQL - Mini Project: Build & Apply

Using Subqueries in WHERE with IN in PostgreSQL

📖 Scenario: You work at a bookstore that keeps track of books and their authors in two tables. You want to find all books written by authors from a specific country.

🎯 Goal: Build a SQL query using a subquery with IN in the WHERE clause to list all books by authors from 'Canada'.

📋 What You'll Learn

Create a table called authors with columns author_id, name, and country.

Create a table called books with columns book_id, title, and author_id.

Insert the specified data into both tables.

Write a SQL query that selects title from books where author_id is in a subquery selecting author_id from authors where country is 'Canada'.

💡 Why This Matters

🌍 Real World

Bookstores and libraries often need to find books by authors from specific countries or categories. Using subqueries with IN helps filter data efficiently.

💼 Career

Database developers and analysts use subqueries to write complex queries that combine data from multiple tables based on conditions.

1

Create the authors table and insert data

Create a table called authors with columns author_id as integer primary key, name as text, and country as text. Then insert these rows exactly: (1, 'Alice Munro', 'Canada'), (2, 'Gabriel Garcia Marquez', 'Colombia'), (3, 'Margaret Atwood', 'Canada').

PostgreSQL

-- Create the authors 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 books table and insert data

Create a table called books with columns book_id as integer primary key, title as text, and author_id as integer. Then insert these rows exactly: (1, 'Dear Life', 1), (2, 'One Hundred Years of Solitude', 2), (3, 'The Handmaid''s Tale', 3).

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO authors (author_id, name, country) VALUES
(1, 'Alice Munro', 'Canada'),
(2, 'Gabriel Garcia Marquez', 'Colombia'),
(3, 'Margaret Atwood', 'Canada');

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

Need a hint?

Remember to escape single quotes inside strings by doubling them.

3

Write the subquery to find Canadian authors' IDs

Write a SQL subquery that selects author_id from the authors table where country is 'Canada'. Assign this subquery to a variable called canadian_authors_subquery (for explanation only, actual SQL does not assign variables). Just write the subquery part: (SELECT author_id FROM authors WHERE country = 'Canada').

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO authors (author_id, name, country) VALUES
(1, 'Alice Munro', 'Canada'),
(2, 'Gabriel Garcia Marquez', 'Colombia'),
(3, 'Margaret Atwood', 'Canada');

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'Dear Life', 1),
(2, 'One Hundred Years of Solitude', 2),
(3, 'The Handmaid''s Tale', 3);

-- Write the subquery selecting author_id from authors where country is 'Canada'
-- Your code here

Need a hint?

The subquery must select author_id from authors where country = 'Canada'.

4

Write the main query using the subquery with IN

Write a SQL query that selects title from books where author_id is in the subquery (SELECT author_id FROM authors WHERE country = 'Canada'). This will list all books by Canadian authors.

PostgreSQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO authors (author_id, name, country) VALUES
(1, 'Alice Munro', 'Canada'),
(2, 'Gabriel Garcia Marquez', 'Colombia'),
(3, 'Margaret Atwood', 'Canada');

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'Dear Life', 1),
(2, 'One Hundred Years of Solitude', 2),
(3, 'The Handmaid''s Tale', 3);

-- Write the main query selecting title from books where author_id is in the subquery
-- Your code here

Need a hint?

Use IN with the subquery inside the WHERE clause.