Both terminals are open and the BiblioRed schema is at hand, exactly as we left it when closing module 6. From here on the course changes register: you are not going to read new concepts, you are going to write SQL. This lesson is a bank of fifteen exercises about the municipal library network of Vallmar, ordered by increasing difficulty, and the way to use it is very specific.

How to work through this lesson. Read the task. Before looking at anything else, write your query in the psql session and run it. Compare your result with the Expected result block. Only then unfold the Solution and compare it with yours: it is perfectly normal —and frequently desirable— for your query to be different from the proposed one and give the same result, because in SQL there is almost always more than one road. What is not normal is reading the solution first: in that case the exercise teaches you nothing, it only gives you the feeling of having understood.

If a query will not come out, hold on for at least five minutes before looking at the Hint. The muscle you are training here is not the one that remembers the syntax of HAVING, but the one that translates a question in human language into a query, and that is trained only by failing.

This lesson covers SELECT, WHERE, JOIN, aggregation and subqueries. Window functions, transactions and execution plans do not appear here: they are the material of lesson 07-04.

The exercises use a small, specific dataset —8 members, 12 materials, 15 copies, 20 loans— so that you can verify every result by eye. If you have been building BiblioRed up along the course, your data will be different and the results will not match. That is why it is worth starting from scratch with this lesson's script.

Create a clean database and run this whole script. In PostgreSQL:

Run this query before you start. If any number does not match, the script has not run all the way through:

The reference date. Several exercises compute delays. So that the results are reproducible today and two years from now, the solutions use the literal date DATE '2026-08-02' instead of CURRENT_DATE. In production you would write CURRENT_DATE; here we need the number of days not to change.

SQLite. The dataset works in SQLite by changing SERIAL/BOOLEAN for integers and NUMERIC(8,2) for REAL. Wherever there are relevant differences in a query, the solution points them out.

1. Block A — Basic: single-table queries (exercises 1-3)
2. Block B — Intermediate: multi-table queries (exercises 4-7)
3. Block C — Intermediate: aggregation and grouping (exercises 8-10)
4. Block D — Advanced: subqueries, derived tables and CTEs (exercises 11-12)
5. Block E — Advanced: real management reports (exercises 13-15)
6. Common mistakes and tips
7. Reinforcement exercises

Difficulty: Basic

Task. The heritage department wants the listing of the five oldest materials in the catalog. Return three columns with these exact headers: Title, Publisher and Year. Order from oldest to most recent and, when two materials are from the same year, alphabetically by title.

Solution

Expected result

Explanation. There are three details that separate a correct query from a careless one:

• The double quotes around the aliases. AS Title would come out as title in lowercase, because PostgreSQL folds every unquoted identifier to lowercase. To keep the capital letter you need double quotes, not single ones: single quotes delimit text strings, not identifiers. AS 'Title' is a syntax error.
• The second ordering criterion. Without , title ASC, the two 2007 rows would come out in an order the engine does not guarantee. It may look stable to you today and change tomorrow when a row is added or an index is created. An ORDER BY that does not break ties is not deterministic, and in a listing with LIMIT that means the row you see may vary between runs.
• LIMIT goes after ORDER BY, not before: first the whole set is ordered, then five rows are cut off. If you did it the other way round you would get any five rows and then order them, which is a different question.

In SQLite the query works the same, but quoted aliases behave more loosely (it accepts double quotes and also square brackets).

Difficulty: Basic

Task. Answer the following five questions, each one with its own query:

• (a) Materials published between 2000 and 2010, both included, with title and year.
• (b) Copies whose status is on_loan or reserved, with code, status and branch.
• (c) Materials whose title contains the word "Chernobyl", regardless of upper or lower case.
• (d) Materials with no cover (cover_url not filled in) and authors with no birth year.
• (e) The distinct material types there are in the catalog and how many distinct publishers appear.

Hint. For (c), PostgreSQL has ILIKE; for (d), remember that = NULL is never true.

Solution

Expected result

(a) 5 rows: Kafka on the Shore (2002), The Frozen Heart (2007), World Without End (2007), The Map of Time (2008), The Pillars of the Earth (series) (2010).

