For the following questions, use the relational schema defined as follow ISBN, i

Question

For the following questions, use the relational schema defined as follow ISBN, itle, author, edition, year) BookCopy (copyNo, ISBN, available) Borower(borrowerNo, borrowerName, borrowerAddress) BookLoan (copyNo, dateOut, dateDue, borrowerNo) where Book contains details of book titles in the library and the ISBN is the primary key BookCopy cains details of the individual copies of books in the library and copyNo is the primary key. ISBN is a foreign key identifying the book title contains details of library members who can borrow books and borrowerNo is the primary key Borrower BookLoan cotains details of the book copies that are borrowed by library members and key. borrowerNo is a foreign key identifying copyNoldateOut forms the primary the borrower 1. List all book titles 2. List all borrower details, in alphabetical order of borrower name. 3. List all book titles published in the year 2005 written by "J. K. Rowling" 4. How many copies of ISBN “0-7475-8108-8" are there? 5. List all copies of the book with "Harry Potter" keyword in itle that are available for borrowing 6. List the names of borrowers who currently have the book title "Lord of the Rings" on loan 7. List the names of borrowers who will overdue books today 8. List the top 10 most-borrowed books in the library

Explanation / Answer

Note : I am assuming all the relational schemas have the same table names and belong to same database else please edit the answers with database_name.table_name

1. Select title from Book;

2. Select * from Borrower order by borrowerName;

3. Select title from Book where year = '2005' AND author = 'J. K. Rowling';

4. Select count(*) from BookCopy where ISBN = '0-7475-8108-8';

5. Select * from Book INNER JOIN BookCopy on Book.ISBN = BookCopy.ISBN where title like '%Harry Potter%';

6. Select borrowerName from Borrower INNER JOIN BookLoan on borrower.borrowerNo = BookLoan.borrowerNo INNER JOIN BookCopy on BookCopy.CopyNo = BookLoan.CopyNo INNER JOIN Book on BookCopy.ISBN = Book.ISBN where title = 'Lord of the rings';

7. SELECT borrowerName from Borrower INNER JOIN BookLoan on Borrower.borrowerNo = BookLoan.borrowerNo where datedue < 'Select CONVERT(date, getdate())';

8. SELECT TOP 10 ISBN,title from Borrower INNER JOIN BookLoan on Borrower.borrowerNo = BookLoan.borrowerNo INNER JOIN BookCopy on BookCopy.CopyNo = BookLoan.CopyNo INNER JOIN Book on BookCopy.ISBN = Book.ISBN order by count (BookLoan.CopyNo);

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

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