Computer Science Assignment

Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

May/June 2016.P11 and May/June 2016.P12

8 A school stores a large amount of data. This includes student attendance, qualification, and contact details.
The school’s software uses a file-based approach to store this data.

(b) The database design has three tables to store the classes that students attend.

STUDENT(StudentID, FirstName, LastName, Year, TutorGroup)

CLASS(ClassID, Subject)

CLASS-GROUP(StudentID, ClassID)

Primary keys are not shown.

There is a one-to-many relationship between CLASS and CLASS–GROUP.

(iii) Write an SQL script to display the StudentID and FirstName of all students who are in the tutor
group 10B. Display the list in alphabetical order of LastName. [4]

(iv) Write an SQL script to display the LastName of all students who attend the class whose ClassID is
CS1. [4]

Page 1 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

May/June 2016.P13

5 (a) A school stores a large amount of data that includes student attendance, qualification and contact
details. The school is setting up a relational database to store these data.

(d) (i) The database will store each student’s date of birth.
Write an SQL script to add a date of birth attribute to the appropriate table. [2]
(ii) Write an SQL script to display the StudentID, Grade and DateOfAward for the QualCode value
of SC12. [3]
(iii) Write an SQL script to display the FirstName and LastName and QualCode for all STUDENT-
QUALIFICATIONs for which the Grade value is A. [4]

Page 2 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

Oct/Nov 2016.P12

9 A health club offers classes to its members. A member needs to book into each class in advance.

(c) The CLASS table has primary key ClassID and stores the following data:

Write an SQL script to create the CLASS table. [6]

Page 3 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

May/June 2017.P11 and May/June 2017.P13

1 A hospital is divided into two areas, Area A and Area B. Each area has several wards. All the ward names are
different.
A number of nurses are based in Area A. These nurses always work on the same ward. Each nurse has a
unique Nurse ID of STRING data type.

(c) In Area B of the hospital, there are a number of wards and a number of nurses.
Each Area B ward has a specialism.
Each Area B nurse has a specialism.
A nurse can be asked to work in any of the Area B wards where their specialism matches with the ward
specialism.
The relationship for Area B of the hospital is:

(ii) The design for the Area B data is as follows:

B-NURSE(NurseID, FirstName, FamilyName, Specialism)
B-WARD(WardName, NumberOfBeds, Specialism)
B-WARD-NURSE( ............................................................................................................ )
Complete the attributes for the third table. Underline its primary key. [2]

(d) Use the table designs in part (c)(ii).

(i) Write an SQL query to display the Nurse ID and family name for all Area B nurses with a
specialism of ‘THEATRE’. [3]
(ii) Fatima Woo is an Area B nurse with the nurse ID of 076. She has recently married, and her
new family name is Chi.
Write an SQL command to update her record.

Update .....................................................................................................................
Set ............................................................................................................................
Where .......................................................................................................................

[3]

Page 4 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

May/June 2017.P12

1 Some shops belong to the Rainbow Retail buying group. They buy their goods from one or more
suppliers.
Each shop has:
• a unique shop ID
• a single retail specialism (for example, food, electrical, garden).

Each supplier has:

• a unique supplier ID
• a similar single specialism recorded.

Rainbow Retail creates a relational database to record data about the shops and their suppliers.

The entity-relationship (E-R) diagram for the relationship between the SHOP and SUPPLIER tables is
shown.

The database design is as follows:

SHOP(ShopID, ShopName, Location, RetailSpecialism)

SUPPLIER(SupplierID, SupplierName, ContactPerson, RetailSpecialism)

SHOP-SUPPLIER(ShopID, SupplierID)

The SHOP–SUPPLIER table stores the suppliers that each shop has previously used.

Primary keys are not shown.

Page 5 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

(c) (i) Write an SQL query to display the shop ID and location of all shops with a ‘GROCERY’ specialism. [2]

(ii) The existing shop with ID 8765 has just used the existing supplier SUP89 for the first time.

Write an SQL script to add this data to the database. [3]

Oct/Nov 2017.P11 and Oct/Nov 2017.P13

7 A clinic is staffed by several doctors. The clinic serves thousands of patients. Each day and at any one time,
there is only one doctor in the clinic available for appointments.

The clinic stores patient, doctor and appointment data in a relational database.

(a) (i) PATIENT(PatientID, PatientName, Address, Gender)

DOCTOR(DoctorID, Gender, Qualification)

APPOINTMENT(AppointmentDate, AppointmentTime, DoctorID, PatientID)

[2]

(d) The doctor with the ID of 117 has recently been allocated a new DoctorID of 017.

(i) Write an SQL script to update this doctor’s record in the database.

UPDATE .............................................................................................................................

