Databases and SQL (GCSE & IGCSE Computer Science): Revision Notes and Worksheets

GCSE and IGCSE Computer Science databases: tables, records and fields, data types, validation, primary keys, and SQL SELECT, FROM, WHERE, ORDER BY, SUM, COUNT.

Diagram of a database table called PRODUCT with 6 fields and 8 records: one row is outlined as a record, the Category column is outlined as a field, and the ProductID column is marked as the primary key.

A shop's stock list, a library catalogue and a race's results table are all databases: data about one type of thing, stored so it can be searched. This guide covers GCSE and IGCSE databases and SQL: tables, records and fields, data types and validation, primary keys, and SQL queries with SELECT, FROM and WHERE, then ORDER BY, SUM, COUNT, AND and OR. Each part has a worked example and a worksheet for your exam board.

OCR J277 students: you only need N1 and N4, and only the points marked GCSE or Both boards. Parts N2, N3 and N5 are on the Cambridge IGCSE 0478 syllabus only.

What does databases and SQL cover for OCR and Cambridge?

A database is an organised, persistent store of data that can be searched. Both boards use the same core words: table, record and field, and both ask you to read and write SQL queries on a single table. Cambridge IGCSE 0478 has a whole topic on databases (topic 9): defining a table, data types, validation, primary keys and a longer list of SQL keywords. OCR J277 covers it inside 2.2.3, Additional programming techniques: the use of records, 2D arrays that emulate a database table, and SQL with SELECT, FROM and WHERE only. The table below is based on the OCR J277 specification (version 3.1) and the Cambridge IGCSE 0478 syllabus for 2026-2028, as checked in November 2026.

Part

Topic

OCR J277

Cambridge 0478

N1

Tables, records and fields (both boards)

2.2.3

9.1

N2

Data types and validation (IGCSE only)

not on spec

9.1, 9.2 (with 7.5(a))

N3

Primary keys (IGCSE only)

not on spec

9.3

N4

SQL: SELECT, FROM and WHERE (both boards)

2.2.3

9.4

N5

SQL: ORDER BY, SUM, COUNT, AND and OR (IGCSE only)

not on spec

9.4

All the examples below use one made-up table, PRODUCT, from a bakery. Price is in pounds and Vegan is TRUE or FALSE.

ProductID

Name

Category

Price

Stock

Vegan

P01

Sourdough loaf

Bread

3.80

12

TRUE

P02

Cheese scone

Savoury

1.60

30

FALSE

P03

Seeded rye

Bread

4.20

8

TRUE

P04

Lemon tart

Sweet

2.90

15

FALSE

P05

Cinnamon bun

Sweet

2.40

24

FALSE

P06

Bagel

Bread

1.10

40

TRUE

P07

Fruit flapjack

Sweet

1.90

18

TRUE

P08

Sausage roll

Savoury

2.20

20

FALSE

N1: What are tables, records and fields in a database?

Both boards. A table stores data about one type of item, organised in rows and columns. Each row is a record: all the data about one item, such as one product. Each column is a field: one item of data stored for every record, such as the price. Every record in a table has the same fields, and each field has a name, such as Category. The heading row holds the field names; it is not a record.

GCSE only: OCR J277 links databases to programming:

  • Records are a way to store data in a program: a group of related items of data, about one thing, kept together. See 2D arrays and records in our programming notes.

  • A 2D array can emulate a database table: each row of the array holds one record and each column holds one field.

IGCSE only: Cambridge 0478 9.1 asks you to define a single-table database from given data storage requirements. That means naming the table, making one field for each item of data with a meaningful name (no spaces), choosing a data type and validation for each field (N2) and choosing a primary key (N3). Cambridge only asks about single-table databases.

