SQL Conditions: AND, OR, NOT, BETWEEN, LIKE & IN

🎯 Syllabus & Goals 3 min

Cambridge 9.1 · SQL scripts to query data Paper 2 · Algorithms, Programming and Logic

By the end of this lesson you can:

1. Combine conditions with AND, OR and NOT, using brackets where needed.
2. Use BETWEEN for a range, IN for a list and LIKE for a pattern.
3. Predict the exact output of a query that uses these operators.

Textbook: Chapter 9, §9.1.4 (pp. 346–348)

Recap / Warm-Up 5 min

Last lesson: SELECT picks fields, FROM the table, WHERE filters rows, ORDER BY sorts. Today's table is FILM again.

Quick starter

How many records does WHERE Copies >= 5 keep?

🧠 Key Concept 14 min

1 · AND, OR, NOT

AND — Sci-Fi films shorter than 130 minutes:

SELECT Title, Minutes
FROM FILM
WHERE Genre = 'Sci-Fi' AND Minutes < 130;

OR — dramas, or any film with 7 or more copies:

SELECT Title, Genre, Copies
FROM FILM
WHERE Genre = 'Drama' OR Copies >= 7;

2 · BETWEEN — a range

BETWEEN low AND high keeps values from low to high. Both ends are included.

3 · IN — a list of values

Genre IN ('Animation', 'Comedy') is a short way to write Genre = 'Animation' OR Genre = 'Comedy'.

4 · LIKE — a pattern

LIKE matches text against a pattern built with wildcards:

5 · NOT

NOT reverses the condition that follows it. WHERE NOT Genre = 'Comedy' gives the same rows as WHERE Genre <> 'Comedy'. It also combines with IN, LIKE and BETWEEN:

SELECT Title, Genre
FROM FILM
WHERE NOT Genre IN ('Comedy', 'Drama', 'Sci-Fi');

Worked Example 12 min

(a) BETWEEN — "films priced from 2.00 to 4.00"

1. SELECT Title, Price
2. FROM FILM
3. WHERE Price BETWEEN 2.00 AND 4.00;Price is real, so no quotes. Both limits are included.
4. Test each price: 3.99 ✓, 2.99 ✓, 5.49 ✗, 1.99 ✗, 4.49 ✗, 3.49 ✓, 4.99 ✗, 1.49 ✗, 3.99 ✓, 2.49 ✓.Keep the ticks, in table order.

SELECT Title, Price
FROM FILM
WHERE Price BETWEEN 2.00 AND 4.00;

(b) LIKE — "films whose title starts with S"

1. SELECT FilmID, Title
2. FROM FILM
3. WHERE Title LIKE 'S%';S must be first; % then matches whatever follows, even nothing.

SELECT FilmID, Title
FROM FILM
WHERE Title LIKE 'S%';

(c) IN — "all animations and comedies"

SELECT Title, Genre
FROM FILM
WHERE Genre IN ('Animation', 'Comedy');

(d) Why brackets matter

Goal: comedies or animations, but only those under 4.00. Compare these two queries.

SELECT Title, Genre, Price
FROM FILM
WHERE Genre = 'Comedy'
  OR Genre = 'Animation'
  AND Price < 4.00;

SELECT Title, Genre, Price
FROM FILM
WHERE (Genre = 'Comedy'
  OR Genre = 'Animation')
  AND Price < 4.00;

1. SQL does AND before OR. So the first query means: Comedy, or (Animation and under 4.00).Pizza Party is a comedy, so it passes even though it costs 4.99.
2. Brackets force the OR to be worked out first. The price test then applies to both genres.Pizza Party now fails the price test and drops out.

Try It Yourself 12 min

📝 Exam Practice 10 min

A running club records a fun run in the table RACE. Questions 1–3 use this table.

SELECT FirstName, TimeMins
FROM RACE
WHERE Distance = 5 AND TimeMins < 25;

SELECT RunnerID, FirstName
FROM RACE
WHERE Club IN ('Hilltop', 'Lakeside')
  AND NOT AgeGroup = 'U14';

SELECT FirstName, ................
FROM ................
WHERE TimeMins ................ 25 ................ 50
ORDER BY ................;

Recap & Key Terms 3 min

AND needs every condition true; OR needs at least one; NOT reverses one. Use brackets when mixing them. BETWEEN includes both ends, IN tests a list, LIKE matches a pattern.

AND

Specifies multiple conditions that must all be true.

OR

Specifies multiple conditions where one or more must be true.

NOT

Specifies a condition that must be false.

BETWEEN

Tests for a value in a range between two values, both included.

IN

Tests a field against a list of several values.

LIKE

Searches for a pattern; % stands for any number of characters, _ for exactly one.

Homework 1 min

Task (≤ 15 min): Using the RACE table, show the output of this query and explain in one sentence what '%e%' matches.

SELECT FirstName, Club
FROM RACE
WHERE FirstName LIKE '%e%' AND Distance = 10;