(b)

(c) 2 rows: 909 "Documentary: The Voice of Chernobyl" and 912 "Voices from Chernobyl".

(d) 6 materials with no cover (903, 905, 907, 909, 910, 911) and 1 author with no birth year (Delia Marchetti).

(e) 4 types (audiobook, book, dvd, magazine) and 8 distinct publishers.

Explanation. Each part hides a classic trap:

• BETWEEN 2000 AND 2010 includes both ends. If you need to exclude them, BETWEEN is no use: you have to write > 2000 AND < 2010. And the order matters: BETWEEN 2010 AND 2000 returns zero rows, with no error and no warning.
• IN ('on_loan','reserved') is exactly equivalent to status = 'on_loan' OR status = 'reserved'. It is more readable and, above all, it avoids the precedence mistake of writing WHERE material_id = 901 AND status = 'on_loan' OR status = 'reserved', which does not mean what it looks like: AND binds more tightly than OR.
• ILIKE is a PostgreSQL extension. In SQLite, LIKE is already case-insensitive for ASCII characters, but not for accented ones —a surname like Ferrán would not match ferran—; there the portable form is WHERE lower(title) LIKE lower('%chernobyl%').
• cover_url = NULL returns NULL, which in a WHERE behaves as false: the query does not raise an error, it gives zero rows. It is one of the hardest failures to spot because there is no symptom at all.
• count(DISTINCT publisher) counts 8 and not 9: there are 12 materials but only 8 distinct publishers, and count(DISTINCT ...) ignores NULLs (there are no null publishers here, but it is worth remembering).

Difficulty: Basic

Task. Three maintenance operations. In all three, before modifying anything, write and run the SELECT with the same WHERE to see exactly which rows you are going to touch.

• (a) Register member 19, Rubén Ortells, email ruben.ortells@example.org, joining today (2026-08-02), East branch, active.
• (b) Member 18 (Berta Quintana) moves from the South branch to the North branch. Update her branch.
• (c) Delete the expired reservations whose expiry date is earlier than June 30, 2026.

Solution

Expected result

Explanation. Three habits that prevent almost every front-desk disaster:

1. An explicit column list in the INSERT. INSERT INTO members VALUES (...) works until the day somebody adds a column to members; then every INSERT without a list breaks or, worse, puts the values in the wrong column.
2. The SELECT with the same WHERE, first. This is not a style recommendation: it is the only way of knowing how many rows you are going to touch before touching them. An UPDATE members SET branch_id = 2 with no WHERE affects all eight members, and there is no "undo" outside a transaction.
3. RETURNING (PostgreSQL) returns the rows actually affected. It is the after-the-fact confirmation that the WHERE did what was intended. SQLite has supported it since version 3.35; in MySQL it does not exist and you have to query again.

About (c): if you tried to delete reservation 501 (fulfilled), nothing would fail because no table points at reservations. But if you tried to delete member 14 it would fail, because loans, reservations, fines and registrations reference it. That is the referential integrity of module 2 doing its job.

Undo the changes before moving on, so that the later results match: DELETE FROM members WHERE member_id = 19; UPDATE members SET branch_id = 3 WHERE member_id = 18; INSERT INTO reservations VALUES (503,16,906,'2026-06-01','2026-06-15','expired'), (506,12,901,'2026-03-30','2026-04-13','expired');

Difficulty: Intermediate

Task. A member asks at the front desk where they can find "The Pillars of the Earth" (material 901). Return all its copies with the code, the status and the name of the branch where each one is, ordered by branch.

Hint. The branch is in copies.branch_id, not in materials.

Solution

Expected result

Explanation. The JOIN connects two tables through the foreign key → primary key pair: copies.branch_id points at branches.branch_id. The aliases c and b are not decorative: as soon as there are two tables with a column of the same name —and here branch_id is in both— writing plain branch_id produces the error column reference "branch_id" is ambiguous.

