Question
use the following database: BOOK(pages,title,isbn,category,author,year,press) PATRON(name,id,age,address) BORROWS(bisbn,pid,day,month,year) Assume that isbn is the primary key in BOOK and id is the primary key in PATRON. The
use the following database:
BOOK(pages,title,isbn,category,author,year,press)
PATRON(name,id,age,address)
BORROWS(bisbn,pid,day,month,year)
Assume that isbn is the primary key in BOOK and id is the primary key in PATRON. The category of a book can be one of fiction, non-fiction, comedy, biography, or similar; author is the name of the author of the book (note that this design assumes each book has only one author).
In BORROWS, bisbn is a foreign key to BOOK and pid is a foreign key to PATRON, and together they form the primary key. This means that a patron may borrow several books and a book may be borrowed by several patrons.
SQL Write the following queries in SQL. You can use (and reuse) temporary tables and subqueries, but no rank, limit or outer joins.
(a) List the names of patrons who have borrowed two or more books.
(b) List the isbn of books that have never been borrowed by a patron over 50 years old.
(c) List the titles of books borrowed by any patron who has borrowed Macbeth (that is the title of a book)
(d) List, for each patron, the number of books s/he has borrowed, but only if the patron has borrowed at least 3 books.
(e) List the names of patrons who have borrowed the same book as Mr. Jim Jones (another patron).
(f) The usage of a book is the number of times it has been borrowed. List the titles of books with a usage larger than the average usage (average over all books, that is).
(g) List the year(s) of peak usage, that is, those where the number of books borrowed has been the highest.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started