Creating SQL queries and algebraic expression from text

Question

Given from the task, I have these relations:

• Department (DepCode:pk, DepName, DepAdress)
• LoanAccs (LoanAcc:pk, Name, DepCode:fk, Phone:ck)
• Book (ISBN:pk, Copy:pk, Author, Titel, PublishedYear, BoughtYear)
• LoanedBooks (ISBN:fk, Copy:fk, LoanDate:pk, LoanAcc:fk, Returndate)

From that the ISBN in relation book defines the author, title and publishing year. For each combination of ISBN, loanAcc and loanDate in LoanedBooks, there is only max one copy.

Then I'm supposed to write a SQL query and an algebraic expression from the task that says:

Make a list that shows all loaned books that are older than four weeks which is not yet has been returned. The list is supposed to contain name and address of the loaners. And also the author and title off the books. The list is supposed to be sorted on depAdress, and if those with the same address, on the loaners name within each department.

I've come up with a query that looks like this =>

SELECT 
    LoanAccs.name, Department.depAdress, book.author, book.title
LEFT OUTER JOIN 
    LoanedBooks ON loanAccs.loanAcc = loanedBooks.loanAcc
LEFT OUTER JOIN 
    Department ON loanAccs.depCode = department.DepCode
LEFT OUTER JOIN 
    Book ON loanedBooks.ISBN = books.ISBN
HAVING 
    (CURRENT_DATE - loanedbooks.returndate) <= 28
ORDER BY 
    depAdress, depCode, name desc;

I'm not quite sure that I've set the query up right, so any comments / explanations will be appreciated :)

Also I'm wondering how to write it down as an algebraic expression. How to begin, what to look for when writing.

Answer 1

Give this a try

SELECT LoanAccs.name
, Department.depAdress
, book.author
, book.title
LEFT OUTER JOIN LoanedBooks 
  ON loanAccs.loanAcc = loanedBooks.loanAcc
LEFT OUTER JOIN Department 
  ON loanAccs.depCode = department.DepCode
LEFT OUTER JOIN Book 
  ON loanedBooks.ISBN = books.ISBN
WHERE DATEDIFF(dd, CURRENT_DATE, loanedbooks.returndate) <= 28
ORDER BY depAdress, depCode, name desc