The easy mistake here is forgetting the ON condition. A FROM copies c, branches b WHERE c.material_id = 901 with no join condition produces a Cartesian product: 3 copies × 4 branches = 12 rows, all of them plausible-looking. It is a mistake that does not jump out at you with three rows and that, with 40,000 copies, brings the server down.

Difficulty: Intermediate

Task. List all the open loans (the ones not yet returned) with: the member's first and last name, the copy code, the material title, the branch the copy belongs to and the due date. Order by due date.

Hint. The chain is members → loans → copies → materials, and the branch hangs off copies.

Solution

Expected result

Explanation. Four chained JOINs are no harder than one: each JOIN adds a table and its condition, and the accumulated result keeps growing to the right. The key is to start from the table that contains the rows you want to count —here loans, because one result row is one loan— and hang the others off it as decoration.

Two observations:

• l.return_date IS NULL is the definition of "open loan" in this schema. There is no open column; the fact of having no return date is the fact of being open. It is a legitimate use of NULL: it means "it has not happened yet".
• The branch comes from copies, not from members. Marta Alsina is registered at Central and her two loans are of Central copies, so the difference does not show; but if she had borrowed a North copy, b.name would say North. Confusing "the member's branch" with "the copy's branch" is the most frequent modeling mistake in this schema. In SQLite, || concatenates just as in PostgreSQL.

Difficulty: Intermediate

Task. Two questions from the annual report:

• (a) Which materials have never been loaned out? Include also the ones that do not even have copies.
• (b) Which members have no loan on record? Say whether they are active.

Hint. A LEFT JOIN followed by WHERE <column of the right-hand table> IS NULL leaves exactly the rows that did not find a match.

Solution

Expected result

(a)

(b)

Explanation. The anti-join pattern has three parts that must be respected together:

1. LEFT JOIN, not INNER JOIN. The INNER discards precisely the rows we are looking for.
2. The matching condition goes in the ON.
3. The IS NULL filter goes in the WHERE and must point at a column of the right-hand table that is never null by itself: that is why we use l.loan_id (primary key, never null) and not l.return_date, which is null in open loans and would slip five false positives past us.

In (a) the second LEFT JOIN is essential. Materials 907 and 911 have no copies: after the first LEFT JOIN, c.copy_id is already NULL, and the second LEFT JOIN propagates that NULL to l.loan_id, so they make it into the result. If you had used INNER JOIN copies, those two materials would have vanished and the report would have said there is only one material never loaned out. The anti-join does not fail with an error: it fails by giving fewer rows than it should.

Material 910 is different: it does have a copy (EJ-3094), but that copy has never gone out. All three cases —no copies, and copies that never went out— fall into the same result, which is exactly what the task asked for.

Difficulty: Intermediate

Task. For an "if you liked this, you will like that" campaign, we need the pairs of materials by the same author. Each pair must appear only once (we do not want to see A-B and also B-A) and no material must be paired with itself. Show the author and the two titles.

Hint. The condition m1.material_id < m2.material_id solves both problems at once.

Solution

Expected result

Explanation. A SELF JOIN is a JOIN of a table with itself; the only thing that makes it possible are the mandatory aliases (m1, m2), which turn one table into two independent "copies" for the engine.

The condition m1.material_id < m2.material_id does two jobs:

• With <> in its place you would get 10 rows: each pair duplicated in both orders.
• With no additional condition at all (only m2.author_id = m1.author_id) you would get 15 rows: the previous 10 plus the 5 of each material with itself.

Notice too that materials 910 and 911 (the magazines, with a null author_id) do not appear, and that is correct: NULL = NULL is not true, so two rows with an unknown author never pair up. That behavior, which is a nuisance in other contexts, is exactly what we want here: we do not know whether they are by the same author.

Difficulty: Intermediate

Task. Three inventory and finance reports:

• (a) Number of copies per branch, from highest to lowest.
• (b) Number of copies per branch and status.
• (c) Number, total amount and average amount of fines by reason, showing only the reasons with more than one fine.

Solution

Expected result

(a)

(b)

(c)

Explanation. The point to internalize is the logical order of execution we saw in 02-05: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Two practical rules follow from it:

• WHERE filters rows before grouping; HAVING filters groups afterwards. WHERE count(*) > 1 is a syntax error, and not out of caprice: when the WHERE is evaluated the groups do not exist yet.
• Everything in the SELECT that is not inside an aggregate function has to be in the GROUP BY. If in (a) you added b.branch_id to the SELECT without adding it to the GROUP BY, PostgreSQL would raise an error. (Curiously it would accept it if you grouped by b.branch_id, because it is a primary key and functionally determines b.name: PostgreSQL recognizes that dependency. SQLite and MySQL in loose mode never raise an error and return an arbitrary value, which is much worse.)

In (c), the reason loss disappears because of the HAVING despite being the most expensive fine (€24.00). It is a reminder that a badly chosen HAVING can hide exactly what matters.

round(avg(...), 2) requires the argument to be numeric; since amount is NUMERIC(8,2), it works. If the column were double precision, PostgreSQL would demand round(avg(amount)::numeric, 2).

Difficulty: Intermediate

Task. We want the number of loans of each member, including the members who have never borrowed anything (they must appear with 0). Write the query and explain why count(*) would give an incorrect result.

Hint. Count what the LEFT JOIN did not find.

Solution

Expected result

Explanation. This is probably the most expensive trap in reporting SQL, because it produces no error at all: it produces a plausible, wrong number.

• count(*) counts rows. After the LEFT JOIN, Óscar Vilanova generates one row —his own, with every column of loans set to NULL—, so count(*) returns 1.
• count(l.loan_id) counts non-null values of that column. In Óscar's row, l.loan_id is NULL, so it counts nothing: 0.

The rule worth memorizing: in a LEFT JOIN, always count a column of the right-hand table, and let that column be its primary key. If you counted count(l.return_date), you would get the number of loans already returned (Marta Alsina would give 3 instead of 5), which is another perfectly valid question... but not the one you were asked.

The same applies to sum(): sum(amount) over a group with no rows returns NULL, not 0. If the report is going to be shown on screen, wrap it: COALESCE(sum(amount), 0).

Difficulty: Intermediate

Task. The five most-loaned materials of the whole history, with the title, the author's name (or (no author) if there is none) and the number of loans. Break ties alphabetically by title.

Hint. The loan points at the copy, not at the material: you have to go up one step.

Solution

Expected result

Explanation. Three deliberate decisions in this query:

• Group by m.material_id, not by m.title. If two different materials shared a title —perfectly possible: a print edition and an audiobook one— grouping by the title would melt them into a single row. Grouping by the primary key and adding the title to the GROUP BY so that it can be projected is the safe pattern.
• LEFT JOIN authors, not INNER JOIN. Magazines 910 and 911 have a null author_id. It does not show here because they are not among the most loaned, but an INNER JOIN would silently expel them from any future ranking.
• COALESCE on the whole concatenation, not on each piece. a.first_name || ' ' || a.last_name with a null a.first_name returns a whole NULL, because any concatenation with NULL is NULL. That is why the COALESCE wraps the already-assembled expression.

The tie between "Kafka on the Shore" and "The Pillars of the Earth" (3 loans each) is resolved by title: K before T. Without that second criterion, the order between the two would not be guaranteed and the "top 5" could change from one run to the next.

This ranking is a global LIMIT 5. The "most loaned in each branch" version needs window functions and you will see it in 07-04.

Difficulty: Advanced

Task. Five questions, each one with the tool that fits it:

• (a) Materials older than the average year of the catalog (scalar subquery).
• (b) Members with some pending fine, using IN.
• (c) Members with some loan not returned, using EXISTS.
• (d) Branches with no lost copy, using NOT EXISTS.
• (e) For each member, the date of their last loan (correlated subquery); members with no loans must come out with NULL.

Solution

Expected result

(a) The catalog average is 2009.58, so 6 rows come out: Norwegian Wood (1987), The Pillars of the Earth (1989), Kafka on the Shore (2002), The Frozen Heart (2007), World Without End (2007), The Map of Time (2008).

(b) Members 13 (Mestre), 14 (Alsina) and 15 (Pereda).

(c) Members 13, 14, 15 and 16.

