Relational Databases and SQL
This GCSE computing lesson explores SQL Queries and Relational Databases. Students understand tables, records, fields, primary keys and relationships, then use SELECT, WHERE, ORDER BY and related SQL commands to retrieve data. It supports KS4 classroom teaching, independent study and exam revision using clear, subject-specific vocabulary.
Relational Databases and SQL
This GCSE computing lesson explores SQL Queries and Relational Databases. Students understand tables, records, fields, primary keys and relationships, then use SELECT, WHERE, ORDER BY and related SQL commands to retrieve data. It supports KS4 classroom teaching, independent study and exam revision using clear, subject-specific vocabulary.
SQL Basics, KS4 Computer Science, GCSE Computer Science, SQL, Structured Query Language, Relational Databases, SELECT, FROM, WHERE, Tables, Queries, Primary Key, Foreign Key, Database Management, MCQs, Open Questions, Polls, Emoji Polls, Exit Tickets
Learning goal
I can explain how relational databases are structured and write and interpret basic SQL queries to retrieve and filter data.
Deze slide heeft geen instructies
What words do yu assocaite with the concept of a database?
Use this to surface prior knowledge and misconceptions. Likely answers may include table, data, rows, columns, code, query, spreadsheet, filter or sort.
What is a relational database?
A relational database stores data in tables.
Each table focuses on one type of thing, such as customers, books or orders.
The tables are linked so data can be connected without repeating it unnecessarily.
Stress that 'relational' refers to relationships between tables, not just storing rows and columns.
Tables, records and fields
A table stores data about one topic
A record is one row in the table
A field is one column in the table
Each field stores one type of value, such as a name, date or number
You can compare a table to a spreadsheet, but note that databases are designed for querying and linking data, not just viewing it.
Example table
A school table might include:
StudentID
Name
TutorGroup
Year
Each row would represent one student.
Use this as a concrete example before introducing keys. Point out that StudentID is a better identifier than name because names can repeat.
Primary keys
A primary key is a field that uniquely identifies each record in a table.
Examples include StudentID, CustomerID or BookID.
A primary key must not be duplicated for different records.
If helpful, mention that primary keys are often numbers or codes because these are easier to keep unique than names.
Foreign keys and relationships
A foreign key is a field in one table that matches the primary key in another table.
This creates a relationship between the tables.
For example, an Orders table can use CustomerID to link each order to a customer.
Clarify that the foreign key does not need to be unique, because one customer may appear in many orders.
Why use linked tables?
Linked tables help to:
reduce repeated data
keep data more consistent
update information in one place
answer questions across related data
A simple contrast helps here: without links, customer details might need to be typed again for every order, increasing errors.
Which field is the best primary key for a student table?
Name
Year
StudentID
TutorGroup
Tests understanding that a primary key must uniquely identify each record. Names, years and tutor groups can repeat.
Key database terms
A relational database stores data in linked . One is a row, and one is a column. A identifies each record uniquely, while a foreign key links one table to another.
Correct answers in order: tables, record, field, primary key.
Why are linked tables better than repeating the same data?
Students should explain that linked tables reduce repeated data, improve consistency, make updates easier because information is changed in one place, and help answer questions across related data.
What is SQL?
SQL stands for Structured Query Language.
It is used to ask questions about data stored in a database.
A query can retrieve, filter, sort or summarise records.
Pronunciation note if needed: many people say 'sequel', while others say each letter separately.
The basic query pattern
Most simple queries follow this structure:
`SELECT` what you want to see
`FROM` which table
`WHERE` which records to include
This is the core pattern for the rest of the lesson. Keep the wording consistent so students can parse later examples more easily.
Selecting all columns
```sql
SELECT *
FROM Students;
```
The `` means all columns*.
This query returns every field from the Students table.
Mention that `*` is useful for testing, but selecting only the columns needed is often clearer and more efficient.
Selecting specific columns
```sql
SELECT Name, Year
FROM Students;
```
This query returns only the Name and Year columns.
It hides fields that are not needed.
Point out the comma-separated list and that column names must match fields in the table.
Filtering with WHERE
```sql
SELECT Title, Author
FROM Books
WHERE Genre = 'Science Fiction';
```
`WHERE` limits the results to records that match a condition.
Remind students that text values are usually written in quotes in SQL examples.
Comparison operators
Common operators in `WHERE` clauses:
`=` equal to
`>` greater than
`<` less than
`>=` greater than or equal to
`<=` less than or equal to
`<>` not equal to
If you give quick examples orally, use familiar fields such as marks, prices or years to avoid adding complexity to the slide.
Combining conditions
`AND` means both conditions must be true.
`OR` means either condition can be true.
```sql
SELECT Name
FROM Students
WHERE Year = 11 AND TutorGroup = '11A';
```
A common misconception is mixing up AND and OR. You may want to restate the result set aloud: only students who are in Year 11 and also in 11A.
Sorting results
`ORDER BY` sorts the results of a query.
```sql
SELECT Title, YearPublished
FROM Books
ORDER BY YearPublished;
```
This sorts books by YearPublished, usually in ascending order.
If useful, mention that descending order is often written with DESC, but keep the main focus on basic ORDER BY.
Example: customer orders
```sql
SELECT OrderID, TotalCost
FROM Orders
WHERE TotalCost > 50
ORDER BY TotalCost;
```
This finds orders above £50 and sorts them by cost.
Use this to show how clauses stack: SELECT columns, FROM table, WHERE filter, ORDER BY sort.
What does WHERE do in an SQL query?
Sorts results
Filters records
Names columns
Chooses a table
Distractors map to ORDER BY, SELECT and FROM.
Reading a basic SQL query
In SQL, chooses the columns to show and names the table. filters the records, and sorts the final results. You can also use AND or OR to combine conditions.
Correct answers in order: SELECT, FROM, WHERE, ORDER BY.
Which query finds orders above £50 and sorts by TotalCost?
SELECT OrderID, TotalCost FROM Orders WHERE TotalCost > 50 ORDER BY TotalCost;
SELECT Orders FROM TotalCost WHERE OrderID > 50 ORDER BY TotalCost;
SELECT OrderID, TotalCost WHERE Orders > 50 FROM TotalCost ORDER BY Orders;
FROM Orders SELECT OrderID, TotalCost ORDER BY TotalCost WHERE TotalCost > 50;
Checks clause order and purpose together using the lesson's customer orders example.
Aggregate queries
Aggregate queries calculate a single result from many records.
Two useful examples are:
`COUNT` to count records
`SUM` to add values in a numeric field
Keep this introductory: the lesson only needs simple aggregates, not grouping.
Using COUNT
```sql
SELECT COUNT(*)
FROM Books
WHERE Genre = 'Mystery';
```
This returns the number of mystery books in the table.
Explain that the result is a single number, not a list of records.
Using SUM
```sql
SELECT SUM(TotalCost)
FROM Orders
WHERE CustomerID = 104;
```
This adds the TotalCost values for all orders made by customer 104.
Reinforce that SUM only works with numeric fields.
Interpreting a query
Read SQL one clause at a time:
`FROM` identifies the table
`SELECT` shows the output columns
`WHERE` filters records
`ORDER BY` sorts the final results
Although SQL is written SELECT first, students often interpret it more successfully by asking which table is used, then what is shown, then how it is filtered and sorted.
What does COUNT(*) return?
All columns
A total value
A single number
A sorted list
Reinforces that aggregate queries return one result, not many records.
Aggregate queries
Aggregate queries summarise data. counts matching records, while adds values in a numeric field. To understand a full query, first identify the , then the output columns, then any filter or sort.
Correct answers in order: COUNT, SUM, table.
How would you explain a query step by step?
Students should mention the table, selected columns, any WHERE condition, any ORDER BY sort, and whether the result is a list of records or a single number.
How confident do you feel in explaining about SQL queries and Databases?
Deze slide heeft geen instructies
Glossary
relational database - a database that stores data in linked tables
table - a collection of data about one topic
record - one row in a table
field - one column in a table
primary key - a field that uniquely identifies each record
foreign key - a field that links one table to another
relationship - the connection between linked tables
Deze slide heeft geen instructies
Glossary
SQL - Structured Query Language, used to query databases
SELECT - chooses the columns to show
FROM - names the table to query
WHERE - filters records using a condition
ORDER BY - sorts the query results
COUNT - returns the number of matching records
SUM - adds values in a numeric field
Deze slide heeft geen instructies