Worked example: counting records and fields (both boards)

  • Step 1 (both boards): PRODUCT has 6 fields (ProductID, Name, Category, Price, Stock, Vegan) and 8 records (P01 to P08). Do not count the heading row.

  • Step 2 (both boards): the bakery wants to store the date each product was baked. It adds a new field, DateBaked, so the table has 7 fields and still 8 records. Adding a new product would add a record instead: 9 records, and the number of fields stays the same.

  • Step 3 (GCSE only): the same data held in a 2D array called products, indexed from 0, with every value stored as a string: products[3,1] is row 3 (the record for P04) and column 1 (the Name field), so it holds "Lemon tart". This OCR Exam Reference Language loop outputs the name of every product in the Bread category: Sourdough loaf, Seeded rye and Bagel.

for r = 0 to 7
    if products[r,2] == "Bread" then
        print(products[r,1])
    endif
next r
for r = 0 to 7
    if products[r,2] == "Bread" then
        print(products[r,1])
    endif
next r
for r = 0 to 7
    if products[r,2] == "Bread" then
        print(products[r,1])
    endif
next r

Practise N1: OCR questions · OCR mark scheme · Cambridge questions · Cambridge mark scheme

N2: Which data types and validation checks does a database table use?

IGCSE only. OCR J277 does not include database data types or validation for database fields, but OCR can ask for the most suitable data type for a field in a table or record, using its programming data types (integer, real, Boolean, character, string; J277 2.2.2). The Cambridge names below (text/alphanumeric, date/time) are IGCSE only. Cambridge 0478 9.2 asks you to suggest a suitable data type for each field from these six:

Data type

What it stores

Example field in PRODUCT

Text/alphanumeric

letters, digits and symbols

Name, ProductID; also phone numbers and postcodes

Character

a single character

a Size field holding 'S', 'M' or 'L'

Boolean

one of two values

Vegan (TRUE or FALSE)

Integer

whole numbers

Stock

Real

numbers with a decimal part

Price

Date/time

a date and/or a time

a DateBaked field

Use these exact names for database fields: Cambridge examiners do not credit "string" for a database field. A phone number is text/alphanumeric, not integer: it can start with a 0, which an integer would lose, it can contain a space, and no calculations are done with it.

Validation is an automatic check on data as it is entered, to make sure it is reasonable and meets a set of rules. Data that breaks the rule is rejected. Validation cannot prove the data is correct: a stock level of 21 passes a range check even if the real number is 12. The checks named in Cambridge 0478 (7.5(a)) are:

  • Range check: the value is between a lower and an upper limit, for example Stock from 0 to 100 inclusive.

  • Length check: the number of characters is within a limit, for example Name has no more than 30 characters.

  • Type check: the value is the right data type, for example Price must be a number.

  • Presence check: the field is not left empty.

  • Format check: the value follows a pattern, for example ProductID is P followed by two digits, such as P04.

  • Check digit: the last digit of a code such as a barcode is calculated from the other digits. When the code is entered, the digit is calculated again and compared with the check digit; if they do not match, the code is rejected.

Worked example: choose a data type and a validation check (IGCSE only)

  • Step 1: Stock is a count of items, always a whole number: integer. Suitable check: a range check, from 0 to 100 inclusive.

  • Step 2: ProductID holds a letter and digits, such as P04: text/alphanumeric. Suitable check: a format check, P followed by exactly two digits.

  • Step 3: DateBaked holds a date: date/time. Suitable check: a presence check, so the date cannot be left empty. Always say what the check would test for that field, not just its name.

Practise N2: Cambridge questions · Cambridge mark scheme

N3: What is a primary key and how do you choose one?

IGCSE only. Primary keys are not on the OCR J277 specification. A primary key is a field that uniquely identifies each record in a table. No two records can have the same primary key value. Its purpose is to let any single record be found, updated or referred to without mixing it up with another one.

To choose a primary key, look for a field whose values can never repeat, for every record that could ever be stored, not just the ones shown. Names, surnames, categories, prices and dates are usually unsuitable, because two records could share them. If no existing field is unique, add an ID field to be the primary key. A primary key does not have to be a number: P04 is fine.