(d)

(e)

Explanation. Each form of subquery answers a different form of question:

Two warnings worth their weight in debugging hours:

1. NOT IN and NULLs do not get along. If you wrote WHERE branch_id NOT IN (SELECT branch_id FROM copies WHERE status = 'lost') and that subquery could return a NULL, the result would be zero rows, always, because x NOT IN (1, NULL) evaluates to NULL. NOT EXISTS does not have that problem: that is why it is the recommended form for anti-joins with a subquery.
2. SELECT 1 inside EXISTS is neither superstition nor an optimization: it is a statement of intent. EXISTS does not care what you project —the engine does not even evaluate it—, and writing SELECT 1 makes it clear to the next reader that the columns do not matter.

In (e), the correlated subquery returns NULL for members with no loans because max() over an empty set is NULL. That is exactly what the task asked for; if you wanted a text, COALESCE(..., 'never') would require converting the date to text first.

Difficulty: Advanced

Task. Three queries that reorganize the problem before answering it:

• (a) The average number of loans per active member, using a derived table.
• (b) One card per branch with its number of members and its number of copies, using two CTEs.
• (c) A unified contact list: all the emails of active members and of speakers, in a single list, with a column stating where each one comes from.

Hint. In (b), do not try to do it with two direct JOINs on branches: the counts would multiply each other.

Solution

Expected result

(a) 2.86 — there are 7 active members (all but Óscar Vilanova) adding up to 20 loans: 20 / 7 = 2.857...

(b)

(c) 10 rows: 7 active members and 3 speakers (Calduch, Lemus, Marchetti).

Explanation. All three are variants of the same move: compute an intermediate result and query it as if it were a table.

In (a), the double aggregation is mandatory. avg(count(*)) does not exist: aggregate functions cannot be nested. You have to aggregate once (loans per member), materialize that result as a derived table and aggregate again. Notice the AS counts: in PostgreSQL a derived table needs an alias or the engine raises an error, even if you never use it.

In (b) lies the important lesson. If you wrote this:

...the count(DISTINCT ...) would save you by a miracle, but a count(*) would give 3 × 8 = 24 for Central. The JOIN of two independent branches against the same table produces a Cartesian product inside each branch. This is the most expensive aggregation mistake there is because the result looks reasonable. The robust solution is to aggregate each branch separately —in CTEs or in subqueries— and join afterwards.

In (c), UNION removes duplicates and UNION ALL does not. Here no duplicates are possible because the first column already tells them apart, so UNION ALL would be faster. Both branches must have the same number of columns and compatible types, and the names are always set by the first branch: that is why the aliases are only needed at the top. The ORDER BY goes once only, at the end, and applies to the united set.

Difficulty: Advanced

Task. The municipal audit office asks for the fine collection: by month and payment method, the number of payments and the total amount, plus a channel column worth 'in person' for cash and card and 'online' for gateway. Add a final query with the total outstanding (fines in pending status).

Hint. to_char(payment_date, 'YYYY-MM') groups by month in PostgreSQL.

Solution

Expected result

Total collected: €11.70. Outstanding: 3 fines worth €30.60.

Explanation. The report has three fine points:

• It groups by payments, not by fines. A fine can be collected in several installments: fine 5 (€6.50, damage, Nuria Bastos) was collected in two payments, €4.00 by card in May and €2.50 by gateway also in May. If the report were built on fines, that case would be counted wrongly as soon as the two installments fell in different months. The question "how much came into the till in March" is always answered on the table of movements, not on the table of debts.
• GROUP BY 1, 2, 3 groups by position in the SELECT. It is convenient, but fragile: if you insert a column at the beginning, the grouping changes without warning. In queries that are going to last, write the names or repeat the whole CASE expression in the GROUP BY.
• The waived and voided fines do not appear anywhere in the report, and that is correct: they have not been collected and never will be. Fine 6 (€4.00, waived) and fine 8 (€3.20, voided) add up to €7.20 that is neither income nor debt. A report that mixed them in with the pending ones would inflate the collection forecast by 23%.

In SQLite, to_char does not exist: you use strftime('%Y-%m', payment_date).

