Develop a SQL query that will select all of the books that are currently checked

Question

Develop a SQL query that will select all of the books that are currently checked out and are past due to be returned, which have a value greater than $10.00.

To find out if a loaned book is past due, you must compare the return date with the current date.

Your query must return: the name, address, postal code, and phone number of the borrower, the name and phone number of the librarian responsible for the transaction, the date the book was due to be returned, and the name, cost, and ISBN number of the book.

You query should sort the list by the return date in descending order which is the oldest date should appear first in the list.

USE syntax for the OpenOffice Base (HyperSQL).

These are the columns that each table has:

Table Booklended : Library_card_ID, Copy_ID, Chechout_date, Return_date, Librarian_ID;

Table BookCopy: Copy_ID, Book_ID, Sequence, Publication_date;

Table Book; Book_ID, Title, ISBN_number, Cost;

Table Borrower: Library_card_ID, First_name, Last_name, Address, City, Postal_code, Country, Phone_number, Membership_date;

Table Librarian: Librarian_ID, First_name, Last_name, Phone_number, Supervisor

Explanation / Answer

SQL Query:

SELECT br.First_name, br.Last_name, br.Address, br.Postal_code, br.Phone_number, l.First_name, l.Last_name, l.phone_number, bl.Return_date, b.Title, b.ISBN_number, b.Cost
FROM Booklended bl, BookCopy bc, Book b, Borrower br, Librarian l
WHERE bl.Library_card_ID = l.Library_card_ID AND bl.Copy_ID = bc. Copy_ID AND Librarian_ID = l.Librarian_ID AND bc.Book_ID = b.Book_ID AND bl.Return_date < (select GETDATE()) AND b.cost > 10

Letme know if this does not help you

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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