UD07 · Consultes avançades: composicions, resums i subconsultes

Resum del tema

A la UD06 vas aprendre a consultar una taula. Però una base de dades ben dissenyada reparteix la informació entre moltes taules: el nom de l’alumne està a ALUMNO, la seua nota a MATRICULA i el nom del mòdul a MODULO. Quasi cap pregunta real del centre es respon amb una sola taula. Esta unitat ensenya a tornar a juntar el que el disseny va separar (composicions o joins), a resumir moltes files en poques (funcions d’agregat i GROUP BY) i a encadenar consultes unes dins d’altres (subconsultes).

El recorregut és acumulatiu. Primer es resumix una sola taula, perquè agrupar és més fàcil d’entendre sense composicions pel mig. Després es combinen taules, primer de forma interna (només les files que emparellen) i després externa (conservant també les que no emparellen: el grup sense tutor, el cicle sense grups). A continuació s’usen subconsultes per a preguntes que necessiten dos passos («qui està per damunt de la mitjana del seu mòdul»). Finalment es guarden les consultes útils com a vistes, es combinen resultats amb UNION, INTERSECT i MINUS, i s’introduïxen les funcions analítiques i l’optimització amb el pla d’execució.

Esta és la unitat que converteix l’SQL en una eina professional: en acabar-la seràs capaç de contestar pràcticament qualsevol pregunta que secretaria, cap d’estudis o l’equip docent li puguen fer a EduGest. A la UD08 reutilitzaràs quasi tot el d’ací, perquè UPDATE i DELETE admeten subconsultes, i a la UD09 encapsularàs estes consultes en procediments i vistes més elaborades.

Tots els exemples s’executen sobre l’esquema de referència EDUGEST amb les dades del curs 2025-26 carregades amb els scripts del projecte: 5 departaments, 12 professors, 4 cicles, 24 mòduls, 6 grups, 32 alumnes, 143 matrícules, 24 assignacions docents i 46 faltes d’assistència.

Objectius d’aprenentatge

En acabar esta unitat seràs capaç de:

• Resumir el contingut d’una taula amb funcions d’agregat, raonant com afecten els valors nuls.
• Formar grups amb GROUP BY i filtrar-los amb HAVING, distingint el seu paper del de WHERE.
• Combinar diverses taules amb composicions internes seguint el camí de les claus alienes.
• Decidir quan una pregunta exigix una composició externa i escriure-la sense anul·lar-la amb un filtre mal col·locat.
• Resoldre amb autocomposicions les preguntes en què una taula es relaciona amb si mateixa.
• Escriure subconsultes d’una fila, de diverses files, correlacionades i amb EXISTS, i substituir-les per una clàusula WITH quan millore la llegibilitat.
• Crear vistes que encapsulen composicions i valorar quan són actualitzables.
• Combinar resultats de diverses consultes amb UNION, UNION ALL, INTERSECT i MINUS.
• Utilitzar funcions analítiques per a comparar cada fila amb el seu grup sense perdre el detall.
• Obtindre i interpretar el pla d’execució d’una consulta i aplicar criteris d’optimització.

Temporalització

La unitat ocupa 16 hores d’aula (10 de teoria i 6 de pràctica). Les composicions i les subconsultes són el nucli del RA3: convé no avançar fins que cada consulta es puga escriure sense consultar els apunts.

Estudia esta unitat amb la base de dades oberta i amb un mètode fix per a cada consulta:

1. Escriu en castellà la pregunta que vols respondre.
2. Decidix quines taules la contenen i per quines claus s’enllacen.
3. Prediu quantes files ha de tornar i de quin tipus.
4. Executa i compara amb la teua predicció. Si no coincidix, l’error està en el teu model mental, no en Oracle: esbrina on.

Si una consulta torna moltes més files de les esperades, quasi sempre falta una condició de composició. Si en torna menys, quasi sempre sobra un filtre o falta una composició externa.

1. Funcions d’agregat

1.1 Quin problema resolen

Les funcions de fila de la UD06 (UPPER, ROUND, TO_CHAR…) tornen un valor per cada fila: entren 143 files i en isen 143. Però moltes preguntes no demanen files, demanen un número que resumisca les files: «quantes matrícules hi ha?», «quina és la nota mitjana?», «quina és la nota més alta?».

Per a això estan les funcions d’agregat (també anomenades funcions de grup o de columna): reben un conjunt de files i tornen un únic valor.

flowchart LR
    A[143 files de MATRICULA] -->|AVG nota_final| B[1 fila: 6,36]

COUNT, SUM, AVG, MIN i MAX són SQL estàndard i existixen en tots els SGBD. STDDEV, VARIANCE, MEDIAN i LISTAGG són extensions d’Oracle (algunes estan també en PostgreSQL amb un altre nom). MEDIAN i LISTAGG es tracten com a ampliació: l’avaluable del RA3.e són les cinc primeres.

1.2 Exemple senzill: comptar

SELECT COUNT(*)                    AS matriculas,
       COUNT(nota_final)           AS calificadas,
       COUNT(*) - COUNT(nota_final) AS sin_calificar,
       COUNT(DISTINCT id_alumno)   AS alumnos_distintos,
       COUNT(DISTINCT id_modulo)   AS modulos_distintos
FROM   matricula;

1 fila

Element a element:

Totes les funcions d’agregat excepte COUNT(*) ignoren els nuls. No els compten, no els sumen i no els tenen en compte en la mitjana. Esta és la causa número u de resultats «rars» en les consultes resum, i la raó per la qual COUNT(*) i COUNT(columna) donen números distints.

1.3 Exemple aplicat: estadístics de les notes

SELECT COUNT(nota_final)            AS calificadas,
       ROUND(AVG(nota_final), 2)    AS media,
       MIN(nota_final)              AS minima,
       MAX(nota_final)              AS maxima,
       SUM(nota_final)              AS suma,
       ROUND(SUM(nota_final) / COUNT(*), 2) AS media_incorrecta
FROM   matricula;

1 fila

L’última columna és deliberadament errònia i convé entendre-la bé:

• AVG(nota_final) calcula 871 / 137 = 6,36: dividix entre les matrícules qualificades.
• SUM(nota_final) / COUNT(*) calcula 871 / 143 = 6,09: dividix entre totes les matrícules, incloses les sis sense nota, com si foren zeros.

Les dos xifres responen a preguntes distintes i cap és més correcta que l’altra en abstracte: depén del que et demanen. «Nota mitjana dels alumnes qualificats» és 6,36. «Nota mitjana considerant els no presentats com un zero» és 6,09, i llavors convé escriure-ho explícitament amb AVG(NVL(nota_final, 0)) perquè el lector veja la decisió. El que mai ha de passar és prendre una per l’altra sense adonar-se’n.

1.4 MIN i MAX no són només per a números

MIN i MAX funcionen amb text (ordre alfabètic segons NLS_SORT) i amb dates, on MIN és la més antiga:

SELECT MIN(fecha_nacimiento) AS mayor_edad,
       MAX(fecha_nacimiento) AS menor_edad,
       MIN(apellidos)        AS primero_alfabetico
