Subqueries with IN operator in MySQL - Mini Project: Build & Apply

Using Subqueries with IN Operator in MySQL

📖 Scenario: You work for a small bookstore that keeps track of books and their authors in two tables. You want to find all books written by authors who have published more than one book.

🎯 Goal: Create a MySQL query using a subquery with the IN operator to list all books by authors who have more than one book in the database.

📋 What You'll Learn

Create a table called authors with columns author_id (integer) and author_name (varchar).

Create a table called books with columns book_id (integer), title (varchar), and author_id (integer).

Insert exactly 5 authors into the authors table with these author_id values: 1, 2, 3, 4, 5.

Insert exactly 7 books into the books table with these exact titles and author_ids:

'The Lost World', 1, 'Dinosaur Tales', 1, 'Ocean Depths', 2, 'Mountain High', 3, 'City Lights', 3, 'Desert Winds', 4, 'Forest Whisper', 5.

Write a subquery that finds author_id values for authors with more than one book.

Use the IN operator with the subquery to select all books by those authors.

💡 Why This Matters

🌍 Real World

Bookstores and libraries often need to find books by authors with multiple works to create special collections or promotions.

💼 Career

Understanding subqueries and the IN operator is essential for database querying roles, data analysis, and backend development.

1

Create tables and insert authors

Create a table called authors with columns author_id as integer primary key and author_name as varchar(50). Then insert these authors exactly: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'Diana'), (5, 'Evan').

MySQL

-- Create authors table and insert 5 authors
-- Your code here

Need a hint?

Use CREATE TABLE to make the authors table. Use INSERT INTO with multiple rows to add authors.

2

Create books table and insert books

Create a table called books with columns book_id as integer primary key, title as varchar(100), and author_id as integer. Then insert these books exactly with their author_ids: (1, 'The Lost World', 1), (2, 'Dinosaur Tales', 1), (3, 'Ocean Depths', 2), (4, 'Mountain High', 3), (5, 'City Lights', 3), (6, 'Desert Winds', 4), (7, 'Forest Whisper', 5).

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'),
(5, 'Evan');

-- Create books table and insert 7 books
-- Your code here

Need a hint?

Use CREATE TABLE to make the books table with the right columns. Use INSERT INTO with multiple rows to add books.

3

Write subquery to find authors with multiple books

Write a subquery that selects author_id from the books table where the count of books per author is more than 1. Use GROUP BY author_id and HAVING COUNT(*) > 1.

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'),
(5, 'Evan');

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'The Lost World', 1),
(2, 'Dinosaur Tales', 1),
(3, 'Ocean Depths', 2),
(4, 'Mountain High', 3),
(5, 'City Lights', 3),
(6, 'Desert Winds', 4),
(7, 'Forest Whisper', 5);

-- Write subquery to find author_ids with more than one book
-- Your code here

Need a hint?

Use GROUP BY author_id to group books by author. Use HAVING COUNT(*) > 1 to find authors with more than one book.

4

Use subquery with IN operator to select books by those authors

Write a query to select title and author_id from books where author_id is in the subquery that finds authors with more than one book. Use the IN operator with the subquery from Step 3.

MySQL

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50)
);

INSERT INTO authors (author_id, author_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana'),
(5, 'Evan');

CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT
);

INSERT INTO books (book_id, title, author_id) VALUES
(1, 'The Lost World', 1),
(2, 'Dinosaur Tales', 1),
(3, 'Ocean Depths', 2),
(4, 'Mountain High', 3),
(5, 'City Lights', 3),
(6, 'Desert Winds', 4),
(7, 'Forest Whisper', 5);

SELECT author_id FROM books
GROUP BY author_id
HAVING COUNT(*) > 1;

-- Select titles and author_ids from books where author_id is in the subquery
-- Your code here

Need a hint?

Use WHERE author_id IN (subquery) to select books by authors with multiple books.