Els dos terminals estan oberts i l'esquema de BiblioRed el tens a mà, tal com vam quedar en tancar el mòdul 6. A partir d'aquí el curs canvia de registre: no llegiràs conceptes nous, escriuràs SQL. Aquesta lliçó és un banc de quinze exercicis sobre la xarxa de biblioteques municipals de Vallmar, ordenats per dificultat creixent, i té una manera d'usar-se molt concreta.

Com treballar aquesta lliçó. Llegeix l'enunciat. Abans de mirar res més, escriu la teva consulta a la sessió psql i executa-la. Compara el teu resultat amb el bloc Resultat esperat. Només llavors desplega la Solució i compara-la amb la teva: és perfectament normal —i sovint desitjable— que la teva consulta sigui diferent de la proposada i doni el mateix resultat, perquè en SQL gairebé sempre hi ha més d'un camí. El que no és normal és llegir la solució primer: en aquest cas l'exercici no t'ensenya res, només et dóna la sensació d'haver entès.

Si una consulta no et surt, resisteix almenys cinc minuts abans de mirar la Pista. El múscul que estàs entrenant aquí no és el de recordar la sintaxi de HAVING, sinó el de traduir una pregunta en llenguatge humà a una consulta, i això només s'entrena fallant.

Aquesta lliçó cobreix SELECT, WHERE, JOIN, agregació i subconsultes. Les funcions de finestra, les transaccions i els plans d'execució no hi apareixen: són el material de la lliçó 07-04.

Els exercicis fan servir un joc de dades concret i petit —8 socis, 12 materials, 15 exemplars, 20 préstecs— perquè puguis verificar cada resultat a ull. Si has anat construint BiblioRed al llarg del curs, les teves dades seran diferents i els resultats no coincidiran. Per això convé partir de zero amb l'script d'aquesta lliçó.

Crea una base de dades neta i executa aquest script sencer. A PostgreSQL:

Executa aquesta consulta abans de començar. Si algun número no coincideix, l'script no s'ha executat sencer:

La data de referència. Diversos exercicis calculen retards. Perquè els resultats siguin reproduïbles avui i d'aquí a dos anys, les solucions fan servir la data literal DATE '2026-08-02' en lloc de CURRENT_DATE. En producció escriuries CURRENT_DATE; aquí necessitem que el nombre de dies no canviï.

SQLite. El joc de dades funciona a SQLite canviant SERIAL/BOOLEAN per enters i NUMERIC(8,2) per REAL. Allà on hi hagi diferències rellevants en una consulta, la solució ho indica.

1. Bloc A — Bàsic: consultes sobre una sola taula (exercicis 1-3)
2. Bloc B — Intermedi: consultes sobre diverses taules (exercicis 4-7)
3. Bloc C — Intermedi: agregació i agrupació (exercicis 8-10)
4. Bloc D — Avançat: subconsultes, taules derivades i CTE (exercicis 11-12)
5. Bloc E — Avançat: informes de gestió reals (exercicis 13-15)
6. Errors habituals i consells
7. Exercicis de reforç

Dificultat: Bàsic

Enunciat. El servei de patrimoni vol el llistat dels cinc materials més antics del catàleg. Retorna tres columnes amb aquestes capçaleres exactes: Títol, Editorial i Any. Ordena de més antic a més modern i, quan dos materials siguin del mateix any, alfabèticament per títol.

Solució

Resultat esperat

Explicació. Hi ha tres detalls que separen una consulta correcta d'una consulta descurada:

• Les cometes dobles als àlies. AS Títol fallaria o es convertiria en títol en minúscules, perquè PostgreSQL passa a minúscules tot identificador sense cometes. Per conservar la majúscula i l'accent calen cometes dobles, no simples: les cometes simples delimiten cadenes de text, no identificadors. AS 'Títol' és un error de sintaxi.
• El segon criteri d'ordenació. Sense , titol ASC, les dues files de 2007 sortirien en un ordre que el motor no garanteix. Pot semblar-te estable avui i canviar demà en afegir una fila o en crear un índex. Un ORDER BY que no desempata no és determinista, i en un llistat amb LIMIT això significa que la fila que veus pot variar entre execucions.
• LIMIT va després d'ORDER BY, no abans: primer s'ordena el conjunt sencer, després es tallen cinc files. Si ho fessis a l'inrevés obtindries cinc files qualssevol i després ordenades, que és una pregunta diferent.

A SQLite la consulta funciona igual, però els àlies entre cometes es comporten de manera més laxa (accepta cometes dobles i també claudàtors).

