El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Solucions comentades del projecte

Següent ►
►

Aquí és on s'aprèn. No llegint aquestes solucions, sinó comparant-les amb les teves: veient on coincideixes, on has triat un altre camí igual de bo i on t'has deixat un LEFT JOIN que calia. Si hi has arribat sense haver intentat el projecte, torna a 12-03: el solucionari llegit en fred no ensenya gairebé res. Dos advertiments: una solució diferent de la de referència pot ser igual de vàlida —no es corregeix la semblança amb aquest text, sinó que compleixi 12-02 i que la decisió estigui justificada—, i tots els resultats estan calculats sobre el joc de dades de 12-03 (36 préstecs, 20 exemplars, 15 socis) amb DATE '2026-06-30' com a data de referència.

Contingut

  1. L'esquema de referència: decisions i alternatives
  2. Les 15 consultes resoltes
  3. Els índexs de referència
  4. La vista, el procediment i el disparador
  5. Errors freqüents en aquest projecte
  6. Errors habituals i consells
  7. Exercicis
  8. Conclusió

  1. L'esquema de referència: decisions i alternatives

El DDL complet és a 12-03. Aquí van les decisions que es corregeixen, cadascuna amb l'alternativa que també seria correcta i quan triar-la.

Decisió de referència Alternativa vàlida Quan triar l'alternativa
obres + exemplars en dues taules, i exemplars.estat sense el valor prestat (cap) Mai. Són RD-05/RD-07, i afegir prestat duplicaria el que ja diu prestecs
obres_autors amb PK composta (obra_id, autor_id) id subrogat + UNIQUE (obra_id, autor_id) Si una altra taula hagués de referenciar la signatura (drets d'autor per autor i obra). Mentre no existeixi, la PK composta expressa millor la regla (05-01)
data_prevista emmagatzemada, i estat del préstec derivat de les dates Calcular-la des del tipus del soci; columna estat amb CHECK mantinguda de nit Si el termini no pogués canviar ni per tipus ni per renovació (aquí canvia per totes dues coses), o si calguessin estats no deduïbles (en_reclamacio, condonat)
multes com a taula 1 a 0..1, i cua per data_reserva Columnes multa_import/multa_pagada a prestecs; columna posicio mantinguda per disparador Si la multa fos un número sense data de pagament ni condonació; i si calgués reordenar la cua a mà (prioritats). Amb FIFO pur, calcular la posició és estrictament millor
Índex únic parcial per a RI-03 Restricció EXCLUDE amb btree_gist Quan a més calgui impedir solapaments històrics: vegeu més avall

L'alternativa forta a RI-03: EXCLUDE

L'índex parcial impedeix dos préstecs actius del mateix exemplar, però no dos préstecs passats que se solapin. Si això importa —i en una migració de dades antigues importa molt—, PostgreSQL té una restricció per exactament això:

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE prestecs ADD CONSTRAINT excl_prestecs_solapament EXCLUDE USING gist (exemplar_id WITH =,
    daterange(data_prestec, COALESCE(data_devolucio, DATE 'infinity'), '[]') WITH &&);

Es llegeix: "no hi pot haver dues files amb el mateix exemplar_id els intervals de préstec de les quals se solapin", i el COALESCE(..., 'infinity') converteix el préstec obert en un interval sense fi, amb la qual cosa també cobreix RI-03. És més potent i més cara: exigeix una extensió, fa servir un índex GiST i no serveix de suport per a WHERE data_devolucio IS NULL. El criteri: índex parcial si només et preocupa el present; EXCLUDE si has de carregar història o si es registren préstecs retroactius. Totes dues són correctes; cal saber dir quina has triat.

  1. Les 15 consultes resoltes

Bloc A — bàsiques i agregació (RC-01 a RC-08)

-- RC-01 · Obres publicades des del 2015
SELECT o.id, o.titol, o.any_publicacio, o.isbn
FROM   obres AS o
WHERE  o.any_publicacio >= 2015 ORDER BY o.any_publicacio DESC, o.titol;

Nou files, encapçalades per Quadern d'ombres (2024) i El bosc dels noms (2023). La decisió clau és el desempat: ORDER BY any_publicacio DESC a seques deixaria els empats a mercè del pla i el resultat canviaria entre execucions. I hi apareix Xarxes i sistemes distribuïts, l'obra sense cap exemplar: està catalogada, així que al catàleg hi surt.

-- RC-02 · Exemplars d'una seu, amb la seva obra i la seva matèria
SELECT e.codi_barres, o.titol, m.nom AS materia, e.estat, e.data_adquisicio
FROM   exemplars AS e   JOIN obres AS o ON o.id = e.obra_id
JOIN   materies AS m ON m.id = o.materia_id   JOIN seus AS sd ON sd.id = e.seu_id
WHERE  sd.nom = 'Biblioteca Infantil do Parque' ORDER BY o.titol, e.codi_barres;
codi_barres titol materia estat data_adquisicio
ALV-0014 La nena que comptava estrelles Infantil disponible 2024-02-05
ALV-0015 La nena que comptava estrelles Infantil disponible 2024-02-05

(2 de 3 files.) Dues files amb el mateix títol, i no és cap error: són dos exemplars diferents de la mateixa obra, que és el cor del projecte. Qui hagi escrit SELECT DISTINCT o.titol ha perdut justament la informació que es demanava.

-- RC-03 · Préstecs actius, amb els dies fora i el retard a data de referència
SELECT p.id, so.nom || ' ' || so.cognoms AS soci, o.titol, sd.nom AS seu,
       p.data_prevista, DATE '2026-06-30' - p.data_prestec AS dies_fora,
       GREATEST(DATE '2026-06-30' - p.data_prevista, 0)    AS dies_retard
FROM   prestecs AS p   JOIN socis AS so ON so.id = p.soci_id
JOIN   exemplars AS e ON e.id = p.exemplar_id   JOIN obres AS o ON o.id = e.obra_id
JOIN   seus AS sd ON sd.id = e.seu_id
WHERE  p.data_devolucio IS NULL ORDER BY dies_retard DESC, p.data_prevista;
id soci titol seu data_prevista dies_fora dies_retard
29 Lena Fuentes El jardí de les hores Biblioteca de Vila Nova 2026-05-25 57 36
34 Nuno Barros Història mínima d'Alvorada Biblioteca de Vila Nova 2026-06-29 22 1

(2 de 6 files; l'Alba Rey també porta 1 dia, i les altres tres van dins de termini, amb 0 de retard.) Sis préstecs actius, i tres són de la mateixa obra —els tres exemplars d'El jardí de les hores són fora, i d'aquí surt la cua de RC-10—. La decisió clau és GREATEST(..., 0): sense ell, els préstecs dins de termini sortirien amb retard negatiu, que no significa res i embruta qualsevol suma posterior. I data_devolucio IS NULL, mai = NULL: l'error de 04-03 que retorna zero files sense avisar.

-- RC-04 · Els tres buits del sistema, en un sol resultat
SELECT 'soci sense prestecs' AS forat, s.id, s.nom || ' ' || s.cognoms AS descripcio
FROM   socis AS s LEFT JOIN prestecs AS p ON p.soci_id = s.id   WHERE p.id IS NULL
UNION ALL SELECT 'obra sense exemplars', o.id, o.titol
FROM   obres AS o LEFT JOIN exemplars AS e ON e.obra_id = o.id  WHERE e.id IS NULL
UNION ALL SELECT 'exemplar mai prestat', e.id, e.codi_barres || ' — ' || o.titol
FROM   exemplars AS e JOIN obres AS o ON o.id = e.obra_id
LEFT   JOIN prestecs AS p ON p.exemplar_id = e.id WHERE p.id IS NULL   ORDER BY 1, 2;
forat id descripcio
obra sense exemplars 12 Xarxes i sistemes distribuïts
soci sense prestecs 12 Irene Sampaio

(2 de 7 files: 3 exemplars mai prestats, 1 obra sense exemplars i 3 socis sense préstecs.) Tres lectures de negoci diferents: exemplars que ocupen prestatgeria sense sortir mai, un títol catalogat que encara no ha arribat i carnets sense estrenar. La tècnica és l'anti-join de 03-03 (LEFT JOIN + WHERE ... IS NULL), equivalent a NOT EXISTS (07-03) i no a NOT IN, que amb un NULL a la subconsulta retornaria zero files en silenci. UNION ALL i no UNION, perquè no hi ha duplicats a eliminar; i les tres branques necessiten el mateix nombre de columnes i tipus compatibles (03-07), d'aquí l'etiqueta forat.

-- RC-05 · Matèries amb 5+ préstecs i la seva durada mitjana. Definició: préstec = qualsevol
-- fila de préstecs; durada = dies fins a la devolució, o fins avui si segueix obert
SELECT m.nom AS materia, COUNT(p.id) AS prestecs, COUNT(DISTINCT o.id) AS obres,
       ROUND(AVG(COALESCE(p.data_devolucio, DATE '2026-06-30')
                 - p.data_prestec), 1) AS dies_mitjans
FROM   prestecs AS p
JOIN   exemplars AS e ON e.id = p.exemplar_id   JOIN obres AS o ON o.id = e.obra_id
JOIN   materies AS m ON m.id = o.materia_id
GROUP  BY m.id, m.nom HAVING COUNT(p.id) >= 5 ORDER BY prestecs DESC, m.nom;

Tres matèries passen el tall: Narrativa (12 préstecs, 2 obres, 32,3 dies de mitjana), Infantil (8, 2, 14,6) i Informàtica (6, 1, 26,8). El filtre va a HAVING i no a WHERE perquè s'aplica al grup ja agregat: WHERE COUNT(*) >= 5 és un error de sintaxi, i és la fallada més repetida de 04-06. Les sis matèries reparteixen els 36 préstecs (12 + 8 + 6 + 4 + 4 + 2). I el COALESCE de la durada és una decisió de definició: sense ell, AVG ignoraria els sis préstecs oberts i la mitjana de Narrativa baixaria, perquè els tres préstecs més llargs que hi ha ara són justament els que no han tornat.

-- RC-06 · Disponibilitat per obra. Habilitats = en estat 'disponible' (exclou reparació,
-- extraviament i baixa); disponibles ara = habilitats menys els que estan prestats
SELECT o.titol, COUNT(e.id) AS exemplars, COUNT(pa.id) AS prestats,
       COUNT(e.id) FILTER (WHERE e.estat = 'disponible') AS habilitats,
       COUNT(e.id) FILTER (WHERE e.estat = 'disponible') - COUNT(pa.id) AS disponibles
FROM   obres AS o
LEFT   JOIN exemplars AS e  ON e.obra_id = o.id
LEFT   JOIN prestecs  AS pa ON pa.exemplar_id = e.id AND pa.data_devolucio IS NULL
GROUP  BY o.id, o.titol ORDER BY disponibles, o.titol;
titol exemplars prestats habilitats disponibles
El jardí de les hores 3 3 3 0
Xarxes i sistemes distribuïts 0 0 0 0

(2 de 12 files.) Les dues files diuen 0 disponibles per raons oposades, i un informe honest les distingeix: d'una n'hi ha tres exemplars i els tres estan prestats; de l'altra no n'hi ha cap. Per això es publiquen les quatre columnes i no només l'última. Tres decisions: COUNT(e.id) i no COUNT(*), o l'obra sense exemplars sortiria amb 1; la condició del LEFT JOIN a prestecs va al ON; i Bases de dades relacionals surt amb 3 exemplars però només 2 habilitats, perquè un és en reparació — aquesta diferència és exactament el que un comptador únic no podria expressar.

-- RC-07 · Obres signades per més d'un autor, en ordre de signatura
SELECT o.titol, COUNT(*) AS n_autors,
       string_agg(a.nom || ' ' || a.cognoms, ', ' ORDER BY oa.ordre) AS autors
FROM   obres AS o JOIN obres_autors AS oa ON oa.obra_id = o.id
JOIN   autors AS a ON a.id = oa.autor_id
GROUP  BY o.id, o.titol HAVING COUNT(*) > 1 ORDER BY o.titol;
titol n_autors autors
Bases de dades relacionals 2 Pere Aymà, Nora Ibáñez
El bosc dels noms 2 Clara Meireles, Ada Quiroga

(2 de 3 files; falta "Història mínima d'Alvorada", de Ruy Castelo i Tomás Vega.) L'ORDER BY oa.ordre dins del string_agg és la decisió clau, i se la salta gairebé tothom: sense ell, l'ordre dels autors dins de la cel·la és el que vulgui el motor, i una portada signada "Aymà i Ibáñez" podria sortir invertida. És la raó que obres_autors tingui columna ordre. I fixa't que la taula pont porta dades pròpies (rol i ordre): això la converteix en una entitat de ple dret i no en un simple parell de claus.

-- RC-08 · Multes per tipus de soci. Multa = fila de multes, que només existeix després de la
-- devolució (RN-10); pendent = sense data_pagament. NO inclou el deute potencial dels vençuts
SELECT so.tipus, COUNT(mu.id) AS multes,
       COALESCE(SUM(mu.import), 0)                                            AS import_total,
       COALESCE(SUM(mu.import) FILTER (WHERE mu.data_pagament IS NOT NULL), 0) AS cobrat,
       COALESCE(SUM(mu.import) FILTER (WHERE mu.data_pagament IS NULL), 0)     AS pendent
FROM   socis AS so
LEFT   JOIN prestecs AS p ON p.soci_id = so.id   LEFT JOIN multes AS mu ON mu.prestec_id = p.id
GROUP  BY so.tipus ORDER BY import_total DESC;
tipus multes import_total cobrat pendent
general 5 25.80 12.40 13.40
infantil 1 1.40 1.40 0.00
senior 0 0.00 0.00 0.00

Els 13,40 € pendents són d'un sol soci, en Diego Andrade, i són exactament els que superen el llindar de 10 € de la RN-11 i expliquen el seu estat bloquejat. La xifra de control: 6 multes sobre 30 préstecs retornats són una taxa de retard del 20,00 %, i 25,80 + 1,40 = 27,20 € és el total del sistema. El COALESCE és imprescindible —sense ell, la fila dels sènior mostraria NULL en una columna de diners, que algú llegirà com a "no hi ha dada" (06-04)— i aquesta fila hi ha d'aparèixer: la salva el LEFT JOIN.

Bloc B — subconsultes, cues, finestres i recursives (RC-09 a RC-15)

-- RC-09 · Socis amb més préstecs que la mitjana del seu tipus
SELECT s.id, s.nom || ' ' || s.cognoms AS soci, s.tipus,
       (SELECT COUNT(*) FROM prestecs AS p WHERE p.soci_id = s.id) AS prestecs,
       ROUND((SELECT COUNT(p2.id)::numeric / COUNT(DISTINCT s2.id) FROM socis AS s2
              LEFT JOIN prestecs AS p2 ON p2.soci_id = s2.id
              WHERE s2.tipus = s.tipus), 2)                        AS mitjana_del_seu_tipus
FROM   socis AS s
WHERE  (SELECT COUNT(*) FROM prestecs AS p WHERE p.soci_id = s.id)
     > (SELECT COUNT(p2.id)::numeric / COUNT(DISTINCT s2.id) FROM socis AS s2
        LEFT JOIN prestecs AS p2 ON p2.soci_id = s2.id WHERE s2.tipus = s.tipus)
ORDER  BY s.tipus, prestecs DESC, s.id;
id soci tipus prestecs mitjana_del_seu_tipus
1 Marta Coelho general 4 2.56
5 Alba Rey infantil 4 2.33

(2 de 10 files: 6 generals, 2 infantils i 2 sènior.) La subconsulta és correlacionada pel WHERE s2.tipus = s.tipus: s'avalua una vegada per soci, amb el seu tipus (07-02). I el detall que decideix si la xifra és correcta és COUNT(p2.id) enfront de COUNT(*): amb COUNT(*), els socis sense préstecs aportarien una fila fantasma cadascun i la mitjana dels generals sortiria 2,67 en lloc de 2,56 — prou semblant perquè ningú no ho noti. Alternativa igual de vàlida i més llegible: una CTE amb les mitjanes per tipus i un JOIN contra ella; amb quinze socis tant se val, amb quinze mil la CTE s'avalua una vegada en lloc d'una per fila.

-- RC-10 · La cua de reserves en espera, amb la posició de cada soci
SELECT o.titol, s.nom || ' ' || s.cognoms AS soci, r.data_reserva, sd.nom AS recollida_a,
       ROW_NUMBER() OVER (PARTITION BY r.obra_id ORDER BY r.data_reserva, r.id) AS posicio
FROM   reserves AS r
JOIN   obres AS o ON o.id = r.obra_id     JOIN socis AS s ON s.id = r.soci_id
JOIN   seus AS sd ON sd.id = r.seu_id     WHERE r.estat = 'en_espera'
ORDER  BY o.titol, posicio;
titol soci data_reserva recollida_a posicio
El jardí de les hores Nuno Barros 2026-06-10 Biblioteca Central d'Alvorada 1
El jardí de les hores Manuel Otero 2026-06-18 Biblioteca Central d'Alvorada 2
El jardí de les hores Óscar Vilar 2026-06-22 Biblioteca de Vila Nova 3

La posició no és a cap columna: la calcula ROW_NUMBER(). Aquesta és tota la solució al problema de la cua, i per això el PARTITION BY r.obra_id és obligatori: cada obra té la seva cua i les numeracions no s'han de barrejar. L'r.id com a segon criteri no és opcional —dues reserves del mateix dia quedarien en ordre arbitrari, i la posició d'un soci canviaria entre dues consultes—. És ROW_NUMBER i no RANK expressament: en una cua no hi pot haver dos primers. I el WHERE deixa fora la reserva ja recollida i la caducada, que són història.

-- RC-11 · Les 3 obres més prestades de cada seu
SELECT sd.nom AS seu, t.lloc, t.titol, t.prestecs
FROM   seus AS sd LEFT JOIN LATERAL (
           SELECT o.titol, COUNT(*) AS prestecs,
                  ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, o.titol) AS lloc
           FROM   exemplars AS e JOIN obres AS o ON o.id = e.obra_id
           JOIN   prestecs AS p ON p.exemplar_id = e.id
           WHERE  e.seu_id = sd.id   GROUP BY o.id, o.titol
           ORDER  BY prestecs DESC, o.titol LIMIT 3) AS t ON TRUE
ORDER  BY sd.id, t.lloc;
seu lloc titol prestecs
Biblioteca Central d'Alvorada 1 El jardí de les hores 6
Biblioteca Infantil do Parque 1 La nena que comptava estrelles 4

(2 de 8 files; a Vila Nova el primer lloc és "Bases de dades relacionals", amb 2.) LATERAL és el que permet que la subconsulta vegi sd.id de la fila de fora; sense ell, un LIMIT 3 dins d'una subconsulta normal donaria les 3 millors del sistema sencer, repetides a les tres seus (07-04). El LEFT JOIN LATERAL ... ON TRUE en lloc de CROSS JOIN LATERAL és el que salva una seu sense préstecs. Alternativa igual de vàlida: una CTE amb ROW_NUMBER() particionat i WHERE lloc <= 3 fora — més portable, perquè LATERAL no és a tots els motors. LATERAL guanya quan la taula de fora és petita i la de dins enorme, perquè només llegeix el que necessita de cada grup.

-- RC-12 · Préstecs per mes dels darrers 12 mesos, sense buits
WITH calendari AS (SELECT generate_series(DATE '2025-07-01', DATE '2026-06-01',
                                          INTERVAL '1 month')::date AS mes),
mensual AS (SELECT date_trunc('month', data_prestec)::date AS mes, COUNT(*) AS n
            FROM prestecs GROUP BY 1)
SELECT to_char(c.mes, 'YYYY-MM') AS mes, COALESCE(m.n, 0) AS prestecs,
       SUM(COALESCE(m.n, 0)) OVER (ORDER BY c.mes) AS acumulat,
       ROUND(AVG(COALESCE(m.n, 0)) OVER (ORDER BY c.mes
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS mitjana_mobil_3
FROM   calendari AS c LEFT JOIN mensual AS m ON m.mes = c.mes ORDER BY c.mes;
-- Sense el calendari, la sèrie tindria 10 files en lloc de 12 i començaria al setembre
mes prestecs acumulat mitjana_mobil_3
2025-07 0 0 0.00
2026-06 4 36 4.00

(2 de 12 files: la primera i l'última; 2025-07 i 2025-08 són buits.) Els dos mesos amb zero són el motiu de la consulta. Sense el calendari, un GROUP BY sobre prestecs retornaria deu files i el gràfic començaria al setembre com si el servei no hagués existit abans; el LEFT JOIN contra generate_series els fa visibles (11-01). El COALESCE no és cosmètic: sense ell la finestra arrossegaria NULL i l'acumulat quedaria inservible des de la primera fila buida. L'acumulat tanca en 36, el total de préstecs: la xifra de control que valida la sèrie sencera (11-04).

-- RC-13 · Els 3 socis més lectors de cada seu, amb desempat explícit
WITH ranquing AS (
    SELECT sd.id AS seu_id, sd.nom AS seu, s.nom || ' ' || s.cognoms AS soci,
           COUNT(p.id) AS prestecs, MAX(p.data_prestec) AS ultim,
           ROW_NUMBER() OVER (PARTITION BY sd.id ORDER BY COUNT(p.id) DESC,
                              MAX(p.data_prestec) DESC, s.id) AS lloc
    FROM   socis AS s
    JOIN   seus AS sd ON sd.id = s.seu_id   LEFT JOIN prestecs AS p ON p.soci_id = s.id
    WHERE  s.estat <> 'baixa'   GROUP BY s.id, sd.id, sd.nom, s.nom, s.cognoms)
SELECT seu, lloc, soci, prestecs, ultim FROM ranquing
WHERE  lloc <= 3 ORDER BY seu_id, lloc;
seu lloc soci prestecs ultim
Biblioteca Central d'Alvorada 1 Iván Losada 4 2026-05-12
Biblioteca Infantil do Parque 3 Carla Nieto 0 (null)

(2 de 9 files; la Marta Coelho és la segona de la Central, també amb 4.) El desempat és la decisió que es corregeix. L'Iván i la Marta tenen els mateixos 4 préstecs: amb RANK() tots dos serien primers, i a Vila Nova, on hi ha un triple empat a 3, un "top 3" retornaria tres primers. Amb ROW_NUMBER() i el criteri més préstecs → més recent → id menor, l'ordre és total i estable. Cap de les dues no és incorrecta —RANK és el que vols en una classificació esportiva—, però cal triar a consciència i dir-ho. I la segona fila és una lliçó d'honestedat: la seu Infantil només té tres socis, així que el seu "tercer soci més lector" té zero préstecs. És l'avís d'11-04 sobre publicar rànquings amb mostres minúscules.

-- RC-14 · Organigrama de bibliotecaris, amb el seu nivell i la seva ruta jeràrquica
WITH RECURSIVE arbre AS (
    SELECT b.id, b.nom || ' ' || b.cognoms AS bibliotecari, b.carrec,
           1 AS nivell, b.cognoms::text AS ruta
    FROM   bibliotecaris AS b WHERE b.responsable_id IS NULL    -- cas base: la direcció
    UNION ALL
    SELECT h.id, h.nom || ' ' || h.cognoms, h.carrec, a.nivell + 1, a.ruta || ' > ' || h.cognoms
    FROM   bibliotecaris AS h JOIN arbre AS a ON a.id = h.responsable_id)  -- pas recursiu
SELECT nivell, repeat('    ', nivell - 1) || bibliotecari AS organigrama, carrec, ruta
FROM   arbre ORDER BY ruta;   -- ORDER BY ruta = cada persona sota el seu responsable
nivell organigrama carrec ruta
1 Helena Corvo Directora de la xarxa Corvo
3 ········Fátima Cordero Auxiliar de préstec Corvo > Nogueira > Cordero

(2 de 8 files: 1 al nivell 1, 3 al 2 i 4 al 3.) La ruta fa dues feines alhora, i aquest és el truc que cal conèixer: es llegeix d'un cop d'ull i, sobretot, és el que permet ORDER BY ruta perquè cada persona surti sota el seu responsable. Ordenar per nivell donaria tots els responsables junts i després tots els auxiliars, que no és un organigrama. El cas base és responsable_id IS NULL, i per això el joc de dades necessita un bibliotecari sense responsable: sense ell la recursió no arrenca i la consulta retorna zero files. Amb dades reals convé a més acumular els id visitats i tallar els cicles, perquè un responsable_id en bucle faria girar la consulta per sempre (10-02).

-- RC-15 · Informe pivotat: préstecs per seu i matèria, sense perdre cap seu
SELECT sd.nom AS seu,
       COUNT(p.id) FILTER (WHERE m.nom = 'Narrativa')   AS narrativa,
       COUNT(p.id) FILTER (WHERE m.nom = 'Poesia')      AS poesia,
       COUNT(p.id) FILTER (WHERE m.nom = 'Història')    AS historia,
       COUNT(p.id) FILTER (WHERE m.nom = 'Ciència')     AS ciencia,
       COUNT(p.id) FILTER (WHERE m.nom = 'Infantil')    AS infantil,
       COUNT(p.id) FILTER (WHERE m.nom = 'Informàtica') AS informatica, COUNT(p.id) AS total
FROM   seus AS sd
LEFT   JOIN exemplars AS e ON e.seu_id = sd.id      LEFT JOIN obres AS o ON o.id = e.obra_id
LEFT   JOIN materies AS m ON m.id = o.materia_id    LEFT JOIN prestecs AS p ON p.exemplar_id = e.id
GROUP  BY sd.id, sd.nom ORDER BY total DESC;
seu narrativa poesia historia ciencia infantil informatica total
Biblioteca Central d'Alvorada 8 2 2 2 2 4 20
Biblioteca de Vila Nova 4 0 2 2 0 2 10
Biblioteca Infantil do Parque 0 0 0 0 6 0 6

La validació per dos camins: les files sumen 20 + 10 + 6 = 36, el total de préstecs, i les columnes sumen 12 + 2 + 4 + 4 + 8 + 6 = 36 també. Si qualsevol de les dues no donés, la consulta estaria malament encara que les xifres semblessin raonables (11-04). La cadena de quatre LEFT JOIN és deliberada: n'hi ha prou que un sigui INNER perquè una seu sense exemplars desaparegui. I la lectura de negoci és immediata: la Infantil do Parque presta només infantil, mentre que la Central és l'única amb fons en les sis matèries — un desequilibri que cap total general no ensenya.

  1. Els índexs de referència

Set índexs, cadascun amb la consulta que el justifica (RP-05). Cap més:

Índex Consulta que el fa servir Què canvia al pla
uq_prestec_actiu_per_exemplar (únic, parcial) RC-03, RC-06, vista de vençuts És la restricció RI-03 i l'accés als actius: Index Scan sobre unes poques entrades en lloc de Seq Scan sobre tot l'històric
idx_prestecs_soci RC-09, RC-13, fitxa de soci PostgreSQL no indexa les FK (08-01): sense ell, la fitxa d'un soci llegeix la taula sencera. El mateix val per a idx_exemplars_obra i idx_exemplars_seu, les dues navegacions més freqüents del sistema (RC-02, RC-06, RC-11, RC-15)
idx_prestecs_exemplar_data sobre (exemplar_id, data_prestec), i idx_prestecs_data RC-11, RC-12, RC-15 i informes per període El compost filtra per exemplar i de passada dona l'ordre per data sense ordenar (08-02); el segon converteix el recorregut de l'històric en una lectura de rang
idx_obres_titol_lower sobre LOWER(titol) Cerca del catàleg Índex d'expressió: un WHERE LOWER(titol) = ... no pot fer servir un índex sobre titol (08-03)

El que es decideix no indexar (RP-07): socis.tipus i estat (tres i quatre valors, el planificador preferirà el recorregut), materies, seus i editorials senceres (caben en una pàgina), i obres_autors, la PK composta de la qual ja serveix per anar de l'obra a l'autor — encara que no al revés: si calgués "totes les obres d'un autor", caldria afegir (autor_id, obra_id). Aquest matís és l'ordre de les columnes d'un índex compost que explicava 08-02. I sobre la cerca per títol (RP-04): LOWER(titol) resol la igualtat i el prefix (LIKE 'jardi%'), però no una paraula al mig; per a això calen trigrames (pg_trgm amb GIN) o text complet (to_tsvector). La resposta correcta a l'informe no és "faig servir GIN", és dir quin tipus de cerca cal i triar en conseqüència.

  1. La vista, el procediment i el disparador

CREATE OR REPLACE VIEW v_prestecs_vencuts AS
SELECT p.id AS prestec_id, so.id AS soci_id, so.nom || ' ' || so.cognoms AS soci,
       so.email, o.titol, e.codi_barres, sd.nom AS seu, p.data_prevista,
       CURRENT_DATE - p.data_prevista AS dies_retard,
       LEAST(0.20 * (CURRENT_DATE - p.data_prevista), 20.00) AS multa_estimada
FROM   prestecs AS p JOIN exemplars AS e ON e.id = p.exemplar_id
JOIN   obres AS o ON o.id = e.obra_id            JOIN seus AS sd ON sd.id = e.seu_id
JOIN   socis AS so ON so.id = p.soci_id
WHERE  p.data_devolucio IS NULL AND p.data_prevista < CURRENT_DATE;

Amb la data de referència retorna tres files: la Lena Fuentes amb 36 dies i 7,20 €, i en Nuno Barros i l'Alba Rey amb 1 dia i 0,20 € cadascun. Dues decisions: s'anomena multa_estimada perquè la multa no existeix fins a la devolució (RN-10), i el LEAST aplica el topall de 20 € dins de la vista, perquè ningú no se n'hagi de recordar.

CREATE OR REPLACE PROCEDURE registrar_devolucio(p_prestec_id INTEGER)
LANGUAGE plpgsql AS $$
DECLARE v_prevista DATE; v_obra_id INTEGER; v_retard INTEGER;
BEGIN   UPDATE prestecs SET data_devolucio = CURRENT_DATE               -- 1. tancar
    WHERE  id = p_prestec_id AND data_devolucio IS NULL RETURNING data_prevista INTO v_prevista;
    IF NOT FOUND THEN RAISE EXCEPTION 'El préstec % ja estava retornat', p_prestec_id; END IF;
    v_retard := CURRENT_DATE - v_prevista;                          -- 2. multa (RN-09)
    IF v_retard > 0 THEN INSERT INTO multes (prestec_id, import, dies_retard, data_generacio)
        VALUES (p_prestec_id, LEAST(0.20 * v_retard, 20.00), v_retard, CURRENT_DATE);
    END IF;
    SELECT e.obra_id INTO v_obra_id                                 -- 3. avisar (RN-08)
    FROM   prestecs AS p JOIN exemplars AS e ON e.id = p.exemplar_id WHERE p.id = p_prestec_id;
    UPDATE reserves SET estat = 'disponible', data_avis = CURRENT_DATE
    WHERE  id = (SELECT r.id FROM reserves AS r WHERE r.obra_id = v_obra_id
                 AND r.estat = 'en_espera' ORDER BY r.data_reserva, r.id LIMIT 1);
END; $$;

Quatre detalls que convé copiar: l'UPDATE ... RETURNING llegeix i escriu en un sol pas (05-02); l'AND data_devolucio IS NULL fa l'operació idempotent, així que cridar-la dues vegades no tanca dues vegades ni genera dues multes; l'IF NOT FOUND converteix una fallada silenciosa en una excepció que desfà la transacció sencera; i l'ORDER BY r.data_reserva, r.id respecta la cua amb desempat, igual que RC-10. Amb diverses caixes obertes alhora, aquell SELECT ... LIMIT 1 voldria a més FOR UPDATE SKIP LOCKED (09-05), perquè dues devolucions simultànies de la mateixa obra no avisin el mateix soci.

CREATE OR REPLACE FUNCTION fn_verificar_soci() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE v_estat TEXT;
BEGIN   SELECT estat INTO v_estat FROM socis WHERE id = NEW.soci_id;
    IF v_estat <> 'actiu' THEN
        RAISE EXCEPTION 'El soci % està en estat "%": no pot agafar en préstec (RN-11)',
                        NEW.soci_id, v_estat;
    END IF;   RETURN NEW;
END; $$;
CREATE TRIGGER trg_prestec_soci_actiu BEFORE INSERT ON prestecs
    FOR EACH ROW EXECUTE FUNCTION fn_verificar_soci();

Amb en Diego Andrade (soci 6, bloquejat pels seus 13,40 € impagats), qualsevol INSERT a prestecs acaba en ERROR: El soci 6 està en estat "bloquejat": no pot agafar en préstec (RN-11). És l'únic disparador del projecte, i la seva justificació és la de 10-05: la regla depèn d'una altra taula, així que un CHECK no la pot expressar (05-01), i s'ha de complir vingui l'INSERT d'on vingui.

  1. Errors freqüents en aquest projecte

Error Com es manifesta Com s'arregla
Confondre obra i exemplar, o no poder expressar "préstec actiu" Una taula llibres amb num_exemplars, o prestecs.obra_id; un actiu BOOLEAN que acaba contradient data_devolucio Refer el model —és la fallada que anul·la el bloc sencer de la rúbrica—; i recordar que data_devolucio IS NULL és l'estat, blindat per l'índex parcial
Restar dates malament, o sumar deute tancat i potencial Retards negatius, multes calculades sobre data_prestec, o un pendent més gran que la suma de multes GREATEST(dif, 0), LEAST(import, topall), retard sempre contra la data prevista, i dues mètriques separades (RN-10)
Esborrar en lloc de donar de baixa, o modelar la cua amb un booleà Un CASCADE que s'emporta l'històric; un es_el_seguent que dos processos posen a TRUE alhora estat = 'baixa' amb ON DELETE RESTRICT (RD-10), i data_reserva + ROW_NUMBER() (RC-10)
COUNT(*) després d'un LEFT JOIN, o filtrar la taula dreta al WHERE Socis sense préstecs amb 1; o el LEFT JOIN convertit en JOIN i els zeros desapareguts COUNT(columna_de_la_dreta), i la condició de la taula dreta al ON

Errors habituals i consells

  • Llegir aquestes solucions en lloc de comparar-les. Obre el teu fitxer al costat i ves consulta per consulta: on coincideixis, confirma que saps per què; on difereixis, decideix quina és millor i anota-ho a l'informe. Aquesta és la part que es corregeix.
  • Copiar una solució que no entens. A la defensa (12-05) et preguntaran per què LATERAL i no una CTE, o per què ROW_NUMBER i no RANK; si no ho saps, es nota en deu segons. I donar per bona una consulta perquè retorna files. Retornar files no és retornar les correctes. valida per dos camins: RC-15 suma 36 per files i per columnes, RC-12 tanca l'acumulat en 36 i RC-08 quadra amb les 6 multes.
  • Consell: desa la sortida de les quinze consultes en un fitxer. Quan toquis l'esquema o les dades, un diff et dirà a l'instant què s'ha mogut i si era el que esperaves (11-04). I escriu al costat de cada consulta la lliçó que aplica: converteix el projecte en un índex del que saps fer.

Exercicis

Exercici 1

Sobre RC-08. (1) Escriu la consulta del deute total real de cada soci: multes impagades més la multa estimada dels seus préstecs vençuts sense retornar. (2) Quant deu en Diego Andrade amb aquesta definició i quant amb la de RC-08? (3) Quina publicaries a tresoreria i quina a direcció?

Exercici 2

RC-11 fa servir LATERAL. (1) Reescriu-la amb una CTE i ROW_NUMBER(). (2) Dona un motiu per preferir cada versió. (3) Què li passa a cadascuna si una seu no té cap préstec?

Exercici 3

Un company lliura això com a "obres més prestades": SELECT o.titol, COUNT(*) FROM obres o JOIN exemplars e ON e.obra_id = o.id JOIN prestecs p ON p.exemplar_id = e.id GROUP BY o.titol ORDER BY 2 DESC LIMIT 5; (1) Quins tres problemes té? (2) Corregeix-la. (3) Quin dels tres es manifesta avui amb aquestes dades?

Solucions

Solució 1 — Dues subconsultes correlacionades, una per mètrica, i mai sumades a la mateixa columna:

SELECT s.id, s.nom || ' ' || s.cognoms AS soci, s.estat,
       COALESCE((SELECT SUM(mu.import) FROM multes AS mu JOIN prestecs AS p2 ON p2.id = mu.prestec_id
                 WHERE p2.soci_id = s.id AND mu.data_pagament IS NULL), 0) AS deute_tancat,
       COALESCE((SELECT SUM(LEAST(0.20 * (DATE '2026-06-30' - p3.data_prevista), 20.00))
                 FROM prestecs AS p3 WHERE p3.soci_id = s.id
                 AND p3.data_devolucio IS NULL
                 AND p3.data_prevista < DATE '2026-06-30'), 0)             AS deute_potencial
FROM socis AS s ORDER BY deute_tancat + deute_potencial DESC, s.id;

(2) En Diego Andrade deu 13,40 € amb les dues definicions, perquè no té cap préstec vençut sense retornar. Els qui canvien són uns altres: la Lena Fuentes passa de 0,00 € a 7,20 €, i en Nuno Barros i l'Alba Rey de 0,00 € a 0,20 €; el deute potencial total és de 7,60 €. (3) A tresoreria, la de RC-08: són els únics euros exigibles avui, amb una multa emesa al darrere. A direcció, les dues columnes juntes, perquè la potencial anticipa el que entrarà i assenyala a qui trucar abans que el deute creixi. El que no es pot fer mai és sumar-les en una columna anomenada "deute": és l'error de l'apartat 5.

Solució 2 — La CTE és la de RC-13 aplicada a obres: ROW_NUMBER() OVER (PARTITION BY sd.id ORDER BY COUNT(*) DESC, o.titol) sobre un GROUP BY sd.id, o.id, i un WHERE lloc <= 3 fora. (2) A favor de la CTE: és SQL estàndard i portable —LATERAL no existeix a MySQL abans de la 8.0.14 ni a SQLite— i es llegeix de dalt a baix. A favor de LATERAL: només porta 3 files per seu en lloc de calcular el rànquing de totes les obres per descartar-ne gairebé totes, cosa que amb cent mil títols és la diferència entre mil·lisegons i segons. (3) La CTE perd la seu sense préstecs, perquè no hi hauria res a agrupar; la versió LEFT JOIN LATERAL ... ON TRUE la conserva amb NULL a les columnes de la subconsulta. Per igualar-les caldria partir de seus amb un LEFT JOIN contra el recompte.

Solució 3 — (1) Agrupa per titol en lloc de per id: dues obres diferents amb el mateix títol —dues edicions d'un clàssic, cosa habitual en una biblioteca— es fondrien en una fila amb la suma de totes dues. ORDER BY 2 DESC sense desempat: amb LIMIT 5 i diverses obres empatades, quines cinc surten depèn del pla i l'informe deixa de ser reproduïble. I COUNT(*) sense àlies: la columna s'anomenarà count i qui llegeixi el resultat no sabrà si compta préstecs, exemplars o files del JOIN (11-02). Falta a més la definició: compta tots els préstecs, inclosos els oberts i els de socis de baixa. (2) La versió correcta:

SELECT o.id, o.titol, COUNT(p.id) AS prestecs
FROM   obres AS o JOIN exemplars AS e ON e.obra_id = o.id JOIN prestecs AS p ON p.exemplar_id = e.id
GROUP  BY o.id, o.titol ORDER BY prestecs DESC, o.titol LIMIT 5;

(3) El del desempat. Amb aquestes dades no hi ha dues obres amb el mateix títol, així que el primer problema no es manifesta — i aquest és el perill: la consulta passa les proves i falla el dia que algú catalogui una segona edició. En canvi Cartes des del far, L'àtom i el dubte, Història mínima d'Alvorada i La nena que comptava estrelles tenen totes quatre 4 préstecs, així que la cinquena posició del LIMIT 5 és avui una loteria entre quatre candidates.

Conclusió

Ja tens amb què comparar-te:

  • L'esquema de referència i les seves decisions, cadascuna amb la seva alternativa vàlida: la PK composta d'obres_autors enfront de l'id subrogat, la data_prevista emmagatzemada, l'estat derivat enfront de l'emmagatzemat, les multes com a taula, la cua calculada, i sobretot l'índex únic parcial enfront de la restricció EXCLUDE — la primera si només importa el present, la segona si també cal impedir els solapaments històrics.
  • Les 15 consultes resoltes, cadascuna amb la seva decisió clau: el desempat de l'ORDER BY (RC-01, RC-13), els dos exemplars del mateix títol que no són un duplicat (RC-02), el GREATEST(..., 0) del retard (RC-03), l'anti-join que no és NOT IN (RC-04), el HAVING que no és WHERE (RC-05), el COUNT(e.id) que no és COUNT(*) (RC-06, RC-09), l'ORDER BY dins del string_agg (RC-07), el COALESCE que converteix el NULL en 0.00 (RC-08), la posició calculada amb ROW_NUMBER (RC-10), el LATERAL que veu la fila de fora (RC-11), el calendari que fa visibles els mesos buits (RC-12), la ruta que ordena l'organigrama (RC-14) i el pivot que quadra per files i per columnes (RC-15).
  • Set índexs amb la consulta que justifica cadascun i la llista del que es decideix no indexar; més una vista, un procediment i un disparador, i ni un més: la vista que defineix "vençut" i anomena multa_estimada el que encara no és multa, el procediment idempotent i atòmic, i l'únic disparador que expressa una regla que cap CHECK no pot. I els errors freqüents del projecte, encapçalats pel que l'anul·la —confondre obra amb exemplar—, amb el recordatori que una solució diferent pot ser igual de vàlida si compleix els requisits i està justificada.

Falta l'última part, i és la que decideix com es valora tot l'anterior. A la lliçó següent, Presentació del projecte, veuràs com es comunica un treball tècnic: l'estructura de l'informe apartat per apartat, com es presenten resultats de dades sense enganyar de bona fe, les preguntes que et faran a la defensa i com preparar-les, com es publica el projecte en un repositori que algú pugui executar en cinc minuts, l'autoavaluació amb la rúbrica convertida en checklist — i el tancament del curs sencer.

◄ Anterior
◄
Següent ►
►

Curs de SQL

Mòdul 1: Introducció a SQL

Mòdul 2: Consultes bàsiques de SQL

Mòdul 3: Treballar amb múltiples taules

Mòdul 4: Filtratge avançat de dades

Mòdul 5: Manipulació de dades

Mòdul 6: Funcions avançades de SQL

Mòdul 7: Subconsultes i consultes imbricades

Mòdul 8: Índexs i optimització del rendiment

Mòdul 9: Transaccions i concurrència

Mòdul 10: Temes avançats

Mòdul 11: SQL a la pràctica

Mòdul 12: Projecte final

El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2026. Tots els drets reservats