Spec & Goals 3 min
AQA Spec 3.7 · Relational databases & SQL (whole unit)
By the end of this lesson you can:
- Revise and self-check the whole of Unit 7, spotting your weak topics.
- Answer exam questions that span all of spec 3.7, for the marks on offer.
- Make a focused revision plan that targets the topics you got wrong.
Warm-Up — command words & the paper 5 min
This is the last lesson of Unit 7. First, know the exam you are sitting.
Command words tell you what to do
| Command word | What it demands |
|---|---|
| State / Identify | A word or short phrase. No explanation. |
| Define | The precise meaning of a term. |
| Write a program | Working SQL — marked on correct keywords, fields and syntax. |
| Complete | Fill the missing part of a query exactly. |
Key Concept — your Unit 7 spec checklist 14 min
Tick each spec point. If you cannot do one, that is a topic to revise tonight.
| Spec point | You should be able to… |
|---|---|
| Databases & flat files | Define a database; explain redundancy and inconsistency in flat files. |
| Relational databases | Use table, record and field correctly. |
| Keys | Define primary key and foreign key; identify them in a pair of tables. |
| SELECT … FROM | Retrieve chosen fields or all (*). |
| WHERE | Filter rows; comparison operators; AND/OR. |
| LIKE | Pattern-match with % and _. |
| ORDER BY | Sort results ASC/DESC. |
| INSERT INTO | Add a record with correct fields, values and quoting. |
The Student table (for the questions below)
| StudentID | Name | Form | Town | Mark |
|---|---|---|---|---|
| 101 | Aisyah | 10A | Ipoh | 78 |
| 102 | Wei Jie | 10B | Penang | 65 |
| 103 | Priya | 10A | Ipoh | 91 |
| 104 | Arjun | 11C | Johor Bahru | 54 |
| 105 | Mei Ling | 11C | Penang | 88 |
| 106 | Hafiz | 10B | Kuala Lumpur | 72 |
Worked Example — a full multi-part question 12 min
Here is one question with three parts. We earn the marks aloud.
Part (a) — Define a foreign key. [1 mark]
A field that refers to the primary key of another table, linking the two (1).
Part (b) — Write a query for the name and mark of students who scored over 75, highest first. [3 marks]
SELECT Name, Mark FROM Student WHERE Mark > 75 ORDER BY Mark DESC;
Result: Priya (91), Mei Ling (88), Aisyah (78). Marks: SELECT fields (1); WHERE condition (1); ORDER BY DESC (1).
Part (c) — Write an SQL statement to add student 110, "Devi", 10A, Ipoh, 80. [2 marks]
INSERT INTO Student (StudentID, Name, Form, Town, Mark) VALUES (110, 'Devi', '10A', 'Ipoh', 80);
Marks: INSERT INTO + fields (1); VALUES in order, correct quoting (1).
Try It Yourself — timed mini-paper 12 min
Give yourself 12 minutes. Write full answers; check them with your teacher.
State what is meant by a record and a field. [2 marks]
Write a query for the names of all students in form 10A. [2 marks]
Write a query for students whose town starts with "P", sorted by mark, highest first. [3 marks]
Explain how a relational database reduces data redundancy compared with a flat file. [2 marks]
Write an SQL statement to add a new student of your choice. [2 marks]
📝 Exam Practice 10 min
These questions span the whole of Unit 7. Match your answer to the command word and the marks.
Define the term primary key.
Mark scheme
- A field that uniquely identifies each record in a table (1).
Explain one problem with storing all data in a single flat file.
Mark scheme
- Data is repeated/redundant across many rows (1).
- Leading to wasted storage / inconsistency when copies are not all updated (1).
Write a query to display the Name and Form of every student from Ipoh, sorted alphabetically by name.
Mark scheme
SELECT Name, Form FROM Student(1).WHERE Town = 'Ipoh'(1).ORDER BY Name;(1).
State the result of SELECT Name FROM Student WHERE Form LIKE '11_';
Mark scheme
- Arjun and Mei Ling (both in 11C) (1).
Write an SQL statement to add a new student: ID 111, name Kavitha, form 10B, town Penang, mark 84.
Mark scheme
INSERT INTO Student (StudentID, Name, Form, Town, Mark)(1).VALUES (111, 'Kavitha', '10B', 'Penang', 84);(1).- Correct quoting — text quoted, numbers not (1).
Recap & Key Terms 3 min
Use your mini-paper marks to plan tonight's revision.
- Vague on redundancy/inconsistency? Redo CS-L7-01.
- Muddled table/record/field? Redo CS-L7-02.
- Unsure on keys? Redo CS-L7-03 — primary vs foreign.
- SELECT shaky? Redo CS-L7-04.
- WHERE / AND / OR wrong? Redo CS-L7-05.
- Wildcards confusing? Redo CS-L7-06 — % vs _.
- Sorting slips? Redo CS-L7-07 — ASC vs DESC.
- INSERT errors? Redo CS-L7-08 — quoting and field/value match.
- Struggling to combine clauses? Redo CS-L7-09.
- Data redundancy / inconsistency
- Data repeated unnecessarily / repeated copies that disagree.
- Primary key / foreign key
- Uniquely identifies a record / links to another table's primary key.
- SELECT … FROM … WHERE … ORDER BY
- Choose columns, table, filter rows, sort results.
- LIKE / wildcards
- Pattern-match text with
%(any) and_(one). - INSERT INTO … VALUES
- Add a new record with values matching the fields.
Homework 1 min
Task (≤ 15 min): Revisit your weakest topic from the mini-paper. Open that lesson and redo its Exam Practice section.
Bring one question you still find hard to the next session, so we can work through it together.