SET.....................................................................................................................................

WHERE ...............................................................................................................................

[3]

(ii) Describe why this update could cause problems with the existing data stored. [2]

(e) Write an SQL script to display the date and time of all appointments made by the patient with the
PatientID of 556. [3]

Page 6 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

Oct/Nov 2017.P12

7 A company takes customer service for its clients very seriously.

The client

• The client names are unique.

A visit

• The company arranges a date for a visit to gather feedback from a client.

• A visit to a client never takes more than one day.

• Over time, the client receives many visits.

Staff (Interviewers)

• One or more staff attend the visit.

• If there is more than one staff member visiting, each performs a separate interview.

Interviews

 Each interview is classified as either ‘general’ or by some specialism, for example, marketing,
customer service or sales.
 A report is produced for each interview, InterviewText.
 Each interview is conducted by a single staff member.

The client, visit, staff and interview data will be stored in a relational database.

(a) (i)

STAFF(StaffID, StaffName, Department)

CLIENT(ClientName, Address, Town)

VISIT(ClientName, VisitDate)

INTERVIEW(ClientName, VisitDate, StaffID, SpecialistFocus, InterviewText)

Page 7 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

(c) Client ABC Holdings are now trading under the name of Albright Holdings.

(i) Write an SQL script to update this client’s records in the database.

UPDATE .............................................................................................................................

SET ....................................................................................................................................

WHERE ............................................................................................................................... [3]

(ii) Describe why this update could cause problems with the existing data stored. [2]

(d) Write an SQL script to display the Staff ID of each member of staff who performed an interview when
they visited New Age Toys on 13/10/2016. [3]

Page 8 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

May/June 2018.P12

7 A social media website has a relational database, WEBDATA, that stores the site’s information.

The database has three tables to store users’ details, and details of the images and text that they post.

USER(UserName, FirstName, SecondName, DateOfBirth)

PHOTO(PhotoID, UserName, Comment, UploadDate)

TEXTPOST(PostID, UserName, DateOfPost, TheText)

(d) The following shows sample data from the USER table.

(i) Write an SQL script to create the USER table. [5]

(ii) The database administrator needs to alter the USER table. A new field, Country, needs to be added.

Write an SQL script to add the field Country to the USER table. [2]

Page 9 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

May/June 2018.P13

2 A company writes applications (apps) for smartphones. The company has a relational database,
PURPLEGAME, which stores the information for one of its online game apps.

The database has three tables to store player’s details, dates when they have logged into the app and in-app
purchase details.

LOGIN(LoginID, PlayerID, Date)

PURCHASE(PurchaseID, PlayerID, PurchaseDate, Cost)

PLAYER(PlayerID, PlayerName, SkillLevel)

(d) (i) The following table shows some sample data for the PLAYER table.

Write an SQL script to create the PLAYER table. [5]

(ii) The table, PLAYER, needs to be altered. A new field, DateOfBirth, needs to be added.

Write an SQL script to add the DateOfBirth field to the PLAYER table. [2]

Page 10 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

Oct/Nov 2018.P11

7 (c) The database needs to store the name of the company that produced each movie, for example,
Rocking Movies.

Write an SQL script to add the attribute ProductionCompany to the MOVIE table. [2]

(d) Write an SQL script to display the title and rating of all movies scheduled to play on screen number 3. [4]

May/June 2019.P11

2 (c)(iii) The company needs a list of all software licences that have an expiry date on or before 31/12/2019.

Write an SQL query to return the fields CustomerID, SoftwareID, LicenceType, Cost and
ExpiryDate for all licences that expire on, or before 31/12/2019. Group the output by CustomerID,
and in ascending order of cost. [5]

May/June 2019.P13

3 A company uses a relational database, EMPLOYEES, to store data about its employees and departments.

(e) Part of the EMPLOYEE_DATA table is shown.

(i) Write a Data Definition Language (DDL) statement to create the EMPLOYEES database. [1]

(ii) Write a DDL statement to define the table EMPLOYEE_DATA, and declare EmployeeID as the primary
key. [5]

Page 11 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

(iii) Write a Data Manipulation Language (DML) statement to return the first name and last name of all
female employees in the department named Finance. [5]

Oct/Nov 2019.P12

4 Anushka needs to store information about bookings at a sports club.

(b)(ii) Anushka first needs to create the database that she has designed.

Write a Data Definition Language (DDL) statement to create the SPORTS_CLUB database. [1]

(iii) The table shows some sample data for the table SESSION.

Write a DDL script to create the table SESSION. [5]

(iv) Write a Data Manipulation Language (DML) script to return the first name and last name of all members
who have Peak membership type. [3]

Page 12 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

Oct/Nov 2019.P13