FROM   alumno;

1 fila

1.5 Comptar condicions: SUM amb CASE

Una tècnica molt habitual en informes és comptar quantes files complixen una condició dins del mateix resum, en compte de llançar una consulta per cada condició:

SELECT COUNT(*)                                            AS matriculas,
       SUM(CASE WHEN nota_final >= 5 THEN 1 ELSE 0 END)     AS aprobadas,
       SUM(CASE WHEN nota_final <  5 THEN 1 ELSE 0 END)     AS suspensas,
       COUNT(*) - COUNT(nota_final)                         AS sin_calificar,
       ROUND(100 * SUM(CASE WHEN nota_final >= 5 THEN 1 ELSE 0 END)
             / COUNT(nota_final), 1)                        AS pct_aprobados
FROM   matricula;

1 fila

Fixa’t en el denominador del percentatge: COUNT(nota_final), no COUNT(*). El percentatge d’aprovats es calcula sobre els qualificats.

En lloc de SUM(CASE WHEN … THEN 1 ELSE 0 END) també funciona COUNT(CASE WHEN … THEN 1 END): com que COUNT ignora els nuls i el CASE sense ELSE torna NULL quan no es complix, l’efecte és el mateix. Usa la forma que et resulte més llegible, però sigues coherent dins d’un mateix informe.

1.6 Errors habituals amb els agregats

-- (a) Barrejar una columna normal amb un agregat sense GROUP BY
SELECT localidad, COUNT(*) FROM alumno;
-- ORA-00937: not a single-group group function

-- (b) Usar un agregat al WHERE
SELECT localidad FROM alumno WHERE COUNT(*) > 3 GROUP BY localidad;
-- ORA-00934: group function is not allowed here

-- (c) Aniuar agregats sense GROUP BY intermedi
SELECT MAX(AVG(nota_final)) FROM matricula;
-- ORA-00978: nested group function without GROUP BY

L’error (a) és el més freqüent: si demanes una columna normal junt amb un agregat, Oracle no sap quina localitat posar al costat d’un recompte de 32 alumnes. La solució és el GROUP BY de l’apartat següent.

2. Agrupament: GROUP BY i HAVING

2.1 Com es formen els grups

GROUP BY dividix les files en grups que compartixen el mateix valor en les columnes indicades, i després aplica les funcions d’agregat a cada grup per separat. El resultat té una fila per grup.

SELECT cod_grupo, COUNT(*) AS alumnos
FROM   alumno
GROUP  BY cod_grupo
ORDER  BY cod_grupo;

6 files

Observacions importants:

• Hi ha sis files perquè hi ha cinc valors distints de cod_grupo més el grup dels nuls.
• GROUP BY reunix tots els nuls en un únic grup (al contrari que l’operador =, que mai considera iguals dos nuls). Eixos són els 3 alumnes sense grup assignat.
• La suma dels recomptes és 32: cap fila es perd i cap es compta dos voltes.
• Els NULL apareixen al final perquè ORDER BY … ASC els col·loca al final en Oracle.

GROUP BY no garantix l’ordre del resultat, encara que a vegades ho semble. Si l’ordre importa, escriu ORDER BY. És un error clàssic confiar que els grups isquen ordenats.

2.2 Què pot aparéixer al SELECT

Esta és la regla que més errors provoca del tema:

En una consulta amb GROUP BY, el SELECT només pot contindre:

1. les columnes o expressions que apareixen al GROUP BY,
2. funcions d’agregat,
3. constants i expressions calculades a partir d'1 i 2.

Qualsevol altra columna provoca ORA-00979: not a GROUP BY expression.

-- Incorrecta: nombre no està al GROUP BY i no és un agregat
SELECT localidad, nombre, COUNT(*)
FROM   alumno
GROUP  BY localidad;
-- ORA-00979: not a GROUP BY expression

La consulta no té sentit: el grup «Alacant» conté 13 alumnes amb 13 noms distints; quin hauria de mostrar? Tens tres eixides possibles, segons el que realment vulgues:

SELECT localidad,
       COUNT(*) AS alumnos,
       MIN(apellidos) AS primero,
       LISTAGG(nombre, ', ') WITHIN GROUP (ORDER BY nombre) AS nombres
FROM   alumno
WHERE  localidad IN ('Elche', 'El Campello')
GROUP  BY localidad
ORDER  BY localidad;

2 files

2.3 Ordre d’avaluació: WHERE enfront de HAVING

Ja coneixes l’ordre lògic de les clàusules; ara es completa amb GROUP BY i HAVING:

FROM  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY
  1        2          3            4         5           6

flowchart LR
    F["1 · FROM<br/>143 files"] --> W["2 · WHERE<br/>filtra FILES"]
    W --> G["3 · GROUP BY<br/>forma grups"]
    G --> H["4 · HAVING<br/>filtra GRUPS"]
    H --> S["5 · SELECT<br/>calcula columnes"]
    S --> O["6 · ORDER BY<br/>ordena"]

D’este ordre es deriven totes les regles pràctiques:

Les dos clàusules es combinen amb freqüència, i cadascuna fa una feina distinta:

-- Mòduls amb 3 suspesos o més: qui està suspés es decidix fila a fila
-- (WHERE); quants suspesos té el grup es decidix després (HAVING).
SELECT id_modulo,
       COUNT(*)                  AS suspensos,
       ROUND(AVG(nota_final), 2) AS media_de_los_suspensos
FROM   matricula
WHERE  nota_final < 5
GROUP  BY id_modulo
HAVING COUNT(*) >= 3
ORDER  BY suspensos DESC, id_modulo;

2 files

2.4 Agrupar per diverses columnes i per expressions

El GROUP BY pot contindre diverses columnes: llavors es forma un grup per cada combinació de valors present en les dades (mai s’inventen combinacions que no existisquen).

SELECT curso_academico, convocatoria,
       COUNT(*)                  AS matriculas,
       COUNT(nota_final)         AS calificadas,
       ROUND(AVG(nota_final), 2) AS media
FROM   matricula
GROUP  BY curso_academico, convocatoria
ORDER  BY curso_academico, convocatoria;

2 files

També es pot agrupar per una expressió, sempre que la mateixa expressió aparega al GROUP BY:

SELECT CASE WHEN nota_final IS NULL THEN 'Sin calificar'
            WHEN nota_final < 5     THEN 'Insuficiente'
            WHEN nota_final < 7     THEN 'Suficiente / Bien'
            WHEN nota_final < 9     THEN 'Notable'
            ELSE                         'Sobresaliente'
       END      AS tramo,
       COUNT(*) AS matriculas
FROM   matricula
GROUP  BY CASE WHEN nota_final IS NULL THEN 'Sin calificar'
               WHEN nota_final < 5     THEN 'Insuficiente'
               WHEN nota_final < 7     THEN 'Suficiente / Bien'
               WHEN nota_final < 9     THEN 'Notable'
               ELSE                         'Sobresaliente'
          END
ORDER  BY matriculas DESC;

5 files

Repetir el CASE complet és incòmode i propens a errors. En Oracle no es pot escriure GROUP BY tramo usant l’àlies, però sí que es pot donar nom al càlcul amb una subconsulta al FROM o amb una clàusula WITH (apartat 6.6):

