This is where you learn. Not by reading these solutions, but by comparing them with yours: seeing where you agree, where you've chosen an equally good route and where you've left out a LEFT JOIN that was needed. If you've arrived here without having attempted the project, go back to 12-03: the solution set read cold teaches you almost nothing. Two warnings: a solution that differs from the reference one can be just as valid —what's marked isn't how closely it resembles this text, but whether it meets 12-02 and whether the decision is justified—, and every result is computed over 12-03's data set (36 loans, 20 copies, 15 members) with DATE '2026-06-30' as the reference date.

1. The reference schema: decisions and alternatives
2. The 15 solved queries
3. The reference indexes
4. The view, the procedure and the trigger
5. Frequent mistakes in this project
6. Common Mistakes and Tips
7. Exercises
8. Conclusion

The complete DDL is in 12-03. Here are the decisions that get marked, each with the alternative that would also be correct and when to choose it.

The partial index prevents two active loans of the same copy, but not two past loans that overlap. If that matters —and in a migration of old data it matters a great deal—, PostgreSQL has a constraint for exactly this:

It reads: "there can't be two rows with the same copy_id whose loan intervals overlap", and the COALESCE(..., 'infinity') turns the open loan into an interval with no end, so it also covers IR-03. It's more powerful and more expensive: it needs an extension, it uses a GiST index and it's no help for WHERE return_date IS NULL. The criterion: a partial index if you only care about the present; EXCLUDE if you're going to load history or if loans are registered retroactively. Both are correct; you have to be able to say which one you chose.

Nine rows, led by Notebook of Shadows (2024) and The Forest of Names (2023). The key decision is the tie-breaker: a bare ORDER BY publication_year DESC would leave ties at the mercy of the plan and the result would change between runs. And Distributed Networks and Systems shows up, the work with no copies at all: it's catalogued, so it appears in the catalogue.

(2 of 3 rows.) Two rows with the same title, and it isn't a mistake: they're two different copies of the same work, which is the heart of the project. Anyone who wrote SELECT DISTINCT w.title has lost exactly the information that was being asked for.

(2 of 6 rows; Alba Rey is also 1 day late, and the other three are within term, with 0 days late.) Six active loans, and three are of the same work —the three copies of The Garden of Hours are all out, and that's where QR-10's queue comes from—. The key decision is GREATEST(..., 0): without it, loans within term would come out with a negative delay, which means nothing and pollutes any later sum. And return_date IS NULL, never = NULL: 04-03's mistake that returns zero rows without warning.

(2 of 7 rows: 3 copies never lent, 1 work without copies and 3 members without loans.) Three different business readings: copies taking up shelf space without ever going out, a catalogued title that hasn't arrived yet, and cards never used. The technique is 03-03's anti-join (LEFT JOIN + WHERE ... IS NULL), equivalent to NOT EXISTS (07-03) and not to NOT IN, which with a NULL in the subquery would silently return zero rows. UNION ALL and not UNION, because there are no duplicates to remove; and the three branches need the same number of columns and compatible types (03-07), hence the gap label.

Three subjects make the cut: Fiction (12 loans, 2 works, 32.3 days on average), Children (8, 2, 14.6) and Computing (6, 1, 26.8). The filter goes in HAVING and not in WHERE because it applies to the already aggregated group: WHERE COUNT(*) >= 5 is a syntax error, and it's 04-06's most repeated failure. The six subjects share out the 36 loans (12 + 8 + 6 + 4 + 4 + 2). And the COALESCE in the duration is a definition decision: without it, AVG would ignore the six open loans and Fiction's average would drop, because the three longest loans right now are precisely the ones that haven't come back.

(2 of 12 rows.) The two rows say 0 available for opposite reasons, and an honest report distinguishes them: of the first there isn't a single copy; of the second there are three copies and all three are out on loan. That's why all four columns are published and not just the last one. Three decisions: COUNT(c.id) and not COUNT(*), or the work with no copies would come out with 1; the condition of the LEFT JOIN to loans goes in the ON; and Relational Databases comes out with 3 copies but only 2 enabled, because one is in repair — that difference is exactly what a single counter couldn't express.

