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

DDD5 — SQL: first queries with SELECT and WHERE

Lesson DDD5 of 10 Approx 60 min Films database required
Learning intentions
  • Understand the purpose of a SQL query and the role of SELECT and FROM
  • Choose which fields should appear in query results, including when to use SELECT *
  • Use a simple WHERE clause to filter records using comparison operators
  • Combine two simple conditions using AND or OR
Success criteria
  • I can write a complete SELECT ... FROM ...; query for one table
  • I can decide whether to list specific field names or use *
  • I can write simple WHERE conditions for text, whole numbers, and decimal values
  • I can put quotation marks around text values and leave number values unquoted
  • I can use AND when both conditions must be true and OR when either condition can be true
💾
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 prior knowledge — it's fine if you're unsure.

1. In a database table, what is a field?

2. Which table in the films database stores the title, year, certificate, runtime, rating, and genre of each film?

3. The value 8.4 is stored in the rating field. Which data type is suitable?

Key vocabulary

SQL
Structured Query Language — the language used to ask a database for data and to manage records.
Query
An instruction sent to a database. In this lesson, queries retrieve records from one table.
SELECT
SQL keyword that states which fields should be displayed in the results.
FROM
SQL keyword that states which table the data should come from.
SELECT *
Shortcut meaning display all fields from the table. Useful for exploration, but not always best for a final answer.
WHERE
SQL keyword that filters records so only records matching a condition are returned.
Comparison operator
A symbol used in a condition, such as =, <>, >, <, >=, or <=.
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.

SQL: first queries with SELECT and WHERE

What a SELECT query does

A database is useful because we can ask precise questions about the data it stores. A SQL query is one of those questions written in a formal language that the database can understand. In this lesson, every query retrieves data from one table in the films database. The result is a new table on screen: it might show every record, or only the records that match a condition.

The simplest useful query has two parts: SELECT and FROM. SELECT says which fields should be displayed. FROM says which table contains those fields. SQL keywords are often written in capital letters because it makes queries easier to read, although MySQL will still understand them in lower case.

SELECT title, year FROM film;

This query displays the title and year fields for every record in the film table. The semicolon at the end tells DataGrip that the query is finished.

SELECT query — every part labelled SELECT title, year FROM film ; No WHERE means: return every record SELECT Lists fields to display Field names, comma-separated must match exact spelling in table FROM — names the table Table name film ; ends the query (required in DataGrip)

Choosing fields carefully

When an exam question asks you to display particular fields, list only those field names after SELECT, separated by commas. For example, SELECT title, rating, genre displays exactly three fields. The field names must match the database spelling, so runtime_mins is correct but runtime is not.

The shortcut SELECT * means "display every field". It is very useful when exploring a table for the first time, because it lets you see the field names and sample values. However, if a question asks for only title and rating, using * would display extra fields and would not be the best answer.

SELECT * FROM director;

Filtering records with WHERE

A WHERE clause filters records. It comes after FROM and contains a condition that must be true for a record to appear. A condition usually compares a field to a value. For text values, put the value in quotation marks. For number values, do not use quotation marks.

SELECT title, certificate FROM film WHERE certificate = "12A";

The query above displays only films whose certificate field equals "12A". Because certificate is a text field, the value is quoted. The next query uses a number field, so the value is not quoted:

SELECT title, year FROM film WHERE year > 2015;
WHERE works as a filter — only matching records pass through film table (52 records) all records, all years WHERE condition year > 2015 Result (matching records only) only films from 2016+ Records where year ≤ 2015 do not appear in the result

Comparison operators

The operator in a condition decides how the field is compared to the value. The most common operators are = for equal to, <> for not equal to, > for greater than, < for less than, >= for greater than or equal to, and <= for less than or equal to. Choose the operator that matches the wording of the question. "More than 8.0" means > 8.0; "at least 8.0" means >= 8.0.

Simple AND and OR

You can filter using two simple conditions. Use AND when both conditions must be true. For example, a film must be both a drama and rated at least 8.0:

SELECT title, genre, rating FROM film WHERE genre = "Drama" AND rating >= 8.0;

Use OR when either condition can be true. For example, the next query displays films that are either comedy films or animation films:

SELECT title, genre FROM film WHERE genre = "Comedy" OR genre = "Animation";

In this first SQL lesson, keep conditions simple: one table, one or two conditions, and clear field names. Later lessons will build your confidence with more detailed filtering.

Worked examples

Example 1 — Display specific fields

Question: Display the title, year, and genre for every film.

1
The question says to display three fields: title, year, and genre.
2
The data is stored in the film table, so the query uses FROM film.
3
No filter is needed, because the question asks for every film.
SELECT title, year, genre FROM film;
Example 2 — Filter using a text value