WITH clasificadas AS (
    SELECT CASE WHEN nota_final IS NULL THEN 'Sin calificar'
                WHEN nota_final < 5     THEN 'Insuficiente'
                ELSE                         'Aprobada'
           END AS tramo
    FROM   matricula)
SELECT tramo, COUNT(*) AS matriculas
FROM   clasificadas
GROUP  BY tramo
ORDER  BY matriculas DESC;

2.5 Subtotals: ROLLUP, CUBE i GROUPING SETS

Un informe sol necessitar, a més del detall per grup, una fila de totals. GROUP BY ROLLUP(...) l’afig automàticament:

SELECT id_modulo,
       COUNT(*)                  AS matriculas,
       COUNT(nota_final)         AS calificadas,
       ROUND(AVG(nota_final), 2) AS media,
       GROUPING(id_modulo)       AS es_total
FROM   matricula
WHERE  id_modulo IN (16, 17, 18, 19)
GROUP  BY ROLLUP(id_modulo)
ORDER  BY GROUPING(id_modulo), id_modulo;

5 files

A les files de subtotal, la columna agrupada val NULL. Si la columna ja tenia valors nuls propis (com cod_grupo a ALUMNO), no podràs distingir «els alumnes sense grup» de «el total» mirant la columna: necessites GROUPING(cod_grupo). Per a presentar-ho es combina amb CASE: CASE WHEN GROUPING(cod_grupo) = 1 THEN 'TOTAL' ELSE NVL(cod_grupo, '(sin grupo)') END.

ROLLUP, CUBE i GROUPING SETS són SQL estàndard i existixen en Oracle, SQL Server i PostgreSQL; en MySQL només hi ha WITH ROLLUP. Ací són ampliació: resol primer els informes sense subtotals.

2.6 Laboratori d’agrupament

Al laboratori següent treballes amb les 143 matrícules reals d’EduGest (hi ha 6 notes sense posar, dos d’elles al mòdul 0490). Tria la columna d’agrupament, la funció d’agregat i, si vols, una condició HAVING, i observa tres coses alhora: quines files formen cada grup, què torna l’agregat i quin SQL estàs construint en realitat.

Presta atenció especialment a dos experiments:

1. Agrupa per id_modulo i compara COUNT(*) amb COUNT(nota_final) al mòdul 0490: hi ha dos matrícules sense nota.
2. Marca la casella «afegir id_alumno al SELECT»: veuràs l’error ORA-00979, que és el que més voltes apareix als primers exercicis del tema.

3. Composicions internes: INNER JOIN

3.1 Quin problema resolen

La normalització (UD04) reparteix la informació per a no repetir-la: a MATRICULA no es guarda el nom de l’alumne, sinó el seu id_alumno. És el disseny correcte, però deixa un problema pràctic: l’acta d’avaluació necessita el nom, no l’identificador.

Una composició (join) és l’operació que torna a unir eixa informació: combina files de dues o més taules emparellant-les per una condició, normalment la igualtat entre una clau aliena i la clau primària a la qual apunta.

flowchart LR
    M["MATRICULA<br/>id_alumno = 3<br/>nota = 8"] -->|id_alumno| A["ALUMNO<br/>id_alumno = 3<br/>Noelia Verdú Espí"]
    M -->|id_modulo| MO["MODULO<br/>id_modulo = 2<br/>Bases de datos"]

3.2 El punt de partida: el producte cartesià

Si escrius dues taules al FROM sense condició, Oracle combina cada fila de la primera amb cada fila de la segona. Això és el producte cartesià, i en SQL modern s’escriu CROSS JOIN:

SELECT (SELECT COUNT(*) FROM grupo) AS grupos,
       (SELECT COUNT(*) FROM ciclo) AS ciclos,
       (SELECT COUNT(*) FROM grupo g CROSS JOIN ciclo c) AS producto
FROM   dual;

1 fila

El producte cartesià de GRUPO i CICLO té 24 files: 6 × 4. Predir este número és just el que cal aprendre a fer. Les primeres files, ordenades, deixen veure el problema:

SELECT g.cod_grupo, g.cod_ciclo AS ciclo_del_grupo, c.cod_ciclo AS ciclo_de_la_tabla
FROM   grupo g CROSS JOIN ciclo c
ORDER  BY g.cod_grupo, c.cod_ciclo
FETCH FIRST 8 ROWS ONLY;

8 files

De les quatre files de cada grup, només una té sentit: la que empareja el grup amb el seu cicle. Una composició interna és exactament el producte cartesià més la condició que selecciona eixes files amb sentit.

El producte cartesià creix multiplicant. Amb les 143 matrícules i les 46 faltes d’EduGest són 6 578 files; amb dues taules d’un milió de files serien un bilió. Si una consulta tarda moltíssim o torna un nombre absurd de files, el primer que cal sospitar és una condició de composició oblidada.

3.3 Sintaxi JOIN … ON

SELECT columnas
FROM   tabla1 alias1
       [INNER] JOIN tabla2 alias2 ON condició_d_emparellament
       [INNER] JOIN tabla3 alias3 ON condició_d_emparellament
WHERE  condicions_de_filtrat

Exemple senzill, cada mòdul amb el nom complet del seu cicle:

SELECT mo.codigo, mo.nombre AS modulo, c.nombre AS ciclo, mo.curso, mo.horas
FROM   modulo mo
       JOIN ciclo c ON c.cod_ciclo = mo.cod_ciclo
WHERE  mo.cod_ciclo = 'ASIR'
ORDER  BY mo.codigo;

5 files

Resultat esperat: 5 files, les mateixes que mòduls d’ASIR. Una composició per clau aliena cap a una clau primària no pot multiplicar files: cada mòdul té exactament un cicle. Esta és la comprovació més útil del tema.

Usa sempre àlies de taula i qualifica totes les columnes. No és una qüestió estètica:

• MODULO i CICLO tenen les dos una columna nombre. Sense qualificar, SELECT nombre donaria ORA-00918: column ambiguously defined.
• Quan llegisques la consulta mesos després, c.nombre diu d’on ix la dada; nombre no.
• Si algú afig una columna a una taula, una consulta sense qualificar pot tornar-se ambigua de sobte i deixar de compilar.

3.4 Composició de tres i quatre taules

Les composicions s’encadenen. El mètode és sempre el mateix: traçar el camí de claus alienes des de la taula que té la dada que busques fins a la que té la dada que vols mostrar.

flowchart LR
    F[FALTA_ASISTENCIA] -->|id_matricula| M[MATRICULA]
    M -->|id_alumno| A[ALUMNO]
    M -->|id_modulo| MO[MODULO]
    MO -->|cod_ciclo| C[CICLO]
    A -->|cod_grupo| G[GRUPO]

Tres taules: alumnat de 2DAM amb el seu grup i el nom del cicle.

SELECT a.apellidos || ', ' || a.nombre AS alumno,
       g.cod_grupo, g.turno, c.nombre AS ciclo