Worked example: find the primary key of PRODUCT (IGCSE only)

  • Step 1: Category is not suitable: Bread appears in 3 records (P01, P03, P06), so it does not identify one record.

  • Step 2: Price is not suitable either. All 8 prices in the table are different today, but the bakery could add a new product that also costs 1.60, and then two records would share a value.

  • Step 3: ProductID is the primary key: the bakery gives every product a different ID, so each value identifies exactly one record.

Practise N3: Cambridge questions · Cambridge mark scheme

N4: How do you search a database with SQL SELECT, FROM and WHERE?

Both boards. SQL (Structured Query Language) is used to search for (query) data in a database table. A query is written one clause per line, in this order:

  • SELECT lists the fields to show, separated by commas. SELECT * shows all the fields.

  • FROM names the table to search.

  • WHERE gives the condition a record must meet to be included. It can use =, <>, <, >, <=, >=.

  • Text values go in quotation marks, for example WHERE Category = 'Bread' (single or double quotes); numbers do not.

The output is the fields listed in SELECT, in that order, for only the records that meet the WHERE condition.

GCSE only: OCR J277 lists only SELECT, FROM and WHERE. In SQL, equality is tested with a single =, unlike == in OCR Exam Reference Language. OCR questions often name tables with a Tbl prefix, such as TblProduct, and the closing semicolon is optional.

IGCSE only: Cambridge 0478 9.4 asks you to read, understand and complete SQL scripts. Statements in Cambridge papers end with a semicolon, and when you state the output you copy the values exactly, in the order of the fields in SELECT, with no commas or quotation marks added.

Diagram showing the query SELECT Name, Price FROM PRODUCT WHERE Price &lt; 2.00 on the table PRODUCT: the three records with a price below 2.00 and the Name and Price fields are highlighted, and the output is Cheese scone 1.60, Bagel 1.10 and Fruit flapjack 1.90.

Worked example: work out the output of a query (both boards)

SELECT Name, Price
FROM PRODUCT
WHERE Price < 2.00

SELECT Name, Price
FROM PRODUCT
WHERE Price < 2.00

SELECT Name, Price
FROM PRODUCT
WHERE Price < 2.00

  • Step 1: FROM: search the table PRODUCT.

  • Step 2: WHERE: keep only the records with a price below 2.00: P02 (1.60), P06 (1.10) and P07 (1.90). P08 costs 2.20, so it is left out.

  • Step 3: SELECT: show only Name and Price, in that order. The output is Cheese scone 1.60, Bagel 1.10 and Fruit flapjack 1.90: 3 records, 2 fields.

Writing one is the same in reverse. To show the ID and name of every product with 20 or more in stock: SELECT ProductID, Name, then FROM PRODUCT, then WHERE Stock >= 20. It outputs P02 Cheese scone, P05 Cinnamon bun, P06 Bagel and P08 Sausage roll.

Practise N4: OCR questions · OCR mark scheme · Cambridge questions · Cambridge mark scheme

N5: How do ORDER BY, SUM, COUNT, AND and OR work in SQL?

IGCSE only. OCR J277 lists only SELECT, FROM and WHERE, so ORDER BY, SUM and COUNT are on the Cambridge 0478 syllabus only (9.4).

  • ORDER BY sorts the output by a field: ORDER BY Stock ASC (ascending, smallest first) or ORDER BY Stock DESC (descending, largest first). Ascending is the default if you leave out ASC. ORDER BY changes only the order of the output, never which records are in it.

  • SUM(field) adds up the values of a numeric field for the records that match.

  • COUNT(field) gives the number of records that match.

  • AND: a record is included only if both conditions are true. OR: it is included if at least one is true.

  • The clause order is SELECT, FROM, WHERE, ORDER BY.

Both boards: AND and OR are also the Boolean operators in OCR J277 2.2.1, so an OCR WHERE condition can join two conditions with them.

Worked example: sort, count and add (IGCSE only)

SELECT Name, Stock
FROM PRODUCT
WHERE Category = 'Sweet'
ORDER BY Stock DESC