Dificultat: Bàsic

Enunciat. Resol les cinc preguntes següents, cadascuna amb la seva pròpia consulta:

• (a) Materials publicats entre 2000 i 2010, tots dos inclosos, amb títol i any.
• (b) Exemplars l'estat dels quals sigui prestat o reservat, amb codi, estat i sucursal.
• (c) Materials el títol dels quals contingui la paraula «Txernòbil», sense importar majúscules ni minúscules.
• (d) Materials sense portada (portada_url no informada) i autors sense any de naixement.
• (e) Els tipus de material diferents que hi ha al catàleg i quantes editorials diferents hi apareixen.

Pista. Per a (c), a PostgreSQL existeix ILIKE; per a (d), recorda que = NULL mai no és cert.

Solució

Resultat esperat

(a) 5 files: Kafka a la platja (2002), El cor gelat (2007), Un món sense fi (2007), El mapa del temps (2008), Els pilars de la Terra (sèrie) (2010).

(b)

(c) 2 files: 909 «Documental: la veu de Txernòbil» i 912 «Veus de Txernòbil».

(d) 6 materials sense portada (903, 905, 907, 909, 910, 911) i 1 autora sense any de naixement (Delia Marchetti).

(e) 4 tipus (audiollibre, dvd, llibre, revista) i 8 editorials diferents.

Explicació. Cada apartat amaga un parany clàssic:

• BETWEEN 2000 AND 2010 inclou els dos extrems. Si necessites excloure'ls, BETWEEN no serveix: cal escriure > 2000 AND < 2010. I l'ordre importa: BETWEEN 2010 AND 2000 retorna zero files, sense error i sense avís.
• IN ('prestat','reservat') és exactament equivalent a estat = 'prestat' OR estat = 'reservat'. És més llegible i, sobretot, evita l'error de precedència d'escriure WHERE material_id = 901 AND estat = 'prestat' OR estat = 'reservat', que no significa el que sembla: AND lliga més fort que OR.
• ILIKE és una extensió de PostgreSQL. A SQLite, LIKE ja és insensible a majúscules per als caràcters ASCII, però no per als accents ni per a la Ò de «Txernòbil»; allà el que és portable és WHERE lower(titol) LIKE lower('%txernòbil%').
• portada_url = NULL retorna NULL, que en un WHERE es comporta com a fals: la consulta no dóna error, dóna zero files. És una de les errades més difícils de detectar perquè no hi ha cap símptoma.
• count(DISTINCT editorial) compta 8 i no 9: hi ha 12 materials però només 8 editorials diferents, i count(DISTINCT ...) ignora els NULL (aquí no hi ha editorials nul·les, però convé recordar-ho).

Dificultat: Bàsic

Enunciat. Tres operacions de manteniment. En totes tres, abans de modificar res, escriu i executa el SELECT amb el mateix WHERE per veure exactament quines files tocaràs.

• (a) Dóna d'alta el soci 19, Ruben Ortells, correu [email protected], alta avui (2026-08-02), sucursal Est, actiu.
• (b) La sòcia 18 (Berta Quintana) es trasllada de la sucursal Sud a la sucursal Nord. Actualitza la seva sucursal.
• (c) Esborra les reserves expirades la data d'expiració de les quals sigui anterior al 30 de juny de 2026.

Solució

Resultat esperat

Explicació. Tres hàbits que eviten gairebé tots els desastres d'un taulell:

1. Llista de columnes explícita a l'INSERT. INSERT INTO socis VALUES (...) funciona fins al dia que algú afegeix una columna a socis; llavors tots els INSERT sense llista es trenquen o, pitjor, col·loquen els valors a la columna equivocada.
2. El SELECT amb el mateix WHERE, primer. No és una recomanació d'estil: és l'única manera de saber quantes files tocaràs abans de tocar-les. Un UPDATE socis SET sucursal_id = 2 sense WHERE afecta els vuit socis i no hi ha «desfer» fora d'una transacció.
3. RETURNING (PostgreSQL) retorna les files realment afectades. És la confirmació posterior que el WHERE va fer el que estava previst. SQLite ho admet des de la versió 3.35; a MySQL no existeix i cal tornar a consultar.

Sobre (c): si intentessis esborrar la reserva 501 (atesa), no fallaria res perquè cap taula no apunta a reserves. Però si intentessis esborrar la sòcia 14 sí que fallaria, perquè prestecs, reserves, multes i inscripcions la referencien. És la integritat referencial del mòdul 2 fent la seva feina.

