Consider the given Relational database schema for a LIBRARY database. The primar
ID: 3842342 • Letter: C
Question
Consider the given Relational database schema for a LIBRARY database. The primary key of each relation is underlined. BOOK (Bookld, Title, PublisherName, PublishingYear) BOOK_AUTHORS (Bookld, AuthorName) PUBLISHER (Name, Address, Phone) LIBRARY_BRANCH (Branchld, BranchName, Address) BOOK_COPIES (Bookld, Branchld, No_of_Copies) BORROWER (CardNo, Name, Address, Phone) BOOK_LOANS (Bookld, Branchld, CardNo, Dateout, ReturnDate) Specify the following queries in SQL2 on the same given database schema: a) List titles of all books authored by Deitel" published in the last five years (2001-2006), and owned by the "Central" library ("Central" is a library branch name). b) List titles of all books that are available in the "Rare Books" branch and not available in the "Central" branch ("Central" and "Rare Books" are library branch names). c How many book copies of each publisher in each library branch? d) Retrieve library branches that have more than 20 books checked out during the first ten days of December 2006, and not yet returned back to the library branch (ReturnDate not yet recorded). e) Retrieve authors who have books published only in the current year (2006). f) Create a view to get the total number of books in each year for each author. g) Redo query (e) using the created view.Explanation / Answer
a)
Select Title from BOOK inner join BOOK_AUTHORS on BOOK.BookId = BOOK_AUTHORS.BookId inner join PUBLISHER on BOOK.PublisherName = PUBLISHER.Name inner join BOOK_COPIES on BOOK.BookId = BOOK_COPIES.BookId inner join LIBRARY_BRANCH on BOOK_COPIES.BranchId = LIBRARY_BRANCH.BranchId where BOOK_AUTHORS.AuthorName = 'Deitel' and BOOK.PublishingYear >= 2001 and BOOK.PublishingYear <= 2006 and LIBRARY_BRANCH.BranchName = 'Central' ;
b)
Select Title from BOOK inner join BOOK_COPIES on BOOK.BookId = BOOK_COPIES.BookId inner join LIBRARY_BRANCH on BOOK_COPIES.BranchId = LIBRARY_BRANCH.BranchId where LIBRARY_BRANCH.BranchName = 'Rare Books' and LIBRARY_BRANCH.BranchName != 'Central';
c)
Select No_Of_Copies from BOOK_COPIES inner join BOOK on BOOK.BookId = BOOK_COPIES.BookId inner join PUBLISHER on BOOK.PublisherName = PUBLISHER.Name inner join LIBRARY_BRANCH on BOOK_COPIES.BranchId = LIBRARY_BRANCH.BranchId group by PUBLISHER.Name, LIBRARY_BRANCH.BranchName;
d)
Select BranchName from LIBRARY_BRANCH inner join BOOK_LOANS on LIBRARY_BRANCH.BranchId = BOOK_LOANS.BranchId where COUNT(BOOK_LOANS.BookId) > 20 and BOOK_LOANS.DateOut >= '12/01/2006' and BOOK_LOANS.DateOut <= '12/10/2006' and BOOK_LOANS.ReturnDate IS NULL;
e)
Select AuthorName from BOOK_AUTHORS inner join BOOK on BOOK.BookId = BOOK_AUTHORS.BookId inner join PUBLISHER on BOOK.PublisherName = PUBLISHER.Name where BOOK.PublishingYear = 2006;
f)
Create view TotalBooks as Select count(*) from BOOK inner join BOOK_AUTHORS on BOOK.BookId = BOOK_AUTHORS.BookId group by BOOK_AUTHORS.AuthorName , BOOK.PublishingYear;
g)
Select AuthorName from TotalBooks where PublishingYear = 2006;