3 A hotel needs to record information about customers and their bookings.

(c) The following table has four SQL scripts.

Tick (✓) one box in each row to identify whether the script is an example of a Data Definition Language
(DDL) statement or a Data Manipulation Language (DML) statement.

[2]

May/June 2020.P11

7 A driving school teaches people how to drive cars. The school has a relational database,
DRIVING_SCHOOL, to store information about instructors, students, lessons and the cars used by
instructors.

INSTRUCTOR(InstructorID, FirstName, LastName, DateOfBirth, Level)

CAR(Registration, Make, Model, EngineSize)

INSTRUCTOR_CAR(InstructorID, Registration)

STUDENT(StudentID, FirstName, LastName, DateOfBirth, Address1)

Page 13 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

LESSON(LessonID, StudentID, InstructorID, LessonDate, LessonTime)

(a) Give two benefits to the driving school of using a relational database instead of a flat file. [2]

(c) The table shows some sample data for the table INSTRUCTOR.

Complete the Data Definition Language (DDL) statement to create the table INSTRUCTOR

………………………………………………………………………… TABLE INSTRUCTOR(

InstructorID VARCHAR(5),

FirstName VARCHAR(15),

LastName VARCHAR(15),

DateOfBirth DATE,

Level ………………………………………………………………………… ,

………………………………………………………………………… (InstructorID)

); [3]

(d) The table STUDENT needs an additional field to store the student’s telephone number, for example 012-
3456.

Write a Data Definition Language (DDL) statement to add the new field to the table STUDENT. [2]

Page 14 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

(e) Write a Data Manipulation Language (DML) statement to return the date and time of all future lessons
booked with the instructor whose InstructorID is Ins01. [4]

May/June 2020.P12

6 A software development company has a relational database, SOFTWARE_MANAGEMENT. The database

stores details of the customers who have purchased software, as well as the software and licences that
customers have purchased.

The SOFTWARE_MANAGEMENT database has the following tables:

CUSTOMER_DETAILS(CustomerID, CompanyName, Address1, Address2, City)

SOFTWARE_PURCHASED(SoftwareName, SoftwareDescription, CustomerID,

LicenceType, LicenceCost, RenewalDate)

(c) The company also develops computer games. They extend the relational database
SOFTWARE_MANAGEMENT by adding a new table. The new table, GAME_DEVELOPMENT, stores details
about the games and the software development teams creating them.

The table shows example data in GAME_DEVELOPMENT.

Page 15 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

(i) Complete the Data Definition Language (DDL) statement to create the table GAME_DEVELOPMENT.

CREATE ………………………………………………………… ………………………………………………………… (

GameName VarChar,

Genre VarChar, ………………………………………………………… ………………………………………………………… ,

DevelopmentStage VarChar,

ManagerID VarChar,

………………………………………………………… (GameName)

); [5]

(ii) Another table, PRODUCT_MANAGER, is created.

PRODUCT_MANAGER(ManagerID, FirstName, LastName)

Complete the Data Manipulation Language (DML) statement to return the game name, genre and team
number of all games managed by the product manager with the first name ‘James’ and the last name ‘Fitz’.

………………………………………………………… GameName, Genre, TeamNumber

FROM GAME_DEVELOPMENT, PRODUCT_MANAGER

WHERE PRODUCT_MANAGER.FirstName = "James"

AND PRODUCT_MANAGER.LastName = "Fitz"

AND …………………………………………………………………………………………………

= …………………………………………………………………………………………………… ; [3]

Page 16 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

May/June 2020.P13

6 Sheila creates a relational database for her hotel using a Database Management System (DBMS).

(c) Sheila creates the database HOTEL with the following table structure:

ROOM(RoomNumber, RoomType)

BOOKING(BookingID, RoomNumber, CustomerID, StartDate)

CUSTOMER(CustomerID, FirstName, LastName, Address, Tel_Num)

(i) The following table shows some sample data for the table ROOM.

Complete the Data Definition Language (DDL) statement to create the table ROOM.

................................................ TABLE ROOM(

RoomNumber Integer,

RoomType ................................................,

................................................ (RoomNumber)

); [3]

Page 17 of 18
Computer Science Assignment
Topical Past Papers

1.8.3 Data Definition Language and Data Manipulation Language

(ii) Room number 5 is a Double room.

Complete the Data Manipulation Language (DML) statement to add the details for room number 5 to the
table ROOM.

INSERT ................................................ ROOM

VALUES(......................................................................................................); [2]

(iii) The table BOOKING needs an additional field to store the number of nights (for example, 3) a customer
is staying.

Write a Data Definition Language (DDL) statement to add the new field to the table BOOKING. [2]

Page 18 of 18