Question 1 [6] (c) Modify your query to also order the results by
Price in descending order. [2]
(a) Define a database table. [2]
(d) Write an SQL statement to increase the Price of
(b) Explain what is meant by: all Technical books by 10%. [3]
(i) a field
(ii) a record [2] Section B
(c) State the purpose of a primary key in a database Question 4 [12]
table [2]
A library requires a database to store details of
Question 2 [8] books, members, and loans.
A company sets up a table called EMPLOYEE with Each book must have a unique book number, title,
the following data: author, publication year, and whether it is on loan
(Yes/No).
EmployeeID FirstName LastName DateOfBirth Department Salary Each member must have a unique member number,
E001 Maria Ahmad 1985-07-10 Sales 45000
name, address, email, and join date.
For each loan, you must record which book number,
E002 Ahmed Khan 1990-12-02 Marketing 38000 which member number, date borrowed and date
due back.
E003 Saira Jones 1988-03-15 Sales 42000
(a) Draw the structure of three tables (Book,
(a) Identify the most suitable field to be used as the Member, Loan) showing primary keys and one
primary key. example record for each. [6]
Explain your choice. [2]
(b) Explain why the Loan table must be separate
(b) Suggest appropriate data types for each of the from the Book and Member tables. [2]
six fields. [6]
(c) Suggest suitable data types for each field in the
Question 3 [10] Loan table, including the primary key. [4]
The following table shows a list of books. Question 5 [15]
BookID Title Author Genre Price StockLevel A travel agency stores data about trips in a table
called TRIPS.
Data Science Non-
B100 Lee Chen 25.50 12
101 fiction Tri Desti Depar Retu Pr Numb MaxC
pI nati tureD rnDa ic erBoo apaci
Mystery at Priya D on ate te e ked ty
B101 Fiction 18.99 5
Dawn Singh
1
2025
Python T2 2025- 5
B102 Alan Mui Technical 40.00 8 Bali -11- 8 12
Programming 00 11-10 0
20
0
Keny 2
(a) How many fields and records are shown? 2026
T2 a 2026- 2
Fields: _______ Records: _______ [2] 01 Safa 01-05
-01-
0
12 12
15
ri 0
(b) Write an SQL SELECT query to display the
Title and Author of all books whose StockLevel is less Icela 2026 3
T2 nd 2026-
than 10. [3] -03- 0 4 10
02 Adve 03-12
22 0
ntur 0
Tri Desti Depar Retu Pr Numb MaxC
pI nati tureD rnDa ic erBoo apaci
D on ate te e ked ty
(a) State the number of fields and records in the
table.
Fields: _______ Records: _______ [2]
(b) Which field is best used as the primary key?
Explain why. [2]
(c) Write an SQL query to display Destination and
Price for all trips where NumberBooked <
MaxCapacity. [3]
(d) Write an SQL query to show all fields for trips
with Price > 2000, ordered by DepartureDate
ascending. [3]
(e) Write an SQL DELETE statement to remove all
trips where ReturnDate is not in the year 2026. [3]
(f) Give one advantage and one disadvantage of
using a single-table database instead of multiple
related tables. [2]
Question 6 [9]
A university keeps student data in a table called
STUDENTS with fields:
StudentID, Name, Course, Year, Email.
They plan to create a second table called ALUMNI
once students graduate.
(a) Explain how separating the tables can improve
data integrity and efficiency. [4]
(b) Suggest how a foreign key relationship could be
used between the STUDENTS and ALUMNI tables.
[3]
(c) Give two potential issues with storing student
email addresses as plain text in a database.
[2]