Q4) Consider the following database schema: Library (Branchld, BranchName, Addre

Question

Q4) Consider the following database schema: Library (Branchld, BranchName, Address) Book (BookID, title,publisherNamc) Publisher (Name,address,phone) Book_copies(BookID.branchld.NoOrcopies) Book authors(BookId,AuthorName) Book_loans(Book_id, Branchld.CardNo.DateOut,DueDatc) Borrower(CardNo, Name, Address. phone) Write SQL statements to find the following a) Ilow many copies of book titled " The Lost Tribe" are owned by the library branch whose name is "Al-manara" b) llow many copies ofthe book titled·The lost Tribe.. owned by cach library branch. o) Retrieve the nmames of all burrowers who do not have any books checked our. d) For each book that is loaned out from "Al-Manara" branch and whose due date is today, retrieve the book title, the borrower's name and the borrower's address

Explanation / Answer

a) slect NoOfCopies from Book_copies , Book , Library where Book.title = "The Lost Tribe" and

Library.BranchName = "Al-manara" and

Book.BookID = Book_copies.BookID and

Book_copies.branchId = Library.BranchId;

b) select NoOfCopies,BranchName from Book_copies,Library,Book where Book.title = "The Lost Tribe" and

Book.BookID = Book_copies.BookID and

Book_copies.branchId = Library.BranchId;

c) select Name from Borrower where CardNo NOT IN (select CardNo from Book_loans) ;

d) select Book.title,Borrowers.Name,Borrowers.Address from Book , Borrowers , Book_copies , Book_loans

where BranchName = "Al-manara" and Book.BookID = Book_copies.BookID and

Book_copies.branchId = Book_loans.BranchId and Book_loans.CardNo = Borrowers.CardNo

and getdate() = DueDate ;

  

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.