Desfés els canvis abans de continuar, perquè els resultats posteriors coincideixin: DELETE FROM socis WHERE soci_id = 19; UPDATE socis SET sucursal_id = 3 WHERE soci_id = 18; INSERT INTO reserves VALUES (503,16,906,'2026-06-01','2026-06-15','expirada'), (506,12,901,'2026-03-30','2026-04-13','expirada');

Dificultat: Intermedi

Enunciat. Un soci pregunta al taulell on pot trobar «Els pilars de la Terra» (material 901). Retorna tots els seus exemplars amb el codi, l'estat i el nom de la sucursal on és cadascun, ordenats per sucursal.

Pista. La sucursal és a exemplars.sucursal_id, no a materials.

Solució

Resultat esperat

Explicació. El JOIN connecta dues taules per la parella clau forana → clau primària: exemplars.sucursal_id apunta a sucursals.sucursal_id. Els àlies e i s no són decoratius: així que hi hagi dues taules amb una columna del mateix nom —i aquí sucursal_id és a totes dues— escriure sucursal_id a seques produeix l'error column reference "sucursal_id" is ambiguous.

L'error fàcil aquí és oblidar la condició de l'ON. Un FROM exemplars e, sucursals s WHERE e.material_id = 901 sense condició de reunió produeix un producte cartesià: 3 exemplars × 4 sucursals = 12 files, totes amb aspecte plausible. És un error que no salta a la vista amb tres files i que amb 40.000 exemplars tomba el servidor.

Dificultat: Intermedi

