Photo AI

A company offers driving lessons - Scottish Highers Computing Science - Question 17 - 2019

Question icon

Question 17

A-company-offers-driving-lessons-Scottish Highers Computing Science-Question 17-2019.png

A company offers driving lessons. They have a number of instructors and currently organise their customer bookings as shown in the three database tables below. Inst... show full transcript

Worked Solution & Example Answer:A company offers driving lessons - Scottish Highers Computing Science - Question 17 - 2019

Step 1

Design a query to display the customer's forename, surname and the total amount of hours of lessons booked by each customer during May 2019.

96%

114 rated

Answer

To create this query, we will use the Customer and Booking tables. We need to select the customer's forename and surname, along with the total hours of lessons booked in May 2019.

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

    • Select customer.forename, customer.surname, and SUM(booking.lessonDuration) for the calculation of total lessons.

  2. Table(s):

    • Join the Customer and Booking tables on customer.customerID = booking.customerID.

  3. Search Criteria:

    • Filter the results where booking.date is between 01/05/2019 and 31/05/2019.

  4. Grouping:

    • Group the results by customer.forename and customer.surname to aggregate the total lessons per customer.

  5. Sort Order:

    • Sort by customer.surname and then by customer.forename.

Step 2

Using the sample data provided, write the SQL statement that would produce the following output.

99%

104 rated

Answer

To calculate the total payments:

SELECT SUM(lessonDuration) * 35 AS "Total Payments"
FROM Booking;

Step 3

Write the SQL statement to make these changes.

96%

101 rated

Answer

To update Simon Wenger's address:

UPDATE Customer
SET Address = '27 Drummer Street', Postcode = 'AB33 7QR'
WHERE Forename = 'Simon' AND Surname = 'Wenger';

Step 4

Design a query to display the forename, surname and the number of lessons booked for each instructor.

98%

120 rated

Answer

For this query, we need to join both the Instructor and Booking tables and count the lessons:

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

    • Select instructor.forename, instructor.surname, and COUNT(booking.bookingID) as the number of lessons.

  2. Table(s):

    • Join the Instructor and Booking tables on instructor.instructorID = booking.instructorID.

  3. Search Criteria:

    • No additional filters are necessary.

  4. Grouping:

    • Group by instructor.forename and instructor.surname to count lessons per instructor.

  5. Sort Order:

    • Sort the results by the COUNT of lessons in descending order.

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

;