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

DDD6 — SQL: filtering with conditions

Lesson DDD6 of 10 Approx 60 min Films database required
Learning intentions
  • Build confidence writing WHERE conditions for one-table SQL queries
  • Choose comparison operators that match wording such as "more than", "at least", and "not equal to"
  • Handle text values and number values correctly in SQL conditions
  • Debug common SQL syntax errors by reading the query and the error message carefully
Success criteria
  • I can write complete SELECT ... FROM ... WHERE ...; queries using the film table
  • I can use =, <>, >, <, >=, and <= accurately
  • I can decide when to use AND and when to use OR
  • I can spot missing commas, missing quotes, wrong table names, and wrong data types in broken SQL
💾
Database file required for this lesson

Use films.sql in DataGrip. If the films database is not already available, download the file, open it in DataGrip, and run the whole script.

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

Answer before the lesson begins. These check your memory of DDD5 — it's fine if you're unsure.

1. Which clause filters records so only matching rows appear?

2. Which condition correctly finds films with certificate 15?

3. A query needs films that are Comedy or Animation. Which word combines the conditions?

Key vocabulary

WHERE
SQL keyword that filters records so only records matching a condition are returned.
Condition
A test inside a query, usually comparing a field with a value, such as rating >= 8.0.
Comparison operator
A symbol used in a condition, such as =, <>, >, <, >=, or <=.
Text value
A word or label stored in a text field. Text values need quotation marks in SQL, for example "Drama".
Number value
A whole number or decimal value stored in a number field. Number values are not placed in quotation marks.
AND
Combines conditions where both must be true for a record to appear.
OR
Combines conditions where at least one condition must be true for a record to appear.
Syntax error
An error caused by writing SQL in a way MySQL cannot understand, such as missing a comma or quotation mark.

Filtering with conditions

Filtering is about precision

In DDD5, you learned that a SQL query can display selected fields from a table and use WHERE to include only certain records. This lesson slows that idea down and gives you more practice. Most National 5 SQL questions are not trying to trick you with long code; they are checking whether you can translate a short English request into a precise condition.

The basic structure stays steady. First choose the fields for SELECT. Then choose the table for FROM. Then write the filter after WHERE. In this lesson, all queries use one table only: film. That means you should not add other table names or try to connect tables together. The skill is choosing the correct condition.

SELECT title, rating FROM film WHERE rating >= 8.0;

This query asks for the title and rating fields, from the film table, but only for records where the rating is at least 8.0.

Choosing the right comparison operator

Small wording changes matter. More than means >. Less than means <. At least means >=, because the boundary value is included. No more than means <=. Not equal to means <>. If a question says "films rated 8.0 or above", the value 8.0 must be included, so the condition is rating >= 8.0, not rating > 8.0.

Comparison operators — watch boundary words carefully Operator Means Example Boundary included? = equal to rating = 8.0 Yes — exact match <> not equal to rating <> 8.0 No — excludes that value > more than / after year > 2015 No — 2015 is excluded >= at least / from rating >= 8.0 Yes — 8.0 IS included < / <= less than / no more than year <= 2010 < excludes; <= includes

Text values and number values

A common SQL error is putting quotation marks in the wrong place. Field names are not quoted in these lessons: write genre, not "genre". Text values are quoted: write "Sci-Fi", "PG", or "15". Number values are not quoted: write year > 2015, runtime_mins <= 120, or rating < 7.5.

The certificate "15" looks like a number, but in the films database it is a text value because certificates can also be "PG", "U", "12A", and "18". Always think about the field's data type, not just the characters you can see.

Quoting rules: text vs number values ✓ Text values — use quotes VARCHAR fields: genre, title, certificate WHERE genre = "Sci-Fi" WHERE certificate = "PG" WHERE certificate = "15" "15" is text — stored as VARCHAR ✓ Number values — no quotes INT/DECIMAL fields: year, rating, runtime WHERE year > 2015 WHERE rating >= 8.0 WHERE runtime_mins <= 120 No quotes — MySQL compares numerically

Using AND and OR without overcomplicating it

Use AND when every condition must be true for the same record. For example, if a film must be both a Sci-Fi film and released after 2010, use AND. A film that is Sci-Fi but from 1979 fails the second condition. A film from 2019 that is Drama fails the first condition.

SELECT title, genre, year FROM film WHERE genre = "Sci-Fi" AND year > 2010;

Use OR when either condition is acceptable. For example, if the question asks for films with certificate "U" or "PG", a record only has to match one of those certificate values to appear.

SELECT title, certificate FROM film WHERE certificate = "U" OR certificate = "PG";

Reading error messages

When DataGrip shows a red error message, do not delete everything and start again. Read from the top of the query and check the structure. Are the selected fields separated by commas? Is the table called film, not films? Are text values inside quotation marks? Is there a comparison operator between the field and the value? Error messages often point near the problem, but not always exactly at it, so use them as clues.

Debugging SQL is a normal part of working with databases. The best habit is to fix one issue at a time and run the query again. If the error changes, you are making progress. If the query runs but the result is empty, the syntax may be correct but the logic might be wrong, such as using AND where OR was needed.

Worked examples

Example 1 — At least means include the boundary

Question: Display the title and rating of films rated at least 8.5.

1
The result should display title and rating.
2
The records come from the film table.
3
"At least 8.5" means 8.5 is included, so use >=, not >.
SELECT title, rating FROM film WHERE rating >= 8.5;
Example 2 — Text value that looks like a number

