Question

Consider the following table schemas (primary keys are underlined): Book (BookId, Title, Publishername)  Book_Authors (BookId, AuthorName)  Publisher (Name, Address, Phone) Book_Copies (BookId, BranchId, No_Of_Copies) Book_Loans (BookId, BranchId, CardNo, DateOut, DueDate)   Library_Branch (BranchId, BranchName, Address)  Borrower (CardNo, Name, Address, Phone) Write SQL statements for the following queries: List the number of copies of the book titled “The Lost Tribe” owned by each library branch and the corresponding library branch name. List the names and addresses of all borrowers who have borrowed more than 5 books that are published by “ABC”. List the name and the phone number of the borrower who borrowed the book authored by “Stephen King” on date “2017-03-05”. List the library branch name where the number of copies of the book titled “The Spring” is greater than the number of copies of the book titled “The Pleasure of Coding”.

Answer 1

1. List the number of copies of the book titled "The Last Tribe" owned by each library branch and the corresponding library branch name.

select bookcopies.No_Of_Copies, library.BranchName
from Book_Copies bookcopies, Library_Branch library
where bookcopies.BookId = library.BranchId and bookcopies.BookId = ( select BookId
from Book where Title="The Lost Tribe");

2. List the names and address of all borrowers who have borrowed more than 5 books that are published by "ABC".

select Name, Address from Borrower
inner join Book_Loans on Borrower.CardNo = Book_Loans.CardNo
inner join Book_Copies on Book_Loans.BookId = Book_Copies.BookId
inner join Book on Book_Copies.BookId =Book.BookId
where Book.Publishername = 'ABC' and Book_Copies.No_Of_Copies > 5;

3. List the name and phone number of the borrower who borrowed the book authored by "Stephen King" on date "2017-03-05".

select Name, Phone
from Borrower
where CardNo = ( select CardNo from Book_Loans
where DateOut = '2017-03-05' and BookId = (select BookId from Book_Authors
where AuthorName = "Stephen King");

4. List the library branch name where the number of copies of the book titled "The Sping" is greater than the number of copies of the book titled "The Pressure of Coding".

select BranchName from Library
where BranchId = (select BranchId from Book_Copies where BookId = (select BookId from Book
where Title = "The Spring" and No_Of_Copies>(select No_Of_Copies from Book_Copies
where BookId = (select BookId from where BookId = (select BookId from Book
where Title = "The Pressure of Coding")))));