(2 of 3 rows; "The Forest of Names", by Clara Meireles and Ada Quiroga, is missing.) The ORDER BY wa.credit_order inside the string_agg is the key decision, and almost everybody skips it: without it, the order of the authors inside the cell is whatever the engine feels like, and a cover credited "Aymà and Ibáñez" could come out reversed. It's the reason works_authors has a credit_order column. And notice that the bridge table carries data of its own (role and credit_order): that turns it into a fully fledged entity and not a mere pair of keys.

The €13.40 outstanding belongs to a single member, Diego Andrade, and it's exactly what pushes him over BR-11's €10 threshold and explains his suspended status. The control figure: 6 fines out of 30 returned loans is a late rate of 20.00 %, and 25.80 + 1.40 = €27.20 is the system total. The COALESCE is essential —without it, the senior row would show NULL in a money column, which somebody will read as "no data" (06-04)— and that row must appear: the LEFT JOIN is what saves it.

(2 of 10 rows: 6 general, 2 child and 2 senior.) The subquery is correlated because of the WHERE m2.type = m.type: it's evaluated once per member, with their type (07-02). And the detail that decides whether the figure is right is COUNT(l2.id) versus COUNT(*): with COUNT(*), members with no loans would each contribute a phantom row and the general average would come out at 2.67 instead of 2.56 — close enough for nobody to notice. An equally valid and more readable alternative: a CTE with the averages per type and a JOIN against it; with fifteen members it makes no difference, with fifteen thousand the CTE is evaluated once instead of once per row.

The position isn't in any column: ROW_NUMBER() computes it. That's the whole solution to the queue problem, and that's why PARTITION BY r.work_id is mandatory: each work has its own queue and the numberings mustn't get mixed up. The r.id as a second criterion isn't optional —two reservations on the same day would end up in arbitrary order, and a member's position would change between two queries—. It's ROW_NUMBER and not RANK on purpose: in a queue there can't be two firsts. And the WHERE leaves out the reservation already collected and the expired one, which are history.

(2 of 8 rows; at Vila Nova first place goes to "Relational Databases", with 2.) LATERAL is what lets the subquery see b.id from the outer row; without it, a LIMIT 3 inside an ordinary subquery would give the top 3 for the whole system, repeated at all three branches (07-04). The LEFT JOIN LATERAL ... ON TRUE instead of CROSS JOIN LATERAL is what saves a branch with no loans. An equally valid alternative: a CTE with a partitioned ROW_NUMBER() and a WHERE rank_ <= 3 outside — more portable, because LATERAL isn't in every engine. LATERAL wins when the outer table is small and the inner one huge, because it only reads what it needs from each group.

(2 of 12 rows: the first and the last; 2025-07 and 2025-08 are empty.) The two months with zero are the reason for the query. Without the calendar, a GROUP BY over loans would return ten rows and the chart would start in September as if the service hadn't existed before; the LEFT JOIN against generate_series makes them visible (11-01). The COALESCE isn't cosmetic: without it the window would drag a NULL along and the running total would be useless from the first empty row onwards. The running total closes at 36, the total number of loans: the control figure that validates the whole series (11-04).

(2 of 9 rows; Marta Coelho is second at the Central, also with 4.) The tie-breaker is the decision that gets marked. Iván and Marta have the same 4 loans: with RANK() both would be first, and at Vila Nova, where there's a three-way tie on 3, a "top 3" would return three firsts. With ROW_NUMBER() and the criterion most loans → most recent → lowest id, the order is total and stable. Neither of the two is incorrect —RANK is what you want in a sports table—, but you have to choose deliberately and say so. And the second row is a lesson in honesty: the Infantil branch only has three members, so its "third heaviest reader" has zero loans. It's 11-04's warning about publishing rankings from tiny samples.

(2 of 8 rows: 1 at level 1, 3 at level 2 and 4 at level 3.) The path does two jobs at once, and that's the trick worth knowing: it can be read at a glance and, above all, it's what makes ORDER BY path put each person below their supervisor. Ordering by level would give you all the supervisors together and then all the assistants, which isn't an org chart. The base case is supervisor_id IS NULL, and that's why the data set needs a librarian with no supervisor: without one the recursion doesn't start and the query returns zero rows. With real data you should also accumulate the visited ids and cut the cycles, because a looping supervisor_id would spin the query forever (10-02).

