SQL Basics · Lesson 4 of 9

Searching Text and Ranges

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:

PatternMatches
'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
TableColumns
customersid, name, city, joined
productsid, name, category, price, stock
ordersid, 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

  1. Find the customers who live in Madrid or Prague and who joined during the year 2024.
  2. Return their name, city and joined date, sorted by joined with the earliest first.
  • The result has the columns name, city and joined
  • Only customers from Madrid or Prague who joined in 2024 are returned
  • The rows are sorted by joined, earliest first