SELECT Name, Stock
FROM PRODUCT
WHERE Category = 'Sweet'
ORDER BY Stock DESC

SELECT Name, Stock
FROM PRODUCT
WHERE Category = 'Sweet'
ORDER BY Stock DESC

  • Step 1: WHERE keeps the three Sweet products, then ORDER BY sorts them by Stock, largest first: Cinnamon bun 24, Fruit flapjack 18, Lemon tart 15. With ASC, the same three records come out in the reverse order.

  • Step 2: SELECT COUNT(ProductID) FROM PRODUCT WHERE Vegan = TRUE; outputs 4: P01, P03, P06 and P07 are vegan. COUNT counts records; it does not add anything up.

  • Step 3: SELECT SUM(Stock) FROM PRODUCT WHERE Category = 'Bread'; outputs 12 + 8 + 40 = 60.

  • Step 4: WHERE Category = 'Sweet' AND Vegan = TRUE matches 1 record (Fruit flapjack). Change AND to OR and it matches 6 records: the 3 Sweet products and the 4 vegan products, with Fruit flapjack counted once.

Practise N5: Cambridge questions · Cambridge mark scheme

Common exam mistakes in databases and SQL

  • Swapping record and field. A record is a row (one item); a field is a column (one item of data for every record).

  • Counting the heading row as a record.

  • (IGCSE) Giving "string" or integer as the data type of a phone number. In a database it is text/alphanumeric.

  • (IGCSE) Describing validation as checking the data is correct. It checks the data is reasonable and meets a rule.

  • (IGCSE) Choosing a field that is unique in the table shown as the primary key, such as a price or a time. It must be unique for every record that could ever be stored.

  • Writing AND between the fields in SELECT. Fields are separated by commas.

  • Leaving the quotation marks off a text value, or putting them round a number.

  • Writing the clauses in the wrong order, or using SELECT * when the question asks for only some fields.

  • (IGCSE) Mixing up COUNT (how many records) and SUM (adds up the values), or thinking ORDER BY changes which records are output.

Quick recap

  • N1: a table holds data about one type of item; a record is a row, a field is a column; 2D arrays and records GCSE only; defining a single table IGCSE only.

  • N2: (IGCSE only) data types text/alphanumeric, character, Boolean, integer, real, date/time; validation checks range, length, type, presence, format, check digit.

  • N3: (IGCSE only) a primary key uniquely identifies each record; no duplicates; add an ID field if nothing is unique.

  • N4: SELECT lists fields, FROM names the table, WHERE gives the condition; text in quotes, numbers not.

  • N5: (IGCSE only) ORDER BY ASC or DESC sorts, SUM adds, COUNT counts; AND needs both conditions, OR at least one.

Download the whole module

Frequently asked questions

Which parts of databases and SQL are on the OCR J277 specification?

Only N1 and N4: tables, records and fields (with 2D arrays and records), and SQL with SELECT, FROM and WHERE. OCR can also ask the data type of a field, using its programming data types (string, integer, real, Boolean, char). The database data type names, validation for database fields, primary keys, and ORDER BY, SUM and COUNT are on Cambridge IGCSE 0478 only.

What is the difference between a record and a field in a database?

A record is one row of a table: all the data about one item. A field is one column: one item of data, such as a price, stored for every record.

What is a primary key in a database?

A primary key is a field that uniquely identifies each record in a table, so no two records can have the same value in it. An ID field such as ProductID is usually used, because names and dates can repeat.

What data type should a phone number be in a database?

Text/alphanumeric. A phone number can start with a 0, which would be lost if it were stored as an integer, it can contain spaces, and it is never used in calculations.

What does SELECT * mean in SQL?

SELECT * shows all the fields in the table. It does not choose which records are shown; the WHERE clause does that.

What is the difference between SUM and COUNT in SQL?

COUNT gives the number of records that match the query. SUM adds up the values in a numeric field for the records that match.

Previous module: Programming revision notes. For how programs check their input, see validation in our robust programs notes.

More help: browse the GCSE Computer Science revision hub.