SQL/DDL/DML Assignment 3 (20%)
1. Write the SQL to update the Replacement Cost of all items published between
2005 and 2010. The new replacement cost = old replacement cost + (0.5% of
old replacement cost) X (Published Year - 2005) and at the same time update
the LostFee. The LostFee is the new ReplacementCost plus a re-stocking fee of
$10. (Note: use only one update statement.)
2. Write the SQL to delete the rows in the Loan table where the overdue fee is less
than the average overdue fee in the Loan table. (Note: use only one delete
statement.)
3. Write the SQL statement to update the “DueDate” of all the “DVD” items
borrowed by “INFO” students in the Loan table. The new ”DueDate” is the
original “DueDate” plus 10 days and 12 hours. (Note: ignore the “CheckedIn”
column.)
4. The Library manager plans to group items by genre and asks the database
administrator for these changes to the database. Database administrator makes
the following suggestions. Write the SQL statements to implement his
suggestions.
(a) Create a table named Category. The Category table contains three attributes:
i. CategoryID: Integer, Primary Key (without auto-increment)
ii. CategoryDescription: Text
iii. CategoryCode: Text
(b) After creating the above table, the library manager wants to add a MediaID
attribute which is a foreign key from the Media table to the Category table.
Write the alter SQL to add MediaID in the table. Use the Integer data type
for the MediaID attribute.
*Important*
(1) Use the join syntax below for questions that require an inner join. Other syntax will not
be accepted.
SELECT column-list
FROM table 1 JOIN table 2 ON joint-condition
Where conditions
(2) Use “=” sign, for example “percent_time =100” not “percent_time IS 100”.
5. Use a single insert SQL to insert the data shown in the following table into the
Category table. The insert SQL must include the names of the attributes.
CategoryID CategoryCode CategoryDescription MediaID
1 SCI Science 4
2 MED Medical 4
3 CLA Classics 4
6. Use a correlated sub query method to find the customers who had made at least
one loan. Show the CustomerID and the ‘full name’ (firstname, lastname).(Hint:
use the customer table, the loan table and the “where exists’ method.)
7. Update the customer table to create an email for the active member who has
been a member on and before 2011 and made at least one loan. In the same
update, change the ‘active’ status to ‘active gold’ for those customers who meet
the criteria. The email format is . (Hint: use the
“where exists” method.)
8. Delete the rows in the Loan table which item has been ‘checkedin’ and with a
‘damaged’ status in the InventoryItem table. (Hint: use the “where exists”
method.)