Data Definition Language (DDL) & Data Manipulation Language (DML) (Cambridge (CIE) A Level Computer Science): Exam Questions

The database also has the table INVOICE that stores data about each invoice that is sent to a customer.

Example data from the table INVOICE is given.

Write a Structured Query Language (SQL) script to return the total amount of all the invoices sent in the year 2023 that have been paid.

Write an SQL script to return the total quantity of ice cream sold to the customer with the ID of 0034E in the year 2023.

The table definition for BATCH is repeated here:

BATCH(BatchID, Type, Flavour, Size, SellingPrice, EndDate)

Sample data for the table BATCH is given:

Write an SQL script to define the table BATCH.

Include constraints (restrictions) on the data that can be entered into each field where appropriate.

The company has another database, FARMING, for a different game.

The database FARMING has a table named EVENT which is shown with some sample data.

(i) The database FARMING has a second table created named PLAYER that has the primary key PlayerID.

The field PlayerID in EVENT needs to be set up as a foreign key to link to PlayerID in PLAYER.

Write a Structured Query Language (SQL) script to change the table definition for EVENT to link the foreign key to PLAYER.

[2]

(ii) Write an SQL script to return the number of events that each player has completed.

[3]

Sample data for the table EXAM is shown:

Write a Structured Query Language (SQL) script to define the table EXAM.

The table EXAM_QUESTION has been created but the foreign key has not been linked.

Write an SQL script to update EXAM_QUESTION and link the foreign key to EXAM.

Sample data for the table PERFORMANCE is shown:

Write a Structured Query Language (SQL) script to define the table PERFORMANCE.

Write an SQL script to return the number of times each show is scheduled. For example, in the sample data in part (a), the show MK12 is scheduled three times.

The result needs to include the show name and a suitable field name for the number of times it is scheduled.

(i) Write a Structured Query Language (SQL) script to define the database called SHOP.

[1]

(ii) Write the SQL script to return the total quantity of items that the customer with the ID of HJ231 has ordered.

[4]

Write the Structured Query Language (SQL) script to return the total number of courses that have started after 9 September 2023.

The value returned must have an appropriate field name.

A company stores its data in a relational database. The database management system (DBMS) uses Structured Query Language (SQL).

SQL has two parts.

(i) State the name of the part of SQL that the DBMS uses to create and change the structure of a database.

[1]

(ii) State the name of the part of SQL that the DBMS uses to add, change and query the data that is stored in a database.

[1]

A library uses a relational database called LIBRARY. The database has the table BOOK, which has the following definition.

BOOK(BookID, Title, Author, YearPublished, OnLoan)

OnLoan is a Boolean field. It stores TRUE when the book is out on loan and FALSE when the book is on the shelf.

(i) The book with the ID B1044 has been returned to the library.

Write a Structured Query Language (SQL) script to change the value of OnLoan for this book to FALSE.

[2]

(ii) The book with the ID B1043 has been damaged and is no longer in the library.

Write an SQL script to remove the record for this book from the table BOOK.

[1]

A music school uses a relational database called MUSIC_SCHOOL. The database has two tables with the following definitions.

STUDENT(StudentID, FirstName, LastName, Grade)

LESSON(LessonID, StudentID, LessonDate, Instrument)

A student wants to return the number of lessons that have been taken by the student with the last name Silva.

The student writes the following Structured Query Language (SQL) script.

SELECT SUM(LessonID)

FROM STUDENT

WHERE STUDENT.LastName = Silva

OR STUDENT.StudentID = LESSON.StudentID;

The script does not work.

Identify the four errors in the script.

A city transport authority is setting up a new relational database to store data about its public bicycle hire scheme. The database will be called CYCLE_HIRE.

Write a Structured Query Language (SQL) script to create the database.

The database CYCLE_HIRE stores data about a public bicycle hire scheme.

Part of the table BICYCLE is shown with some sample data.

BicycleID is the primary key of BICYCLE. Every BicycleID value is exactly six characters long.

The database only supports these data types:

• character

• varchar

• Boolean

• integer

• real

• date

• time

Write a Structured Query Language (SQL) script to define the table BICYCLE.

The database CYCLE_HIRE has two tables with the following definitions.

BICYCLE(BicycleID, Model, DatePurchased, Electric)

HIRE(HireID, BicycleID, MemberID, StartTime)

BicycleID is the primary key of BICYCLE. HireID is the primary key of HIRE.

(i) The table HIRE needs a new field, EndTime, to store the time at which each hire ended.

Write a Structured Query Language (SQL) script to change the definition of the table HIRE to add this field.

[2]

(ii) The field BicycleID in HIRE needs to be set up as a foreign key that links to BicycleID in BICYCLE.

Write an SQL script to change the definition of the table HIRE to add this foreign key.

[2]

A hotel uses a relational database called HOTEL. The database has the table ROOM, which has the following definition.

ROOM(RoomNumber, RoomType, Floor, MaxGuests)

MaxGuests stores the largest number of guests who can sleep in the room.

Write a Structured Query Language (SQL) script to return the room number of every room that can sleep four or more guests.

The room numbers must be listed in order, starting with the smallest.

The database HOTEL has the table GUEST, which has the following definition.

GUEST(GuestID, FullName, Country, LoyaltyMember)

LoyaltyMember is a Boolean field.

Write a Structured Query Language (SQL) script to add a new record to the table GUEST to store the following data.

The database HOTEL has two tables with the following definitions.

GUEST(GuestID, FullName, Country, LoyaltyMember)

BOOKING(BookingID, GuestID, RoomNumber, CheckInDate)

GuestID is the primary key of GUEST and a foreign key in BOOKING. No two guests have the same full name.

Write a Structured Query Language (SQL) script to return the full name of each guest together with the number of bookings that guest has made.

The number of bookings must be returned with the field name NumberOfBookings.