SQL Assignment 2

SQL Assignment 2  T1 (17%)
1. (a) List the total number of items published by each author and exclude author with no name. Sort the
“Total Items Published” in descending.
(b) List the total number of authors for each media code type published and exclude author with no
name.
(c) List the total number of distinct authors and the total number of items published exclude author
with no name.
2. (a) Items that are not “checkedin” are shown as “NULL” in the loan table. List the total number of
items that are not checked in.
Total Number of Non-Checked in Items
(b) List the customer “Full Name” in ascending order, the “Title”, the “Due date” without the time
component, and the “Condition” of the items that are not checked in. You can use the date()
function to extract the date.
Full Name Title Due Date Condition
Firstname, Lastname
3. The account manager would like to compare between genders the number of customers, the number
of times overdue, the total overdue fee and the overdue fee per customer. Sort the “Number of
Customers” in descending order. The result displays only two rows. The overdue fee per customer
displays 2 decimal places.
Author Total Items Published
Media Code Total Number of Authors
Total Number of Authors Total Items Published
*IMPORTANT*
(1) Only use the “join on” syntax for questions that require an inner join.
SELECT column-list
FROM table 1 JOIN table 2 ON joint-condition
Where conditions
(2) For numeric attributes use the “=” sign, for example “percent_time =100” not “percent_time IS 100”.
(3) No need to use sub-query in this assignment.
Gender Number of
Customers
Number of Times
Overdue
Total Overdue
Fee
Overdue Fee Per
Customer
4. List the “CustomerID”, “Full Name”, “Number of Loans” and “Major” for each customer including
customer with no loan and exclude customers with no major. The result in the “Number of Loans”
column should display “0” for customer with no loan. Sort the CustomerID in ascending order.
CustomerID Full Name Number of Loans Major
Firstname, Lastname
5. (a) List the “Full Name” and “Inventoryitemid” of customers who borrowed items with the
inventoryitemid 3 or 5.
Full Name Inventoryitemid
Firstname, Lastname
(b) Now, list the customers who borrowed the two items, inventoryitemid 3 and inventoryitemid 5.
Modify the SQL in part (a) to display a list of distinct “Full Name” and “CustomerID” who meets the
requirement. Use only the aggregate method. Note, a person who borrowed a single item with the
same inventoryitemsid twice or more does not meet the requirement.
Full Name CustomerID
Firstname, Lastname
(c) Repeat the question in (b) but use a self-join method to display a list of distinct “Full Name” and
“CustomerID” who meets the requirement.
6. (a) The manager wants to know the yearly breakdown of the monthly total number of loans and the
monthly total overdue fee for each month. Write the SQL that will meet the requirement. Sort the
results in ascending order by Year and ascending order by Month. Hint: (1) Use CheckedOut date to
extract the year and month when the book is loaned; (2) Use strftime() function to deal with the year
and month. The table below shows a partial example.
Year Month Number of Loans Monthly Overdue Fee
2012 04 1 NULL
2012 05 1 NULL
2012 10 1 NULL
2013 05 1 NULL
2013 07 1 NULL
2013 09 1 NULL
… … … …
2015 04 2 118.0
(b) Modify (a) with one extra line of code to show only the rows with an overdue fee.
~END~