FROM   alumno a
       JOIN grupo g ON g.cod_grupo = a.cod_grupo
       JOIN ciclo c ON c.cod_ciclo = g.cod_ciclo
WHERE  g.cod_grupo = '2DAM'
ORDER  BY a.apellidos;

6 files

Quatre taules: l’assignació docent de 2DAM, que creua IMPARTE amb MODULO, GRUPO i PROFESOR.

SELECT g.cod_grupo, mo.codigo, mo.nombre AS modulo,
       p.apellidos AS profesor, i.horas_semanales
FROM   imparte i
       JOIN modulo mo   ON mo.id_modulo   = i.id_modulo
       JOIN grupo g     ON g.cod_grupo    = i.cod_grupo
       JOIN profesor p  ON p.id_profesor  = i.id_profesor
WHERE  i.cod_grupo = '2DAM'
ORDER  BY mo.codigo;

5 files

Construïx les composicions d’una en una. Afig una taula, executa SELECT COUNT(*), comprova que el número és el que esperes i passa a la següent. Així, quan el número se’n vaja de mare, sabràs exactament quin JOIN l’ha causat. La pràctica 7.2 està muntada sobre esta tècnica.

3.5 Composicions i agrupaments junts

Allò habitual en un informe és compondre i després resumir. L’ordre lògic no canvia: primer FROM (amb les seues composicions), després WHERE, després GROUP BY.

SELECT c.cod_ciclo, c.nombre AS ciclo,
       COUNT(m.id_matricula)     AS matriculas,
       COUNT(m.nota_final)       AS calificadas,
       ROUND(AVG(m.nota_final), 2) AS media
FROM   ciclo c
       JOIN modulo mo    ON mo.cod_ciclo = c.cod_ciclo
       JOIN matricula m  ON m.id_modulo  = mo.id_modulo
GROUP  BY c.cod_ciclo, c.nombre
ORDER  BY c.cod_ciclo;

3 files

Falta un dels quatre cicles: SMR no apareix perquè no té mòduls i, per tant, cap fila sobreviu a la composició interna. Eixe és precisament el problema que resol l’apartat 4.

Al GROUP BY s’han posat c.cod_ciclo i c.nombre, encara que el nom depenga funcionalment del codi. Oracle exigix al GROUP BY totes les columnes no agregades del SELECT. Quan agrupes per una entitat, inclou la seua clau primària al GROUP BY: garantix que no es fonguen dues entitats amb el mateix nom.

3.6 Sintaxi antiga i altres formes de composició

Sintaxi amb comes (heretada)

Abans de l’estàndard SQL-92, la condició de composició s’escrivia al WHERE:

-- Sintaxi antiga: NO l'uses en codi nou
SELECT mo.codigo, c.nombre
FROM   modulo mo, ciclo c
WHERE  c.cod_ciclo = mo.cod_ciclo
AND    mo.curso = 2;

Torna exactament el mateix que la versió amb JOIN … ON, però té dos problemes greus que expliquen per què la norma professional és no usar-la:

1. Barreja les condicions de composició amb les de filtrat: en una consulta de sis taules i deu condicions és impossible veure d’un colp d’ull si falta un emparellament.
2. Si oblides una condició, no hi ha cap error: obtens silenciosament un producte cartesià parcial amb resultats erronis.

Has de saber llegir-la perquè apareix en moltíssim codi heretat i en documentació antiga d’Oracle, però escriu sempre JOIN … ON.

USING

Quan les columnes que s’emparellen tenen el mateix nom a les dues taules, USING abreuja:

SELECT codigo, cod_ciclo, nombre_ciclo
FROM   (SELECT mo.codigo, mo.cod_ciclo, c.nombre AS nombre_ciclo
        FROM   modulo mo JOIN ciclo c USING (cod_ciclo))
FETCH FIRST 3 ROWS ONLY;

USING té una peculiaritat que sorprén: la columna comuna deixa de pertànyer a una taula concreta i no es pot qualificar. SELECT mo.cod_ciclo … USING (cod_ciclo) dona ORA-25154: column part of USING clause cannot have qualifier. Cal escriure cod_ciclo sense àlies.

NATURAL JOIN

NATURAL JOIN empareja automàticament per totes les columnes que tinguen el mateix nom a les dues taules. Sona còmode i és perillós:

SELECT COUNT(*) FROM modulo NATURAL JOIN ciclo;

1 fila

Zero files. MODULO i CICLO compartixen dos noms de columna: cod_ciclo i nombre. El NATURAL JOIN exigix que coincidisquen els dos, i cap mòdul es diu igual que el seu cicle.

No uses NATURAL JOIN en codi real. La composició depén dels noms de les columnes, així que afegir una columna anomenada nombre, fecha_alta o observaciones a una taula pot canviar en silenci el resultat de consultes que portaven anys funcionant. Escriu sempre la condició explícitament a l’ON.

Condicions a l’ON o al WHERE

En una composició interna és igual posar una condició de filtrat a l’ON o al WHERE: el resultat és idèntic, i l’optimitzador d’Oracle ho reorganitza igual.

-- Equivalents (composició interna)
FROM modulo mo JOIN ciclo c ON c.cod_ciclo = mo.cod_ciclo AND mo.curso = 2
FROM modulo mo JOIN ciclo c ON c.cod_ciclo = mo.cod_ciclo WHERE mo.curso = 2

En una composició externa no són equivalents, i la diferència és una de les trampes més repetides del mòdul: s’analitza a l’apartat 4.4. Convenció recomanada: a l’ON, només el que empareja; al WHERE, el que filtra.

3.7 Laboratori de composicions

El laboratori següent compon dues taules reals d’EduGest: els 6 grups (un d’ells, 2ASIR, sense tutor assignat) i 7 professors (dos dels quals no són tutors de cap grup). Canvia el tipus de composició i observa tres coses: l’SQL que es genera, quines files es conserven i quantes files ixen.

Abans de tocar res, prediu els números: quantes files hauria de tornar cada tipus de composició? Després comprova-ho. Usa també les dos caselles inferiors, que reproduïxen els dos errors clàssics del tema: el filtre al WHERE que anul·la una composició externa i la diferència entre posar una condició a l’ON o al WHERE.

4. Composicions externes: LEFT, RIGHT i FULL JOIN

4.1 Quin problema resolen

Una composició interna només torna les files que emparellen. Això fa desaparéixer silenciosament informació que moltes vegades és la més important de l’informe:

En tots estos casos, la fila «sense parella» és justament la que cal detectar i resoldre. Una composició externa conserva les files d’una taula encara que no troben parella en l’altra, omplint amb NULL les columnes que falten.

La paraula OUTER és opcional: LEFT JOIN i LEFT OUTER JOIN són el mateix.

4.2 LEFT JOIN: el cas habitual

SELECT g.cod_grupo, g.cod_ciclo, g.turno, g.id_tutor,
       p.apellidos || ', ' || p.nombre AS tutor
FROM   grupo g
       LEFT JOIN profesor p ON p.id_profesor = g.id_tutor
ORDER  BY g.cod_grupo;

6 files

