Skip to content
Gallery
csH2019paper
about this doc
section 1
section 2
Share
Explore
section 2

icon picker
q-17

You can run and edit code on this page to explore the question and better understand the concepts
aggregate functions,
group by,
column alias
testing
update
⁠
date formats in SQL-lite
format : date('YYYY-MM-DD')
example : select * from bookings where lessonDate = date('2019-01-01')
⁠
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.
instructor
instructorID forename surname
------------ ---------- ----------
1 James Smith
2 Anna Bloggs
3 George Grant
4 Rachel Smith
booking
bookingID instructorID customerID lessonDuration lessonDate lessonTime
---------- ------------ ---------- -------------- ---------- ----------
BK0001 4 1 1 2019-05-01 14:00:00
BK0002 1 2 1 2019-05-22 16:00:00
BK0003 4 1 2 2019-05-08 14:30:00
BK0004 2 3 2 2019-06-12 10:00:00
BK0005 3 2 1 2019-05-29 14:00:00
customer
customerID forename surname address telephone postcode
---------- ---------- ---------- ------------------ ----------- ----------
1 Matthew Thomas 113 Cherry Terrace 07850843527 AB43 4SH
2 Simon Wenger 27 Drummer Street 07892843825 AB33 7QR
3 Nicola McKay 9 Prairie Crescent 07945043032 AB43 9NZ

⁠
part a
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. You should use the customer and booking tables.
You might need: fields, calculations, tables, search criteria, grouping and sort order

(edit querya.sql)
--- paste the result here

--- paste the SQL code here
⁠
part b
The company charges £35 per hour for each lesson booked.
Using the sample data provided, write the SQL statement that would produce the following output.
Total Payments (£)
------------------
245
(edit queryb.sql)

--- paste the SQL code here

⁠
part c
The customer Simon Wenger has moved home. His new address and postcode is 27 Drummer Street, AB33 7QR.
Write the SQL statement to make these changes.
(edit queryc.sql)
--- paste the SQL code here

⁠
part d
Design a query to display the forename, surname and the number of lessons booked for each instructor.
The instructor who has the most lessons booked should be displayed first.
(edit queryd.sql)
--- paste the result here

--- paste the SQL code here

⁠
edit the files querya.sql, queryb.sql, queryc.sql and queryd.sql

⁠
Loading…
⁠
⁠


Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.