Les set taules de BiblioRed existeixen i són buides. Aquesta lliçó les omple i ensenya a interrogar-les. És la lliçó més pràctica del mòdul: aquí aprens les quatre operacions que cobreixen el 90 % de la feina diària amb una base de dades —inserir, consultar, modificar i esborrar—, conegudes col·lectivament com a CRUD (Create, Read, Update, Delete).

Ens limitem deliberadament a una sola taula per consulta. Recompondre informació repartida entre diverses taules és un tema amb prou substància per a la seva pròpia lliçó (02-04), i agrupar i resumir, per a la següent (02-05). Aquí construïm els fonaments: sense dominar WHERE i ORDER BY sobre una taula, cap JOIN no sortirà bé.

El lliurable de la lliçó és el joc de dades de prova de BiblioRed: quatre sucursals, vuit autors, deu socis, nou llibres, quinze exemplars, dotze préstecs i cinc reserves. Tots ficticis i tots coherents entre si. Els farem servir fins al final del curs, així que executa'ls amb atenció i guarda'ls en un fitxer.

1. INSERT: donar d'alta files
2. Lliurable: el joc de dades de BiblioRed
3. Reajustar els generadors d'identificadors
4. SELECT: projecció, àlies i DISTINCT
5. WHERE: filtrar files
6. ORDER BY: ordenar el resultat
7. LIMIT i OFFSET: paginació
8. UPDATE: modificar files existents
9. DELETE: eliminar files
10. TRUNCATE: buidar una taula sencera
11. Errors habituals i consells
12. Exercicis
13. Conclusió

Comencem per les sucursals de BiblioRed. La xarxa opera a la ciutat fictícia de Vallmar i té quatre biblioteques.

La sortida de PostgreSQL es llegeix així: INSERT <oid> <nombre de files inserides>. El primer número és un vestigi històric i sempre val 0; el segon és el que importa.

Fixa't en tres coses:

• No hem donat sucursal_id. La columna és GENERATED BY DEFAULT AS IDENTITY, així que PostgreSQL li assigna l'1.
• La data va entre cometes simples, en format ISO AAAA-MM-DD. El gestor la converteix a DATE perquè la columna és d'aquest tipus.
• El telèfon va entre cometes, encara que sembli un número: és VARCHAR, com vam decidir a la lliçó anterior.

SQL permet ometre la llista de columnes si dónes un valor per a totes, en l'ordre exacte de la definició de la taula:

Per què evitar-ho? Perquè el dia que algú afegeixi una columna amb ALTER TABLE, o reordeni la definició, tots els INSERT sense llista de columnes es trencaran o, pitjor, inseriran valors a la columna equivocada sense donar error. Escriu sempre la llista de columnes. És la primera regla d'higiene del SQL professional.

Fem-ho bé:

Aquesta és la sucursal Nord, que a partir d'ara tindrà sucursal_id = 2.

Es poden encadenar diverses tuples de valors separades per comes. És més ràpid (una sola operació en lloc de N) i més llegible:

La sucursal Est encara no té telèfon propi: escrivim NULL sense cometes. Si escrivíssim 'NULL' estaríem guardant la cadena de quatre lletres N-U-L-L, que no és el mateix en absolut.

Una altra opció és ometre la columna de la llista: el que no s'esmenta queda a NULL (o al seu valor per defecte, si en tingués).

Quan deixes que el gestor generi la clau, sorgeix un problema pràctic immediat: quin número li ha tocat? El necessitaràs per inserir les files filles. PostgreSQL ho resol amb RETURNING:

RETURNING funciona igual amb UPDATE i DELETE, i pot retornar qualsevol expressió, inclòs *. És una extensió de PostgreSQL molt còmoda que evita el clàssic "insereixo i després consulto".

SQLite no té RETURNING fins a la versió 3.35 (2021); en versions anteriors es fa servir la funció last_insert_rowid():

Abans d'acceptar la fila, el gestor verifica les tres regles d'integritat de la lliçó 02-01:

Això és exactament el que el full de càlcul de BiblioRed no feia. Aquí l'error no és una molèstia: és el sistema fent la seva feina.

Aquest és l'script de càrrega. Executa'l sencer i en aquest ordre (les filles necessiten les pares) i guarda'l com a dades_biblioredb.sql.

Un matís sobre els identificadors que hi veuràs:

• A sucursals, autors, exemplars, prestecs i reserves deixem que el gestor els generi.
• A socis i llibres els imposem: BiblioRed està migrant des del full de càlcul i vol conservar els números de carnet (Marta Alsina és la sòcia 14 des del 2021) i les signatures del catàleg antic (331 és "El mapa del temps"). És un cas realíssim, i a l'apartat 3 veurem l'efecte secundari que provoca.

(COUNT i UNION ALL són de les lliçons 02-05 i 02-04; aquí només ho fem servir com a recompte de control.)

I la correspondència entre codis i identificadors d'exemplar, que necessitaràs per entendre els préstecs:

Si els teus recomptes coincideixen, tens el mateix joc de dades que la resta del curs.

Tres canvis a l'script:

• TRUE/FALSE a socis.actiu → 1/0.
• Les línies ALTER TABLE ... RESTART WITH del pas 0 no existeixen a SQLite: elimina-les. Amb INTEGER PRIMARY KEY sense AUTOINCREMENT, el següent identificador es calcula sol a partir del màxim existent.
• Les dates s'escriuen igual ('2026-03-02'), però es guarden com a text. Mentre facis servir el format ISO, les comparacions i ordenacions continuaran funcionant, perquè l'ordre alfabètic d'AAAA-MM-DD coincideix amb el cronològic. És exactament la raó per la qual aquest format és el bo.

Aquí arriba l'efecte secundari promès. A socis i llibres vam inserir identificadors explícits, però el generador de la columna no se n'ha assabentat: continua apuntant a l'1. Comprovem-ho donant d'alta un soci sense indicar-ne l'identificador:

El nou soci ha rebut l'1, quan els carnets de BiblioRed comencen a l'11. No ha fallat res, però el desajust ja està sembrat: els deu següents que es donin d'alta s'endurien el 2, el 3… i l'onzè xocaria:

És un dels errors més desconcertants per a qui comença, perquè apareix setmanes després de la càrrega i sense relació aparent amb ella. La causa sempre és la mateixa: s'han inserit claus a mà sense resincronitzar el generador. Esborrem el soci de prova i arreglem-ho:

La solució a PostgreSQL:

O, de manera automàtica i sense haver de mirar el màxim a ull:

A SQLite el problema no existeix si la clau es va declarar com a INTEGER PRIMARY KEY sense AUTOINCREMENT: el valor següent es calcula com el màxim actual més un, així que s'ajusta sol.

SELECT és la instrucció més utilitzada de SQL i la que més lliçons ocupa en aquest curs. La seva forma mínima:

Això és la projecció π de l'àlgebra relacional. Recorda que l'ordre en què apareixen les files no està garantit sense ORDER BY: aquí surten en ordre d'inserció perquè la taula és petita i acabada de carregar, però no hi comptis.

* significa "totes les columnes". Va perfecte per explorar a la consola, però no el facis servir en codi d'aplicació: portes dades que no necessites per la xarxa, i si demà algú afegeix una columna, el teu programa rep alguna cosa que no esperava. En un script guardat, columnes explícites.

Observa que el NULL del telèfon d'Est apareix com a cel·la buida a psql. El pots fer visible:

Un àlies reanomena una columna al resultat, sense tocar la taula. És l'operador de reanomenament ρ de l'àlgebra relacional.

Punts a retenir:

• AS és opcional (codi etiqueta funciona igual), però escriure'l fa el SQL molt més llegible.
• Perquè un àlies porti espais, majúscules o accents cal posar-lo entre cometes dobles: és un identificador. Aquí sí que és acceptable, perquè l'àlies només viu a la sortida.

També es poden calcular columnes noves:

|| és l'operador estàndard de concatenació (funciona a PostgreSQL i SQLite; MySQL fa servir CONCAT()).

