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

SQL SELECT — Querying a Database

Lesson DDD3 of 10 Approx 75 min Handball database required
Learning intentions
  • Understand what SQL is and what the SELECT statement does
  • Write SELECT queries using FROM and WHERE clauses
  • Use comparison operators (=, <, >) in WHERE conditions
  • Combine conditions using AND and OR operators
  • Sort results using ORDER BY with up to two fields
  • Write equi-joins to retrieve data from two linked tables
Success criteria
  • I can write a SELECT query to retrieve specific fields from one table
  • I can add a WHERE clause to filter results based on conditions
  • I can combine multiple conditions using AND and OR
  • I can use comparison operators to find records matching a range (e.g. goals > 20)
  • I can sort results using ORDER BY in ascending or descending order
  • I can write an equi-join between two tables using primary and foreign keys
Before you start

Open the Handball database:

  1. Open DB Browser for SQLite
  2. Click File → Open Database
  3. Navigate to the databases folder and select handball.db (relative path: ../databases/handball.db)
  4. Click the Execute SQL tab at the top
  5. You're ready to start the practical tasks!

💡 Keep DB Browser open throughout the lesson — you'll be writing and running SQL queries in every practical task.

📊
Handball database required for this lesson

Download handball.sql and import it into DB Browser for SQLite before the warm-up. You'll need this database open for all the practical tasks. If you haven't set up DB Browser yet, follow the setup guide first.

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

Answer before the lesson begins. These check your knowledge of database structure.

1. What does SQL stand for?

2. Which SQL keyword do you use to choose which fields to display in the results?

Key vocabulary

SQL
Structured Query Language — a standard language for retrieving and manipulating data in relational databases.
Query
A question asked of a database, usually written in SQL, that retrieves specific data matching conditions you set.
SELECT
The SQL keyword that specifies which fields (columns) to retrieve. Comes at the start of every query.
FROM
The SQL keyword that specifies which table to search for data.
WHERE
The SQL keyword that sets conditions to filter results — only records matching the conditions are returned.
Condition
A statement that is either true or false, used in WHERE to decide which records to include (e.g. goalsScored > 20).
AND
Combines two conditions in WHERE — both must be true for a record to be included in results.
OR
Combines two conditions in WHERE — either condition can be true for a record to be included in results.
ORDER BY
The SQL keyword that sorts results — can sort by one or more fields in ascending (ASC) or descending (DESC) order.
Equi-join
A query that combines rows from two tables by matching a primary key to a foreign key (e.g. WHERE Team.teamName = Player.teamName).

The Handball Database

This lesson uses the Scottish Handball League database with two linked tables. You will refer back to this throughout.

Player
playerID firstname surname dateOfBirth position goalsScored teamName FK
810JEJackEdwards03/05/1994Left back37Clyde Flyers
Team
teamName PK town leagueDivision district trainingVenue contactPerson emailAddress
Clyde FlyersGreenock1WestBurnside Sports CentreChris Blackclydeflyers@handball.mail.uk

SQL SELECT — Querying a Database

What is SQL?

SQL (Structured Query Language) is a standard language for retrieving and managing data in relational databases. When you run a query in DB Browser for SQLite, you are writing SQL. Every time a web app searches for your name, every time a bank finds your account, every time a shop searches its inventory — they are all using SQL queries to get data from databases. In this course we focus on the SELECT statement, which is the most common SQL command.

The basic SELECT structure

Every SELECT query follows the same pattern:

SELECT fieldName1, fieldName2
FROM tableName
WHERE fieldName = value;

Line 1 (SELECT): Tells the database which fields (columns) to show. You can list specific fields separated by commas, or use * to select all fields.

Line 2 (FROM): Tells the database which table to search in.

Line 3 (WHERE): Sets conditions to filter the results. Only records where the condition is true are returned. (The WHERE clause is optional — if you omit it, all records are returned.)

Text values vs numbers in WHERE

