Write SQL Queries based on the following relationships: Table 1: Relationships T

Question

Write SQL Queries based on the following relationships:

Table 1: Relationships

Table Name

Columns

Book

Book ID (Primary Key), Title, PublisherName

Publisher

Name (Primary Key), Address, Phone

Book_Authors

BookId (Foreign Key), AuthorName (PrimaryKey)

Book_Loans

BookId (Foreign Key), BranchId (Foreign Key), CardNo (Foreign Key), DateOut, DueDate

Borrower

CardNo (Primary Key), Name, Address, Phone

Book_Copies

BookId (Foreign Key), BranchId (Foreign Key), No_Of_Copies

Library_Branch

BranchId (Foreign Key), BranchName, Address

Q1) Write SQL Select Query that shows “For each book that is loaned out from the ‘Sharpstown’ branch and whose DueDate is today, retrieve the book title, the borrower’s name, and the borrower’s address.”

Q2) Write SQL Select Query that shows “Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.”

Q3) Write sequence of SQL INSERT statements to put this information into the database without violate any constraints. “We’ve been busy at the library. We have a new book borrower, Bugs Bunny, assigned a CardNO of 70, who lives at 12 Tune St, Tune Town, MN phone 555-555-5555. Bugs wants to borrow the book Exam One, identified as 456123 published by Pratt and Whitney. Bugs wants to borrow 1 copy for his reading circle from the library branch near him so he will pick up the copy at New Borough Branch B, identified by 21b and located on 222 Grant, Eden Prairie. We just received 200 copies of this book at branch 21b. Bugs needs to check out Exam One from 06/12/2001 to 07/12/2001. “

Q4) Write SQL Udate Query that shows “Now Bugs was given a new CardNO of 200. Write the sequence of SQL statements to update all records pertaining to Bugs new CardNO.”

Q5) Write SQL Delete Query that shows “Now assume Bugs was such a terrible book borrower that the head librarian demanded that all record of him must be deleted from the library database. Write the sequence of SQL statements to remove Bugs from all records.”

Table Name

Columns

Book

Book ID (Primary Key), Title, PublisherName

Publisher

Name (Primary Key), Address, Phone

Book_Authors

BookId (Foreign Key), AuthorName (PrimaryKey)

Book_Loans

BookId (Foreign Key), BranchId (Foreign Key), CardNo (Foreign Key), DateOut, DueDate

Borrower

CardNo (Primary Key), Name, Address, Phone

Book_Copies

BookId (Foreign Key), BranchId (Foreign Key), No_Of_Copies

Library_Branch

BranchId (Foreign Key), BranchName, Address

Explanation / Answer

Q1)
select title,name,address
from book_loans natural join book natural join borrower natural join library_branch
where branchname='Sharpstown' and duedate = now()::timestamp::date;

Q2)
select name,address,count from (select cardno,count(cardno) from book_loans group by cardno having count(cardno)>5) as temptable natural join borrower;

Q3)

INSERT INTO borrower VALUES (70, 'Bugs Bunny','12 Tune St, Tune Town, MN','555-555-5555');
INSERT INTO book_copies VALUES (456123,'21b',200);
INSERT INTO book_loans VALUES (456123,'21b',200,'06/12/2001','07/12/2001');

Q4)

--update in borrower table first then in book_loans table

--cardno is used only in book_loans table so we can do.

UPDATE borrower SET cardno=200 WHERE cardno=70;

UPDATE book_loans SET cardno=200 WHERE cardno=70;

Q5)

-- delete from book_loans first then delete in borower table

DELETE FROM book_loans WHERE cardno=70;

DELETE FROM borrower WHERE cardno=70;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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