SQL Assignment 3

SQL Assignment 3 (18%)
1. In the loan table, items that are still on loan appear as NULL in the “checkedin” column. For
the items still on loan, write the SQL using subquery to find the MediaID which has a maximum
total replacement cost and the total number of loans. The total replacement cost is the sum
of the replacement cost for the items on loan. (Note: You may need multiple subquery)
MediaID Maximum Total Replacement Cost Total Number of loans
2. Use the SQL subquery method to show the total number of students with an average overdue
fee less than twice the average overdue fee of ‘Soing, Kim’. An example of the result is shown
below. (Note: You can put a subquery as a field in the main select statement).
Total Number of Students Kim Average Overdue Fee
6 11.5
3. Write the SQL using subquery method to update the ‘DueDate’ of all the items with a
MediaCode= ‘FD’ borrowed by the ‘INFO’ students where the ‘CheckedIn’ column is NULL in
the Loan table. The new ‘DueDate’ is the original ‘DueDate’ plus 7 days, 7 hours and 7 minutes.
(Note: The update query might not update any rows. This question uses only a single update).
4. Tranzit-Bus Company wants to record the passenger's information on the time a passenger
gets on the bus and alight from the bus. The database administrator suggests using three
tables, the BusReg table, Passenger table and the Transport table. The requirements for each
of the tables are list below.
*IMPORTANT*
(1) Use the SQL syntaxes that are taught in lectures and workshops. Other syntax will not be
accepted. Ask a tutor if you are not sure.
(2) Use the join syntax below for an inner join. Other syntax will not be accepted.
SELECT column-list
FROM table 1 JOIN table 2 ON joint-condition
Where conditions
(3) For numeric, use “=” sign, for example “percent_time =100” not “percent_time IS 100”.
(4) Some questions may need to use subquery.
(a) Write the DDL to create the tables with the suggested attributes, data types and
constraints.
(b) Which table should be created last? Why?
(c) What is the entity relationship between the BusReg table and the Passenger table?
(d) Name the type of primary key for the Transport table.
PRIMARY KEY("PassengerID")
PRIMARY KEY("BusID", "Date","PassengerID","TIMEIN")
FOREIGN KEY("BusID")
FOREIGN KEY("PassengerID")
5. For the table data given below, write the insert SQL to populate the BusReg table, Passenger
table, and the Transport table. Use only the single insert method to complete the task. Which
table you should insert last and why?
6. This question is based on the tables in Question 5. COVID-19 requires the database developer
to construct SQL query to extract any pair of passengers (A and B) who might contact each
other in the bus.
(a) Write the SQL to extract the pairs of passengers. The output table should show
A_passengersID, B_passengerID, date, route, A_TimeIN, A_TimeOut and B_TimeIN. (Note:
Consider self-join, date, time and bus in the ON conditions).
(b) Write the SQL to determine the number of times the pair of passengers contacted each
other, group the output by date, A_passengerID and B_passengerID.
Date A_PassengerID B_PassengerID Number of Times
Contacted
7. Delete the rows in the Loan table where the item has not been ‘checkedIn’ and with a ‘Lost’
status in the InventoryItem table. (Note: This question accepts only the ‘where exists’
method. The query may or may not delete any rows.)
~END~