SQL LIKE with the % wildcard
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query finds students whose first name starts with the letter A?
2. What does LIKE '%s' find in a text field?
3. Which pattern finds product names that contain the letters at anywhere?
4. True or false: the % wildcard can stand for any number of characters.
5. Which query is written correctly?
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%' | AFinds students whose last name starts with S, because % stands for any characters after the S | |
| 2WHERE name LIKE '%s' | BThe pattern is missing its quote marks. It should be LIKE '%at%'. | |
| 3Find products whose name contains the letters at anywhere in the text | CSELECT firstName FROM students WHERE firstName LIKE 'L%'; | |
| 4Find students whose first name starts with L | DFinds products whose name ends with s, because % stands for any characters before the s | |
| 5SELECT * FROM students WHERE firstName = 'A%'; | ESELECT name FROM products WHERE name LIKE '%at%'; | |
| 6SELECT name FROM products WHERE name LIKE %at%; | FThe = operator looks for the exact text A%, so the wildcard does not work. It should be LIKE 'A%'. |
C
Definitions
LIKE '%at%'
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT firstName FROM students
WHERE firstName ____ 'A%';
2.
SELECT name FROM products
WHERE name LIKE 'P_';
3.
SELECT name FROM products
WHERE name LIKE '_s';
4.
SELECT name FROM products
WHERE ____ LIKE '%at%';
5.
SELECT name FROM products
_____ name LIKE 'P%';
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT name, price
B
FROM products
C
WHERE stock > 0
D
AND name LIKE 'P';
Question 2
A
SELECT name price
B
FROM products
C
WHERE name LIKE '%ball'
D
ORDER BY price;
Question 3
A
SELECT firstName,
B
FROM students
C
WHERE firstName LIKE 'S%';
Question 4
A
SELECT name FROM products
B
WHERE name IS '%s'
C
ORDER BY name;
Question 5
A
SELECT firstName, house
B
FROM students
C
WHERE house = 'R%'
D
ORDER BY firstName;
Answer Key
SQL LIKE with the % wildcard
Section A: Quick Questions
Q1
A — SELECT * FROM students WHERE firstName LIKE 'A%';
Q2
A — Text that ends with s
Q3
B — LIKE '%at%'
Q4
B — True
Q5
A — SELECT name FROM products WHERE name LIKE 'P%';
Section B: Match the Pairs
Q1
WHERE lastName LIKE 'S%' → Finds students whose last name starts with S, because % stands for any characters after the S
Q2
WHERE name LIKE '%s' → Finds products whose name ends with s, because % stands for any characters before the s
Q3
Find products whose name contains the letters at anywhere in the text → SELECT name FROM products WHERE name LIKE '%at%';
Q4
Find students whose first name starts with L → SELECT firstName FROM students WHERE firstName LIKE 'L%';
Q5
SELECT * FROM students WHERE firstName = 'A%'; → The = operator looks for the exact text A%, so the wildcard does not work. It should be LIKE 'A%'.
Q6
SELECT name FROM products WHERE name LIKE %at%; → The pattern is missing its quote marks. It should be LIKE '%at%'.
Section C: Definitions
Q1
LIKE '%at%' — LIKE in a WHERE clause matches part of a text value. The % wildcard stands for any characters, so '%at%' finds text containing at, 'S%' starts with S and '%s' ends with s.Section D: Fill in the Blanks
Q1
SELECT firstName FROM students
WHERE firstName LIKE 'A%';Q2
SELECT name FROM products
WHERE name LIKE 'P%';Q3
SELECT name FROM products
WHERE name LIKE '%s';Q4
SELECT name FROM products
WHERE name LIKE '%at%';Q5
SELECT name FROM products
WHERE name LIKE 'P%';Section E: Spot the Error
Q1
D — Without the % wildcard, LIKE 'P' only matches a name that is exactly P. To find names that start with P, write LIKE 'P%'.
Q2
A — The fields in a SELECT list must be separated by a comma. It should be SELECT name, price.
Q3
A — There must be no comma after the last field in the SELECT list. A trailing comma makes the query fail. It should be SELECT firstName.
Q4
B — Pattern matching needs the LIKE keyword. IS cannot be used with the % wildcard. It should be WHERE name LIKE '%s'.
Q5
C — The = operator looks for the exact text R%, so the % is not treated as a wildcard. Use LIKE: WHERE house LIKE 'R%'.