Compte amb NULL a les concatenacions: 'Pau' || NULL dóna NULL, no 'Pau'. Si cognoms pogués ser nul, el nom complet desapareixeria sencer. A 02-05 veurem COALESCE, que resol exactament això.

Retorna 15 files amb moltes repeticions. Amb DISTINCT:

DISTINCT s'aplica a la combinació completa de columnes seleccionades, no a la primera:

Són les vuit combinacions diferents que existeixen, de les 15 files originals.

Recorda de la lliçó 02-01: a l'àlgebra relacional la projecció sempre elimina duplicats; en SQL cal demanar-ho. DISTINCT obliga el gestor a ordenar o a construir una taula de dispersió, així que té cost: no el posis "per si de cas".

WHERE és la selecció σ de l'àlgebra: es queda amb les files la condició de les quals sigui CERTA (recorda: DESCONEGUT no passa).

Les comparacions funcionen també sobre text (ordre alfabètic segons la configuració regional) i sobre dates:

AND té més precedència que OR, igual que la multiplicació sobre la suma. Això provoca errors silenciosos:

Retorna 7 files: els sis de la sucursal 1 més EJ-3081. Amb parèntesis:

Consell: fes servir parèntesis sempre que barregis AND i OR, encara que sàpigues la precedència. Qui llegeixi la teva consulta d'aquí a un any t'ho agrairà.

BETWEEN a AND b és sucre sintàctic de >= a AND <= b: tots dos extrems hi són inclosos. És la font d'un error clàssic amb dates i hores: BETWEEN '2026-03-01' AND '2026-03-31' sobre una columna TIMESTAMP deixa fora tot el que va passar el dia 31 després de mitjanit, perquè 2026-03-31 09:00 és més gran que 2026-03-31 00:00. Amb columnes DATE com les de BiblioRed no hi ha problema.

IN equival a una cadena d'OR, però és molt més llegible. També existeix NOT IN:

Mateix resultat que abans.

Avís important sobre NOT IN i NULL: si la llista conté un NULL, NOT IN no retorna cap fila, per la lògica de tres valors de la lliçó 02-01. x NOT IN (1, 2, NULL) equival a x <> 1 AND x <> 2 AND x <> NULL, i aquest últim terme és sempre DESCONEGUT. Amb llistes escrites a mà no passa; amb llistes que vénen d'una subconsulta (lliçó 02-04) és un parany habitual.

Dos comodins:

LIKE distingeix majúscules i minúscules a PostgreSQL:

