Databases Querying Relational Databases Subqueries Review and Practice

jun cheng wong
jun cheng wong
10,741 Points

Check answer for question 2

Hi, I am not quite sure my answer is accurate. Can anyone take a look at it?

-- Generate a report that lists a patron's first name, email and loan count for loans that haven't been returned.

SELECT first_name AS "First Name",
       email,
       COUNT(*) AS "Outstanding loans"
       FROM patrons INNER JOIN 
       (
         SELECT patron_id FROM loans_south WHERE returned_on IS NULL
          UNION ALL
         SELECT patron_id FROM loans_north WHERE returned_on IS NULL
       ) 
       AS patronsWhoHaveNotReturnedTheBooks ON patrons.id =
       patronsWhoHaveNotReturnedTheBooks.patron_id 
       GROUP BY patrons.first_name;