SQL Queries and Relational Databases

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.

1 / 34
volgende
Slide 1: Slide
newEditorComputingUpper Secondary (Key Stage 4)GCSE

In deze les zitten 34 slides, met interactieve quizzen en tekstslides.

time-iconLesduur is: 55 min
Dit is niet oké

Onderdelen in deze les

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?

A

Name

B

Year

C

StudentID

D

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.

A relational database stores data in linked
. One
is a row, and one
is a column. A
queries
record
relationship
table
filter

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?

A

Sorts results

B

Filters records

C

Names columns

D

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.

In SQL,
chooses the columns to show and
names the table.
filters the records, and
COUNT
SUM
UPDATE
FIELD

Correct answers in order: SELECT, FROM, WHERE, ORDER BY.

Which query finds orders above £50 and sorts by TotalCost?

A

SELECT OrderID, TotalCost FROM Orders WHERE TotalCost > 50 ORDER BY TotalCost;

B

SELECT Orders FROM TotalCost WHERE OrderID > 50 ORDER BY TotalCost;

C

SELECT OrderID, TotalCost WHERE Orders > 50 FROM TotalCost ORDER BY Orders;

D

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?

A

All columns

B

A total value

C

A single number

D

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.

Aggregate queries summarise data.
counts matching records, while
adds values in a numeric field. To understand a full query, first identify the
ORDER BY
SELECT
field
record

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