Question

The CIS Department at Tiny College maintains the Free Access to Current Technology (FACT) library of e- books. FACT is a collection of current technology e-books for use by faculty and students. Agreements with the publishers allow patrons to electronically check out a book, which gives them exclusive access to the book online through the FACT website, but only one patron at a time can have access to a book. A book must have at least one author but can have many. An author must have written at least one book to be included in the system but may have written many. A book may have never been checked out but can be checked out many times by the same patron or different patrons over time. Because all faculty and staff in the department are given accounts at the online library, a patron may have never checked out a book or they may have checked out many books over time. To simplify determining which patron currently has a given book checked out, a redundant relationship between BOOK and PATRON is maintained. The ERD for this system is shown below.

CHECKOUT Check Num PK PATRON PK Pat ID -----H FK1 FK2 Book_Num Pat_ID Check Out Date Check_Due_Date Check_In_Date Pat_FName Pat_L Name Pat_Type BOOK PK Book Num AUTHOR Au ID PK DO---- Book Title Book_Year Book_Cost Book_Subject FK1 Pat_ID Au_FName Au_LName Au Birth Year WRITES PK,FK1 Book Num PK,FK2 Au ID

1. Write a query to display checkout number, checkout date, due date, book number, and book title for every checked out that has ever occurred in the system. Sort the output by book title (ascending).

2. Write a query to display number of books that are currently checked out (not available).

3. Write a query to display book subject, the average cost of books within that subject, and the difference between average cost of book within that subject and average cost of all books. Sort the results by book subject (ascending).

4. Write a query to display book subject, maximum and minimum number of days a book is kept during a check out within that subject (group by book subject). Sort the results by book subject (ascending).  

Answer 1

1.

SELECT CHECK_NUM, CHECK_OUT_DATE, CHECK_DUE_DATE, BOOK_NUM, BOOK_TITLE

FROM CHECKOUT

ORDER BY BOOK_TITLE;

2.

SELECT Count(BOOK_NUM) AS "Unavailable Books"

FROM BOOK

WHERE PAT_ID IS NOT NULL;