Databases and SQL: Computer Science 0478 (Cambridge O Level / IGCSE)

"Locked Room Adventures" runs several escape rooms and stores every booking made for them in a single database table called ROOM_BOOKING, shown below.

Which field should be chosen as the primary key for the ROOM_BOOKING table?

Bramblewood Beekeeping Collective manages hives across two orchards. It plans to store details of each hive in a single database table called HIVE. A sample of the records it plans to store is shown below.

(a) Using the data shown, identify the most suitable field to use as the primary key for the HIVE table. Justify your choice by referring to the table. [2]

(b) State the most suitable basic data type for each of these fields: (i) QueenPresent (ii) EstimatedHoneyKg [2]

(c) The collective wants any new hive record to be rejected unless the value entered for NumberOfFrames is between 1 and 12 inclusive. Identify a suitable validation check for this rule, and describe how it would be applied to a value entered for NumberOfFrames. [3]

Echo Peak Studios records every recording session it hosts in a single database table called SESSION, shown below.

(a) A studio assistant runs this SQL statement:

SELECT ArtistName, Genre, DurationMins
FROM SESSION
WHERE Genre = "Rock" OR Genre = "Jazz"
ORDER BY DurationMins DESCENDING;

Using the table, write out the output produced by this statement. [4]

(b) Write an SQL statement that will output the SessionID and ArtistName of every session where the Genre is "Pop" and the session has already been mastered. [3]

A school makerspace tracks every 3D-printing job in a single database table called PRINTJOB, shown below. The Priority field always stores a single letter: H (high), M (medium) or L (low).

(a) State the most suitable basic data type for the Priority field. [1]

(b) Write an SQL statement to output the JobID and FilamentGrams of every job with Priority "H" or Priority "M", with the job using the largest amount of filament listed first. [4]

(c) Write an SQL statement to calculate the total number of grams of filament used by jobs that have already been completed. [3]

A small aquarium keeps details of every fish tank it maintains in a single database table called TANK, shown below.

An aquarium assistant runs this SQL statement:

SELECT COUNT(TankID)
FROM TANK
WHERE FishCount > 20;

What value does this statement return?

Trailhead Bike Rentals stores every bike hire it makes in a single database table called BIKE_RENTAL, shown below.

Which field should be chosen as the primary key for the BIKE_RENTAL table?

Sunnyvale Tutoring Centre books private lessons and stores details of each one in a single database table called LESSON. A sample of the records it plans to store is shown below. SubjectCode always stores a two-letter code, such as MA for Mathematics or EN for English.

(a) Using the data shown, identify the most suitable field to use as the primary key for the LESSON table. Justify your choice by referring to the table. [2]

(b) State the most suitable basic data type for each of these fields: (i) SubjectCode (ii) Attended [2]

(c) The centre wants any new lesson record to be rejected unless the value entered for SubjectCode is exactly two characters long. Identify a suitable validation check for this rule, and describe how it would be applied to a value entered for SubjectCode. [3]

Greenfield Weather Station records readings taken at two sites in a single database table called READING, shown below.

(a) A student runs this SQL statement:

SELECT AVG(TemperatureC)
FROM READING
WHERE StationName = "North Ridge";

Using the table, work out the value produced by this statement, showing your working. [3]

(b) Write an SQL statement that will output the average TemperatureC of every reading where RainfallMm is greater than 5. [3]

Peak Trail Running Club records the results of every timed run in a single database table called RACE_TIME, shown below.

A club member runs this SQL statement:

SELECT MIN(TimeMinutes)
FROM RACE_TIME;

What value does this statement return?

Neon Arcade tracks every game played in a single database table called GAME_SCORE, shown below. PrizeClaimed records whether a prize has already been given for that score.

(a) Write an SQL statement to output the PlayerName and Points of every score for "Pixel Racer" where the Points are greater than 3000, with the highest Points listed first. [4]

(b) Write an SQL statement to find the highest Points value recorded for "Star Blaster". [2]

(c) Write an SQL statement to output the PlayerName and GameName of every score where a prize has already been claimed and the Points exceed 4000. [3]