Question: Display the title, certificate, and genre of films with certificate 12A.

1
The required fields are title, certificate, and genre.
2
certificate is a text field, because values can include letters such as PG and 12A.
3
Quote the text value: "12A".
SELECT title, certificate, genre FROM film WHERE certificate = "12A";
Example 3 — Two conditions with AND

Question: Display the title, genre, and year of Sci-Fi films released after 2010.

1
The film must satisfy both parts: it must be "Sci-Fi" and the year must be greater than 2010.
2
The text value "Sci-Fi" is quoted. The number value 2010 is not quoted.
3
Use AND because both conditions must be true.
SELECT title, genre, year FROM film WHERE genre = "Sci-Fi" AND year > 2010;
Example 4 — Debugging broken syntax

Broken query:

SELECT title rating FROM films WHERE genre = Drama;
1
title rating is missing a comma between field names.
2
The table is called film, not films.
3
Drama is a text value, so it needs quotation marks.
SELECT title, rating FROM film WHERE genre = "Drama";
Now you try

A pupil writes this query to find Drama films released after 2000:

SELECT title year FROM films WHERE genre = Drama AND year > "2000";

Answer the following:

  1. Find two syntax errors in the query.
  2. Find one data type mistake in the condition.
  3. Write the corrected query.
  1. SELECT title year is missing a comma, and the table should be film, not films. Drama also needs quotation marks.
  2. year is a number field, so 2000 should not be in quotation marks.
  3. SELECT title, year FROM film WHERE genre = "Drama" AND year > 2000;
Common mistakes
✕
Using the wrong boundary operator. "More than 8.0" is > 8.0, but "8.0 or above" is >= 8.0. Read the phrase before choosing the operator.
✕
Quoting numbers but not text. In this course, text values such as "Horror" and "PG" need quotes. Number values such as 2019 and 7.5 do not.
✕
Forgetting commas after SELECT field names. Write SELECT title, year, rating. Without commas, MySQL cannot separate the field names properly.
✕
Using AND for alternatives. certificate = "U" AND certificate = "PG" asks one record to have two different certificates at once. Use OR when either value is acceptable.
Exam tip

When asked to write a SQL query, underline the comparison phrase before writing the answer: more than, less than, at least, no more than, equal to, or not equal to. Then decide whether the value is text or a number. This two-second check prevents most filtering errors.

If you are fixing broken SQL, work in this order: commas between fields, correct table name, quotation marks around text values, no quotation marks around number values, then the correct AND or OR.

Task Set

Questions 1-5 are auto-checked. Questions 6-7 are self-marked SQL writing and debugging questions. Questions 8-10 are practical DataGrip filtering tasks.

1. Which condition means "rating is at least 8.0"? TYPE 1

2. Which value is written correctly for the text field genre? TYPE 1

3. Which word is best for "films that are Horror and have a rating below 7.0"? TYPE 1

4. Which condition finds films whose certificate is not "15"? TYPE 1

5. What is wrong with SELECT title year FROM film;? TYPE 1

6. Write a SQL query to display the title, year, and certificate of films with certificate "15" and rating of at least 8.0. TYPE 2

SELECT title, year, certificate FROM film WHERE certificate = "15" AND rating >= 8.0;
This returns 10 records in the films database.

7. Debug this query. It should display title and rating for Drama films released after 2000. TYPE 2

SELECT title rating FROM films WHERE genre = Drama AND year > "2000";
SELECT title, rating FROM film WHERE genre = "Drama" AND year > 2000;
Fixes: comma after title, table name film, quotes around "Drama", and no quotes around the number 2000. This returns 5 records.

8. Practical: In DataGrip, run a query that displays title and rating for films with a rating below 7.5. Paste your SQL and record how many films are returned. TYPE 3

SELECT title, rating FROM film WHERE rating < 7.5;
This returns 9 records.

9. Practical: In DataGrip, write and run a query to display title and certificate for films with certificate "U" or certificate "PG". Paste your SQL and record how many records are returned. TYPE 3

SELECT title, certificate FROM film WHERE certificate = "U" OR certificate = "PG";
This returns 18 records.

10. Practical debugging: Type this broken query into DataGrip, run it, read the error message, then fix it. It should find Horror films with rating no more than 7.0. Paste the corrected SQL and record the result count. TYPE 3

SELECT title, rating FROM films WHERE genre = Horror AND rating =< 7.0;
SELECT title, rating FROM film WHERE genre = "Horror" AND rating <= 7.0;
Fixes: table name film, quotes around "Horror", and the operator <=. This returns 2 records: Us and Nope.
Teacher notes — Shift+T to hide

Suggested timing: 60 minutes. Warm up + vocabulary 10 min; filtering notes and live demo 20 min; worked examples 10 min; now you try 5 min; DataGrip task set 15 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: Certificates such as "15" look numeric but are text values. Keep comparing certificate = "15" with rating >= 8.0.

Live demo suggestion: Start with a working one-condition query. Deliberately remove a comma, change film to films, and remove quotes around a text value. Ask pupils to predict the error before running each version.

Extension question: Ask pupils to write two different queries for "family-friendly" certificates: one using OR for "U" and "PG", and one adding a rating condition with AND.

SQA command words covered: "write", "identify", "describe", "explain". Emphasise that debugging questions need both the corrected SQL and a short reason for each fix.