IN and NOT IN operators in MySQL - Mini Project: Build & Apply

Using IN and NOT IN Operators in SQL

📖 Scenario: You are managing a small bookstore database. You want to find books by certain authors and exclude others.

🎯 Goal: Build SQL queries using IN and NOT IN operators to filter books by author names.

📋 What You'll Learn

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

Insert specific book records into the books table

Write a query using IN to select books by given authors

Write a query using NOT IN to select books excluding certain authors

💡 Why This Matters

🌍 Real World

Filtering data by multiple values is common in databases for reports, searches, and data analysis.

💼 Career

Knowing how to use IN and NOT IN helps database developers and analysts write efficient queries to get precise data.

1

Create the books table

Write a SQL statement to create a table called books with columns id as integer primary key, title as text, and author as text.

MySQL

-- Write the CREATE TABLE statement for books
-- Your code here

Need a hint?

Use CREATE TABLE books and define three columns with correct types and primary key.

2

Insert book records

Insert these exact records into the books table: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, '1984', 'George Orwell'), (3, 'To Kill a Mockingbird', 'Harper Lee'), (4, 'Animal Farm', 'George Orwell'), (5, 'The Catcher in the Rye', 'J.D. Salinger').

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT
);
-- Insert the 5 book records below
-- Your code here

Need a hint?

Use a single INSERT INTO books statement with multiple value tuples.

3

Select books by specific authors using IN

Write a SQL query to select all columns from books where the author is either 'George Orwell' or 'Harper Lee' using the IN operator.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'To Kill a Mockingbird', 'Harper Lee'),
(4, 'Animal Farm', 'George Orwell'),
(5, 'The Catcher in the Rye', 'J.D. Salinger');

-- Select books where author is 'George Orwell' or 'Harper Lee' using IN
-- Your code here

Need a hint?

Use WHERE author IN ('George Orwell', 'Harper Lee') to filter authors.

4

Select books excluding specific authors using NOT IN

Write a SQL query to select all columns from books where the author is not 'F. Scott Fitzgerald' and not 'J.D. Salinger' using the NOT IN operator.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title TEXT,
  author TEXT
);

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, '1984', 'George Orwell'),
(3, 'To Kill a Mockingbird', 'Harper Lee'),
(4, 'Animal Farm', 'George Orwell'),
(5, 'The Catcher in the Rye', 'J.D. Salinger');

SELECT * FROM books WHERE author IN ('George Orwell', 'Harper Lee');

-- Select books where author is NOT 'F. Scott Fitzgerald' or 'J.D. Salinger' using NOT IN
-- Your code here

Need a hint?

Use WHERE author NOT IN ('F. Scott Fitzgerald', 'J.D. Salinger') to exclude authors.