← Course home N5 Computing Science  ·  James Gillespie's
DDD  ·  Database Design & Development

SQL: Complex Conditions and Brackets

Lesson DDD8 of 10 Approx 60 min Films database required
Learning intentions
  • Use brackets to control mixed AND / OR conditions so a query returns exactly the right records.
  • Predict how a query behaves differently with and without brackets.
  • [Extension] Use LIKE and the % wildcard to search for text patterns.
Success criteria
  • I can explain why brackets change query results when AND and OR are mixed.
  • I can add brackets to a WHERE clause so it matches the intended logic.
  • [Extension] I can choose the correct LIKE pattern for starts-with, ends-with, and contains searches.
📊
Films database required for this lesson

Use the same films.sql database from the previous SQL lessons. Open DataGrip, start Laragon if needed, and create a query console for the films database.

↓ Download films.sql
Warm up — what do you already know?

Answer before the lesson begins. These recap filtering, ranges, and sorting from earlier SQL lessons.

1. Which condition finds films with the exact genre "Drama"?

2. What does rating >= 8.0 AND certificate = "PG" mean?

3. Which clause belongs last in this query?

Key vocabulary

Brackets
Used to group conditions so SQL checks them in the intended order, especially when AND and OR are mixed. Core N5 spec content — may be examined directly.
LIKE [Extension]
SQL operator used in a WHERE clause to match a text pattern instead of one exact value. Not required for N5 exam.
Wildcard [Extension]
A symbol that stands for unknown characters. % means any number of characters in a LIKE pattern.
Starts / ends / contains [Extension]
"B%" = starts with B. "%on" = ends with on. "%the%" = contains the. All use LIKE with % wildcard.

Complex conditions and brackets

Why brackets matter with AND and OR

This is the core topic of this lesson — and it was directly examined in the 2024 SQA paper. Brackets become important when a query mixes AND and OR. Without brackets, SQL uses its own default precedence (which makes AND evaluate before OR), and this may not group the conditions the way the question intended.

The safest habit at N5 is: put brackets around the alternatives that belong together. For example, if a user wants all Comedy or Drama films with a rating above 8.0:

SELECT title, genre, rating
FROM film
WHERE (genre = "Comedy" OR genre = "Drama")
AND rating >= 8.0;

The brackets tell SQL that the genre choice is one grouped unit. Without brackets:

WHERE genre = "Comedy" OR genre = "Drama" AND rating >= 8.0

This gives a different result — SQL evaluates the AND first, so it finds Drama films with rating ≥ 8.0, then adds all Comedy films regardless of rating. One pair of brackets makes a large difference to which records appear.

AND evaluates before OR — use brackets to control the order ✗ Without brackets WHERE genre="Comedy" OR genre="Drama" AND rating>=8 SQL evaluates AND first: Drama AND rating ≥ 8 (evaluated first) OR ALL Comedy any rating Wrong — Comedy films included regardless of rating ✓ With brackets WHERE ( genre="Comedy" OR genre="Drama" ) AND r≥8 Brackets force OR to evaluate first: (Comedy OR Drama) evaluated as one group AND rating ≥ 8 Correct — both genres filtered by rating ≥ 8.0
Extension — LIKE and wildcard pattern matching (not required for N5 exam)

LIKE is a SQL operator that matches a text pattern. It uses the % wildcard, which means any number of characters. The position of % sets the search type:

PatternMeaningExample matches
"The%"Starts with TheThe Dark Knight, The Martian
"%on"Ends with onInception, Lost in Translation
"%of%"Contains of anywhereRaiders of the Lost Ark
SELECT title, year
FROM film
WHERE title LIKE "Blade%";

LIKE does not appear in the N5 spec appendix or in any past paper question. You will encounter it in DataGrip practicals and it is useful to understand, but you do not need to write it in the exam. In MySQL, LIKE text matching is usually case-insensitive — "%the%" will also match The.

Worked examples

Example 1 — starts with search

Task: Display the title and year for films whose title starts with Blade.

1
The field being searched is title, so the condition starts title LIKE.
2
Starts with Blade uses "Blade%". The wildcard is at the end because anything may come after Blade.
3
Display only the requested fields: title and year.
SELECT title, year
FROM film
WHERE title LIKE "Blade%";

Verified result: 2 records are returned — Blade Runner and Blade Runner 2049.

Example 2 — contains search with a number condition

Task: Find films whose title contains the and whose rating is at least 8.0. Display title and rating, highest rating first.

