Lesson 5 of 13 · about 12 minutes · LIKE with % and _, and IS NULL — on a train timetable.
⚡ 0 XP
Step 1 of 2
Searching text with LIKE
New data! The trains table is a sample timetable for practice — made-up trains, not real railway data. There is also a stations table; we will use it later.
LIKE matches text with a pattern:
% means “any number of characters”
_ means “exactly one character”
So name LIKE '%Mail' finds names ending in Mail, name LIKE 'P%' finds names starting with P, and name LIKE '%City%' finds City anywhere. In SQLite, LIKE ignores upper/lower case for English letters.
Example
-- Sample timetable for practice
SELECT no, name, departs
FROM trains
WHERE name LIKE '%Mail';
🎯 Challenge: Find the Express trains +10 XP
Show no and name of every train whose name ends withExpress.
Try twice to unlock the solution (0/2)
query.sqlCtrl ⏎
Tables (practice database)
Results
Press Run to see your query’s rows here.
Checklist
Press Run to check your code.
○
Your rows match the expected answer
○
Uses LIKE
Lesson notes (text version)
1. Searching text with LIKE
New data! The trains table is a sample timetable for practice — made-up trains, not real railway data. There is also a stations table; we will use it later.
LIKE matches text with a pattern:
% means “any number of characters”
_ means “exactly one character”
So name LIKE '%Mail' finds names ending in Mail, name LIKE 'P%' finds names starting with P, and name LIKE '%City%' finds City anywhere. In SQLite, LIKE ignores upper/lower case for English letters.
-- Sample timetable for practice
SELECT no, name, departs
FROM trains
WHERE name LIKE '%Mail';
Challenge: Find the Express trains
Show no and name of every train whose name ends withExpress.
2. NULL: the value that is not there
Some trains have no platform announced yet. Their platform is NULL — not 0, not empty text, but “unknown”.
NULL is tricky: platform = NULL is never true, not even for NULL rows! Always use:
WHERE platform IS NULL — rows with no value
WHERE platform IS NOT NULL — rows that have a value
In the result grid, NULL is shown as NULL.
SELECT no, name, platform
FROM trains
WHERE platform IS NOT NULL;
Challenge: Platform not announced
The starter tries platform = NULL and gets zero rows. Fix it: show no and name of trains whose platform is NULL.