Databases Querying Relational Databases Joining Table Data with SQL Review and Practice

George May
George May
3,324 Points

Answers to Review and Practice

Can someone check if my queries for this exercise are correct? Or is there another way to find out the answers?

Task 1 -- Use a JOIN to select all patrons with outstanding books. Select their first name and email address.

SELECT first_name, email FROM patrons JOIN loans ON patron_id WHERE returned_on IS NULL;

Task 2 -- Use a JOIN to find out which patrons haven't had any loans. Select their first name and email address.

SELECT patrons.first_name, patrons.email FROM patrons LEFT JOIN loans ON patrons.id = loans.patron_id WHERE loans.id IS NULL;

Task 3 -- Create a report that shows the title of the book, first and last name of the patron, email and all date fields of the loan.

SELECT books.title, patrons.first_name, patrons.last_name, loans.loaned_on, loans.return_by, loans.returned_on FROM books JOIN patrons ON patrons.id = loans.patron_id JOIN loans ON books.id = loans.book_id;

2 Answers

On question #3, I made my joins more explicit. I welcome anyone's feedback:

SELECT books.title
, patrons.first_name || ' ' || patrons.last_name AS full_name
, patrons.email
, loans.loaned_on, loans.return_by, loans.returned_on
FROM books
LEFT OUTER JOIN loans
ON loans.book_id = books.id
INNER JOIN patrons
ON patrons.id = loans.patron_id;
Michael Pashkov
Michael Pashkov
22,022 Points

Hi! check out task 1 -- Use a JOIN to select all patrons with outstanding books. Select their first name and email address.

select 
p.first_name, 
p.email,
l.book_id,
l.loaned_on,
l.returned_on
from patrons as p
inner join loans as l on l.patron_id = p.id
where l.returned_on is null;

task 2 -- Use a JOIN to find out which patrons haven't had any loans. Select their first name and email address.

select 
p.first_name, 
p.email,
l.book_id,
l.loaned_on,
l.return_by
from patrons as p
inner join loans as l on l.patron_id = p.id
where l.returned_on is not null group by p.email;

task 3

select DISTINCT 
b.title,
p.first_name, 
p.email,
P.last_name,
l.loaned_on,
l.return_by,
l.returned_on
from books as b
inner join loans as l on l.book_id = b.id
inner join patrons as p on l.patron_id = p.id;