Question
Use the given schema (primary keys are bolded) : Book( bookID , ISBN, title, author, publish-year, category) Member( memberID , lastname, firstname, address, phone-number, limit)
Use the given schema (primary keys are bolded) :
Book(bookID, ISBN, title, author, publish-year, category)
Member(memberID, lastname, firstname, address, phone-number, limit)
CurrentLoan(memberID, bookID, loan-date, due-date)
History(memberID, bookID, loan-date, return-date)
Members can borrow books from the library. The number of books they can borrow is limited by the limit field of the Member relation (it may differ for different members). The category of a book includes fiction, non-fiction, childrens and reference. The CurrentLoan table represents the information about books that are currently checked out. When the book is returned to the library, the record will be removed from CurrentLoad relation, and will be inserted into History relation with the return-date. A library may have more than one copy of the same book, in which case each copy has its own bookID, but all copies share the same ISBN.
Write SQL statements for each of the following questions:
1. Find the bookID, title, author, and publish year of the book that has the oldest publish-year.
2. Find the lastname and firstname of members who have borrowed both books titled Dogs and cats and Dogs are the best either currently or in the past.
3.Find the name of the author(s) that has the largest number of different books owned by the
library (multiple copies of the same book only count as one book).
4.For each member (using both member ID, last name, and first name), list the number of book he/she has currently checked out, and the number of books he/she checked out in the past. If a person checked out the same book multiple times, it will be counted multiple times. Zero count should also be listed as 0.
5. List the member ID, first name and last name of members who have borrowed (either currently or in the past) all the books in the library with MySQL in the title. If any of such books have multiple copies, he or she must have borrowed at least one copy of each of such books.
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