Photo AI

The Caledonian Drone Racing League stores the results of their first season’s competitions in a relational database - Scottish Highers Computing Science - Question 15 - 2023

Question icon

Question 15

The-Caledonian-Drone-Racing-League-stores-the-results-of-their-first-season’s-competitions-in-a-relational-database-Scottish Highers Computing Science-Question 15-2023.png

The Caledonian Drone Racing League stores the results of their first season’s competitions in a relational database. Pilots’ times for each race are recorded in seco... show full transcript

Worked Solution & Example Answer:The Caledonian Drone Racing League stores the results of their first season’s competitions in a relational database - Scottish Highers Computing Science - Question 15 - 2023

Step 1

Design a query to display the output above.

96%

114 rated

Answer

To achieve the output, perform the following steps:

  1. Field(s) and Calculation(s):

    • Select pilotID, forename, surname, and calculate total Winnings from the Entry table where position = 1. Multiply the count of results by £150.
    SELECT pilotID, forename, surname, COUNT(*) * 150 AS Winnings

  2. Tables(s):

    • Use the Pilot and Entry tables.

  3. Search Criteria:

    • Filter results where position = 1.

  4. Grouping:

    • Group the results by pilotID, forename, and surname.

  5. Sort Order:

    • Results can be sorted by pilotID or any other relevant field.

Step 2

Write an SQL statement to display the title of each race and the average time for that race.

99%

104 rated

Answer

To create the SQL statement:

SELECT title, AVG(raceTime) AS AverageTime
FROM Race
INNER JOIN Entry ON Race.raceID = Entry.raceID
GROUP BY title;

Step 3

Using the ‘Fastest’ query, complete the SQL statement below to display the name of the pilot(s) who achieved this time.

96%

101 rated

Answer

To complete the SQL statement:

SELECT forename, surname
FROM Pilot
INNER JOIN Entry ON Pilot.pilotID = Entry.pilotID
WHERE raceTime = (SELECT * FROM Fastest);

Step 4

Explain why this record could not be added to the Entry table.

98%

120 rated

Answer

The record could not be added to the Entry table because the combination of raceID and pilotID must be unique. If a pilot has already competed in a race, adding another entry for the same pilot in the same race violates this uniqueness constraint.

Join the Scottish Highers students using SimpleStudy...

97% of Students

Report Improved Results

98% of Students

Recommend to friends

100,000+

Students Supported

1 Million+

Questions answered

;