Resultat esperat: les 6 files de GRUPO, perquè GRUPO està a l’esquerra. La fila de 2ASIR es conserva amb totes les columnes de PROFESOR a NULL. Per a presentar-ho es combina amb les funcions de la UD06:

SELECT g.cod_grupo,
       NVL(p.apellidos || ', ' || p.nombre, '(sin tutor asignado)') AS tutor
FROM   grupo g
       LEFT JOIN profesor p ON p.id_profesor = g.id_tutor
ORDER  BY g.cod_grupo;

Un altre cas clàssic, amb la composició en el sentit 1:N. Tots els cicles, amb els seus grups:

SELECT c.cod_ciclo, c.nombre AS ciclo, g.cod_grupo, g.turno
FROM   ciclo c
       LEFT JOIN grupo g ON g.cod_ciclo = c.cod_ciclo
ORDER  BY c.cod_ciclo, g.cod_grupo;

7 files

Set files: les sis parelles reals més SMR, que apareix una vegada amb les columnes de grup nul·les.

4.3 COUNT en una composició externa: COUNT(*) enfront de COUNT(columna)

Ací els dos conceptes del tema es creuen i produïxen l’error més subtil de la unitat:

SELECT g.cod_grupo,
       COUNT(*)           AS filas_del_grupo,
       COUNT(a.id_alumno) AS alumnos
FROM   grupo g
       LEFT JOIN alumno a ON a.cod_grupo = g.cod_grupo
GROUP  BY g.cod_grupo
ORDER  BY g.cod_grupo;

6 files

En una composició externa, la fila sense parella existix (per això COUNT(*) val 1 per a 2ASIR) però no té dades de la taula opcional (per això COUNT(a.id_alumno) val 0). En un LEFT JOIN, compta sempre una columna de la taula opcional, mai COUNT(*), si el que vols és «quants fills té cada pare». El mateix val per a SUM, que tornarà NULL en compte de 0: embolica’l en NVL(SUM(...), 0).

4.4 La trampa del WHERE: com anul·lar una composició externa

-- (a) INCORRECTA: el WHERE descarta la fila nul·la que acaba de crear el LEFT JOIN
SELECT g.cod_grupo, p.apellidos AS tutor
FROM   grupo g LEFT JOIN profesor p ON p.id_profesor = g.id_tutor
WHERE  p.id_departamento = 1;
-- 5 files: 2ASIR desapareix

-- (b) CORRECTA: la condició sobre la taula opcional va a l'ON
SELECT g.cod_grupo, p.apellidos AS tutor
FROM   grupo g LEFT JOIN profesor p
       ON p.id_profesor = g.id_tutor AND p.id_departamento = 1;
-- 6 files: 2ASIR apareix amb tutor nul

L’explicació està en l’ordre d’avaluació: el LEFT JOIN crea la fila de 2ASIR amb p.id_departamento a NULL; després, el WHERE avalua NULL = 1, que és UNKNOWN, i descarta la fila. Un WHERE sobre una columna de la taula opcional converteix un LEFT JOIN en un INNER JOIN.

Regla per a recordar: en una composició externa, les condicions sobre la taula opcional van a l’ON; les condicions sobre la taula conservada poden anar al WHERE.

Hi ha una excepció deliberada: WHERE columna_opcional IS NULL, que és just el contrari, com es veu a l’apartat següent.

4.5 Anticomposició: buscar el que NO existix

Si el que vols són precisament les files sense parella, el patró és composició externa + IS NULL sobre una columna de la taula opcional. Es diu anti-join:

-- Alumnat sense cap matrícula
SELECT a.id_alumno, a.nombre, a.apellidos, a.cod_grupo
FROM   alumno a
       LEFT JOIN matricula m ON m.id_alumno = a.id_alumno
WHERE  m.id_matricula IS NULL
ORDER  BY a.id_alumno;

3 files

Són els tres alumnes que ni tenen grup ni estan matriculats: una dada de qualitat de dades que secretaria ha de revisar abans de tancar la matrícula.

Tria per a l’IS NULL una columna de la taula opcional que no admeta nuls en la seua taula (normalment la seua clau primària). Si triares una columna que sí que admet nuls, no podries distingir «no hi ha parella» de «hi ha parella amb eixe valor nul».

4.6 FULL OUTER JOIN

Conserva les files de les dues taules. És l’eina per a conciliar dos llistats:

-- Conciliació grup ↔ tutor: grups sense tutor i professorat que no tutoritza
SELECT g.cod_grupo, p.id_profesor, p.apellidos
FROM   grupo g
       FULL OUTER JOIN profesor p ON p.id_profesor = g.id_tutor
WHERE  g.cod_grupo IS NULL OR p.id_profesor IS NULL
ORDER  BY g.cod_grupo, p.id_profesor;

8 files

Un grup sense tutor i set professors que no són tutors de cap grup. Sense el WHERE, la composició tornaria 13 files: 5 parelles + 1 + 7.

4.7 Nota històrica: l’operador (+) d’Oracle

Abans de SQL-92, Oracle expressava les composicions externes amb l’operador (+) junt a la columna de la taula que pot quedar sense dades:

-- Sintaxi propietària antiga: equivalent a GRUPO LEFT JOIN PROFESOR
SELECT g.cod_grupo, p.apellidos
FROM   grupo g, profesor p
WHERE  p.id_profesor(+) = g.id_tutor;

Oracle la continua admetent per compatibilitat, però la mateixa documentació recomana la sintaxi estàndard: (+) no és portable, no permet FULL OUTER JOIN, no es pot combinar lliurement amb OR ni amb IN, i en consultes de diverses taules és molt difícil de llegir. Has de reconéixer-la en llegir codi antic i no escriure-la.

SELECT c.cod_ciclo, COUNT(g.cod_grupo) AS grupos
FROM   ciclo c LEFT JOIN grupo g ON g.cod_ciclo = c.cod_ciclo
WHERE  g.turno = 'M'
GROUP  BY c.cod_ciclo;

SELECT c.cod_ciclo, COUNT(g.cod_grupo) AS grupos
FROM   ciclo c LEFT JOIN grupo g
       ON g.cod_ciclo = c.cod_ciclo AND g.turno = 'M'
GROUP  BY c.cod_ciclo
ORDER  BY c.cod_ciclo;

5. Autocomposició i altres composicions

5.1 Autocomposició (self join)

Una taula també es pot compondre amb si mateixa. Ocorre sempre que una relació s’establix entre files de la mateixa taula: el cap d’un empleat, el prerequisit d’un mòdul, el professor que dirigix un departament del qual forma part.

La clau tècnica és que calen dos àlies distints perquè Oracle (i el lector) sàpia de quina «còpia» de la taula es parla.

A EduGest, cada professor pertany a un departament i cada departament té un cap que és, al seu torn, un professor. Per a posar al costat de cada docent el nom de la seua cap de departament, PROFESOR apareix dues voltes:

SELECT p.apellidos || ', ' || p.nombre AS profesor,
       d.nombre                        AS departamento,
       NVL(j.apellidos || ', ' || j.nombre, '(vacante)') AS jefe_departamento,
       CASE WHEN j.id_profesor = p.id_profesor THEN 'Sí' ELSE 'No' END AS es_el_jefe
