Demo: answering library questions with SQL

Examples

All results are real output from PostgreSQL 18.3 on the seeded library.

Example 1: a simple filter, then a grouped count (simple)

Question: "Which books were published after 2000? Sort by title."

One row is a book, one table, one row-level condition, one sort:

SELECT title, published_year
FROM books
WHERE published_year > 2000
ORDER BY title;

   title   | published_year 
-----------+----------------
 Fledgling |           2005
 Lavinia   |           2008
(2 rows)

Question: "How many loans has each member made?" One row is a member with a number, so group by member. The names live in members, the loans in loans, joined by loans.member_id = members.id:

SELECT m.name, COUNT(*) AS loans
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
ORDER BY loans DESC, m.name;

 name  | loans 
-------+-------
 Amal  |     3
 Bilal |     2
 Carla |     2
 Dina  |     1
(4 rows)

Sanity check: 3 + 2 + 2 + 1 = 8, the number of loans in the table. Emil is missing, because he has no loans and this is an inner join. That is acceptable for this question ("how many loans has each member made"), but not for "every member with their count", which would need a left join and COUNT(l.id).

Example 2: the planned mistake (practical)

Question: "Which members have made at least two loans?" The tempting version puts the condition on the count in WHERE:

SELECT m.name, COUNT(*) AS loans
FROM loans l
JOIN members m ON m.id = l.member_id
WHERE COUNT(*) >= 2
GROUP BY m.id, m.name;

ERROR:  aggregate functions are not allowed in WHERE

Read the message. It says what the lesson on HAVING said: WHERE runs before the groups exist. "At least two loans" is a statement about a group, so it belongs in HAVING:

SELECT m.name, COUNT(*) AS loans
FROM loans l
JOIN members m ON m.id = l.member_id
GROUP BY m.id, m.name
HAVING COUNT(*) >= 2
ORDER BY loans DESC, m.name;

 name  | loans 
-------+-------
 Amal  |     3
 Bilal |     2
 Carla |     2
(3 rows)

Dina, with one loan, is gone. Notice how little changed: one clause moved. This is a good rule for debugging: the error message, read slowly, usually tells you the fix.

Example 3: overdue loans and the most-borrowed authors (realistic)

Question: "Which loans are overdue, who has them, and how late are they?" Using the definition above (not returned, and due date before "today", here 5 November 2025). One row is a loan, and we want names from two other tables, so three tables are joined through the junction loans:

SELECT m.name, b.title, l.due_on,
       DATE '2025-11-05' - l.due_on AS days_late
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books   b ON b.id = l.book_id
WHERE l.returned_on IS NULL
  AND l.due_on < DATE '2025-11-05'
ORDER BY l.due_on;

 name  |        title         |   due_on   | days_late 
-------+----------------------+------------+-----------
 Amal  | A Wizard of Earthsea | 2025-09-15 |        51
 Bilal | Kindred              | 2025-10-04 |        32
 Carla | The Dispossessed     | 2025-10-24 |        12
 Dina  | Palace Walk          | 2025-11-03 |         2
(4 rows)

All four open loans are overdue on that date. Subtracting two dates giving a number of days is PostgreSQL behaviour; other databases differ. In real use you would replace the fixed date with CURRENT_DATE, which gives different rows on different days.

Question: "Which authors are borrowed most?" Decision: count loans, not books. One row is an author with a number. The chain is loans to books to authors:

SELECT a.name, COUNT(*) AS times_borrowed
FROM loans l
JOIN books   b ON b.id = l.book_id
JOIN authors a ON a.id = b.author_id
GROUP BY a.id, a.name
ORDER BY times_borrowed DESC, a.name;

      name      | times_borrowed 
----------------+----------------
 Ursula Le Guin |              4
 Octavia Butler |              2
 Chinua Achebe  |              1
 Naguib Mahfouz |              1
(4 rows)

Check: 4 + 2 + 1 + 1 = 8 loans. The anonymous poet has no loans and is not listed, which is fine for "most borrowed". For "top three", add LIMIT 3 after the ORDER BY; the name in the sort breaks ties, so the result is stable.