Validation by two routes: the rows add up to 20 + 10 + 6 = 36, the total number of loans, and the columns add up to 12 + 2 + 4 + 4 + 8 + 6 = 36 as well. If either of the two didn't work out, the query would be wrong even if the figures looked reasonable (11-04). The chain of four LEFT JOINs is deliberate: it only takes one of them being INNER for a branch with no copies to disappear. And the business reading is immediate: the Infantil do Parque lends only children's books, while the Central is the only one with holdings in all six subjects — an imbalance no overall total would show.

Seven indexes, each with the query that justifies it (PR-05). Not one more:

What is deliberately not indexed (PR-07): members.type and status (three and four values, the planner will prefer the scan), subjects, branches and publishers in their entirety (they fit in one page), and works_authors, whose composite PK already serves for going from the work to the author — although not the other way round: if "all the works by an author" were needed, you'd have to add (author_id, work_id). That nuance is the column ordering of a composite index that 08-02 explained. And on title search (PR-04): LOWER(title) resolves equality and prefixes (LIKE 'garden%'), but not a word in the middle; for that you need trigrams (pg_trgm with GIN) or full text (to_tsvector). The right answer in the report isn't "I use GIN", it's saying what kind of search is needed and choosing accordingly.

With the reference date it returns three rows: Lena Fuentes with 36 days and €7.20, and Nuno Barros and Alba Rey with 1 day and €0.20 each. Two decisions: it's called estimated_fine because the fine doesn't exist until the return (BR-10), and the LEAST applies the €20 cap inside the view, so nobody has to remember it.

Four details worth copying: the UPDATE ... RETURNING reads and writes in a single step (05-02); the AND return_date IS NULL makes the operation idempotent, so calling it twice doesn't close it twice or issue two fines; the IF NOT FOUND turns a silent failure into an exception that rolls the whole transaction back; and the ORDER BY r.reservation_date, r.id respects the queue with a tie-breaker, just like QR-10. With several desks open at once, that SELECT ... LIMIT 1 would also want FOR UPDATE SKIP LOCKED (09-05), so two simultaneous returns of the same work don't notify the same member.

With Diego Andrade (member 6, suspended over his €13.40 unpaid), any INSERT into loans ends in ERROR: Member 6 has status "suspended": they cannot borrow (BR-11). It's the only trigger in the project, and its justification is 10-05's: the rule depends on another table, so a CHECK can't express it (05-01), and it must hold wherever the INSERT comes from.

• Reading these solutions instead of comparing against them. Open your file alongside and go query by query: where you agree, confirm that you know why; where you differ, decide which is better and note it in the report. That's the part that gets marked.
• Copying a solution you don't understand. In the defence (12-05) they'll ask you why LATERAL and not a CTE, or why ROW_NUMBER and not RANK; if you don't know, it shows in ten seconds. And taking a query as good because it returns rows. Returning rows isn't returning the right ones. Validate by two routes: QR-15 adds up to 36 by rows and by columns, QR-12 closes the running total at 36 and QR-08 squares with the 6 fines.
• Tip: save the output of the fifteen queries to a file. When you touch the schema or the data, a diff will tell you instantly what has moved and whether it was what you expected (11-04). And write next to each query the lesson it applies: it turns the project into an index of what you know how to do.

On QR-08. (1) Write the query for each member's real total debt: unpaid fines plus the estimated fine on their overdue loans not yet returned. (2) How much does Diego Andrade owe under that definition and how much under QR-08's? (3) Which one would you publish to the treasury and which to management?

QR-11 uses LATERAL. (1) Rewrite it with a CTE and ROW_NUMBER(). (2) Give one reason to prefer each version. (3) What happens to each one if a branch has no loans at all?

A colleague hands this in as "most-borrowed works": SELECT w.title, COUNT(*) FROM works w JOIN copies c ON c.work_id = w.id JOIN loans l ON l.copy_id = c.id GROUP BY w.title ORDER BY 2 DESC LIMIT 5; (1) What three problems does it have? (2) Fix it. (3) Which of the three shows up today with this data?

Solution 1 — Two correlated subqueries, one per metric, and never added together in the same column:

(2) Diego Andrade owes €13.40 under both definitions, because he has no overdue loans still out. The ones who change are others: Lena Fuentes goes from €0.00 to €7.20, and Nuno Barros and Alba Rey from €0.00 to €0.20; the total potential debt is €7.60. (3) To the treasury, QR-08's: those are the only euros collectable today, with an issued fine behind them. To management, both columns together, because the potential one anticipates what will come in and points at who to call before the debt grows. What you can never do is add them up in a column called "debt": that's the mistake in section 5.

Solution 2 — The CTE is QR-13's applied to works: ROW_NUMBER() OVER (PARTITION BY b.id ORDER BY COUNT(*) DESC, w.title) over a GROUP BY b.id, w.id, and a WHERE rank_ <= 3 outside. (2) In favour of the CTE: it's standard, portable SQL —LATERAL doesn't exist in MySQL before 8.0.14 or in SQLite— and it reads from top to bottom. In favour of LATERAL: it only brings back 3 rows per branch instead of computing the ranking of every work only to discard almost all of them, which with a hundred thousand titles is the difference between milliseconds and seconds. (3) The CTE loses the branch with no loans, because there'd be nothing to group; the LEFT JOIN LATERAL ... ON TRUE version keeps it with NULL in the subquery's columns. To make them equivalent you'd have to start from branches with a LEFT JOIN against the count.

Solution 3 — (1) It groups by title instead of by id: two different works with the same title —two editions of a classic, a common thing in a library— would be merged into one row with the sum of both. ORDER BY 2 DESC with no tie-breaker: with LIMIT 5 and several works tied, which five come out depends on the plan and the report stops being reproducible. And COUNT(*) with no alias: the column will be called count and whoever reads the result won't know whether it counts loans, copies or rows of the JOIN (11-02). The definition is missing too: it counts all loans, including the open ones and those of members whose accounts were closed. (2) The correct version:

(3) The tie-breaker one. With this data there are no two works with the same title, so the first problem doesn't show up — and that's the danger: the query passes the tests and fails the day somebody catalogues a second edition. On the other hand A Brief History of Alvorada, Letters from the Lighthouse, The Atom and the Doubt and The Girl Who Counted Stars all four have 4 loans, so fifth place in the LIMIT 5 is currently a lottery between four candidates.

You now have something to compare yourself against:

• The reference schema and its decisions, each with its valid alternative: the composite PK of works_authors versus the surrogate id, the stored due_date, derived versus stored state, fines as a table, the computed queue, and above all the partial unique index versus the EXCLUDE constraint — the first if only the present matters, the second if historical overlaps have to be prevented as well.
• The 15 solved queries, each with its key decision: the ORDER BY tie-breaker (QR-01, QR-13), the two copies of the same title that aren't a duplicate (QR-02), the GREATEST(..., 0) for the delay (QR-03), the anti-join that isn't NOT IN (QR-04), the HAVING that isn't a WHERE (QR-05), the COUNT(c.id) that isn't COUNT(*) (QR-06, QR-09), the ORDER BY inside the string_agg (QR-07), the COALESCE that turns the NULL into 0.00 (QR-08), the position computed with ROW_NUMBER (QR-10), the LATERAL that sees the outer row (QR-11), the calendar that makes the empty months visible (QR-12), the path that orders the org chart (QR-14) and the pivot that squares by rows and by columns (QR-15).
• Seven indexes with the query that justifies each one and the list of what is deliberately not indexed; plus one view, one procedure and one trigger, and not one more: the view that defines "overdue" and calls estimated_fine what isn't a fine yet, the idempotent and atomic procedure, and the only trigger that expresses a rule no CHECK can. And the frequent mistakes in the project, led by the one that wipes it out —confusing work with copy—, with the reminder that a different solution can be just as valid if it meets the requirements and is justified.

The last part is still missing, and it's the one that decides how everything above is judged. In the next lesson, Presenting the Project, you'll see how a piece of technical work is communicated: the structure of the report section by section, how data results are presented without misleading in good faith, the questions you'll be asked in the defence and how to prepare them, how the project is published in a repository somebody can run in five minutes, the self-assessment with the rubric turned into a checklist — and the close of the whole course.