Three more operators make WHERE far more flexible.
LIKE: pattern matching
LIKE compares text with a pattern. In the pattern, % stands for any number of characters, including none:
| Pattern | Matches |
|---|---|
'B%' | text that starts with B |
'%e' | text that ends with e |
'%lamp%' | text that contains "lamp" anywhere |
SELECT name FROM products WHERE name LIKE '%Bottle%';
An underscore _ stands for exactly one character.
IN: one of a list
IN checks whether a value is in a list. It is a short way to write several OR conditions:
SELECT name, city
FROM customers
WHERE city IN ('Madrid', 'Oslo');
BETWEEN: a range
BETWEEN matches values from a lower to an upper limit, both included:
SELECT name, price
FROM products
WHERE price BETWEEN 5 AND 15;
Dates stored as text in the form 2024-03-22 (year, month, day) sort and compare correctly, so ranges work on them as well:
WHERE joined BETWEEN '2024-01-01' AND '2024-12-31'
NOT
Put NOT in front to reverse any of them: NOT LIKE, NOT IN, NOT BETWEEN.
Tables in the practice database
| Table | Columns |
|---|---|
customers | id, name, city, joined |
products | id, name, category, price, stock |
orders | id, customer_id, product_id, quantity, ordered |
Every run starts from a fresh copy of the database, so nothing you do can break it.
Your Task
- Find the customers who live in
MadridorPragueand who joined during the year 2024. - Return their
name,cityandjoineddate, sorted byjoinedwith the earliest first.
- The result has the columns
name,cityandjoined - Only customers from Madrid or Prague who joined in 2024 are returned
- The rows are sorted by
joined, earliest first
Lesson complete!
Next Lesson: Counting and Calculating