Pattern matching with LIKE and ILIKE in PostgreSQL - Mini Project: Build & Apply

Pattern Matching with LIKE and ILIKE in PostgreSQL

📖 Scenario: You are working with a small bookstore database. You want to find books based on parts of their titles or authors' names. Sometimes you want to match exactly, and sometimes you want to ignore case differences.

🎯 Goal: Build SQL queries using LIKE and ILIKE to find books by title or author with pattern matching.

📋 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 LIKE to find books with titles containing 'adventure'.

Write a query using ILIKE to find books with authors containing 'smith' ignoring case.

💡 Why This Matters

🌍 Real World

Pattern matching is useful in searching databases for partial matches, such as finding products, names, or titles that contain certain words or letters.

💼 Career

Database developers and analysts often use LIKE and ILIKE to filter data based on text patterns, improving search features and reports.

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.

PostgreSQL

-- Create the books table with id, title, and author columns
-- Your code here

Need a hint?

Use CREATE TABLE books and define the columns with their types. Remember to set id as the primary key.

2

Insert book records

Insert these exact records into the books table: (1, 'The Great Adventure', 'John Smith'), (2, 'Mystery of the Old House', 'Anna Brown'), (3, 'Adventures in Coding', 'Sam Smith'), (4, 'Cooking 101', 'Mary Johnson').

PostgreSQL

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

Need a hint?

Use a single INSERT INTO books (id, title, author) VALUES statement with all four records separated by commas.

3

Query books with titles containing 'adventure' using LIKE

Write a SQL query to select all columns from books where the title contains the word 'adventure' using the LIKE operator. Use lowercase 'adventure' exactly and the percent sign (%) wildcard before and after the word.

PostgreSQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Adventure', 'John Smith'),
(2, 'Mystery of the Old House', 'Anna Brown'),
(3, 'Adventures in Coding', 'Sam Smith'),
(4, 'Cooking 101', 'Mary Johnson');

-- Select books where title LIKE '%adventure%'
-- Your code here

Need a hint?

Use SELECT * FROM books WHERE title LIKE '%adventure%' to find titles containing 'adventure'. Remember LIKE is case sensitive.

4

Query books with authors containing 'smith' using ILIKE

Write a SQL query to select all columns from books where the author contains the word 'smith' ignoring case, using the ILIKE operator. Use the percent sign (%) wildcard before and after 'smith'.

PostgreSQL

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

INSERT INTO books (id, title, author) VALUES
(1, 'The Great Adventure', 'John Smith'),
(2, 'Mystery of the Old House', 'Anna Brown'),
(3, 'Adventures in Coding', 'Sam Smith'),
(4, 'Cooking 101', 'Mary Johnson');

SELECT * FROM books WHERE title LIKE '%adventure%';

-- Select books where author ILIKE '%smith%'
-- Your code here

Need a hint?

Use ILIKE instead of LIKE to ignore case when searching authors.