Cap títol no comença per el en minúscula. Per ignorar la caixa, PostgreSQL ofereix ILIKE (la I és d'insensitive), que no és estàndard però és comodíssim:

Alternativa portable, vàlida també a SQLite:

A SQLite, LIKE ja és insensible a majúscules per als caràcters ASCII (però no per a les vocals accentuades), i no existeix ILIKE. És una de les diferències que més sorprenen en portar consultes.

Exemple amb _:

Concretament retorna EJ-3081 a EJ-3089: nou files, perquè EJ-3080 no existeix.

Aquí tornem al parany que vam anunciar a 02-01.

I el seu complementari, que a BiblioRed té un significat molt concret:

Quatre préstecs oberts, que coincideixen exactament amb els quatre exemplars en estat prestat. La base de dades és coherent.

I una comprovació de l'altre parany dels NULL, el de les desigualtats:

Cap de les dues consultes no veu els NULL. Si vols els préstecs "sense recàrrec pendent", ho has de dir: WHERE recarrec = 0 OR recarrec IS NULL.

Com que una relació és un conjunt i no té ordre, ORDER BY és l'única manera de garantir-lo.

ASC (ascendent) és el valor per defecte; DESC inverteix.

S'ordena pel primer i, dins dels empats, pel segon:

Cada criteri porta el seu propi ASC/DESC: ORDER BY sucursal_id ASC, data_alta DESC és perfectament vàlid.

On va un NULL en ordenar? L'estàndard deixa llibertat, i PostgreSQL els col·loca al final en ASC i al principi en DESC (equival a tractar-los com el valor més gran). SQLite fa el contrari: els posa al principi en ASC.

Com que no volem dependre del gestor, s'especifica:

Fixa't en el segon criteri, prestec_id: sense ell, l'ordre entre els quatre NULL seria arbitrari. Quan l'ordre importi de veritat, acaba sempre amb un criteri que desempati sense ambigüitat, típicament la clau primària.

NULLS FIRST/NULLS LAST és sintaxi estàndard i funciona a PostgreSQL; SQLite l'admet des de la versió 3.30.

Ordenar per àlies és legítim i llegible. Ordenar per número de posició funciona, però és fràgil: si algú reordena la llista del SELECT, la consulta canvia de sentit en silenci. Evita-ho.

ORDER BY titol situa "Àlgebra per a impacients" en primer lloc si la base de dades fa servir una configuració regional catalana, perquè À s'ordena al costat de la A. Amb la configuració C (byte a byte), À aniria després de la Z. No és una errada: és la col·lació. Pots veure la teva amb SHOW lc_collate; a PostgreSQL.

OFFSET salta files abans de començar a comptar:

Aquesta és la mecànica de la paginació de qualsevol catàleg web: la pàgina N s'obté amb LIMIT mida OFFSET (N-1) * mida.

Dos advertiments:

1. LIMIT sense ORDER BY no té sentit. "Dóna'm 3 files de les 9" sense dir quines significa "dóna'm 3 de qualssevol", i poden ser diferents a cada execució. Encara pitjor: si pagines sense ordenar, la mateixa fila pot sortir a la pàgina 1 i a la 3, i una altra no sortir mai.
2. Un OFFSET gran és lent. Per arribar a la fila 100.000 el gestor ha de produir i descartar les 100.000 anteriors. En catàlegs grans es fan servir tècniques de paginació per clau; és tema de rendiment (lliçó 06-03).

La sintaxi estàndard és OFFSET 3 ROWS FETCH FIRST 3 ROWS ONLY, més verbosa i menys utilitzada. LIMIT/OFFSET funciona a PostgreSQL, SQLite i MySQL.

Abans de començar: els exemples d'aquest apartat i del següent modifiquen el joc de dades que acabem de carregar, i les lliçons 02-04 i 02-05 el donen per bo. Al final de cada exemple hi incloem la instrucció que desfà el canvi. Executa-la.

Abans de qualsevol UPDATE o DELETE, executa la mateixa condició amb un SELECT. Si el SELECT retorna les files que esperaves, la modificació també les tocarà a elles.

UPDATE 1 confirma que s'ha tocat exactament una fila. Si veus un número més gran de l'esperat, alguna cosa ha anat malament, i a PostgreSQL, si ets dins d'una transacció, encara hi ets a temps (lliçó 06-01).

Un UPDATE pot calcular el valor nou a partir de l'actual:

Important: recarrec + 0.50 sobre un NULL dóna NULL. Si haguéssim executat aquest UPDATE sense WHERE, els quatre préstecs oberts haurien passat de NULL a… NULL, i els altres vuit haurien pujat de preu. Silenciosament.

Quan la Marta Alsina retorna EJ-3081, cal tocar dues taules:

Això planteja una pregunta incòmoda: què passa si la primera instrucció funciona i la segona falla? La base quedaria en un estat inconsistent: un préstec tancat i un exemplar marcat com a prestat. La resposta és la transacció, i és el contingut de la lliçó 06-01. De moment, desfem:

I a més violaria uq_socis_email, així que en aquest cas concret la restricció ens salvaria. No sempre hi haurà una restricció que et salvi. Un UPDATE exemplars SET estat = 'baixa'; hauria donat de baixa els 40.000 exemplars de BiblioRed sense protestar.

Costums que eviten el desastre:

1. Escriure el WHERE abans que el SET. Comença teclejant UPDATE taula WHERE ... i després torna a inserir-hi el SET. Sona estrany, funciona.
2. Provar amb SELECT primer. Sempre.
3. Treballar dins d'una transacció en les operacions delicades (06-01).
4. A psql, activar \set ON_ERROR_STOP on als scripts perquè s'aturin al primer error.

Crearem una fila d'usar i llençar per no espatllar el joc de dades:

A PostgreSQL pots fer servir RETURNING també aquí, per deixar constància del que s'ha endut per davant:

Intenta esborrar un soci que té préstecs:

El gestor t'està protegint. Si permetés l'esborrament, els tres préstecs de la Marta Alsina quedarien orfes: apuntant a un soci que ja no existeix. Aquest comportament —i les seves alternatives, com l'esborrament en cascada— és el tema íntegre de la lliçó 02-06.

A SQLite, si vas oblidar PRAGMA foreign_keys = ON, aquest DELETE funcionarà i et deixarà tres files òrfenes sense dir res. És exactament l'escenari que la lliçó 02-06 t'ensenyarà a detectar i netejar.

Sense WHERE, DELETE buida la taula sencera, sense preguntar i sense paperera. Si et passa fora d'una transacció, l'única sortida és la còpia de seguretat (lliçó 06-04). Els quatre costums de l'apartat anterior valen aquí amb més raó encara.

Quan el que vols de veritat és buidar una taula completa, existeix una instrucció específica:

SQLite no té TRUNCATE; fes servir DELETE FROM taula sense WHERE, que internament optimitza.

No executis cap TRUNCATE sobre el teu biblioredb: perdries el joc de dades que acabes de carregar. Si ja ho has fet, torna a executar l'script de l'apartat 2.

• UPDATE o DELETE sense WHERE. L'error més car que es comet amb SQL. Prova sempre la condició amb un SELECT abans.
• Escriure 'NULL' en lloc de NULL. Amb cometes és una cadena de text de quatre lletres; sense cometes, l'absència de valor. Una columna que barreja totes dues coses és una columna arruïnada.
• Fer servir = NULL o <> NULL. Zero files, sense error. Sempre IS NULL / IS NOT NULL.
• Oblidar que <> valor exclou els NULL. WHERE recarrec <> 0 no retorna els préstecs amb recàrrec nul. Si els vols, afegeix-hi OR recarrec IS NULL.
• Barrejar AND i OR sense parèntesis. AND guanya. Posa-hi parèntesis sempre.
• INSERT sense llista de columnes. Es trenca tan bon punt algú toca l'esquema, de vegades en silenci.
• Inserir identificadors explícits i no resincronitzar la seqüència. Provoca errors de clau duplicada molt després, quan ja ningú no recorda la càrrega inicial.
• LIMIT sense ORDER BY. El resultat no és reproduïble i la paginació pot repetir o ometre files.
• Refiar-se que LIKE distingeix majúscules. A PostgreSQL sí, a SQLite (ASCII) no, a MySQL depèn de la col·lació. Si necessites certesa, LOWER(columna) LIKE ....
• Consell: a psql, \pset null '(nul)' fa visibles els NULL i \x on mostra els resultats en vertical, ideal per a files amples.
• Consell: guarda tot el SQL en fitxers (esquema_biblioredb.sql, dades_biblioredb.sql) i executa'ls amb \i fitxer.sql a psql o .read fitxer.sql a sqlite3. Poder reconstruir la base en deu segons et donarà llibertat per experimentar sense por.

Tots es resolen sobre una sola taula. Escriu la consulta abans de mirar la solució i compara els resultats.

1. Els títols i editorials dels llibres publicats abans del 2010, del més antic al més recent.
2. Els llibres sense ISBN registrat.
3. Les editorials diferents del fons, en ordre alfabètic.
4. Els títols que contenen la paraula "mar" en qualsevol posició, sense distingir majúscules.
5. Els codis dels exemplars de la sucursal 2 que no estiguin disponibles.
6. Els tres llibres més recents del fons.

1. Els socis donats d'alta el 2021 o el 2022, amb nom i cognoms en una sola columna anomenada soci.
2. Els socis que no estan actius.
3. Els préstecs retornats amb retard (la data real de devolució és posterior a la prevista), ordenats per dies de retard… o, si encara no saps calcular la diferència, simplement per data de préstec.
4. Els préstecs oberts la devolució dels quals estava prevista abans del 10 d'agost de 2026.
5. La segona pàgina d'un llistat de préstecs ordenat per data de préstec descendent, amb 5 préstecs per pàgina.
6. Els préstecs el recàrrec dels quals no és zero, inclosos aquells en què el recàrrec encara no s'ha calculat.

Escriu les instruccions per a cada operació, precedides del SELECT de comprovació i seguides de la instrucció que desfà el canvi.

1. L'exemplar EJ-3088 surt del taller: passa'l a disponible.
2. Corregeix l'editorial del llibre 337: passa de "Edicions Marlia" a "Edicions Marlia SL".
3. Dóna d'alta una sòcia nova, Berta Colomer ([email protected]), a la sucursal Sud, amb data d'avui (fes servir '2026-08-02'), activa, deixant que el gestor li assigni l'identificador. Després, esborra-la.
4. La reserva 4 estava cancel·lada per error: reactiva-la.

Solució 1

Observa que "El mapa" hi entra per mapa i "les marees" per marees: LIKE busca subcadenes, no paraules senceres.

Solució 2

Cal notar que la condició compara dues columnes de la mateixa fila, cosa perfectament legítima. I que els quatre préstecs oberts no hi apareixen: NULL > data és DESCONEGUT. A PostgreSQL, data_devolucio - data_devolucio_prevista donaria directament els dies de retard (7, 7 i 21).

(El segon criteri prestec_id DESC garanteix que la paginació sigui estable encara que hi hagués dates repetides.)

Sense l'OR recarrec IS NULL hauries obtingut només tres files i hauries perdut de vista els quatre préstecs en curs.

Solució 3

Comprovació final que tot ha quedat com estava:

Aquesta lliçó ha portat BiblioRed d'un esquema buit a una base de dades viva i consultable:

• INSERT amb llista de columnes explícita (sempre), en una o diverses files, amb NULL sense cometes i amb RETURNING a PostgreSQL per conèixer els identificadors generats. I la comprovació automàtica d'unicitat i de claus foranes que el full de càlcul mai no va tenir.
• El joc de dades de BiblioRed: 4 sucursals, 8 autors, 10 socis, 9 llibres, 15 exemplars, 12 préstecs i 5 reserves, tot fictici i coherent. És el material de treball de la resta del curs.
• L'efecte secundari d'inserir claus a mà i com resincronitzar el generador amb RESTART WITH o setval.
• SELECT amb projecció de columnes, àlies AS (amb cometes dobles quan porten espais), concatenació amb || i DISTINCT aplicat a la combinació completa de columnes.
• WHERE amb comparacions, AND/OR/NOT i la seva precedència, BETWEEN (extrems inclosos), IN i NOT IN (amb el seu parany amb NULL), LIKE/ILIKE amb % i _, i IS NULL, que és l'única manera de preguntar per l'absència.
• ORDER BY amb diversos criteris, ASC/DESC, NULLS FIRST/NULLS LAST i la importància d'un criteri de desempat.
• LIMIT/OFFSET per paginar, sempre amb ORDER BY.
• UPDATE i DELETE amb la disciplina del SELECT previ, la lectura del nombre de files afectades i el respecte que imposa el WHERE oblidat; més TRUNCATE com a buidatge ràpid de nivell DDL.

Fins aquí, cada consulta ha mirat una sola taula, i això deixa preguntes sense resposta: no sabem qui té EJ-3081, ni quin títol és el més prestat, ni quins socis no han agafat mai res. Les dades estan repartides a propòsit —aquesta és l'essència del model relacional— i ara toca recompondre-les.

A la lliçó 02-04, Consultes Multitaula: JOIN i Subconsultes, aprendràs a unir socis amb prestecs, prestecs amb exemplars, exemplars amb llibres i llibres amb autors, tot en una sola consulta; a fer servir LEFT JOIN per trobar el que no té correspondència; i a niar consultes dins de consultes. És on SQL comença a resultar realment potent.