Implementation (SQA National 5 Computing Science): Exam Questions

A car retailer has four showrooms.

A relational database is used to store details of the four showrooms and the cars they have for sale.

An SQL statement is implemented to find all two seater cars and produces the output below.

Write the SQL statement that will produce this output, in the order shown.

One functional requirement is to output the make, model and price of cars costing less than 60000 which are not in Glasgow.

SELECT make, model, colour, salePrice
FROM Car
WHERE showroomID = "Abd"
AND salePrice < 60000;

Give two reasons why the SQL statement would not produce the required output.

A model collector keeps information about the items collected in a database. A query has been run and the output is shown below.

Complete the SQL statement used to produce this sorted output.

SELECT modelNumber, title, theme, pieceAmount, cost

FROM Model

ORDER BY......................................................................................................

A band booking company has a range of bands that can be booked for various events. It wants a database to store information on bands and their bookings.

A band can play more than one event. An event can only have one band.

When a band is being booked for an event the form below is completed.

When the band has been booked, the following e-mail is sent to the organiser

The database has been created. Sample data from each table is shown below.

Complete the SQL statement below that will display the name of any country band playing at ‘The Old Poet’ and the date of the event.

SELECT ...............................................................................................

FROM..................................................................................................

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

Caledonian Wheels organises a motorsport racing championship. A database is used to store data on racing teams and their individual drivers. The data stored in the database is shown below.

The following SQL statement is implemented in the database.

SELECT teamID, teamName, titlesWon
FROM Team
WHERE (teamID = "PV81" OR location = "Italy") AND titlesWon >=5

Write the expected output from this SQL statement.

Jackie West won her 2nd race of the season increasing her points to 50.

Write the SQL statement to update Jackie’s details.

A new driver is to be added.

INSERT INTO Driver (driverNum, forename, surname, wins,
points, teamID)
VALUES (99, "Thomas", "Webb", 0, 0, "PC81")

When this SQL statement is run, an error message is displayed stating that the record cannot be added.

Explain why an error message is displayed.

Teams take part in a technology competition. Their details are stored in a table called Team. The sorted output below was produced by executing a query.

Complete the SQL statement used to produce this sorted output.

SELECT teamID, schoolID, teamName, noOfMembers, technology

FROM Team

ORDER BY...................................................

Brainy Bunch from schoolID 214 has been disqualified from the competition.

Write the SQL statement to remove them from the table.

The Games Lab is a video game testing company that programmers can send games to for testing. Each game is allocated to a tester who works on several different games. Testers are based in three offices — Scotland, France and Australia — and only accept games rated 12A, 15 and 18.

The Game table is implemented. Some of the data it currently stores is shown below.

The game ‘Water Rage’ has been reclassified as an 18 rating. The following SQL statement was implemented to make this change.

UPDATE Game
SET rating = "18"
WHERE programmer = "CodeQueen"

Explain why the SQL statement above would give an unexpected result.

Rewrite the SQL statement to give the expected result.

A database stores data on a local netball league. The Club table stores data of the clubs that play in the league. The Player table stores data on each player.

Sample data from each table is shown below.

(i) The netball league wants a list of players who meet at least one of the following criteria:

• train in Lanarkshire

• play for a team in division 2.

Complete the SQL query below.

[4]

SELECT clubName, name, playerID
FROM...........................
WHERE..........................

(ii) Describe how this SQL query should be tested.

[2]

Explain why it is better to have referential integrity implemented before adding a new player to the netball database.

The sorted output below was produced by running a query in a database.

Complete the SQL statement used to produce this sorted output.

SELECT contractCode, contractLength, phoneType, storage
FROM contract
ORDER BY...............................................

A window cleaning company wishes to create a database to store information about staff and the houses they clean. The company provides examples of information they would like to include.

The relational database is implemented. Sample data from both tables is shown below.

The database is used to identify all customer names and addresses that have 5 or more windows and are allocated to staff member Fatima Khan.

Complete the SQL statement below that would produce this output.

SELECT ..................................................................................................

FROM .....................................................................................................

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

[5]

(ii)

The owner at 15 Robin Avenue has moved house. The new owner’s name is Sue Horaz. They will still pay via direct debit.

Complete the SQL statement below to update this record.

UPDATE House

SET...........................................................................................
WHERE houseID = "EDI3189" ;

[1]

The following details are to be added into the House table.

When attempting to add this data, the following error message is displayed.

Explain the reason for this.

Bright Stars organise and run classes for children. Classes are organised into blocks, each running for 8 weeks.

The table Class stores data about the classes available.

The table Booking stores data about the children and their parents/guardians.

Sample data from each table is shown below.

A new class is going to be added for Autumn which will be run by Adam. This is added by executing the following SQL statement.

INSERT INTO Class (classID, leaderName, day, time,
location, sessionBlock, classAge)
VALUES ("B1-497", "Adam", "Mon", 15:30, "Grange","Autumn",
"Babies");

Tim Roberts, who made booking TR238270, would like to book their child Lucas onto this new class. He has not yet paid for the class.

Write an SQL statement that will add this booking to the Booking table, using the bookingID TR653726.

A database table stores the following data.

The following SQL statement is implemented in the database.

SELECT forename, surname
FROM Result
WHERE test1 < 10 OR test2 > 90;

Write the expected output from the SQL statement.

A property renovation company requires a relational database to store information about the tasks to be carried out on each room of a house.

The functional requirements for the database are identified:

• Store details of each room in the house.

• Store details of each task.

• Output a list of tasks carried out by one of the following trades people: electrician, builder, plasterer, decorator, carpenter, plumber.

• Output a list of tasks completed for a single room.

The relational database is implemented. The data it currently stores is shown below.

A porch is to be built onto the front of the house.

The porch is added as a new room by executing the following SQL statement.

INSERT INTO Room (name,width,length,height,floor)
VALUES ("Porch",1.20,1.40,2.10,"ground");

The task ‘construct porch’ needs to be added. This task was started by a builder on 19 April 2022. The work is not completed so the hours taken and cost are not yet known.

Write an SQL statement that will add this work to the Task table.

The bedroom is no longer being renovated.

The following SQL statements are written to remove the bedroom tasks from the Task table.

DELETE FROM Task
WHERE taskID = 5;
DELETE FROM Task
WHERE taskID = 7;
DELETE FROM Task
WHERE taskID = 8;

Write a single efficient SQL statement to remove the bedroom tasks.