Data Definition Language (DDL) & Data Manipulation Language (DML) (Cambridge (CIE) AS 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 (b), 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.