Database structure and the SQL you'll use with it. Fields, records, validation, data types and primary keys — the foundation for every query in the Querying and Aggregating Labs.
Every organisation you interact with runs on a database. Your school stores your attendance in one. Your streaming service stores what you've watched in one. When you log into a website and it "remembers" you, that's a database being queried in the background.
Topic 9 splits into two skills: first, how to design a database so it stores data cleanly (this Foundations Lab), and second, how to question a database so you can get information back out (the Querying and Aggregating Labs). This first lab tackles the foundations — the structure that makes everything else possible, and the language you'll use to talk to it.
Prerequisites: none required. If you've met data types in Topic 8 (Programming), you'll notice the database types are slightly different — that's one of the things this lab locks in.
| Term | Meaning |
|---|---|
| Database | An organised collection of data. In this syllabus, always a single-table database. |
| Table | A set of data about one type of object (e.g. students, books, storage units). Made up of fields and records. |
| Field | An individual piece of data being stored — one column in the table (e.g. LastName, Price). |
| Record | All the fields about one object — one row in the table (e.g. everything stored about Aarna Singh). |
| Validation | Automatic checks that data entered into a field is sensible and follows set rules (e.g. a range check on Age). |
| Data type | The characteristic of a piece of data that tells the database what it can hold. |
| Text (alphanumeric) | A data type storing letters, digits, symbols or a mix (e.g. Sparkle, AB12). The syllabus calls it text/alphanumeric — use this database term, not the programming term STRING. |
| Character | A data type storing exactly one letter, digit, or symbol (e.g. 'A'). |
| Integer | A data type storing a whole number, no decimal point (e.g. 23). |
| Real | A data type storing a number with a decimal point (e.g. 3.99). |
| Boolean | A data type storing one of two values, usually True/False or Yes/No. Written without quotation marks. |
| Date/Time | A data type storing a date and/or a time value. |
| Primary key | A unique field in a database used to identify one specific record. No two records can share the same primary key value. |
| SQL | Structured Query Language — a standard language used across most databases to interact with data. |
| SQL script | The SQL commands used to query a table. In this syllabus a script is usually one SELECT statement built from clauses such as SELECT, FROM, WHERE and ORDER BY. |
| Query | A request made to the database to return specific data. |
In SQL, text values go in quotation marks ("China"). Numbers and Boolean values (TRUE, FALSE) do not.
A database table is a grid. Every column is a field (one category of data). Every row is a record (one entry). That's it — that's the whole shape.
| BookID | BookName | Author |
|---|---|---|
| B01 | The Hobbit | Tolkien |
| B02 | Matilda | Dahl |
| B03 | Coraline | Gaiman |
| B04 | Wonder | Palacio |
Sanity check: this table has 3 fields and 4 records. Read down for fields, across for records.
Exam trap: examiner reports have flagged students who reverse fields and records. See Common misconceptions below.
The syllabus lists six basic data types for databases. Learn what each one stores — and whether its values go in quotation marks when they appear in an SQL query.
| Data type | What it stores | Example value | Quotes in SQL? |
|---|---|---|---|
| Text (alphanumeric) | Letters, digits and symbols, e.g. names, codes, addresses | Amsterdam | Yes: "Amsterdam" |
| Character | Exactly one character | A | See note below |
| Integer | Whole number, no decimal | 1566999 | No |
| Real | Number with a decimal point | 3.99 | No |
| Boolean | One of two values: TRUE/FALSE (or Yes/No) | TRUE | No — a common trap |
| Date/Time | A date and/or a time | 14/06/2025 | See note below |
The quotes column looks ahead to WHERE clauses in the Querying Lab. Examiner reports have flagged missing quotation marks around text values (e.g. 2023).
'A' or "A", and #14/06/2025# or "2025-06-14"). If a question needs one, copy the style used in the question.Part A — what a primary key IS. A primary key is a field whose value never repeats across records. It uniquely identifies exactly one record. Examples you'll meet in past papers: StorageID, Code, ContractNumber, BookID, StudentID.
Part B — how you CHOOSE a primary key. Ask of every field: "Could two records ever share this value?"
ItemID or Code."…because it is a unique identifier." An answer that says the field is unique and that it identifies each record is the safest way to explain why a field is the primary key.
When you define a table, you can add validation to a field. Validation checks that the data entered is sensible and follows set rules before it is stored. It cannot prove the data is correct — only that it is reasonable. These are the same checks you met in Topic 7:
| Check | Example on a database field |
|---|---|
| Range check | Age must be between 11 and 18 |
| Length check | Code must be exactly 4 characters |
| Type check | Population must be a whole number |
| Presence check | LastName cannot be left empty |
| Format check | Code must be two letters followed by two digits, e.g. AS01 |
| Check digit | The last digit of an ISBN field is calculated from the other digits |
Don't mix up: visual checks and double entry are verification (checking data was copied correctly), not validation.
SQL = Structured Query Language. It's the standard language across most database systems — learn it once, use it anywhere.
SQL can do many things: create tables, change them, add data, search data and calculate with it. This syllabus only uses SQL to query a single table, with these keywords: SELECT, FROM, WHERE, ORDER BY (ASCENDING / DESCENDING), SUM, COUNT, AND and OR.
An SQL script is the set of SQL commands you are given or asked to write. A query such as SELECT City FROM MajorCity WHERE Capital = TRUE is one statement made of several clauses.
You won't be asked to write SQL that creates or changes a table. You will be asked to read, complete and write SQL scripts, and to identify the output a query gives. That's the Querying and Aggregating Labs.
The table (MajorCity, adapted from 2025 s25):
Code City Capital Country Population ASY1 Beijing TRUE China 21,766,214 EUY3 Amsterdam TRUE Netherlands 1,174,025 EUN1 Frankfurt FALSE Germany 796,437
Q: How many fields? How many records?
A: 5 fields (Code, City, Capital, Country, Population). 3 records (each row = one city).
Why it matters: counting fields and records is a common question, and examiner reports have flagged students who swap the two numbers.
Same table. Which field could be the primary key?
"Code is the primary key because it is a unique identifier / no two records share the same code."
| Field | Stores | Data type |
|---|---|---|
| ItemName | "Sparkle" | Text |
| Colour | "red" | Text |
| Weight | 2 | Integer |
| Price | 3.99 | Real |
| InStock | Yes/No | Boolean |
| DateAdded | 2026-01-15 | Date/Time |
Trap flag: students often write string instead of Text. The syllabus calls this database type text/alphanumeric — STRING is the programming data type from Topic 8.
Which of these three snippets is SQL?
Snippet A: DECLARE x : INTEGER Snippet B: SELECT City FROM MajorCity Snippet C: IF Price > 5 THEN OUTPUT "Expensive"
A: Only B is SQL. A is Cambridge pseudocode (a variable declaration). C is Cambridge pseudocode (a selection statement).
Why it matters: SQL is a separate language from the pseudocode you already know. Mixing keywords is a common mistake.
❌ Saying the table has 23 fields and 6 records (when it has 6 fields and 23 records).
Fields are the columns (categories of data). Records are the rows (each individual entry). Number of records = number of things you're storing. Number of fields = number of details about each thing.
Cited: 2025 s25 Q8(a) examiner report.❌ Giving string as a data type in a database question.
In programming (Topic 8) you learned about STRING. For databases the syllabus uses text/alphanumeric. They store the same kind of data, but in a database question write Text (or alphanumeric).
❌ Saying Type or Name or Country is the primary key because it's the "most important" field.
The primary key isn't the most important field — it's the unique one. If any two records could share the same value in that field, it cannot be a primary key. Ask yourself: "Could two records ever have the same value here?" If yes, it's out.
Cited: 2024 w24 — "Most candidates understood that the data in the Type field was not unique for all records, so could not be the primary key."❌ Identifying "Field" as a primary key when "Field" is the column heading of the table.
Some past-paper tables include a helper column literally labelled "Field" that lists the field names. Students then circle "Field" as the primary key. The primary key is one of the actual data fields — not the label above them.
Cited: 2024 s24 Q9 — "Some candidates incorrectly identified 'Field' as a primary key, which was the heading of the table."❌ Saying the primary key was chosen "because it's important" or "because it comes first".
The safest way to explain why a field is the primary key: it is a unique identifier. Say it is unique and that it identifies each record. "Important" or "first" does not explain anything.
Cited: 2024 s24 Q9(a)(ii) — mark scheme awards "unique identifier" phrasing.❌ Mixing pseudocode keywords into an SQL statement (e.g. writing OUTPUT instead of SELECT, or using IF inside a query).
SQL is a separate language from Cambridge pseudocode. SQL keywords are SELECT, FROM, WHERE, ORDER BY (ASCENDING / DESCENDING), SUM, COUNT, AND, OR. Pseudocode keywords like OUTPUT, DECLARE, IF never appear in SQL. Never mix them.
Key points that earn credit.
Capital = TRUE, never Capital = "TRUE".Country = "China" (double quotes in our examples). Missing quotes is a common mark loss.OUTPUT, DECLARE, IF) into an SQL statement.SELECT, FROM, WHERE and ORDER BY — in that order.Click each question to reveal the answer. 0 / 7 revealed
String — that's the programming term.You've locked in the structural language of databases — fields, records, validation, data types and primary keys — and you know what SQL is for. That's the whole foundation.
Next lab: 9.3–9.5 Querying Lab — where you write SQL to pull specific data out of a table.
Book Notes is the reference. Learn is the walk-through. If you're new to databases entirely, start here and go slow. If Book Notes already clicked, skim.
Every single database table you'll ever see in this course looks the same: a header row of field names, then a stack of data rows. That's it. If you can read a spreadsheet, you can read a database table.
The two questions Cambridge cares about are: which piece is which?
Cambridge often gives you a table and asks "how many fields and how many records?" Point at the top row and count categories → that's fields. Then count everything below the header → that's records. Slow down — students who rush often swap the numbers.
When you design a table, you have to decide what kind of data each field holds. The syllabus lists six types. There's a specific vocabulary — and it doesn't quite match what you learned in programming.
Text (alphanumeric) — letters, digits, symbols or a mix (city names, addresses, codes).
Character — exactly one symbol (like a grade "A").
Integer — whole numbers (population, count in stock).
Real — numbers with decimals (price, weight, percentages).
Boolean — a yes/no or true/false switch (in stock? capital city? paid?).
Date/Time — for dates, times, or both.
Watch: in programming you say String. In databases you say Text. Same idea, different word — use Text in a database question.
Imagine your school has two students both called Alex Chen. If the register just says "Alex Chen was absent," which one? You need something unique — a Student ID. That's a primary key.
Every database table needs one field where no two records share the same value. That field is the primary key. It's how the database points at exactly one row when it needs to find, update, or delete something.
Sometimes a natural field works: ContractNumber, ISBN, StorageID. When nothing natural is unique, the designer adds one: ItemID, Code.
Whenever Cambridge asks "Why is X the primary key?", write: "Because it is a unique identifier — no two records share the same X." Say unique. Say identify. Every time.
Once you have a table, you need a way to talk to it. That language is SQL — Structured Query Language.
SQL is not pseudocode. It's not Python. It's its own thing — a small set of keywords used to ask a database questions. In this syllabus you'll only use SQL to search (find rows that match a rule) and calculate (add them up, count them).
The exam won't ask you to create a table with SQL, add rows with SQL, or delete anything. Your job in the Querying and Aggregating Labs is to read, complete and write SQL scripts, and to work out what they output.
Vocabulary: a query such as SELECT ... FROM ... WHERE ... ORDER BY is one statement made of several clauses. An SQL script is the set of SQL commands you read, complete or write.
When you design a table you decide the fields, their data types — and the rules each field must follow. Those rules are validation. If someone types an age of 150 into a school database, a range check rejects it before it is stored.
The checks are the ones you met in Topic 7: range, length, type, presence, format and check digit. Validation makes data sensible; it can't prove it is true (an age of 15 passes even if the student is really 14).
Time to build a table yourself with the Table Architect. That's where fields, data types, and primary keys stop being definitions and become decisions.
Pick a scenario, add the fields you think it needs, choose a data type for each, and nominate a primary key. The Architect will check your work against the way Cambridge expects a well-formed table to look — and flag the traps if you fall into one.
Add some fields on the left. The preview will fill in as you go.
Rapid-fire questions on foundations vocabulary. Sixty seconds on the clock. Beat your best score.
Multiple-choice checks. Start on Easy if you're new to the topic; move up as you go. Every attempt is logged in Progress. Adaptive: weak skills come up more often.
Written questions in the Paper 2 style. Type your answer, press Mark my answer, then compare it with the model answer. Skills feed into Mastery.
Mark-losing traps that examiner reports have flagged on database structure and SQL basics — plus memory triggers for the key phrases.
Read them before every mock exam.
Fields are columns (categories of data). Records are rows (individual entries). Students who rush swap the numbers under exam pressure.
Cited: 2025 s25 Q8(a) examiner report.In database questions the data type for words is Text (the syllabus says text/alphanumeric), not String. STRING is the programming data type from Topic 8.
Text values in WHERE clauses go in quotation marks: Country = "China". Missing quotes is a common way to lose the mark.
Booleans and numbers are not quoted in SQL: Capital = TRUE, Price > 5. Capital = "TRUE" compares the field with the text "TRUE", not the Boolean value.
The safest answer for "why is X the primary key?" is "because it is a unique identifier". Answers like "because it's important" or "because it's first" won't earn the mark.
Cited: 2025 s25 P2 Q8 MajorCity examiner report — model answer specifies "unique identifier".Names repeat. Prices repeat. Yes/No fields have only two values across the whole table. A primary key must be unique — usually an ID or Code field.
Common exam mistakeThe primary key is a field — a column of data inside the table. It is NOT the label row at the top of a helper column called "Field". Read the question carefully.
Cited: 2024 s24 Q9 examiner report — "Field" (the table heading) identified as the primary key.Trailing commas after the last field (SELECT City, Country,), full stops instead of commas, or stray semicolons mid-query are all syntax errors. Cambridge flags "extra punctuation" as a recurring mark loss.
When asked to "write the output of this SQL statement", students often add commas or full stops. Write plain values, one record per line, with no added punctuation.
Cited: 2024 w24 P2 — "Punctuation marks should not be in the output from an SQL statement." Reinforced 2025 m25 verbatim.SQL keywords are SELECT, FROM, WHERE, ORDER BY (ASCENDING / DESCENDING), SUM, COUNT, AND, OR. Pseudocode keywords like OUTPUT, DECLARE, IF never appear inside an SQL script. They're separate languages.
Short mnemonics for the exam-critical phrases you'll be tested on.
Records = Rows, so fields = columns. "How many fields?" → count the column headings. "How many records?" → count the rows below the header.
"Because it is a unique identifier." Say it is unique, and say it identifies each record.
Text · Integer · Character · Real · Boolean · Date/Time. (Think: "Ticker-B-D".) That's every type in the syllabus list (Text is also called alphanumeric). No "String". No "Number". No "Letter".
The one-line rule: "Words go in quotes. Numbers and TRUE/FALSE don't." Country = "China", Population > 1000000, Capital = TRUE.
If a keyword appears in Topic 8 (Programming) but not in Topic 9 (Databases), it doesn't belong in an SQL script. OUTPUT, DECLARE, IF, DIV, MOD → pseudocode only. SELECT, FROM, WHERE, ORDER BY → SQL only.
SELECT → FROM → WHERE → ORDER BY. That's the clause order you'll use in the Querying Lab, with AND / OR inside WHERE and ASCENDING / DESCENDING after ORDER BY.
No mistakes logged yet. Come back after Practice and Exam.
Nothing in the Vault yet. Save key facts from Book Notes or from question feedback.
Every skill you attempt (in Table Architect, Practice, or Exam) is scored. Get 3+ attempts with 70%+ accuracy on a skill and it goes green — mastered.
Tap a skill badge to override its status (e.g. mark it Mastered by hand once you're confident, or set it back to In Progress to force more practice).
No attempts yet.
Tick each item as you cover it. Save your progress by leaving this browser open.
Complete Practice and Exam to unlock achievements.
Next, the Querying Lab uses SELECT, FROM, WHERE, AND, OR and ORDER BY to pull exactly the data you need out of a table.
No bookmarks yet. Bookmark a question in the Exam tab to save it here.
Spot a bug, a confusing explanation, or something to improve? Jot it down here. These notes are saved on this device only — they are not sent to anyone.