ASSIGNMENT: Develop a SQL query that will select all of the books that are curre
ID: 3734448 • Letter: A
Question
ASSIGNMENT:
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. 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.
WHAT I HAVE TRIED SO FAR:
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 CURRENT_DATE()) AND b.cost > 10
AND
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
THIS IS THE ERROR I RECEIVE WHEN I TRY EITHER OF THE ABOVE OPTIONS:
1: Access is denied: CURRENT_DATE in statement [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 CURRENT_DATE(]
AND
2: Access is denied: GETDATE in statement [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(]
Please help! Thanks. Why am i getting access denied?
Explanation / Answer
Hi,
You are getting error in the sql because of getDate() and current_date().
Correct query is :
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 current_date from dual) AND b.cost > 10;
If you have any doubt, please revert back, Would be happy to help you if you have any doubt.
And if you like this answer give a thumns up! Happy Learning :)