Enunciat. Llista tots els préstecs oberts (els que encara no s'han retornat) amb: nom i cognoms del soci, codi de l'exemplar, títol del material, sucursal a la qual pertany l'exemplar i data de devolució prevista. Ordena per data prevista.

Pista. La cadena és socis → prestecs → exemplars → materials, i la sucursal penja d'exemplars.

Solució

Resultat esperat

Explicació. Quatre JOIN encadenats no són més difícils que un: cada JOIN afegeix una taula i la seva condició, i el resultat acumulat va creixent cap a la dreta. La clau és començar per la taula que conté les files que vols comptar —aquí prestecs, perquè una fila del resultat és un préstec— i penjar-hi les altres com a decoració.

Dues observacions:

• p.data_devolucio IS NULL és la definició de «préstec obert» en aquest esquema. No hi ha cap columna obert; el fet de no tenir data de devolució és el fet d'estar obert. És un ús de NULL legítim: significa «encara no ha passat».
• La sucursal ve d'exemplars, no de socis. La Marta Alsina està adscrita a Centre i els seus dos préstecs són d'exemplars de Centre, així que la diferència no es nota; però si hagués agafat en préstec un exemplar de Nord, su.nom diria Nord. Confondre «la sucursal del soci» amb «la sucursal de l'exemplar» és l'error de model més freqüent en aquest esquema. A SQLite, || concatena igual que a PostgreSQL.

Dificultat: Intermedi

Enunciat. Dues preguntes de la memòria anual:

• (a) Quins materials no s'han prestat mai? Inclou-hi també els que ni tan sols tenen exemplars.
• (b) Quins socis no tenen cap préstec registrat? Indica si estan actius.

Pista. Un LEFT JOIN seguit de WHERE <columna de la taula dreta> IS NULL deixa exactament les files que no van trobar parella.

Solució

Resultat esperat

(a)

(b)

Explicació. El patró d'antireunió té tres parts que cal respectar juntes:

1. LEFT JOIN, no INNER JOIN. L'INNER descarta precisament les files que busquem.
2. La condició de l'aparellament va a l'ON.
3. El filtre IS NULL va al WHERE i ha d'apuntar a una columna de la taula dreta que mai no sigui nul·la per si mateixa: per això fem servir p.prestec_id (clau primària, mai nul·la) i no p.data_devolucio, que és nul·la als préstecs oberts i ens colaria cinc falsos positius.

A (a) el segon LEFT JOIN és imprescindible. Els materials 907 i 911 no tenen exemplars: després del primer LEFT JOIN, e.exemplar_id ja és NULL, i el segon LEFT JOIN propaga aquest NULL a p.prestec_id, de manera que entren al resultat. Si haguessis fet servir INNER JOIN exemplars, aquests dos materials haurien desaparegut i l'informe hauria dit que només hi ha un material sense prestar. L'antireunió no falla amb error: falla donant menys files del compte.

El material 910 és diferent: sí que té exemplar (EJ-3094), però aquest exemplar no ha sortit mai. Els tres casos —sense exemplars i amb exemplars sense sortida— cauen en el mateix resultat, que és just el que demanava l'enunciat.

Dificultat: Intermedi

Enunciat. Per a una campanya de «si t'ha agradat, t'agradarà», necessitem les parelles de materials del mateix autor. Cada parella ha d'aparèixer una sola vegada (no volem veure A-B i també B-A) i cap material no s'ha d'aparellar amb ell mateix. Mostra l'autor i els dos títols.

Pista. La condició m1.material_id < m2.material_id resol els dos problemes alhora.

Solució

Resultat esperat

Explicació. Un SELF JOIN és un JOIN d'una taula amb ella mateixa; l'únic que el fa possible són els àlies obligatoris (m1, m2), que converteixen una taula en dues «còpies» independents per al motor.

La condició m1.material_id < m2.material_id fa dues feines:

• Amb <> al seu lloc obtindries 10 files: cada parella duplicada en els dos ordres.
• Sense cap condició addicional (només m2.autor_id = m1.autor_id) obtindries 15 files: les 10 anteriors més les 5 de cada material amb ell mateix.

Fixa't també que els materials 910 i 911 (les revistes, amb autor_id nul) no hi apareixen, i és correcte: NULL = NULL no és cert, així que dues files amb autor desconegut no s'aparellen mai. Aquest comportament, que en altres contextos molesta, aquí és exactament el desitjat: no sabem si són del mateix autor.

Dificultat: Intermedi

Enunciat. Tres informes d'inventari i economia:

• (a) Nombre d'exemplars per sucursal, de major a menor.
• (b) Nombre d'exemplars per sucursal i estat.
• (c) Nombre, import total i import mitjà de les multes per motiu, mostrant només els motius amb més d'una multa.

Solució

Resultat esperat

(a)

(b)

(c)

Explicació. El punt que cal interioritzar és l'ordre lògic d'execució que vam veure a 02-05: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

D'aquí en surten les dues regles pràctiques:

• WHERE filtra files abans d'agrupar; HAVING filtra grups després. WHERE count(*) > 1 és un error de sintaxi, i no per caprici: quan s'avalua el WHERE els grups encara no existeixen.
• Tot el que va al SELECT i no és dins d'una funció d'agregat ha d'estar al GROUP BY. Si a (a) hi afegissis s.sucursal_id al SELECT sense afegir-lo al GROUP BY, PostgreSQL donaria error. (Curiosament sí que ho acceptaria si agrupessis per s.sucursal_id, perquè és clau primària i determina funcionalment s.nom: PostgreSQL reconeix aquesta dependència. SQLite i MySQL en mode lax no donen error mai i retornen un valor arbitrari, que és molt pitjor.)

A (c), el motiu perdua desapareix pel HAVING tot i ser la multa més cara (24,00 €). És un recordatori que un HAVING mal triat pot amagar just el que és important.

round(avg(...), 2) necessita que l'argument sigui numeric; com que import és NUMERIC(8,2), funciona. Si la columna fos double precision, PostgreSQL exigiria round(avg(import)::numeric, 2).

Dificultat: Intermedi

Enunciat. Volem el nombre de préstecs de cada soci, inclosos els socis que no han demanat res mai (han d'aparèixer amb 0). Escriu la consulta i explica per què count(*) donaria un resultat incorrecte.

Pista. Compta el que el LEFT JOIN no ha trobat.

Solució

Resultat esperat

Explicació. Aquesta és probablement la trampa més cara del SQL d'informes, perquè no produeix cap error: produeix un número plausible i equivocat.

• count(*) compta files. Després del LEFT JOIN, l'Òscar Vilanova genera una fila —la seva, amb totes les columnes de prestecs a NULL—, així que count(*) retorna 1.
• count(p.prestec_id) compta valors no nuls d'aquella columna. A la fila de l'Òscar, p.prestec_id és NULL, així que no compta res: 0.

La regla que convé memoritzar: en un LEFT JOIN, compta sempre una columna de la taula dreta, i que aquesta columna sigui la seva clau primària. Si comptessis count(p.data_devolucio), obtindries el nombre de préstecs ja retornats (la Marta Alsina donaria 3 en lloc de 5), que és una altra pregunta perfectament vàlida... però no la que t'han fet.

El mateix s'aplica a sum(): sum(import) sobre un grup sense files retorna NULL, no 0. Si l'informe s'ha de mostrar per pantalla, embolcalla'l: COALESCE(sum(import), 0).

Dificultat: Intermedi

Enunciat. Els cinc materials més prestats de tot l'històric, amb el títol, el nom de l'autor (o (sense autor) si no en té) i el nombre de préstecs. Desempata alfabèticament per títol.

Pista. El préstec apunta a l'exemplar, no al material: cal pujar un esglaó.

Solució

Resultat esperat

Explicació. Tres decisions deliberades en aquesta consulta:

• Agrupar per m.material_id, no per m.titol. Si dos materials diferents compartissin títol —cosa perfectament possible: una edició en paper i una altra en audiollibre— agrupar pel títol els fondria en una sola fila. Agrupar per la clau primària i afegir el títol al GROUP BY per poder projectar-lo és el patró segur.
• LEFT JOIN autors, no INNER JOIN. Les revistes 910 i 911 tenen autor_id nul. Aquí no es nota perquè no són entre les més prestades, però un INNER JOIN les expulsaria silenciosament de qualsevol rànquing futur.
• COALESCE a la concatenació completa, no a cada tros. a.nom || ' ' || a.cognoms amb a.nom nul retorna NULL sencer, perquè qualsevol concatenació amb NULL és NULL. Per això el COALESCE embolcalla l'expressió ja muntada.

L'empat entre «Els pilars de la Terra» i «Kafka a la platja» (3 préstecs cadascun) es resol pel títol: E abans que K. Sense aquest segon criteri, l'ordre entre tots dos no estaria garantit i el «top 5» podria canviar d'una execució a una altra.

Aquest rànquing és un LIMIT 5 global. La versió «el més prestat de cada sucursal» necessita funcions de finestra i la veuràs a 07-04.

Dificultat: Avançat

Enunciat. Cinc preguntes, cadascuna amb l'eina que li correspon:

• (a) Materials anteriors a l'any mitjà del catàleg (subconsulta escalar).
• (b) Socis amb alguna multa pendent, fent servir IN.
• (c) Socis amb algun préstec sense retornar, fent servir EXISTS.
• (d) Sucursals sense cap exemplar extraviat, fent servir NOT EXISTS.
• (e) Per a cada soci, la data del seu últim préstec (subconsulta correlacionada); els socis sense préstecs han de sortir amb NULL.

Solució

Resultat esperat

(a) La mitjana del catàleg és 2009,58, així que en surten 6 files: Tokio blues (1987), Els pilars de la Terra (1989), Kafka a la platja (2002), El cor gelat (2007), Un món sense fi (2007), El mapa del temps (2008).

(b) Socis 13 (Mestre), 14 (Alsina) i 15 (Pereda).

(c) Socis 13, 14, 15 i 16.

(d)

(e)

Explicació. Cada forma de subconsulta respon a una forma diferent de pregunta:

Dos advertiments que valen el seu pes en hores de depuració:

1. NOT IN i els NULL no s'avenen. Si escrivissis WHERE sucursal_id NOT IN (SELECT sucursal_id FROM exemplars WHERE estat = 'extraviat') i aquesta subconsulta pogués retornar un NULL, el resultat serien zero files sempre, perquè x NOT IN (1, NULL) s'avalua a NULL. NOT EXISTS no té aquest problema: per això és la forma recomanada per a les antireunions amb subconsulta.
2. SELECT 1 dins d'EXISTS no és una superstició ni una optimització: és una declaració d'intencions. A EXISTS li és igual el que hi projectis —el motor ni tan sols ho avalua—, i escriure SELECT 1 deixa clar al lector següent que les columnes no importen.

A (e), la correlacionada retorna NULL per als socis sense préstecs perquè max() sobre un conjunt buit és NULL. És exactament el que demanava l'enunciat; si volguessis un text, COALESCE(..., 'mai') requeriria convertir abans la data a text.

Dificultat: Avançat

Enunciat. Tres consultes que reorganitzen el problema abans de respondre'l:

• (a) El nombre mitjà de préstecs per soci actiu, fent servir una taula derivada.
• (b) Una fitxa per sucursal amb el seu nombre de socis i el seu nombre d'exemplars, fent servir dues CTE.
• (c) Una agenda unificada de contactes: tots els correus de socis actius i de ponents, en una sola llista, amb una columna que indiqui d'on surt cadascun.

Pista. A (b), no intentis fer-ho amb dos JOIN directes sobre sucursals: els recomptes es multiplicarien entre si.

Solució

Resultat esperat

(a) 2.86 — hi ha 7 socis actius (tots menys l'Òscar Vilanova) que sumen 20 préstecs: 20 / 7 = 2,857...

(b)

(c) 10 files: 3 ponents (Calduch, Lemus, Marchetti) i 7 socis actius.

Explicació. Les tres són variants del mateix moviment: calcular un resultat intermedi i consultar-lo com si fos una taula.

A (a), l'agregació doble és obligatòria. avg(count(*)) no existeix: no es poden imbricar funcions d'agregat. Cal agregar una vegada (préstecs per soci), materialitzar aquell resultat com a taula derivada i agregar una altra vegada. Fixa't en l'AS recomptes: a PostgreSQL una taula derivada necessita àlies o el motor dóna error, encara que no el facis servir.

A (b) hi ha la lliçó important. Si escrivissis això:

...el count(DISTINCT ...) et salvaria de miracle, però un count(*) donaria 3 × 8 = 24 per a Centre. El JOIN de dues branques independents contra la mateixa taula produeix un producte cartesià dins de cada sucursal. Aquest és l'error d'agregació més car que existeix perquè el resultat sembla raonable. La solució robusta és agregar cada branca per separat —en CTE o en subconsultes— i reunir després.

A (c), UNION elimina duplicats i UNION ALL no. Aquí no hi ha duplicats possibles perquè la primera columna ja els distingeix, així que UNION ALL seria més ràpid. Les dues branques han de tenir el mateix nombre de columnes i tipus compatibles, i els noms els posa sempre la primera branca: per això els àlies només calen a dalt. L'ORDER BY va una sola vegada, al final, i afecta el conjunt unit.

Dificultat: Avançat

Enunciat. Intervenció municipal demana la recaptació de multes: per mes i mètode de pagament, el nombre de pagaments i l'import total, més una columna canal que valgui 'presencial' per a efectiu i targeta i 'en línia' per a passarel·la. Afegeix-hi una última consulta amb el total pendent de cobrament (multes en estat pendent).

Pista. to_char(data_pagament, 'YYYY-MM') agrupa per mes a PostgreSQL.

Solució

Resultat esperat

Total recaptat: 11,70 €. Pendent: 3 multes per 30,60 €.

Explicació. L'informe té tres punts fins:

• S'agrupa per pagaments, no per multes. Una multa es pot cobrar en diversos terminis: la multa 5 (6,50 €, deteriorament, Núria Bastos) es va cobrar en dos pagaments, 4,00 € amb targeta al maig i 2,50 € per passarel·la també al maig. Si l'informe es construís sobre multes, aquest cas es comptaria malament així que els dos terminis caiguessin en mesos diferents. La pregunta «quant va entrar a caixa al març» es respon sempre sobre la taula de moviments, no sobre la de deutes.
• GROUP BY 1, 2, 3 agrupa per posició al SELECT. És còmode, però fràgil: si hi insereixes una columna al principi, l'agrupació canvia sense avisar. En consultes que han de durar, escriu-hi els noms o repeteix l'expressió CASE completa al GROUP BY.
• Les multes condonada i anullada no apareixen enlloc de l'informe, i és correcte: no s'han cobrat ni es cobraran. La multa 6 (4,00 €, condonada) i la 8 (3,20 €, anul·lada) sumen 7,20 € que no són ni ingrés ni deute. Un informe que les barregés amb les pendents inflaria la previsió de cobrament en un 23 %.

A SQLite, to_char no existeix: es fa servir strftime('%Y-%m', data_pagament).

Dificultat: Avançat

Enunciat. A data 2 d'agost de 2026, llista els préstecs vençuts i sense retornar: soci, codi i títol, data prevista, dies de retard i el recàrrec a raó de 0,20 €/dia amb un topall de 15,00 €. Ordena per recàrrec descendent.

Pista. La resta de dues DATE a PostgreSQL retorna un enter de dies. El topall s'aplica amb LEAST.

Solució

Resultat esperat

Explicació. Tres coses que cal mirar amb lupa:

• El WHERE porta dues condicions i totes dues són imprescindibles. data_devolucio IS NULL selecciona els préstecs oberts; data_devolucio_prevista < '2026-08-02' selecciona els vençuts. Amb només la primera, els préstecs 17 i 19 (previstos per al 10 i el 12 d'agost) apareixerien amb dies de retard negatius i un recàrrec negatiu: la biblioteca devent diners al soci.
• El topall s'aplica i es nota. Sense LEAST, la Sònia Mestre pagaria 19,60 € (98 × 0,20). El reglament fixa 15,00 €, i LEAST(a, b) retorna el menor dels dos. A MySQL la funció també es diu LEAST; a SQLite és MIN(a, b) amb dos arguments, que no és la funció d'agregat MIN() tot i el nom.
• L'aritmètica de dates és específica de cada motor. A PostgreSQL, date - date retorna integer (dies). A SQLite cal escriure julianday('2026-08-02') - julianday(data_devolucio_prevista), i a MySQL DATEDIFF('2026-08-02', data_devolucio_prevista). És dels primers llocs on una consulta portable deixa de ser-ho.

Nota de coherència: l'exemplar EJ-3093 figura com a extraviat i el seu préstec segueix obert. Ja té una multa per pèrdua (24,00 €), així que en el procés real caldria excloure'l dels recàrrecs per retard. Afegir AND e.estat <> 'extraviat' és una decisió de negoci perfectament defensable; l'enunciat no la demanava, però un informe que es lliura a direcció l'hauria de documentar.

Dificultat: Avançat

Enunciat. Per als esdeveniments ja celebrats, calcula per sucursal: nombre d'esdeveniments, places ofertes, places ocupades i percentatge d'ocupació amb un decimal. Només compten com a ocupades les inscripcions en estat confirmada o assistida (les cancel·lades i les de llista d'espera, no).

Pista. Si uneixes esdeveniments amb inscripcions i sumes places_ofertes, el resultat estarà inflat. Agrega primer per esdeveniment.

Solució

Resultat esperat

Explicació. Aquest exercici reuneix gairebé tot el bloc en una sola consulta, i el seu valor és a la CTE.

Per què la CTE és obligatòria. Si sumessis places_ofertes directament sobre el JOIN d'esdeveniments amb inscripcions, cada esdeveniment apareixeria tantes vegades com inscripcions tingui. L'esdeveniment 101 té 5 inscripcions, així que les seves 12 places ofertes es comptarien 5 vegades: 60. Centre passaria de 52 a 100 places ofertes i l'ocupació cauria del 28,8 % al 15 %. L'error no dóna cap símptoma: els números segueixen essent enters raonables. La regla general és: quan un informe suma un atribut de l'«un» i un altre atribut del «molts», el de l'«un» cal agregar-lo a part.

El filtre per estat va al FILTER, no al WHERE. sum(...) FILTER (WHERE ...) aplica la condició només a l'agregat, deixant intactes les altres files del grup. Si posessis i.estat IN ('confirmada','assistida') al WHERE de la CTE, un esdeveniment les inscripcions del qual fossin totes cancel·lades desapareixeria de l'informe en lloc d'aparèixer amb 0 ocupades. FILTER és SQL estàndard i PostgreSQL l'admet; l'equivalent portable és sum(CASE WHEN i.estat IN ('confirmada','assistida') THEN i.places_ocupades ELSE 0 END), que funciona també a SQLite i MySQL.

100.0 i no 100. Si escrius 100 * sum(ocupades) / sum(ofertes) amb enters, PostgreSQL fa divisió entera i Centre donaria 28 en lloc de 28.8, i un cas com 5/8 donaria 62 en comptes de 62.5. Multiplicar primer per 100.0 força l'aritmètica decimal. Aquest error apareix en producció constantment.

El LEFT JOIN continua essent necessari encara que en aquestes dades tots els esdeveniments celebrats tinguin inscripcions: si demà se'n celebra un al qual no s'apunta ningú, amb INNER JOIN desapareixeria del denominador i l'ocupació mitjana sortiria artificialment alta.

1. = NULL en lloc d'IS NULL. No dóna error: dóna zero files. Cada vegada que una consulta et retorni un conjunt buit inesperat, aquesta és la primera sospita.

2. count(*) en un LEFT JOIN. Compta 1 on hauria de comptar 0. Compta sempre la clau primària de la taula dreta.

3. Sumar atributs de l'«un» sobre un JOIN amb el «molts». Els totals es multipliquen pel nombre de files filles. Agrega cada branca a la seva pròpia CTE o subconsulta.

4. HAVING i WHERE intercanviats. WHERE filtra files abans d'agrupar, HAVING filtra grups després. Posar al HAVING una condició que podria anar al WHERE funciona però és més lent: obliga a agrupar files que es descartaran.

5. ORDER BY sense desempat. Un LIMIT sobre un ordre no determinista retorna files diferents en execucions diferents. Afegeix-hi sempre un criteri únic com a últim desempat.

6. Divisió entera. 100 * a / b amb enters trunca. Força els decimals amb 100.0 o amb ::numeric.

7. NOT IN amb subconsultes que poden retornar NULL. Retorna zero files sempre. Fes servir NOT EXISTS.

8. Confondre la sucursal del soci amb la sucursal de l'exemplar. Són dues claus foranes diferents cap a la mateixa taula. Quan la pregunta diu «per sucursal», esbrina quina sucursal abans d'escriure el JOIN.

9. Oblidar l'ON d'un JOIN. Producte cartesià silenciós. Amb taules petites sembla que funciona; amb 40.000 exemplars no.

10. Concatenar amb NULL. 'a' || NULL és NULL. Embolcalla l'expressió completa amb COALESCE, no cada tros.

Consell de mètode. Construeix les consultes grans de dins cap enfora: escriu primer el FROM amb els seus JOIN i un SELECT *, comprova el nombre de files, afegeix-hi el WHERE, torna a comprovar, i només al final agrupa i projecta. El 90 % dels errors d'agregació es detecten mirant quantes files hi ha abans d'agrupar.

Consell de lectura. Quan heretis una consulta aliena, llegeix-la en l'ordre lògic d'execució (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY), no en l'ordre en què està escrita. És l'ordre en què el motor l'entén.

Sense pistes i més exigents. Escriu cadascun com una sola consulta.

Per a la Marta Alsina (sòcia 14), retorna una única fila amb: cognoms, nombre total de préstecs, nombre de préstecs retornats amb retard, nombre de préstecs oberts, import total de les seves multes, import efectivament pagat i import pendent.

Llista els materials que tenen exemplars en tres o més sucursals diferents, amb el títol, el nombre de sucursals i el nombre total d'exemplars. Un material amb cinc exemplars a la mateixa sucursal no compta.

Retorna una llista de socis en risc amb una columna motiu. Un soci està en risc si té algun préstec vençut sense retornar a 2026-08-02 (motiu 'préstec vençut') o si acumula més de 10 € en multes pendents (motiu 'deute alt'). Un soci pot aparèixer pels dos motius.

Solució A

Les tres xifres econòmiques van en subconsultes escalars precisament per evitar el problema de l'exercici 15: si unissis multes i pagaments al mateix JOIN que prestecs, cada multa es repetiria cinc vegades (una per préstec) i el total saltaria de 26,20 € a 131,00 €. Els FILTER sobre count(*) funcionen aquí perquè el LEFT JOIN amb prestecs no duplica res: cada préstec és una fila.

Solució B

Només «Els pilars de la Terra» està repartit en tres sucursals (Centre, Sud i Est). El DISTINCT dins del count és el que distingeix «tres sucursals» de «tres exemplars»: sense ell, el material 906 (dos exemplars, a Sud i Centre) seguiria sense colar-se, però un material amb tres exemplars a Centre sí que ho faria, i seria un error.

Solució C

La Marta Alsina apareix dues vegades, que és just el que demanava l'enunciat; per això cal fer servir UNION ALL i no UNION (encara que aquí UNION donaria el mateix, perquè la columna motiu ja diferencia les files). Fixa't que l'Ivan Pereda no hi surt: té una multa pendent, però d'1,60 €, i el seu préstec obert venç el 12 d'agost. I la Sònia Mestre hi surt només pel préstec, perquè els seus 5,00 € pendents no arriben al llindar.

Has escrit quinze consultes sobre BiblioRed que recorren tot el SQL del mòdul 2, aquesta vegada sense xarxa: projecció amb àlies, filtres amb BETWEEN, IN, LIKE i IS NULL, ordenació determinista amb LIMIT, DML amb la disciplina del SELECT previ, INNER JOIN de dues i de quatre taules, LEFT JOIN amb el patró d'antireunió, SELF JOIN, agregació amb GROUP BY d'una i dues columnes, HAVING, la diferència entre COUNT(*) i COUNT(columna), les cinc formes de subconsulta, taules derivades, CTE, UNION i tres informes de gestió que ja s'assemblen al que demana una direcció de servei.

Més important que la sintaxi són els tres reflexos que se t'haurien d'haver instal·lat: mirar abans de modificar, comprovar el nombre de files abans d'agregar i desconfiar de tot informe que suma un atribut del costat «un» a través d'un JOIN amb el costat «molts». Cap dels tres no l'ensenya a ningú un missatge d'error, perquè tots tres fallen en silenci.

La lliçó següent canvia de múscul. A 07-02, Exercicis de Disseny d'Esquemes, no hi haurà taules per consultar: hi haurà enunciats de requisits —un videoclub, una plataforma de cursos, una clínica, un sistema de tarifes històriques— i hauràs de construir l'esquema des de zero, passant pel diagrama ER, el CREATE TABLE i les restriccions que codifiquen les regles de negoci. Un dels cinc casos serà una ampliació de BiblioRed, perquè comprovis com n'és, de diferent, dissenyar sobre un esquema que ja existeix i que no pots trencar.