This distinction is crucial: when you search for text (like a player's position), the value must be in double quotes. When you search for numbers, no quotes are used.

SELECT firstname, surname
FROM Player
WHERE position = "Left back";
SELECT firstname, surname
FROM Player
WHERE goalsScored > 20;

Comparison operators in WHERE

The WHERE clause uses comparison operators to match records. Here are the main ones:

Operator Meaning Example
=Equal toposition = "Left back"
<Less thangoalsScored < 10
>Greater thangoalsScored > 20
<=Less than or equal togoalsScored <= 25
>=Greater than or equal togoalsScored >= 30
<>Not equal toposition <> "Goalkeeper"
💻

Practical Task 1 of 5 — Running your first SELECT query

What to do: Write and run SELECT queries to retrieve player data, starting simple then adding a WHERE clause.

Step 1: Type and run this query to display all players:
SELECT firstname, surname, position
FROM Player;
Step 2: Modify the query to show only players who scored more than 20 goals:
SELECT firstname, surname, position
FROM Player
WHERE goalsScored > 20;

📝 Capture your evidence here

✅ Evidence checklist:
  • Your SQL queries pasted above
  • Screenshots of both result sets captured above
  • Ready to export to PDF for your evidence portfolio

AND and OR — combining conditions

Often you need to filter by more than one condition. Use AND when both conditions must be true. Use OR when either condition can be true.

AND example: Find all players who play in the West district AND scored more than 10 goals.

SELECT firstname, surname
FROM Player, Team
WHERE Team.district = "West"
AND Player.goalsScored > 10;

OR example: Find all players who play as a centre OR a left wing.

SELECT firstname, surname
FROM Player
WHERE position = "Centre"
OR position = "Left wing";
💻

Practical Task 2 of 5 — AND and OR conditions

What to do: Write queries using OR and AND operators to combine multiple conditions.

Query A: Display firstname, surname and position for all players who play as "Left back" OR "right wing":
SELECT firstname, surname, position
FROM Player
WHERE position = "Left back"
OR position = "right wing";
Query B: Find players who play as "Left back" AND have scored more than 15 goals:
SELECT firstname, surname, position, goalsScored
FROM Player
WHERE position = "Left back"
AND goalsScored > 15;

📝 Capture your evidence here

✅ Evidence checklist:
  • Both SQL queries pasted above
  • Screenshots of both result sets captured above

ORDER BY — sorting results

By default, results appear in the order stored in the database. To sort, use ORDER BY at the end of your query. You can sort by up to two fields at N5. ASC means ascending (A→Z, 0→9); DESC means descending (Z→A, 9→0). If you don't specify, ASC is the default.

SELECT firstname, surname, goalsScored
FROM Player
ORDER BY goalsScored DESC, surname ASC;

This sorts players by goals scored (highest first). Where two players have the same number of goals, those records are sorted alphabetically by surname (A→Z).

💻

Practical Task 3 of 5 — Sorting results with ORDER BY

What to do: Write a query that sorts player data by two fields.

Task: Display firstname, surname and goalsScored for all players, sorted by goalsScored (highest first), then by surname (A→Z):
SELECT firstname, surname, goalsScored
FROM Player
ORDER BY goalsScored DESC, surname ASC;

Notice how the results are first sorted by highest goals scored. Where two players have the same score, those two are sorted alphabetically by surname.

📝 Capture your evidence here

✅ Evidence checklist:
  • Your ORDER BY query pasted above
  • Screenshot showing the two-field sort results

Equi-joins — combining data from two tables

This is the most difficult part of this lesson. When you need data from two linked tables, you must join them using the primary key = foreign key pattern. Without the join, you get incorrect results.

The join pattern: In your WHERE clause, match the primary key from one table to the foreign key from the other.

SELECT fieldName1, fieldName2
FROM Table1, Table2
WHERE Table1.primaryKey = Table2.foreignKey
AND otherCondition = value;

Example from the SQA specification: Find the firstname, surname, and town for all players in the West district.

SELECT firstname, surname, town
FROM Team, Player
WHERE Team.teamName = Player.teamName
AND district = "West";

Why the join is needed: The town field exists in the Team table. The firstname and surname fields exist in the Player table. To get all three in one query, you list both tables in FROM and link them in WHERE using Team.teamName = Player.teamName. This tells the database to only return player records whose teamName matches a team record's teamName.

💻

Practical Task 4 of 5 — Joining two tables (CRITICAL)

What to do: Master equi-joins by writing and running queries that combine data from Player and Team tables.

Query A: Run this equi-join query exactly as shown. Find all players in Greenock:
SELECT firstname, surname, town
FROM Team, Player
WHERE Team.teamName = Player.teamName
AND town = "Greenock";

Notice the WHERE clause has two parts: the join (Team.teamName = Player.teamName) AND the filter (town = "Greenock").

Query B — Write your own: Find firstname, surname, and district for all players in the "West" district. (Hint: Use the same join pattern, but filter by district = "West" instead):
SELECT firstname, surname, district
FROM Player, Team
WHERE Player.teamName = Team.teamName
AND district = "West";

📝 Capture your evidence here

✅ Evidence checklist:
  • Both equi-join queries pasted above
  • Screenshots of both result sets captured above
  • ⚠ CRITICAL: Both queries include the equi-join WHERE clause

Worked examples

Example 1 — Simple SELECT from one table

Question: Display the firstname, surname, and position of all players.

SELECT firstname, surname, position
FROM Player;
1
SELECT firstname, surname, position — we want three fields from the results.
2
FROM Player — all the data comes from the Player table.
3
No WHERE clause, so all records are returned (every player in the table).
Example 2 — SELECT with WHERE and a comparison operator

Question: Find the firstname, surname, and goalsScored for all players who scored more than 20 goals.

SELECT firstname, surname, goalsScored
FROM Player
WHERE goalsScored > 20;
1
SELECT firstname, surname, goalsScored — we want three fields.
2
FROM Player — search in the Player table.
3
WHERE goalsScored > 20 — only include records where goals scored is greater than 20. Note: no quotes around the number.
Example 3 — SELECT with AND combining two conditions

Question: Find players who play as a Left back AND scored more than 10 goals.

SELECT firstname, surname, position, goalsScored
FROM Player
WHERE position = "Left back"
AND goalsScored > 10;
1
WHERE position = "Left back" — position is text, so the value is in double quotes.
2
AND goalsScored > 10 — the second condition is added with AND. Both conditions must be true.
3
Records are only included if they are Left backs AND have scored more than 10 goals. A Left back with 5 goals would not appear.
Example 4 — SELECT with ORDER BY

Question: List all players sorted by goalsScored (highest first), then by surname (A→Z).

SELECT firstname, surname, goalsScored
FROM Player
ORDER BY goalsScored DESC, surname ASC;
1
ORDER BY goalsScored DESC — sort by goals scored in descending order (highest first).
2
, surname ASC — for records with the same goals score, sort those by surname in ascending order (A→Z). ASC is the default but shown here for clarity.
Example 5 — Equi-join combining two tables

Question: Find the firstname, surname, and town for all players in the West district.

SELECT firstname, surname, town
FROM Team, Player
WHERE Team.teamName = Player.teamName
AND district = "West";
1
FROM Team, Player — both table names are listed. The database will combine rows from both tables.
2
WHERE Team.teamName = Player.teamName — this is the equi-join. It matches Team.teamName (primary key) to Player.teamName (foreign key), creating the link between the two tables.
3
AND district = "West" — after the join, we filter: only include records where the district is West.
4
The query returns only players whose team is in the West district, along with their town.
Now you try

Write a SQL query to: Display firstname, surname, and goalsScored for all players who are NOT goalkeepers, sorted by goalsScored in descending order.

SELECT firstname, surname, goalsScored
FROM Player
WHERE position <> "Goalkeeper"
ORDER BY goalsScored DESC;

Explanation: We use <> (not equal to) to exclude goalkeepers. The ORDER BY goalsScored DESC sorts from highest to lowest goals scored.

Common mistakes
✕
Forgetting the equi-join when querying two tables. This is the #1 exam error. If your FROM clause lists two tables (e.g. FROM Team, Player), your WHERE clause MUST include the join line (WHERE Team.teamName = Player.teamName) before any other conditions. Without it, the database returns nonsense — every combination of every player with every team.
✕
Mixing up quotes and no quotes in WHERE. Text values need double quotes: position = "Left back". Numbers never use quotes: goalsScored > 20. Getting this wrong usually causes a database error.
✕
Using AND when you mean OR. Remember: AND means both conditions must be true. OR means at least one can be true. If you write WHERE position = "Left wing" AND position = "Centre", you will get zero results (a player cannot be both positions). You need OR.
✕
Forgetting table names in joins. When you join two tables in the WHERE clause, always use the full name: Team.teamName = Player.teamName, not just teamName = teamName. Otherwise the database does not know which table's teamName you mean.
Exam tip

The #1 reason candidates lose marks: forgetting the equi-join line. When the question asks for data from two tables, you MUST include the join in the WHERE clause. Mark schemes consistently award zero if this line is missing, even if the rest of the query is correct.

Check your WHERE clause: if it lists two tables in FROM, the first line of WHERE must be the equi-join. Only then should you add filtering conditions with AND.

Source: SQA Course Reports 2023, 2024, 2025 — consistently identify missing equi-joins as a major cause of lost marks in database questions.

💻

Practical Task 5 of 5 — Consolidation Challenge

What to do: Write a single query from scratch (no hints) that combines everything you've learned today.

Challenge: Write a query to display the firstname, surname, goalsScored and town of all players who scored more than 10 goals, sorted by goalsScored in descending order.

Think about what you need:

  • Which fields do you need? (firstname, surname, goalsScored, town)
  • Which tables? (Player has firstname/surname/goalsScored; Team has town)
  • Do you need a join? (Yes — because town is in Team)
  • What's the filter? (goalsScored > 10)
  • What's the sort? (goalsScored DESC)

Expected solution structure:
SELECT firstname, surname, goalsScored, town
FROM ... , ...
WHERE ... = ...
AND goalsScored > 10
ORDER BY ...

📝 Capture your evidence here

✅ Evidence checklist:
  • Your completed query pasted above
  • Screenshot of results (players with 10+ goals, sorted by goals DESC)
  • ✨ This is your evidence for today's SQL lesson!
Task Set

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

1. Which SQL keyword specifies which fields to display in the query results? TYPE 1

2. What does ORDER BY goalsScored DESC do? TYPE 1

3. In the WHERE clause, how do you write a condition to find players with a position of "Left back"? TYPE 1

4. What is the primary purpose of an equi-join in a SQL query? TYPE 1

5. Write a SQL query to display firstname, surname, and position for all players who scored more than 15 goals. TYPE 2

SELECT firstname, surname, position
FROM Player
WHERE goalsScored > 15;

6. Write a SQL query to find all players who play in position "Centre" OR "Left wing", displaying their surname and position, sorted by surname A→Z. TYPE 2

SELECT surname, position
FROM Player
WHERE position = "Centre"
OR position = "Left wing"
ORDER BY surname ASC;

Note: We use OR because a player can be in ONE position or the other (not both). If we used AND, we would get zero results.

7. Write a SQL query to find the firstname, surname, and goalsScored for all players in league division 1, sorted by goalsScored (highest first). This requires joining the Player and Team tables. TYPE 2

SELECT firstname, surname, goalsScored
FROM Player, Team
WHERE Player.teamName = Team.teamName
AND leagueDivision = 1
ORDER BY goalsScored DESC;

Key point: The WHERE clause has TWO parts. First, the equi-join (Player.teamName = Team.teamName). Second, the filter condition (leagueDivision = 1), joined with AND.

8. Identify the error in this query. Explain what is wrong and how to fix it.
SELECT firstname, surname FROM Team, Player WHERE district = "West"; TYPE 2

Error: The query lists two tables in FROM (Team and Player) but does not include the equi-join in WHERE. Without WHERE Team.teamName = Player.teamName, the database returns every combination of every player with every team — nonsense results.

Fix: Add the equi-join as the first condition in WHERE: SELECT firstname, surname
FROM Team, Player
WHERE Team.teamName = Player.teamName
AND district = "West";

9. Write a SQL query to display the firstname, surname, position, and town for all players who scored more than 25 goals AND play in the West district. TYPE 3

SELECT firstname, surname, position, town
FROM Player, Team
WHERE Player.teamName = Team.teamName
AND goalsScored > 25
AND district = "West";

10. Using the Handball database, write a query to find all teams (displaying teamName and town) in league division 2 that are in the North district. Write the SQL query and describe what data it will retrieve. TYPE 3

SELECT teamName, town
FROM Team
WHERE leagueDivision = 2
AND district = "North";

Description: This query retrieves the names and towns of all teams that compete in league division 2 AND are located in the North district. No equi-join is needed because we are only selecting from one table (Team). The query returns only records where BOTH conditions are true.

Teacher notes — Shift+T to hide

Suggested timing: 75 minutes total. Warm up + vocabulary 10 min; notes with live query demos 25 min; worked examples 15 min; now you try 5 min; task set 20 min.

Prerequisite: Pupils should have DDD1 and DDD2 mastered (understand tables, records, fields, primary keys, foreign keys, and ERDs).

Key misconception to address: The equi-join. This is consistently the #1 error in exam papers. Many candidates omit the join line entirely when querying two tables, resulting in zero marks. Use live demos in DB Browser to show the difference: run the query without the join (catastrophic results) versus with it (correct results). Make this visually dramatic.

Live demo checklist: (1) Open the Handball database in DB Browser. (2) Run a simple SELECT with WHERE to show filtering. (3) Show ORDER BY with one field, then two fields. (4) Most importantly: demonstrate a two-table query WITHOUT the join (show nonsense output), then add the join line and re-run (show correct output). This visual contrast is powerful.

Common error patterns to watch for: (1) Forgetting quotes around text values. (2) Using AND instead of OR. (3) Omitting the equi-join. (4) Forgetting table names in the join (writing teamName = teamName instead of Team.teamName = Player.teamName).

Extension questions: (1) Can you write a three-field ORDER BY? (Not at N5, but good to explain the limitation.) (2) What happens if you use SELECT * in a join? (You get all fields from both tables.) (3) Why can't you use WHERE teamName = Player.teamName instead of full names? (Ambiguity — the database doesn't know which table's teamName you mean.)

SQA commands covered: "Write a query...", "Identify the error...", "Explain...". Remind pupils that "explain" needs a reason, not just the code.