1
Contains the uses "%the%", with wildcards before and after the search text.
2
Add AND rating >= 8.0 because both the title pattern and rating condition must be true.
3
Add ORDER BY rating DESC because the highest-rated matching films should appear first.
SELECT title, rating
FROM film
WHERE title LIKE "%the%"
AND rating >= 8.0
ORDER BY rating DESC, title ASC;

Verified result: 7 records are returned. The first three are The Dark Knight (9.0), The Lord of the Rings: The Return of the King (8.9), and The Lord of the Rings: The Fellowship of the Ring (8.8).

Example 3 — brackets with mixed conditions

Task: Find PG films where the title starts with B or K. Display title and certificate.

1
The certificate condition is exact: certificate = "PG".
2
The title alternatives are title LIKE "B%" or title LIKE "K%".
3
Put the title alternatives in brackets so the PG certificate applies to both starts-with searches.
SELECT title, certificate
FROM film
WHERE certificate = "PG"
AND (title LIKE "B%" OR title LIKE "K%");

Verified result: 2 records are returned — Batman and Big Fish. Without the brackets, Kill Bill: Vol. 1 and King Kong would also appear even though they are not PG.

Example 4 — prediction and debugging

Task: A pupil writes this query to find Fantasy or Sci-Fi films containing a with a rating of 8.0 or higher.

SELECT title, genre, rating
FROM film
WHERE genre = "Fantasy" OR genre = "Sci-Fi"
AND title LIKE "%a%"
AND rating >= 8.0;
1
The intended genre choice is Fantasy or Sci-Fi, so those two checks should be grouped together.
2
Add brackets around the genre alternatives before adding the title and rating conditions.
3
Now the title and rating filters apply to both genres.
SELECT title, genre, rating
FROM film
WHERE (genre = "Fantasy" OR genre = "Sci-Fi")
AND title LIKE "%a%"
AND rating >= 8.0
ORDER BY genre ASC, rating DESC;

Verified result: 6 records are returned — one Fantasy film and five Sci-Fi films.

Now you try

A teacher wants a list of Drama or Comedy films with a rating of 7.0 or higher. The list should display title, genre, and rating, sorted by rating highest first, then title A to Z.

Answer the following:

  1. Why are brackets needed in this query?
  2. Which conditions go inside the brackets?
  3. Write the complete SQL query.
  1. Brackets are needed because the query mixes AND and OR. The two genre choices must be grouped together so the rating condition applies to both genres.
  2. (genre = "Drama" OR genre = "Comedy") goes in brackets, then AND rating >= 7.0 applies to both.
  3. SELECT title, genre, rating
    FROM film
    WHERE (genre = "Drama" OR genre = "Comedy")
    AND rating >= 7.0
    ORDER BY rating DESC, title ASC;

Verified result: 14 records are returned — a mix of Drama and Comedy films all rated 7.0 or above.

Common mistakes
✕
Using = when a pattern is needed. title = "%the%" looks for the exact text %the%. Use LIKE when the pattern includes wildcards.
✕
Putting the wildcard on the wrong side. "B%" means starts with B. "%B" means ends with B. "%B%" means contains B.
✕
Forgetting brackets around OR choices. If a condition says "PG and starts with B or K", write certificate = "PG" AND (title LIKE "B%" OR title LIKE "K%").
✕
Assuming case-insensitive means typo-insensitive. MySQL may match "the" and "The", but it will not fix misspellings or missing letters.
Exam tip

If an SQL exam question gives you two alternatives (e.g. "Drama or Comedy films") alongside a second condition (e.g. "rating above 7.0"), you need brackets. Put the alternatives in brackets first: (genre = "Drama" OR genre = "Comedy"), then add AND rating > 7.0 after the brackets. This was tested in the 2024 SQA paper — a missing pair of brackets cost marks. When you see OR alongside AND in a question, add brackets around the OR group before anything else.

Task Set

Questions 1–3 are auto-checked. Questions 4–5 are self-marked questions on brackets. Questions 6–7 are practical DataGrip tasks. Questions 8–9 are Extension LIKE tasks for those who finish early.

1. A query is intended to find Action or Horror films with a rating above 8.0. Which WHERE clause is correct? TYPE 1

2. Why do we put brackets around (genre = "Comedy" OR genre = "Drama") before adding AND rating >= 7.0? TYPE 1

3. A pupil writes: WHERE genre = "Sci-Fi" OR genre = "Fantasy" AND year > 2000. What will this return? TYPE 1

4. Prediction: The intended query is "find PG or U films with rating 8.0 or higher." Explain what is wrong with this WHERE clause and write the corrected version. TYPE 2

WHERE certificate = "PG" OR certificate = "U" AND rating >= 8.0

