- Driver Primary Key Driver Id Driver Id Driyer Name Driyer Type 1 Cl818 Charles Leung Lgv 2 Sw572 Sara Wong Medium Truck 1 (97.81 KiB) Viewed 45 times
DRIVER Primary Key: DRIVER_ID DRIVER_ID DRIYER_NAME DRIYER_TYPE 1 CL818 Charles Leung LGV 2 SW572 Sara Wong Medium Truck 3 PT232 Peter Tang Medium Truck 4 EC625 Emily Cheung Heavy Truck > VM888 Vincent Ma LGV 6 DW365 David Wu LGV VEHICLE Primary Key: VEH_ID VEH_ID VEH_DESC 1 LGV859 Toyota Hiace 2 LGV325 Toyota Hiace 3 FM1208 Volvo FM12 4 FH1601 Volvo FH16 VEH_TYPE LGV LGV Medium Truck Heavy Truck BOOKING Primary Key: BOOK_ID Foreign Keys: BOOK_VEH. BOOK DRIVER BOOK_ID BOOK_VEH BOOK_DRIVER BOOK_DATE BOOK_TIME BOOK_HRS 1 B723 LGV325 VM888 2022/3/4 10:15 2.5 2 B724 FH1601 EC625 2022/3/4 10:30 5 3 B725 LGV859 DW365 2022/3/4 11:00 2 4 B726 FM1208 PT232 2022/3/4 11:00 3 5 B727 LGV325 CL818 2022/3/4 14:00 1.5 6 B728 FM1208 VM888 2022/3/4 14:30 2 7 B729 LGV859 DW365 2022/3/4 14:45 1.5 un nu in un in Q8. Write an SQL statement to answer the question: "Compute the total hours driven by drivers in the sequence of driver name. The resulting table is as follows. sorted by driver name. (8 marks) DRIVER_NAME TOTAL_HOURS 1 Charles Leung 1.5 2 David Wu 3 Emily Cheung 4 Peter Tang Vincent Ma 3.5 5 3 4.5 Q9. Write an SQL statement to answer the question: "Are there any bookings not driven by qualified drivers, i.e. driver type not matching vehicle type?' The resulting table is as follows. (8 marks) BOOK_ID VEH_DESC VEH_TYPE DRIVER_NAME DRIVER_TYPE BOOK_DATE BOOK_TIME BOOK_HRS 8728 Volvo FM12 Medium Truck Vincent Ma LGV 2022/3/4 14:30 2 Q10. In view of the possible problem as per 29, suggest a way to prevent such booking. You may consider running query to highlight this or changing the data model to prevent so. Explain your suggestion. (6 marks)