WHERE clause filtering in MySQL - Mini Project: Build & Apply

Filtering Data with WHERE Clause in MySQL

📖 Scenario: You are managing a small bookstore database. You want to find books that meet certain conditions, like books by a specific author or books priced below a certain amount.

🎯 Goal: Build a MySQL query step-by-step that filters books using the WHERE clause to find specific records based on conditions.

📋 What You'll Learn

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

Insert specific book records into the books table

Write a SELECT query with a WHERE clause filtering by author

Modify the WHERE clause to filter books priced less than a certain value

💡 Why This Matters

🌍 Real World

Filtering data is essential in databases to find only the records you need, like searching for books by a certain author or price range.

💼 Career

Database filtering skills are fundamental for roles like data analyst, backend developer, and database administrator to efficiently query and manage data.

1

Create the books table

Write a MySQL statement to create a table called books with these columns: id as an integer primary key, title as a text, author as a text, and price as a decimal(5,2).

MySQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with the specified columns and data types.

2

Insert book records

Insert these three books into the books table: (1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99), (2, '1984', 'George Orwell', 8.99), and (3, 'To Kill a Mockingbird', 'Harper Lee', 12.50).

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2)
);
-- Your code here to insert the book records

Need a hint?

Use a single INSERT INTO statement with multiple rows.

3

Select books by author using WHERE

Write a SELECT query to get all columns from books where the author is exactly 'George Orwell'. Use the WHERE clause with author = 'George Orwell'.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.50);

-- Your code here to select books by George Orwell

Need a hint?

Use SELECT * FROM books WHERE author = 'George Orwell' to filter by author.

4

Select books priced below 11.00

Modify the previous SELECT query to get all columns from books where the price is less than 11.00. Use the WHERE clause with price < 11.00.

MySQL

CREATE TABLE books (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  author VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, author, price) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald', 10.99),
(2, '1984', 'George Orwell', 8.99),
(3, 'To Kill a Mockingbird', 'Harper Lee', 12.50);

-- Your code here to select books priced below 11.00

Need a hint?

Use WHERE price < 11.00 to filter books cheaper than 11 dollars.