Without brackets, SQL evaluates AND before OR. This means the query finds U-rated films with rating ≥ 8.0, then adds all PG films regardless of rating. The corrected version groups the certificate choices in brackets:

WHERE (certificate = "PG" OR certificate = "U")
AND rating >= 8.0

Verified result with corrected query: 8 records are returned — all PG or U films rated 8.0 or above.

5. Write a SQL query to display title, genre, and rating for Drama or Thriller films with a rating of 8.0 or higher. Sort by rating highest first, then title A to Z. TYPE 2

SELECT title, genre, rating
FROM film
WHERE (genre = "Drama" OR genre = "Thriller")
AND rating >= 8.0
ORDER BY rating DESC, title ASC;

Verified result: 6 records are returned including Schindler's List, The Silence of the Lambs, and Parasite.

6. Practical: In DataGrip, write and run a query to find all Comedy or Animation films released from 2010 to 2020 inclusive. Display title, genre, and year. Sort by year newest first, then title A to Z. Paste your SQL and record how many records are returned. TYPE 3

💻

Practical Task 1 of 2 — Brackets with genre choices and year range

Open a query console for the films database in DataGrip. This query needs brackets around the genre choices. Use >= AND <= for the year range.
✅ Evidence checklist:
  • Paste the SQL you ran and the number of records returned.
SELECT title, genre, year
FROM film
WHERE (genre = "Comedy" OR genre = "Animation")
AND year >= 2010 AND year <= 2020
ORDER BY year DESC, title ASC;

Verified result: 7 records are returned.

7. Practical: In DataGrip, write and run a query to find Action or Sci-Fi films rated 8.0 or higher released before 2000. Display title, genre, year, and rating. Sort by rating highest first. Paste your SQL and record the films returned. TYPE 3

💻

Practical Task 2 of 2 — Brackets, comparison, and sort combined

This query has three WHERE conditions: genre (bracketed OR), rating, and year. Make sure the brackets only wrap the genre choices.
✅ Evidence checklist:
  • Paste the SQL and list the titles returned.
SELECT title, genre, year, rating
FROM film
WHERE (genre = "Action" OR genre = "Sci-Fi")
AND rating >= 8.0
AND year < 2000
ORDER BY rating DESC;

Verified result: 5 records — Alien, Star Wars: Episode IV, The Terminator, Terminator 2: Judgment Day, and The Matrix (1999).

8. [Extension] Which LIKE pattern finds titles that end with the word Ring? TYPE 1

9. [Extension] Practical: In DataGrip, run a query to find Drama or Horror films whose title contains the letter o and whose rating is 7.5 or higher. Display title, genre, and rating. Sort by genre A to Z, then title A to Z. This uses brackets (core) and LIKE (extension). TYPE 3

SELECT title, genre, rating
FROM film
WHERE (genre = "Drama" OR genre = "Horror")
AND title LIKE "%o%"
AND rating >= 7.5
ORDER BY genre ASC, title ASC;

Verified result: 4 records — Little Women, Lost in Translation, Roma (Drama), and Get Out (Horror). Note the brackets group the genre choices; the LIKE condition filters by title pattern.

Teacher notes — Shift+T to hide

Suggested timing: 60 minutes. Warm up and vocabulary 10 min; notes and live DataGrip demo 20 min; worked examples 12 min; now you try 5 min; practical task set 13 min.

Practical setup: Pupils need Laragon running, DataGrip open, and the films database selected. Use a query console connected to the films database.

Key misconception to address: Pupils forget brackets when mixing AND and OR. This was directly examined in 2024 Q16(c)(i). Run the wrong version first — show that Comedy films all appear regardless of rating — then add brackets and show how the result changes. Making the "before and after" visible is the single most impactful teaching move in this lesson.

Live demo suggestion: Run WHERE genre = "Comedy" OR genre = "Drama" AND rating >= 8.0 and count the results. Then add brackets and rerun. Ask pupils to predict whether the count goes up or down before they see the result.

Note on LIKE: LIKE is marked as Extension throughout this lesson. It does not appear in the N5 spec appendix or in any past paper. Teach brackets with AND/OR as the core spec content. LIKE content is retained for DataGrip practical enrichment — use Q8 and Q9 for early finishers or as a homework extension. Do not let pupils spend exam prep time memorising LIKE patterns.

Extension question: Ask pupils to write a query that finds all Comedy or Drama films rated 8.0 or higher, without using LIKE. Then ask what would happen if they forgot the brackets around the genre choices.

SQA command words covered: "write", "identify", "describe", "explain", "predict", "debug". Emphasise that "write a query" requires the full SQL statement in the correct clause order.