Difficulty: Advanced

Task. As of August 2, 2026, list the loans overdue and not returned: member, code and title, due date, days late and the surcharge at a rate of €0.20/day with a cap of €15.00. Order by surcharge descending.

Hint. Subtracting two DATEs in PostgreSQL returns an integer number of days. The cap is applied with LEAST.

Solution

Expected result

Explanation. Three things to look at with a magnifying glass:

• The WHERE carries two conditions and both are indispensable. return_date IS NULL selects the open loans; due_date < '2026-08-02' selects the overdue ones. With only the first, loans 17 and 19 (due on August 10 and 12) would appear with negative days late and a negative surcharge: the library owing money to the member.
• The cap is applied and it shows. Without LEAST, Sonia Mestre would pay €19.60 (98 × 0.20). The regulation fixes €15.00, and LEAST(a, b) returns the smaller of the two. In MySQL the function is also called LEAST; in SQLite it is MIN(a, b) with two arguments, which is not the aggregate function MIN() despite the name.
• Date arithmetic is engine-specific. In PostgreSQL, date - date returns integer (days). In SQLite you have to write julianday('2026-08-02') - julianday(due_date), and in MySQL DATEDIFF('2026-08-02', due_date). It is one of the first places where a portable query stops being portable.

A consistency note: copy EJ-3093 is recorded as lost and its loan is still open. It already has a loss fine (€24.00), so in the real process it should be excluded from the late-return surcharges. Adding AND c.status <> 'lost' is a perfectly defensible business decision; the task did not ask for it, but a report handed to management should document it.

Difficulty: Advanced

Task. For the events already held, compute per branch: number of events, offered seats, occupied seats and occupancy percentage with one decimal. Only registrations in status confirmed or attended count as occupied (the cancelled ones and those on the waiting list do not).

Hint. If you join events with registrations and add up offered_seats, the result will be inflated. Aggregate by event first.

Solution

Expected result

Explanation. This exercise brings together almost the whole block in a single query, and its value is in the CTE.

Why the CTE is mandatory. If you added up offered_seats directly over the JOIN of events with registrations, each event would appear as many times as it has registrations. Event 101 has 5 registrations, so its 12 offered seats would be counted 5 times: 60. Central would go from 52 to 100 offered seats and the occupancy would fall from 28.8% to 15%. The mistake produces no symptom at all: the numbers are still reasonable whole numbers. The general rule is: when a report adds up an attribute of the "one" side and another attribute of the "many" side, the one from the "one" side has to be aggregated separately.

The status filter goes in the FILTER, not in the WHERE. sum(...) FILTER (WHERE ...) applies the condition only to the aggregate, leaving the other rows of the group untouched. If you put r.status IN ('confirmed','attended') in the WHERE of the CTE, an event whose registrations were all cancelled would disappear from the report instead of appearing with 0 occupied. FILTER is standard SQL and PostgreSQL supports it; the portable equivalent is sum(CASE WHEN r.status IN ('confirmed','attended') THEN r.occupied_seats ELSE 0 END), which also works in SQLite and MySQL.

100.0 and not 100. If you write 100 * sum(occupied) / sum(offered) with integers, PostgreSQL does integer division and Central would give 28 instead of 28.8, and a case like 5/8 would give 62 instead of 62.5. Multiplying by 100.0 first forces decimal arithmetic. This mistake shows up in production all the time.

The LEFT JOIN is still necessary even though in this data every held event has registrations: if tomorrow an event is held that nobody signed up for, with an INNER JOIN it would disappear from the denominator and the average occupancy would come out artificially high.

1. = NULL instead of IS NULL. It does not raise an error: it gives zero rows. Every time a query returns an unexpectedly empty set, this is the first suspect.

2. count(*) in a LEFT JOIN. It counts 1 where it should count 0. Always count the primary key of the right-hand table.

3. Adding up attributes of the "one" side over a JOIN with the "many" side. The totals get multiplied by the number of child rows. Aggregate each branch in its own CTE or subquery.

