Databases and SQL
Databases and SQL, on the AQA-facing side of the specification, covers writing and interpreting SQL queries against a relational database, and how a database management system (DBMS) keeps data correct and available when many users access it at the same time: transaction processing, the ACID properties (atomicity, consistency, isolation, durability), concurrency control such as record locking and deadlock, and the client-server model used by multi-user database systems. It builds on relational database structure, keys and normalisation, but its own focus is querying data and processing transactions safely, not designing the schema.
Before you start
Make sure you're comfortable with these topics first:
Method
- Read the schema given in the question first: note every table name, its columns, and which columns are the primary key and any foreign keys, before writing any SQL.
- Decide whether the question needs data retrieved (SELECT) or data changed (INSERT, UPDATE or DELETE), then build the statement in order: SELECT columns, FROM table(s), JOIN any related tables, WHERE the row condition, GROUP BY if aggregating, ORDER BY to sort.
- When two tables are needed, JOIN them on the shared key (the foreign key in one table matching the primary key in the other) and qualify a column name with its table name whenever the same column name could belong to more than one table.
- Use an aggregate function (COUNT, SUM, AVG, MAX, MIN) when the question asks for a total, an average, a count or an extreme value, and pair it with GROUP BY when the aggregate must be calculated separately for each group (e.g. per customer).
- For a transaction-processing question, name the specific ACID property at risk (e.g. isolation, if two users could see each other's uncommitted changes) rather than describing the risk only in general terms.
- For a concurrency question, describe the actual mechanism, such as record locking stopping a second transaction from reading or writing a row while the first transaction still holds the lock on it.
- For a client-server question, contrast where processing and locking happen (on the central server) with what crosses the network (only the request and the results), rather than the whole dataset.
Worked example
A car hire company stores its data in three tables: VEHICLES(VehicleReg, Model, DailyRate, BranchID), BOOKINGS(BookingID, VehicleReg, CustomerID, StartDate, EndDate, TotalCost) and CUSTOMERS(CustomerID, CustomerName, Phone). Write an SQL query to list the CustomerName and TotalCost for every booking of a vehicle with the Model 'Ford Focus', with the highest TotalCost listed first.
- Identify the required output columns and where they live: CustomerName is in CUSTOMERS, TotalCost is in BOOKINGS.
- Start the FROM clause at BOOKINGS, since it holds a foreign key to both other tables (CustomerID and VehicleReg).
- JOIN CUSTOMERS ON BOOKINGS.CustomerID = CUSTOMERS.CustomerID to bring in CustomerName.
- JOIN VEHICLES ON BOOKINGS.VehicleReg = VEHICLES.VehicleReg so the query can filter by Model.
- Add WHERE VEHICLES.Model = 'Ford Focus' to restrict the rows to that vehicle model.
- Add ORDER BY BOOKINGS.TotalCost DESC so the highest-cost bookings appear first, giving the final statement: SELECT CUSTOMERS.CustomerName, BOOKINGS.TotalCost FROM BOOKINGS JOIN CUSTOMERS ON BOOKINGS.CustomerID = CUSTOMERS.CustomerID JOIN VEHICLES ON BOOKINGS.VehicleReg = VEHICLES.VehicleReg WHERE VEHICLES.Model = 'Ford Focus' ORDER BY BOOKINGS.TotalCost DESC;
Practice questions
Type your answer and press Check to be marked straight away, or reveal the answer and mark yourself.
Q1In the table BOOKINGS(BookingID, VehicleReg, CustomerID, StartDate, EndDate, TotalCost), state which field is most suitable as the primary key, and explain why VehicleReg would not be suitable as the primary key on its own.Show answer
Answer: BookingID, because it is guaranteed to be unique for every row and never repeats. VehicleReg would not be suitable alone because the same vehicle is hired out across many different bookings over time, so the same VehicleReg value would appear in multiple rows, which a primary key must never do.
Q2In the table VEHICLES(VehicleReg, Model, DailyRate, BranchID), write an SQL statement to select the Model and DailyRate of every vehicle with a DailyRate greater than 40.Show answer
Answer: SELECT Model, DailyRate FROM VEHICLES WHERE DailyRate > 40;
Q3In the table BOOKINGS(BookingID, VehicleReg, CustomerID, StartDate, EndDate, TotalCost), write an SQL statement to count how many bookings it contains.Show answer
Answer: SELECT COUNT(*) FROM BOOKINGS;
Q4In the table CUSTOMERS(CustomerID, CustomerName, Phone), write an SQL statement to list the CustomerName of every customer whose name starts with 'Mc'.Show answer
Answer: SELECT CustomerName FROM CUSTOMERS WHERE CustomerName LIKE 'Mc%';
Q5State what the letter 'A' in ACID stands for, and explain what it guarantees about a database transaction.Show answer
Answer: Atomicity. It guarantees a transaction is treated as a single, indivisible unit: either every operation within it is applied to the database, or none of them are, so if any step fails the whole transaction is rolled back rather than leaving the database partly updated.
Q6Explain what is meant by 'record locking' in a multi-user database.Show answer
Answer: When one user's transaction is reading or updating a record, the DBMS locks that record so that no other user's transaction can access it until the lock is released, which happens once the first transaction commits or rolls back. This stops two transactions making conflicting changes to the same data at the same time.
Q7Explain why a client-server database architecture reduces network traffic compared with every branch keeping its own full local copy of the database.Show answer
Answer: In a client-server system, the data stays on the server and the query is executed there, so only the request (the SQL statement) and the results returned travel across the network. A branch with its own full local copy would instead need to transfer, or repeatedly synchronise, the entire dataset across the network.
Q8Define the term 'deadlock' in the context of database transactions.Show answer
Answer: A situation where two or more transactions are each waiting to acquire a lock currently held by one of the others, so none of them can proceed, and all remain permanently blocked unless the DBMS detects and intervenes (for example by forcing one transaction to roll back).
Exam-style questions
Written in the style of a A Level Computer Science exam paper, with a full mark scheme.
A car hire company uses the tables VEHICLES(VehicleReg, Model, DailyRate, BranchID), BOOKINGS(BookingID, VehicleReg, CustomerID, StartDate, EndDate, TotalCost) and CUSTOMERS(CustomerID, CustomerName, Phone). Write an SQL statement to display the CustomerName and Phone of every customer who has made more than one booking.
Show mark scheme
Tick each line you got. Your score builds from the marks on the scheme.
Nothing ticked yet - 4 available
A car hire company's booking system is used by staff at several branches at the same time. Two staff members at different branches both try to book the same vehicle, VehicleReg 'LD19 XYZ', for overlapping dates within a second of each other. Explain, with reference to ACID properties and concurrency control, how the database management system should prevent this vehicle being double-booked.
Show mark scheme
Tick each line you got. Your score builds from the marks on the scheme.
Nothing ticked yet - 6 available
Evaluate the use of a client-server database architecture, rather than each of a car hire company's 12 branches keeping its own separate local database file, for storing and updating vehicle booking data.
Show mark scheme
Tick each line you got. Your score builds from the marks on the scheme.
Nothing ticked yet - 9 available
See real A Level Computer Science past-paper questions, with official mark schemes →
Free printable worksheet
Want more practice on paper? Download the databases and sql worksheet pack - 14 pages of exam-style questions with a full mark scheme. One email opens every download in this browser for 14 days - no account, no card. Print it for personal and classroom use.
Next topics
Not quite what you needed?
Tell us what is missing on databases and sql, or which topic to write up next. Every request is read, and we reply to every one.
Build a full practice pack.
This topic is one of hundreds in the library - pick the ones a student needs and generate a printable PDF in minutes.