LIKE pattern matching in MySQL - Mini Project: Build & Apply

Using LIKE Pattern Matching in MySQL

📖 Scenario: You are managing a small bookstore database. You want to find books based on parts of their titles or authors' names.

🎯 Goal: Build a simple MySQL query using the LIKE operator to find books with titles or authors matching specific patterns.

📋 What You'll Learn

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

Insert exactly three books with given titles and authors.

Create a variable to hold a pattern string for searching.

Write a SELECT query using LIKE to find books matching the pattern in the title.

Add a condition to also match the author name using LIKE.

💡 Why This Matters

🌍 Real World

Searching for books or products by partial names is common in online stores and libraries.

💼 Career

Knowing how to use LIKE helps in writing flexible search queries in databases for many applications.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer primary key), title (varchar 100), and author (varchar 100). Then insert these three rows exactly: (1, 'The Great Gatsby', 'F. Scott Fitzgerald'), (2, 'Great Expectations', 'Charles Dickens'), and (3, 'Gone with the Wind', 'Margaret Mitchell').

MySQL

-- Create the books table and insert three rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Define a pattern variable for searching

Create a variable called @pattern and set it to the string '%Great%' to search for titles or authors containing the word 'Great'.

MySQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, 'Great Expectations', 'Charles Dickens'),
(3, 'Gone with the Wind', 'Margaret Mitchell');

-- Set the pattern variable
-- Your code here

Need a hint?

Use SET @pattern = '%Great%'; to create the variable.

3

Write a SELECT query using LIKE on the title

Write a SELECT statement to get all columns from books where the title matches the pattern stored in @pattern using the LIKE operator.

MySQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, 'Great Expectations', 'Charles Dickens'),
(3, 'Gone with the Wind', 'Margaret Mitchell');

SET @pattern = '%Great%';

-- Select books where title matches the pattern
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title LIKE @pattern; to find matching titles.

4

Extend the query to match author names too

Modify the SELECT query to also return rows where the author matches the pattern stored in @pattern. Use OR to combine the conditions on title and author with LIKE.

MySQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Gatsby', 'F. Scott Fitzgerald'),
(2, 'Great Expectations', 'Charles Dickens'),
(3, 'Gone with the Wind', 'Margaret Mitchell');

SET @pattern = '%Great%';

-- Modify the query to also match author names
-- Your code here

Need a hint?

Use OR author LIKE @pattern to include authors in the search.