Databases 🗄️

🏷️ Basic data types 9.1.2

Every field needs a data type: it classifies how the data is stored and displayed and which operations can be done on it (an integer is stored and shown as a whole number and can be used in calculations). The syllabus names six; Access uses slightly different names:

🔑 Primary keys 9.1.3

An existing field can be the primary key if it's unique — the ISBN in a BOOK table. In the PATIENT table every existing field could repeat (two patients called Sing, two in bed 8, many in ward 6), so a new field is added: HospitalNumber, e.g. HN284511.

🔍 SQL — Structured Query Language 9.1.4

SQL (say "es-queue-el") is the standard language for getting useful information out of a database: a visitor only needs a patient's ward and bed; a consultant needs a list of all their patients. An SQL script is a list of SQL commands that do a task, often saved in a file so it can be reused. You need to understand and predict the output of these statements:

Only SELECT and FROM are compulsory, and a command ends with ;. * means every field.

⚖️ Writing conditions 9.1.4

🛠️ Using a database for real 9.1

You need to be able to define a single-table database from given data storage requirements, choose a suitable primary key, and read, complete and understand SQL scripts. With a database management system (Access, or free ones such as SQLite and MySQL — check their commands, which can differ slightly):

1. 1️⃣ Start from the data collection form: each box on the form becomes a field with a meaningful name.
2. 2️⃣ Create a blank database, open the table in design view and give it a name (e.g. CUB).
3. 3️⃣ Add each field with a suitable data type — plus an extra field to be the primary key if no existing field is unique.
4. 4️⃣ Add validation to each field — e.g. Gender: a presence check, a length check (1 character) and a format check (M or F).
5. 5️⃣ Enter the records, then use verification (a visual check against the form, or double entry) to make sure they were copied correctly.

🏕️ Case study: the cub scout database 9.1

Children aged seven to eleven can join a cub scout group, which keeps a record of each cub. The CUB table in the SQL lab has a primary key CubNumber, the cub's details, the Six they belong to (a group of up to six cubs, named Red, Blue, Yellow or Green) and the number of Badges they have been awarded.

🎓 A student database 9.1

A school's STUDENT table stores each student's family name, other names, student ID, date of birth, date of entry, class, year/grade and email address. It's in the SQL lab too — use it to test your scripts for Activity 9.8.

🚀 Extension: building a database with SQL A Level preview

A database management system (DBMS) uses a Data Definition Language (DDL) to create, modify and remove the structure of a database, and a Data Manipulation Language (DML) to add, modify, delete and retrieve the data itself. SQL does both — and has been the industry standard since the 1970s. DDL commands: CREATE DATABASE, CREATE TABLE, ALTER TABLE, PRIMARY KEY. SQL data types include CHAR(n) (fixed-length text), VARCHAR(n) (variable length, up to n), BOOLEAN, INTEGER, REAL, DATE (YYYY-MM-DD) and TIME (HH:MM:SS).

✏️ Practice hub 8 activities · every example

Every activity from the coursebook chapter — the SQL ones are marked by running your query's logic on the real tables. Fresh questions every time you hit 🔀 New questions.

📘 Worked examples

Every type of worked example in the chapter, step by step. Hit 🔀 Another example for a new one.

🃏 Key-term flashcards Tap to flip

🧠 Mini-quiz

📝 Exam-style questions Try, then peek

Questions 1–3 use the BIKE table in the SQL lab.

SELECT ModelNumber, Price
FROM BIKE
WHERE InProduction = TRUE
ORDER BY Price DESC;

SELECT Title
FROM SHOWS
WHERE Type = 'Comedy' AND SoldOut = FALSE;