Filtering rows with WHERE
WHERE keeps only the rows that pass a test. Combine tests with AND and OR, and match patterns, lists and ranges with LIKE, IN and BETWEEN.
Keep the rows that pass
WHERE goes after FROM and tests every row. Rows where the test is true stay; the rest are dropped.
SQL
SELECT title, year
FROM songs
WHERE year >= 2023;Comparisons: =, != (or <>), <, >, <=, >=. Text goes in single quotes, WHERE genre = 'pop', and = on text is exact: 'Pop' and 'pop' are different values.
Combine tests and match patterns
ANDneeds both tests true.ORneeds at least one.ANDis worked out beforeOR, like times before plus in maths.a OR b AND cmeansa OR (b AND c). When you mix them, add brackets.LIKEmatches a pattern:%is any run of characters,_is exactly one.title LIKE '%hour%'finds "Golden Hour" and "Blue Hour" (in SQLite, LIKE ignores case for English letters).IN (...)is a short way to write several=joined byOR:country IN ('UK', 'US').BETWEEN a AND bincludes both ends:year BETWEEN 2020 AND 2022keeps 2020, 2021 and 2022.
Run it: mid-length songs from two years
Run it, then change one thing and run it again.
Tables artists, songs, listeners, plays
artists 8 rows, 4 columns
| id | name | country | genre |
|---|---|---|---|
| 1 | Nova Lane | UK | pop |
| 2 | The Tidewater | US | indie |
| 3 | Kaito Mori | JP | electronic |
| 4 | Amara Osei | GH | afrobeats |
| 5 | Lumen Choir | SE | pop |
| 6 | Rust & Velvet | UK | rock |
| 7 | Pixel Hearts | KR | pop |
| 8 | Juno Bay | AU | indie |
songs 30 rows, 5 columns
| id | title | artist_id | seconds | year |
|---|---|---|---|---|
| 1 | Neon Rain | 6 | 178 | 2022 |
| 2 | Slow Motion | 1 | 158 | 2025 |
| 3 | Paper Planes | 2 | 233 | 2023 |
| 4 | Static | 1 | 269 | 2020 |
| 5 | Golden Hour | 1 | 162 | 2022 |
| 6 | Night Bus | 7 | 157 | 2020 |
| 7 | Echoes | 2 | 281 | 2022 |
| 8 | Sugar Rush | 1 | 284 | 2019 |
| 9 | Low Tide | 4 | 289 | 2019 |
| 10 | Glass House | 7 | 152 | 2020 |
| 11 | Satellite | 1 | 282 | 2025 |
| 12 | Wildfire | 3 | 214 | 2022 |
First 12 of 30 rows. Query the table to see the rest.
listeners 10 rows, 4 columns
| id | username | age | country |
|---|---|---|---|
| 1 | maya_k | 15 | UK |
| 2 | devraj | 17 | IN |
| 3 | lucia.p | 16 | ES |
| 4 | tom_h | 14 | UK |
| 5 | ana.s | 18 | BR |
| 6 | kenji | 16 | JP |
| 7 | zoe_r | 15 | US |
| 8 | femi | 17 | NG |
| 9 | liv | 13 | SE |
| 10 | oscar.b | 16 | NULL |
plays 70 rows, 5 columns
| id | listener_id | song_id | day | skipped |
|---|---|---|---|---|
| 1 | 10 | 16 | 2026-09-01 | 0 |
| 2 | 8 | 3 | 2026-09-01 | 0 |
| 3 | 5 | 16 | 2026-09-01 | 0 |
| 4 | 2 | 2 | 2026-09-01 | 0 |
| 5 | 5 | 21 | 2026-09-01 | 0 |
| 6 | 5 | 23 | 2026-09-02 | 0 |
| 7 | 6 | 1 | 2026-09-02 | 0 |
| 8 | 6 | 6 | 2026-09-02 | 0 |
| 9 | 8 | 2 | 2026-09-02 | 1 |
| 10 | 5 | 5 | 2026-09-02 | 0 |
| 11 | 7 | 13 | 2026-09-02 | 0 |
| 12 | 2 | 6 | 2026-09-03 | 0 |
First 12 of 70 rows. Query the table to see the rest.
query.sql to run
SELECT title, year, seconds FROM songs WHERE year IN (2022, 2023) AND seconds BETWEEN 180 AND 240 AND title NOT LIKE 'The %' ORDER BY year, title;
A UK artist on the world playlist
A playlist editor wants the name and country of every pop or indie artist from outside the UK. Nova Lane, who is from the UK, keeps showing up. Fix the query.
exercise_1.sql to run
SELECT name, country FROM artists WHERE genre = 'pop' OR genre = 'indie' AND country != 'UK';