gwecode

sql · level 2 check

the librarian's reports.

Time for a real job! You run the reports for five libraries. Each one has the same three tables: `books` (`id`, `title`, `genre`), `members` (`id`, `name`) and `loans` (`id`, `book_id`, `member_id`, `borrowed`, `due`, `returned`). Dates are ISO text, and `returned` is `NULL` while a book is still out.

Today is `'2026-06-01'`, and the librarian wants four reports, in this order. First: every member, even those with no loans, with how many loans they have had (`loans`) and a `status`: `regular` for 3 or more, `occasional` for 1 or 2, and `new` for none. Busiest first, with ties in name order.

Second: every overdue loan, meaning still out and due before today, with the book's `title`, the member's `name` and `days_late`, latest first. Third: each `genre` borrowed at least twice, with its `loans`, most first, ties in genre order. Fourth: the `title` of every book nobody has borrowed, A to Z.

Your queries will run on five different libraries, so they must work for any of them, not only this one. Fill in each `____` and run it.

Fill in every ____ then run the checks.
  1. 1 It counts every town member's loans, with a status, even for Ava who has noneuses: LEFT JOIN, COUNT and CASE
  2. 2 It lists the school library's overdue books, latest firstuses: three-table JOIN and dates
  3. 3 It finds nothing overdue in the village, and the genres borrowed at least twiceuses: GROUP BY and HAVING
  4. 4 It names the seaside books nobody has borroweduses: NOT IN with a subquery
  5. 5 It answers all four reports for the mobile libraryuses: putting it together

Take as long as you like and try as many times as you want.