Need help with this question. Am I supposed to use a nested query? JOIN? INTERSE
ID: 3586765 • Letter: N
Question
Need help with this question. Am I supposed to use a nested query? JOIN? INTERSECT? GROUP BY? Btw, this is supposed to be in sql/oracle.
Here is the question: 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 “Harry Potter” 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.
The schema:
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)
Explanation / Answer
Yes, IT can be queried by inner query. One possible solution is :
SELECT memberID, lastname, firstname
FROM Member WHERE memberID IN
(SELECT CurrentLoan.memberID
FROM CurrentLoan
INNER JOIN History
ON CurrentLoan.bookID= History.bookID WHERE
(SELECT bookID FROM Book WHERE title =’Harry Potter’ )) );
Hope it helps!