FROM   profesor p
       JOIN departamento d  ON d.id_departamento = p.id_departamento
       LEFT JOIN profesor j ON j.id_profesor     = d.id_jefe
WHERE  d.id_departamento IN (2, 3, 5)
ORDER  BY d.nombre, p.apellidos;

4 files

Observa els dos àlies de PROFESOR: p és «el docent» i j és «el seu cap de departament». El LEFT JOIN és necessari perquè un departament pot tindre la cap vacant.

En una autocomposició, els àlies no són opcionals: sense ells, WHERE id_jefe = id_profesor seria ambigu i Oracle donaria ORA-00918. Tria àlies que diguen el paper que juga cada còpia (p/j, empleado/jefe, modulo/prerrequisito), no t1/t2.

5.2 Autocomposició per a comparar files entre si

L’altre ús de l’autocomposició és comparar files de la mateixa taula: duplicats, parelles, coincidències.

-- Parelles d'alumnes del mateix grup que viuen a la mateixa localitat
SELECT a.cod_grupo, a.localidad,
       a.apellidos AS alumno_1, b.apellidos AS alumno_2
FROM   alumno a
       JOIN alumno b ON b.cod_grupo = a.cod_grupo
                    AND b.localidad = a.localidad
                    AND b.id_alumno > a.id_alumno
WHERE  a.cod_grupo = '1DAM'
ORDER  BY a.localidad, a.apellidos, b.apellidos;

7 files

Dos detalls imprescindibles:

• La condició b.id_alumno > a.id_alumno evita que un alumne s’emparelle amb si mateix i que cada parella isca dues voltes (A-B i B-A). Si la lleves, les 7 files es convertixen en 22.
• A 1DAM hi ha dos alumnes distints que tenen per cognom «Ferri Baeza» (Adrián i Paula), cosa que explica les files aparentment repetides: són parelles diferents amb cognoms iguals. És un bon recordatori que el cognom no identifica una persona: per això la clau primària és id_alumno.

5.3 CROSS JOIN legítim: construir una graella completa

El producte cartesià de l’apartat 3.2 semblava només una font d’errors, però té un ús professional important: generar totes les combinacions possibles perquè en un informe apareguen també les cel·les amb valor zero.

Un exemple real: el part trimestral de faltes demana, per a cada grup, les faltes justificades i les no justificades. Si es calcula amb composicions normals, els grups sense cap falta simplement no apareixen, i les combinacions sense faltes tampoc:

-- Sense graella: només apareixen les combinacions que existixen -> 10 files
SELECT a.cod_grupo, f.justificada, COUNT(*) AS faltas, SUM(f.horas) AS horas
FROM   falta_asistencia f
       JOIN matricula m ON m.id_matricula = f.id_matricula
       JOIN alumno a    ON a.id_alumno    = m.id_alumno
GROUP  BY a.cod_grupo, f.justificada
ORDER  BY a.cod_grupo, f.justificada;

Amb CROSS JOIN es construïx primer la graella «6 grups × 2 tipus de falta» i després es pengen les dades amb composicions externes:

SELECT g.cod_grupo, t.justificada,
       COUNT(f.id_falta)    AS faltas,
       NVL(SUM(f.horas), 0) AS horas
FROM   grupo g
       CROSS JOIN (SELECT 'N' AS justificada FROM dual
                   UNION ALL
                   SELECT 'S'                FROM dual) t
       LEFT JOIN alumno a           ON a.cod_grupo    = g.cod_grupo
       LEFT JOIN matricula m        ON m.id_alumno    = a.id_alumno
       LEFT JOIN falta_asistencia f ON f.id_matricula = m.id_matricula
                                   AND f.justificada  = t.justificada
GROUP  BY g.cod_grupo, t.justificada
ORDER  BY g.cod_grupo, t.justificada;

12 files

Ara hi ha 12 files (6 × 2) en compte de 10, i 2ASIR apareix amb zeros explícits, que és el que cap d’estudis necessita veure en un part.

Fixa’t en la combinació de tècniques: un CROSS JOIN deliberat per a la graella, LEFT JOIN per a no perdre les cel·les buides, la condició f.justificada = t.justificada a l’ON (si estiguera al WHERE perdríem de nou 2ASIR), COUNT d’una columna de la taula opcional i NVL sobre el SUM. Les quatre regles dels apartats anteriors actuant juntes.

5.4 Compondre amb una subconsulta o amb una vista

Una composició no exigix que els dos operands siguen taules: pot ser una vista (apartat 7) o una subconsulta al FROM, també anomenada taula en línia o vista en línia. Això permet resumir primer i compondre després:

-- Resum per mòdul (subconsulta) compost amb el catàleg de mòduls
SELECT mo.codigo, mo.cod_ciclo, mo.nombre AS modulo,
       r.calificadas, ROUND(r.media, 2) AS media
FROM   (SELECT id_modulo,
               COUNT(nota_final) AS calificadas,
               AVG(nota_final)   AS media
        FROM   matricula
        GROUP  BY id_modulo) r
       JOIN modulo mo ON mo.id_modulo = r.id_modulo
WHERE  r.media < 6
ORDER  BY r.media, mo.codigo;

7 files

Els set mòduls amb nota mitjana inferior a 6. Observa que el codi 0484 apareix dues voltes, una per cicle: a EduGest el mateix mòdul oficial existix en DAM i en DAW amb id_modulo distint, i per això la columna cod_ciclo és imprescindible per a interpretar l’informe.

6. Subconsultes

6.1 Quin problema resolen

Hi ha preguntes que no es poden respondre d’una vegada perquè necessiten una dada que al seu torn cal calcular: «els mòduls amb més hores que la mitjana» exigix calcular primer la mitjana. Una subconsulta és una consulta SELECT escrita dins d’una altra, entre parèntesis, el resultat de la qual usa la consulta exterior.

Segons on s’escriga i quantes files torne, es comporta de forma distinta:

6.2 Subconsultes d’una fila

S’usen amb els operadors de comparació normals (=, <>, >, <, >=, <=). La subconsulta ha de tornar exactament una fila i una columna; normalment ho garantix una funció d’agregat sense GROUP BY.

-- Mitjana d'hores dels mòduls de DAM: 132
SELECT codigo, nombre, horas
FROM   modulo
WHERE  cod_ciclo = 'DAM'
AND    horas > (SELECT AVG(horas) FROM modulo WHERE cod_ciclo = 'DAM')
ORDER  BY horas DESC, codigo;

3 files

Si una subconsulta d’una fila en torna més d’una, Oracle llança ORA-01427: single-row subquery returns more than one row. És l’error típic de WHERE horas > (SELECT horas FROM modulo WHERE cod_ciclo = 'DAM'): hi ha 10 mòduls de DAM, així que hi ha 10 valors. La solució és o resumir (AVG, MAX…) o usar un operador de diverses files (ANY, ALL, IN).

Les subconsultes també funcionen al HAVING, quan la condició sobre el grup es compara amb un valor global:

-- Mòduls la nota mitjana dels quals supera la mitjana general del centre (6,36)
SELECT id_modulo, COUNT(nota_final) AS calificadas, ROUND(AVG(nota_final), 2) AS media
FROM   matricula
GROUP  BY id_modulo
HAVING AVG(nota_final) > (SELECT AVG(nota_final) FROM matricula)
ORDER  BY media DESC
FETCH FIRST 5 ROWS ONLY;

5 files

6.3 Subconsultes de diverses files: IN, ANY i ALL

-- Alumnat matriculat en Bases de dades (codi 0484, en qualsevol cicle)
SELECT a.id_alumno, a.apellidos || ', ' || a.nombre AS alumno, a.cod_grupo
FROM   alumno a
WHERE  a.id_alumno IN (SELECT m.id_alumno
                       FROM   matricula m
                              JOIN modulo mo ON mo.id_modulo = m.id_modulo
                       WHERE  mo.codigo = '0484')
ORDER  BY a.cod_grupo, a.apellidos;

13 files

-- Mòduls de DAW amb més hores que el mòdul més curt d'ASIR (96 h)
SELECT codigo, nombre, horas
FROM   modulo
WHERE  cod_ciclo = 'DAW'
AND    horas > ANY (SELECT horas FROM modulo WHERE cod_ciclo = 'ASIR')
ORDER  BY horas, codigo;

7 files

ANY i ALL són difícils de llegir. Tradueix sempre a la forma amb agregat abans d’escriure’ls: > ANY és > (SELECT MIN(...)) i > ALL és > (SELECT MAX(...)). Si dubtes, escriu la versió amb MIN/MAX: és equivalent i molt més clara. L’única diferència pràctica apareix amb subconsultes buides.

6.4 La trampa de NOT IN amb nuls

-- Professorat que NO és tutor de cap grup
SELECT COUNT(*) AS filas
FROM   profesor
WHERE  id_profesor NOT IN (SELECT id_tutor FROM grupo);

1 fila

Zero files, quan sabem que hi ha set professors sense tutoria. No és un error d’Oracle: és la lògica de tres valors de la UD06. La subconsulta torna sis valors, i un d’ells és NULL (2ASIR no té tutor). Llavors:

id_profesor NOT IN (105, 103, 106, NULL, 104, 107)
   ≡  id_profesor <> 105 AND … AND id_profesor <> NULL
   ≡  TRUE AND … AND UNKNOWN
   ≡  UNKNOWN   →  el WHERE no deixa passar cap fila

Hi ha dues formes correctes d’escriure-ho:

-- (a) Excloure els nuls de la subconsulta
SELECT id_profesor, apellidos
FROM   profesor
WHERE  id_profesor NOT IN (SELECT id_tutor FROM grupo WHERE id_tutor IS NOT NULL)
ORDER  BY id_profesor;

-- (b) NOT EXISTS: immune als nuls (recomanada)
SELECT p.id_profesor, p.apellidos || ', ' || p.nombre AS profesor
FROM   profesor p
WHERE  NOT EXISTS (SELECT 1 FROM grupo g WHERE g.id_tutor = p.id_profesor)
ORDER  BY p.id_profesor;

7 files

NOT IN sobre una subconsulta que puga tornar NULL és un error de disseny esperant a ocórrer. El dia que algú deixe una clau aliena a nul, la consulta deixarà de tornar files sense donar cap error. Usa NOT EXISTS o afig IS NOT NULL a la subconsulta. IN (afirmatiu) no patix este problema: amb nuls simplement no empareja.

6.5 Subconsultes correlacionades i EXISTS

Una subconsulta és correlacionada quan fa referència a una columna de la consulta exterior. Conceptualment s’avalua una vegada per cada fila de la consulta exterior, amb el valor d’eixa fila.

-- Matrícules de 2DAW la nota de les quals supera la mitjana del SEU mòdul
SELECT a.apellidos || ', ' || a.nombre AS alumno, mo.codigo, m.nota_final,
       (SELECT ROUND(AVG(m2.nota_final), 2)
        FROM   matricula m2
        WHERE  m2.id_modulo = m.id_modulo) AS media_del_modulo
FROM   matricula m
       JOIN alumno a  ON a.id_alumno  = m.id_alumno
       JOIN modulo mo ON mo.id_modulo = m.id_modulo
WHERE  a.cod_grupo = '2DAW'
AND    m.nota_final > (SELECT AVG(m2.nota_final)
                       FROM   matricula m2
                       WHERE  m2.id_modulo = m.id_modulo)
ORDER  BY mo.codigo, m.nota_final DESC;

9 files

La correlació està en m2.id_modulo = m.id_modulo: m és la fila exterior i m2 recorre totes les matrícules d’eixe mòdul. Sense la correlació, la mitjana seria la general del centre i el resultat un altre.

EXISTS és un operador que només pregunta si la subconsulta torna almenys una fila; no mira els valors. Per això dins s’escriu SELECT 1 (o SELECT *): és igual.

-- Alumnat amb almenys una falta no justificada
SELECT a.id_alumno, a.apellidos || ', ' || a.nombre AS alumno, a.cod_grupo
FROM   alumno a
WHERE  EXISTS (SELECT 1
               FROM   falta_asistencia f
                      JOIN matricula m ON m.id_matricula = f.id_matricula
               WHERE  m.id_alumno    = a.id_alumno
               AND    f.justificada  = 'N')
ORDER  BY a.cod_grupo, a.apellidos
FETCH FIRST 6 ROWS ONLY;

6 files

Una composició pot duplicar files si l’altra taula té diverses coincidències; EXISTS mai ho fa, perquè només pregunta si n’hi ha alguna. Si una consulta amb JOIN et torna files repetides i el que volies era només filtrar, canvia-la per EXISTS en lloc d’afegir un DISTINCT.

6.6 Posar nom als passos: la clàusula WITH (CTE)

Quan una consulta necessita dos o tres passos intermedis, aniuar subconsultes la fa il·legible. La clàusula WITH, també anomenada expressió de taula comuna (Common Table Expression, CTE), permet posar nom a cada pas i escriure’ls en l’ordre en què es pensen.

WITH nom1 AS (SELECT …),
     nom2 AS (SELECT … FROM nom1 …)
SELECT … FROM nom2 …

La mateixa consulta de l’apartat 5.4, reescrita:

WITH resumen_modulo AS (
    SELECT id_modulo,
           COUNT(nota_final) AS calificadas,
           AVG(nota_final)   AS media
    FROM   matricula
    GROUP  BY id_modulo
)
SELECT mo.codigo, mo.cod_ciclo, mo.nombre AS modulo,
       r.calificadas, ROUND(r.media, 2) AS media
FROM   resumen_modulo r
       JOIN modulo mo ON mo.id_modulo = r.id_modulo
WHERE  r.media < 6
ORDER  BY r.media, mo.codigo;

Torna exactament les 7 files de l’apartat 5.4. El que canvia és la llegibilitat:

Un exemple amb dos passos, on l’avantatge és evident:

WITH media_alumno AS (
    SELECT a.id_alumno, a.cod_grupo,
           a.apellidos || ', ' || a.nombre AS alumno,
           AVG(m.nota_final) AS media
    FROM   alumno a
           JOIN matricula m ON m.id_alumno = a.id_alumno
    WHERE  a.cod_grupo IS NOT NULL
    GROUP  BY a.id_alumno, a.cod_grupo, a.apellidos, a.nombre
),
media_grupo AS (
    SELECT cod_grupo, AVG(media) AS media_del_grupo
    FROM   media_alumno
    GROUP  BY cod_grupo
)
SELECT ma.cod_grupo, ma.alumno,
       ROUND(ma.media, 2)                      AS media_alumno,
       ROUND(mg.media_del_grupo, 2)            AS media_grupo,
       ROUND(ma.media - mg.media_del_grupo, 2) AS diferencia
FROM   media_alumno ma
       JOIN media_grupo mg ON mg.cod_grupo = ma.cod_grupo
WHERE  ma.cod_grupo = '2DAM'
ORDER  BY ma.media DESC;

6 files

media_grupo és ací la mitjana de les mitjanes dels alumnes del grup: 6,18. No coincidix amb la mitjana de totes les notes de 2DAM, que és 6,17 (AVG sobre les 31 notes del grup), perquè els alumnes no tenen el mateix nombre de matrícules i la mitjana de mitjanes pondera igual a tots. La diferència és xicoteta amb estes dades, però és real: és una decisió de càlcul que cal documentar a l’informe, perquè en estadística escolar s’usen les dos xifres i confondre-les és un error habitual.

WITH és SQL estàndard i està en Oracle des de la versió 9i. Oracle admet a més CTE recursives (WITH … UNION ALL amb referència a si mateixa) per a recórrer jerarquies; és matèria d’ampliació, igual que la clàusula propietària CONNECT BY.

7. Vistes amb composicions

7.1 De la consulta útil a la vista

A la UD05 vas crear vistes sobre una sola taula. El seu veritable valor apareix ara: una vista pot encapsular una composició de diverses taules i oferir-la a la resta del centre com si fora una taula senzilla.

7.2 La vista v_acta

L’acta d’avaluació d’un mòdul és l’informe més usat del centre: alumne, mòdul, nota i qualificació. Necessita tres taules (MATRICULA, ALUMNO i MODULO), i la qualificació és una regla de negoci que no convé repetir en cada consulta.

CREATE OR REPLACE VIEW v_acta AS
    SELECT m.id_matricula,
           a.cod_grupo,
           a.id_alumno,
           a.nia,
           a.apellidos || ', ' || a.nombre AS alumno,
           mo.codigo                       AS cod_modulo,
           mo.nombre                       AS modulo,
           m.curso_academico,
           m.convocatoria,
           m.nota_final                    AS nota,
           CASE WHEN m.nota_final IS NULL THEN 'NC'
                WHEN m.nota_final >= 5    THEN 'APTO'
                ELSE                           'NO APTO'
           END                             AS calificacion
    FROM   matricula m
           JOIN alumno a  ON a.id_alumno  = m.id_alumno
           JOIN modulo mo ON mo.id_modulo = m.id_modulo
WITH READ ONLY;

Consultar-la és com consultar una taula:

-- Acta de Bases de dades (0484) del grup 1DAM
SELECT nia, alumno, nota, calificacion
FROM   v_acta
WHERE  cod_grupo = '1DAM' AND cod_modulo = '0484'
ORDER  BY alumno;

7 files

I, sobretot, es pot agrupar com qualsevol taula. El resum de la sessió d’avaluació de 1DAW:

SELECT cod_modulo, modulo,
       SUM(CASE WHEN calificacion = 'APTO'    THEN 1 ELSE 0 END) AS aptos,
       SUM(CASE WHEN calificacion = 'NO APTO' THEN 1 ELSE 0 END) AS no_aptos,
       SUM(CASE WHEN calificacion = 'NC'      THEN 1 ELSE 0 END) AS nc
FROM   v_acta
WHERE  cod_grupo = '1DAW'
GROUP  BY cod_modulo, modulo
ORDER  BY cod_modulo;

5 files

Compara la grandària d’esta consulta amb la que faria falta sense la vista: tres composicions, el CASE repetit tres voltes i la regla d’aprovat escrita una altra vegada. Eixa reducció és la raó de ser de les vistes.

Nomena les vistes amb un prefix (v_) i documenta la seua finalitat al diccionari de dades:

COMMENT ON TABLE v_acta IS 'Acta de evaluación: una fila por matrícula con su calificación APTO/NO APTO/NC';

7.3 Vistes actualitzables

Una vista no emmagatzema dades, però en alguns casos es pot modificar a través d’ella i Oracle trasllada el canvi a la taula base. Per a això, la vista ha de tindre una taula amb clau preservada: Oracle ha de poder identificar sense ambigüitat quina fila de quina taula correspon a cada fila de la vista.

En una vista amb composicions, només són modificables les columnes de les taules la clau de les quals es preserva:

-- v_acta té WITH READ ONLY: este UPDATE falla (ORA-42399)
UPDATE v_acta SET nota = 7 WHERE id_matricula = 10001;

Si volguérem permetre al professorat posar notes a través d’una vista, caldria crear-la sense WITH READ ONLY i comprovar que MATRICULA conserva la seua clau:

CREATE OR REPLACE VIEW v_notas_1dam AS
    SELECT m.id_matricula, m.id_alumno, m.id_modulo, m.nota_final, a.cod_grupo
    FROM   matricula m
           JOIN alumno a ON a.id_alumno = m.id_alumno
    WHERE  a.cod_grupo = '1DAM'
WITH CHECK OPTION CONSTRAINT ck_v_notas_1dam;

-- Comprovar quines columnes són actualitzables
SELECT column_name, updatable, insertable, deletable
FROM   user_updatable_columns
WHERE  table_name = 'V_NOTAS_1DAM';

Criteri professional: una vista d’informe es crea sempre WITH READ ONLY. És una decisió de seguretat gratuïta que evita modificacions accidentals i documenta la intenció de la vista. Només s’omet quan la vista existix precisament per a permetre modificacions controlades, i llavors s’afig WITH CHECK OPTION.

7.4 Consultar les vistes al diccionari

SELECT view_name, read_only, text_length
FROM   user_views
ORDER  BY view_name;

-- Veure el SELECT que definix una vista
SELECT text FROM user_views WHERE view_name = 'V_ACTA';

-- De quines taules depén cada vista (útil abans d'un ALTER TABLE)
SELECT name, referenced_name, referenced_type
FROM   user_dependencies
WHERE  name = 'V_ACTA';

DROP VIEW v_notas_1dam;

Si s’elimina o es modifica una taula de la qual depén una vista, la vista queda invàlida (status = 'INVALID' en USER_OBJECTS) i qualsevol consulta falla amb ORA-04063: view has errors. Abans d’un ALTER TABLE o d’un DROP TABLE, consulta USER_DEPENDENCIES. Oracle no impedix esborrar una taula perquè tinga vistes damunt: les vistes no són restriccions.

8. Múltiples seleccions: UNION, INTERSECT i MINUS

8.1 Quin problema resolen