4. HAVING and WHERE swapped. WHERE filters rows before grouping, HAVING filters groups afterwards. Putting a condition in HAVING that could go in WHERE works but is slower: it forces the grouping of rows that are going to be discarded.

5. ORDER BY with no tiebreaker. A LIMIT over a non-deterministic order returns different rows in different runs. Always add a unique criterion as the last tiebreaker.

6. Integer division. 100 * a / b with integers truncates. Force decimals with 100.0 or with ::numeric.

7. NOT IN with subqueries that can return NULL. It returns zero rows, always. Use NOT EXISTS.

8. Confusing the member's branch with the copy's branch. They are two different foreign keys towards the same table. When the question says "by branch", find out which branch before writing the JOIN.

9. Forgetting the ON of a JOIN. A silent Cartesian product. With small tables it looks as though it works; with 40,000 copies it does not.

10. Concatenating with NULL. 'a' || NULL is NULL. Wrap the whole expression in COALESCE, not each piece.

A method tip. Build big queries from the inside out: write the FROM with its JOINs and a SELECT * first, check the number of rows, add the WHERE, check again, and only at the end group and project. 90% of aggregation mistakes are caught by looking at how many rows there are before grouping.

A reading tip. When you inherit somebody else's query, read it in the logical order of execution (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY), not in the order in which it is written. That is the order in which the engine understands it.

No hints and more demanding. Write each one as a single query.

For Marta Alsina (member 14), return a single row with: last name, total number of loans, number of loans returned late, number of open loans, total amount of her fines, amount actually paid and outstanding amount.

List the materials that have copies in three or more distinct branches, with the title, the number of branches and the total number of copies. A material with five copies in the same branch does not count.

Return a list of members at risk with a reason column. A member is at risk if they have some overdue loan not returned as of 2026-08-02 (reason 'overdue loan') or if they accumulate more than €10 in pending fines (reason 'high debt'). A member can appear for both reasons.

Solution A

The three financial figures go in scalar subqueries precisely to avoid the problem of exercise 15: if you joined fines and payments in the same JOIN as loans, each fine would be repeated five times (one per loan) and the total would jump from €26.20 to €131.00. The FILTERs over count(*) work here because the LEFT JOIN with loans does not duplicate anything: each loan is one row.

Solution B

Only "The Pillars of the Earth" is spread across three branches (Central, South and East). The DISTINCT inside the count is what tells "three branches" apart from "three copies": without it, material 906 (two copies, in South and Central) would still not sneak in, but a material with three copies in Central would, and that would be a mistake.

Solution C

Marta Alsina appears twice, which is exactly what the task asked for; that is why you have to use UNION ALL and not UNION (although here UNION would give the same, because the reason column already distinguishes the rows). Note that Iván Pereda does not come out: he has a pending fine, but of €1.60, and his open loan is due on August 12. And Sonia Mestre comes out only because of the loan, because her €5.00 outstanding does not reach the threshold.

You have written fifteen queries about BiblioRed that cover all the SQL of module 2, this time without a safety net: projection with aliases, filters with BETWEEN, IN, LIKE and IS NULL, deterministic ordering with LIMIT, DML with the discipline of the prior SELECT, INNER JOIN of two and of four tables, LEFT JOIN with the anti-join pattern, SELF JOIN, aggregation with GROUP BY on one and two columns, HAVING, the difference between COUNT(*) and COUNT(column), the five forms of subquery, derived tables, CTEs, UNION and three management reports that already look like what a service directorate asks for.

More important than the syntax are the three reflexes that should have been installed: look before you modify, check the number of rows before you aggregate and distrust any report that adds up an attribute of the "one" side through a JOIN with the "many" side. None of the three is taught to anybody by an error message, because all three fail in silence.

The next lesson changes muscle. In 07-02, Schema Design Exercises, there will be no tables to query: there will be requirement statements —a video rental store, a course platform, a clinic, a historical fare system— and you will have to build the schema from scratch, going through the ER diagram, the CREATE TABLE and the constraints that encode the business rules. One of the five cases will be an extension of BiblioRed, so that you can see how different it is to design over a schema that already exists and that you cannot break.