Pattern matching with LIKE, % and _
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which WHERE clause finds every student whose last name starts with S?
2. Which pattern matches last names that end in son, like Jackson or Wilson?
3. Which pattern finds product names that contain the letters at anywhere inside?
4. In a LIKE pattern, what does the underscore _ stand for?
5. Which pattern matches Ava and Eva but not Elva?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1WHERE lastName LIKE 'S%' | AThe = operator compares exact text, so the % is not a wildcard. Use LIKE to match a pattern. | |
| 2WHERE name LIKE '%at%' | BWHERE lastName LIKE '%son' | |
| 3Find last names that end in son by putting % before the ending | C* is not a wildcard in SQL LIKE. Use % to match any run of characters, so the pattern should be 'S%'. | |
| 4The _ wildcard matches exactly one character, so this finds three-letter first names starting with L | DFinds students whose last name starts with S, followed by any number of other characters | |
| 5WHERE lastName LIKE 'S*' | EFinds names that contain the letters 'at' anywhere, such as Water Bottle or Calculator | |
| 6WHERE lastName = 'S%' | FWHERE firstName LIKE 'L__' |
C
Definitions
WHERE lastName LIKE 'S%'
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT * FROM students
WHERE lastName ____ 'S%';
2.
SELECT firstName, lastName
FROM students
WHERE lastName LIKE 'S_';
3.
SELECT * FROM students
WHERE lastName LIKE '_son';
4.
SELECT name FROM products
WHERE name LIKE '%at_';
5.
SELECT * FROM students
WHERE firstName LIKE 'Li_m';
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT firstName, lastName
B
FROM students
C
WHERE lastName LIKE 'S*';
Question 2
A
SELECT firstName, lastName
B
FROM students
C
WHERE yearLevel = 9
D
AND lastName LIKE 'son%';
Question 3
A
SELECT name FROM products
B
WHERE name LIKE %at%
C
ORDER BY name;
Question 4
A
SELECT firstName, lastName,
B
FROM students
C
WHERE firstName LIKE 'Z_e';
Question 5
A
SELECT firstName
B
FROM students
C
WHERE yearLevel = 8
D
AND firstName = 'L___';
Answer Key
Pattern matching with LIKE, % and _
Section A: Quick Questions
Q1
A — WHERE lastName LIKE 'S%'
Q2
A — LIKE '%son'
Q3
B — LIKE '%at%'
Q4
A — Exactly one character
Q5
B — LIKE '_va'
Section B: Match the Pairs
Q1
WHERE lastName LIKE 'S%' → Finds students whose last name starts with S, followed by any number of other characters
Q2
WHERE name LIKE '%at%' → Finds names that contain the letters 'at' anywhere, such as Water Bottle or Calculator
Q3
Find last names that end in son by putting % before the ending → WHERE lastName LIKE '%son'
Q4
The _ wildcard matches exactly one character, so this finds three-letter first names starting with L → WHERE firstName LIKE 'L__'
Q5
WHERE lastName LIKE 'S*' → * is not a wildcard in SQL LIKE. Use % to match any run of characters, so the pattern should be 'S%'.
Q6
WHERE lastName = 'S%' → The = operator compares exact text, so the % is not a wildcard. Use LIKE to match a pattern.
Section C: Definitions
Q1
WHERE lastName LIKE 'S%' — LIKE matches text by pattern in a WHERE clause. % stands for any run of characters and _ for exactly one, so 'S%' finds values starting with S and '%son' finds values ending in son.Section D: Fill in the Blanks
Q1
SELECT * FROM students
WHERE lastName LIKE 'S%';Q2
SELECT firstName, lastName
FROM students
WHERE lastName LIKE 'S%';Q3
SELECT * FROM students
WHERE lastName LIKE '%son';Q4
SELECT name FROM products
WHERE name LIKE '%at%';Q5
SELECT * FROM students
WHERE firstName LIKE 'Li_m';Section E: Spot the Error
Q1
C — SQL LIKE uses % for any run of characters, not *. With 'S*' the * is just a star character, so no names match. It should be 'S%'.
Q2
D — 'son%' finds names that START with son. To find names that END in son, put the % first: '%son'.
Q3
B — The LIKE pattern is text, so it must be wrapped in quotes. It should be WHERE name LIKE '%at%'.
Q4
A — There is a comma after the last field in the SELECT list, but no more fields follow. Remove it: SELECT firstName, lastName.
Q5
D — The = operator compares exact text, so the underscores are treated as real characters. Use LIKE so each _ stands for exactly one character.