SQL Foundations Lesson 4 of 7

SQL AND, OR, IN and LIKE

25 min, 5 exercisesOCR 2.2.3AQA 3.7.2

Builds on lessons 1 to 3. Start at lesson 1

Learn

AND, OR and brackets

One condition is rarely enough. AND needs both sides to be true, OR needs at least one:

SELECT title FROM songs WHERE genre = 'pop' AND plays > 1000;
SELECT title FROM songs WHERE genre = 'pop' OR genre = 'rock';

Here is the trap. AND is stronger than OR, the same way * beats + in maths. So this:

WHERE genre = 'pop' OR genre = 'rock' AND seconds < 200

means "all pop songs, plus rock songs under 200 seconds". If you meant "short songs that are pop or rock", add brackets: (genre = 'pop' OR genre = 'rock') AND seconds < 200. When you mix AND and OR, just always use brackets. Future you says thanks.

Learn

IN, BETWEEN and LIKE

Long OR chains get tiring, so SQL has shortcuts:

  • genre IN ('pop', 'rock', 'indie') matches any value in the list.
  • seconds BETWEEN 180 AND 240 is a range, and both ends are included.
  • NOT flips a condition: genre NOT IN ('pop').

For text patterns there is LIKE, with two wildcards:

  • % means "any number of characters, even none"
  • _ means "exactly one character"
WHERE title LIKE 'the%'     -- starts with "the"
WHERE title LIKE '%love%'   -- contains "love" anywhere

In SQLite, LIKE ignores upper and lower case for plain English letters, so '%love%' also finds Love Story. Other databases can be stricter, so don't build your whole personality around that.

GCSE exam boards
  • OCR 2.2.3 Strings, files, arrays, sub programs, SQL
  • AQA 3.7.2 SQL

GCSE Python path The GCSE lesson

Try

Run it: short pop or rock songs

The query in the editor runs against this lesson's tables (open Tables above the editor to see them). Run it, change the WHERE or the columns, run it again.

Tables songs
songs 10 rows, 6 columns
idtitleartistgenresecondsplays
1Lovesick RobotThe Toasterspop1985400
2Homework Can WaitLofi Owllofi15212000
3Thunder LunchboxGravelrock254870
4Love You Like WiFiKai Neonpop2319100
5The Final BossPixel Choirchiptune1763300
6Monday AgainGravelrock1892100
7Rainy Bus WindowLofi Owllofi245640
8Glow UpKai Neonpop16615000
9The Long GoodbyeVelvet Staticindie302450
10Skate Park SunsetVelvet Staticindie2101800
Show the SQL that built them
CREATE TABLE songs (id INTEGER PRIMARY KEY, title TEXT, artist TEXT, genre TEXT, seconds INTEGER, plays INTEGER);
INSERT INTO songs VALUES
 (1, 'Lovesick Robot', 'The Toasters', 'pop', 198, 5400),
 (2, 'Homework Can Wait', 'Lofi Owl', 'lofi', 152, 12000),
 (3, 'Thunder Lunchbox', 'Gravel', 'rock', 254, 870),
 (4, 'Love You Like WiFi', 'Kai Neon', 'pop', 231, 9100),
 (5, 'The Final Boss', 'Pixel Choir', 'chiptune', 176, 3300),
 (6, 'Monday Again', 'Gravel', 'rock', 189, 2100),
 (7, 'Rainy Bus Window', 'Lofi Owl', 'lofi', 245, 640),
 (8, 'Glow Up', 'Kai Neon', 'pop', 166, 15000),
 (9, 'The Long Goodbye', 'Velvet Static', 'indie', 302, 450),
 (10, 'Skate Park Sunset', 'Velvet Static', 'indie', 210, 1800);
example.sql to run
-- Short songs that are pop or rock (brackets matter!)
SELECT title, genre, seconds
FROM songs
WHERE (genre = 'pop' OR genre = 'rock') AND seconds < 200;

Practice

Filter by genre and length

Return the title and artist of every song whose genre is lofi or indie and that is between 150 and 250 seconds long (including exactly 150 and 250).

exercise_1.sql to run
SELECT title, artist
FROM songs
WHERE genre IN ('lofi', 'indie');

Practice

Songs that start with The

Return the title of every song whose title starts with the word The.

exercise_2.sql to run
SELECT title
FROM songs
WHERE title = 'The';

Break/fixFix the bug

A long pop song sneaks in

The playlist should have only songs under 200 seconds that are pop or lofi. A 231 second song shows up. Fix the condition.

exercise_3.sql to run
SELECT title, genre, seconds
FROM songs
WHERE genre = 'pop' OR genre = 'lofi' AND seconds < 200;

PracticePredict the output

Both ends count

Which songs does this query return? Type one title per line, in the order they appear in the table.

exercise_4.sql
SELECT title
FROM songs
WHERE seconds BETWEEN 176 AND 210;

Your turn

A playlist for revising

Build a revision playlist: the title and genre of every song that is not pop or rock, is under 4 minutes (240 seconds), and has at least 1000 plays.

On your own. Hints open after your first check.

exercise_5.sql to run
-- Your query here
SQLite