Cambridge IGCSE and O level Computer Science 2210 - Paper 2
Chapter 9 – Databases
In this worksheet you will find recent past papers (May-June & Oct-Nov) along with question variant.
***IMPORTANT DEFINTIONS***
1. Database – a persistent structured collection of data that allows people to extract
information in a way that meets their needs
2. Single-table database – a database contains only one table
3. Table – a collection of related records in a database
4. Record – a collection of fields that describe one item
5. Field – a database table attribute
6. Data type – a classification of how data is stored and displayed, and of which operations
that can be performed on the stored value
7. Primary key – a field in a database that uniquely identifies a record
8. Structured Query Language (SQL) – the standard query language for writing scripts to
obtain useful information from a relational database.
9. SQL scripts – a list of SQL commands that perform a given task, often stored in a file so
the script can be reused
[Link] – an SQL command that fetches specified fields (columns) from a table
[Link] – an SQL command that identifies the table to use
[Link] – an SQL command to include only those records (rows) in a query that match a
given condition
[Link] BY – an SQL command that sorts the results from a query by a given column
either alphabetically or numerically
[Link] – an SQL command that returns the sum of all the values in a field (column); used
with SELECT
[Link] – an SQL command that counts the number of records (rows) in which the field
(column) matches a specified condition; used with SELECT
1. A database table called Booking28 stores details of hotel rooms and bookings for the week
beginning Monday 7 July 2025.
O/N/24/2
(a) State the number of fields and records in this database table.
b) State the reason why the Type field would not be suitable as a primary key
(c) The database uses only the data types:
• Alphanumeric • character • Boolean • integer • real • date/time.
Complete the table to show the fields that could have the given data types.
Only one field name is required in each box and each field name must be different.
d) Give the output that would be produced by the structured query language (SQL) statement
2. Building materials are sold in bags. A new database table called BuildStock has been set up to store
details about the materials for sale. Part of this table is given:
O/N/24/3
(a) Write the output from this structured query language (SQL) statement.
(b) (i) Complete this SQL statement to display only the names of all the materials that are out of
stock.
(ii) Explain how another SQL statement using a different field could be used to display the same
information.
[2]
3. A television subscription service has a new database table named Contract to store details of their
subscribers’ contracts. The table contains these fields:
M/J/24/2
• ContractNumber – the contract number, for example CT567
• Months – the length of the contract in months, for example 6
• EndDate – the date the contract finishes, for example 30 November 2024
• News – the news service, yes or no
• Movie – the movie service, yes or no
• Sport – the sports service, yes or no
• Junior – the children’s service, yes or no.
(a) Identify the field that will be the most appropriate primary key for this table.
(b) Complete the table to identify the most appropriate data type for these fields in Contract
(c) Explain the purpose of these structured query language (SQL) statements.
Statement 1: SELECT SUM (Months) FROM Contract;
Statement 2: SELECT COUNT (News) FROM Contract WHERE News;
(d) Complete this SQL statement to find the contract numbers of the subscribers that take both the
news and sports services.
4. A database table called SoftDrinks stores details of the soft drinks sold by a small shop. M/J/24/1
(a) State whether any of the given fields would be suitable as a primary key and give a reason for
your answer.
(b) Complete the structured query language (SQL) statement to return the number of cans the shop
has in stock
5. A database table called Horses stores details about the horses kept at a horse sanctuary.
O/N/23/2
(a) State the number of records in this database table.
(b) Give the name of the field that is most suitable to be the primary key. State the reason for this
choice.
(c) The database only allows the data types:
• Boolean • character • date/time • integer • real • text.
Complete the table to show the most appropriate data type for each field. Each data type must be
different.
(d) Complete the structured query language (SQL) to return the code and breed of all the horses
whose breed originated in Scotland.
6. A sanctuary for pheasants has set up a new database table called PheasantList to store details of
the different species of bird at the sanctuary. Part of this table is given, showing: species, description,
number of birds at the sanctuary, if the birds are breeding or not, and number of young born this
year. O/N/23/3
(a) State the number of records and fields in this part of the database table.
(b) (i) Give the name of a field that could be used for the primary key.
(ii) Explain why the sanctuary might decide not to use the field in (b)(i) as the primary key.
(iii) A new field SpeciesID is added to the database table. This field contains a six-character code, for
example Ph0001. Give a reason why this field would be a better primary key.
(c) Write the output that would be given by this structured query language (SQL) statement:
(d) Complete this SQL statement to display all the species of pheasant where the birds are breeding
and there were no young born this year:
7. A database table called TVRange shows the main features and prices of a range of televisions.
M/J/23/1
(a) Give the name of the field that is most suitable to be the primary key. State the reason for this
choice.
(b) The database uses the data types:
• text • character • Boolean • integer • real • date/time.
Complete the table to show the most appropriate data type for each field. Each data type must be
different.
(c) Complete the structured query language (SQL) query to return the television (TV) code, screen size
and price of all Smart TVs in the database table.
8. A music streaming service has a new database table named Songs to store details of songs available
for streaming. The table contains the fields: M/J/23/2
• SongNumber – the catalogue number, for example AG123
• Title – the title of the song
• Author – the name of the song writer(s)
• Singer – the name of the singer(s)
• Genre – the type of music, for example rock
• Minutes – the length of the song in minutes, for example 3.75
• Recorded – the date the song was recorded.
(a) Identify the field that will be the most appropriate primary key for this table
(b) Complete the table to identify the most appropriate data type for the fields in Songs
(c) Explain the purpose of the structured query language (SQL) statements.
SUM (Minutes) FROM Songs WHERE Genre = "rock";
COUNT (Title) FROM Songs WHERE Genre = "rock";
The end