Question: Display the title and certificate of all films with certificate 15.

1
Display only title and certificate, so list those fields after SELECT.
2
The records come from the film table.
3
certificate is a text field, so the value is written as "15".
SELECT title, certificate FROM film WHERE certificate = "15";
Example 3 — Filter using a number comparison

Question: Display the title, year, and rating of films made after 2015.

1
The required fields are title, year, and rating.
2
The phrase "after 2015" means year > 2015.
3
year is a number field, so 2015 is not placed in quotation marks.
SELECT title, year, rating FROM film WHERE year > 2015;
Example 4 — Two simple conditions

Question: Display the title and rating of drama films rated 8.0 or above.

1
The result should show title and rating.
2
Both conditions must be true: the film must be a drama, and its rating must be at least 8.0.
3
Use AND. Quote the text value "Drama", but do not quote the decimal value 8.0.
SELECT title, rating FROM film WHERE genre = "Drama" AND rating >= 8.0;
Now you try

Write a SQL query for this request:

Display the title, year, and genre of all films that are either horror films or western films.

Answer the following:

  1. Which fields should go after SELECT?
  2. Which table should go after FROM?
  3. Should the two conditions be combined with AND or OR?
  4. Write the complete SQL query.
  1. title, year, and genre
  2. film
  3. Use OR, because a film can be horror or western. It does not need to be both.
  4. SELECT title, year, genre FROM film WHERE genre = "Horror" OR genre = "Western";
Common mistakes
✕
Putting the table name after SELECT. SELECT film FROM title; reverses the structure. Field names go after SELECT; the table name goes after FROM.
✕
Forgetting quotes around text values. Text values need quotation marks, such as WHERE genre = "Sci-Fi". Number values do not: WHERE rating > 8.0.
✕
Using AND when OR is needed. genre = "Comedy" AND genre = "Animation" will not find films, because one record cannot have both genre values at the same time. Use OR when either value is acceptable.
✕
Using SELECT * when the question asks for named fields. * displays every field. If the question asks for title and rating, list only those two fields.
Exam tip

In SQL questions, read the wording in this order: what fields should be displayed? Then which table contains those fields? Then which records should be included? This maps directly to SELECT, FROM, then WHERE.

If the value is text, quote it. If the value is a number, do not quote it. A small syntax error can lose the mark even when your idea is right, so check the final semicolon, commas between field names, and the spelling of every field.

Task Set

Questions 1–5 are auto-checked. Questions 6–9 are self-marked — write your answer, then reveal the model answer to check your work.

1. Which SQL keyword states which fields should appear in the results? TYPE 1

2. What does SELECT * mean? TYPE 1

3. Which condition correctly finds films with genre Sci-Fi? TYPE 1

4. Which operator means "not equal to" in these SQL queries? TYPE 1

5. A query needs films where genre is Action and rating is more than 8.0. Which word combines the conditions? TYPE 1

6. Write a SQL query to display the title, year, and rating for every film. TYPE 2

SELECT title, year, rating FROM film;

7. Write a SQL query to display the title and runtime_mins of films with a runtime greater than 150 minutes. TYPE 2

SELECT title, runtime_mins FROM film WHERE runtime_mins > 150;

8. Practical: In DataGrip, run a query that displays every field for every record in the director table. Paste your SQL and record how many director records are returned. TYPE 3

SELECT * FROM director;
The director table contains 15 records.

9. Practical: In DataGrip, write and run a query to display title, genre, and rating for films that are "Action" and have a rating of at least 8.0. Paste your SQL and record how many records are returned. TYPE 3

SELECT title, genre, rating FROM film WHERE genre = "Action" AND rating >= 8.0;
This returns 5 records: Batman Begins, The Dark Knight, Gladiator, Terminator 2: Judgment Day, and Kill Bill: Vol. 1.
Teacher notes — Shift+T to hide

Suggested timing: 60 minutes. Warm up + vocabulary 10 min; live query modelling 20 min; worked examples 10 min; now you try 5 min; task set 15 min.

Practical setup: Pupils need Laragon running and the films database available in DataGrip. Use a query console connected to the films database.

Key misconception to address: Text values need quotation marks but field names do not. Keep returning to the pattern WHERE field = "text value" and compare it with WHERE year > 2015.

Live demo suggestion: Start with SELECT * FROM film;, then replace * with two named fields. Add one WHERE condition, then change only the operator/value so pupils see the query structure staying stable.

Extension question: Ask pupils to write two different queries that use OR: one using the genre field and one using the certificate field.

SQA command words covered: "write", "identify", "describe". Emphasise that a SQL "write" answer needs the full query, not just the condition.