UD07 · Consultas avanzadas: composiciones, resúmenes y subconsultas

Resumen del tema

En la UD06 aprendiste a consultar una tabla. Pero una base de datos bien diseñada reparte la información entre muchas tablas: el nombre del alumno está en ALUMNO, su nota en MATRICULA y el nombre del módulo en MODULO. Casi ninguna pregunta real del centro se responde con una sola tabla. Esta unidad enseña a volver a juntar lo que el diseño separó (composiciones o joins), a resumir muchas filas en pocas (funciones de agregado y GROUP BY) y a encadenar consultas unas dentro de otras (subconsultas).

El recorrido es acumulativo. Primero se resume una sola tabla, porque agrupar es más fácil de entender sin composiciones de por medio. Después se combinan tablas, primero de forma interna (solo las filas que emparejan) y luego externa (conservando también las que no emparejan: el grupo sin tutor, el ciclo sin grupos). A continuación se usan subconsultas para preguntas que necesitan dos pasos («quién está por encima de la media de su módulo»). Por último se guardan las consultas útiles como vistas, se combinan resultados con UNION, INTERSECT y MINUS, y se introducen las funciones analíticas y la optimización con el plan de ejecución.

Esta es la unidad que convierte el SQL en una herramienta profesional: al terminarla serás capaz de contestar prácticamente cualquier pregunta que secretaría, jefatura de estudios o el equipo docente le puedan hacer a EduGest. En la UD08 reutilizarás casi todo lo de aquí, porque UPDATE y DELETE admiten subconsultas, y en la UD09 encapsularás estas consultas en procedimientos y vistas más elaboradas.

Todos los ejemplos se ejecutan sobre el esquema de referencia EDUGEST con los datos del curso 2025-26 cargados con los scripts del proyecto: 5 departamentos, 12 profesores, 4 ciclos, 24 módulos, 6 grupos, 32 alumnos, 143 matrículas, 24 asignaciones docentes y 46 faltas de asistencia.

Objetivos de aprendizaje

Al terminar esta unidad serás capaz de:

• Resumir el contenido de una tabla con funciones de agregado, razonando cómo afectan los valores nulos.
• Formar grupos con GROUP BY y filtrarlos con HAVING, distinguiendo su papel del de WHERE.
• Combinar varias tablas con composiciones internas siguiendo el camino de las claves ajenas.
• Decidir cuándo una pregunta exige una composición externa y escribirla sin anularla con un filtro mal colocado.
• Resolver con autocomposiciones las preguntas en que una tabla se relaciona consigo misma.
• Escribir subconsultas de una fila, de varias filas, correlacionadas y con EXISTS, y sustituirlas por una cláusula WITH cuando mejore la legibilidad.
• Crear vistas que encapsulen composiciones y valorar cuándo son actualizables.
• Combinar resultados de varias consultas con UNION, UNION ALL, INTERSECT y MINUS.
• Utilizar funciones analíticas para comparar cada fila con su grupo sin perder el detalle.
• Obtener e interpretar el plan de ejecución de una consulta y aplicar criterios de optimización.

Temporalización

La unidad ocupa 16 horas de aula (10 de teoría y 6 de práctica). Las composiciones y las subconsultas son el núcleo del RA3: conviene no avanzar hasta que cada consulta se pueda escribir sin consultar los apuntes.

Estudia esta unidad con la base de datos abierta y con un método fijo para cada consulta:

1. Escribe en castellano la pregunta que quieres responder.
2. Decide qué tablas la contienen y por qué claves se enlazan.
3. Predice cuántas filas debe devolver y de qué tipo.
4. Ejecuta y compara con tu predicción. Si no coincide, el error está en tu modelo mental, no en Oracle: averigua dónde.

Si una consulta devuelve muchas más filas de las esperadas, casi siempre falta una condición de composición. Si devuelve menos, casi siempre sobra un filtro o falta una composición externa.

1. Funciones de agregado

1.1 Qué problema resuelven

Las funciones de fila de la UD06 (UPPER, ROUND, TO_CHAR…) devuelven un valor por cada fila: entran 143 filas y salen 143. Pero muchas preguntas no piden filas, piden un número que resuma las filas: «¿cuántas matrículas hay?», «¿cuál es la nota media?», «¿cuál es la nota más alta?».

Para eso están las funciones de agregado (también llamadas funciones de grupo o de columna): reciben un conjunto de filas y devuelven un único valor.

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

COUNT, SUM, AVG, MIN y MAX son SQL estándar y existen en todos los SGBD. STDDEV, VARIANCE, MEDIAN y LISTAGG son extensiones de Oracle (algunas están también en PostgreSQL con otro nombre). MEDIAN y LISTAGG se tratan como ampliación: lo evaluable del RA3.e son las cinco primeras.

1.2 Ejemplo sencillo: contar

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

Elemento a elemento:

Todas las funciones de agregado excepto COUNT(*) ignoran los nulos. No los cuentan, no los suman y no los tienen en cuenta en la media. Esta es la causa número uno de resultados «raros» en las consultas resumen, y la razón por la que COUNT(*) y COUNT(columna) dan números distintos.

1.3 Ejemplo aplicado: estadísticos de las notas

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

La última columna es deliberadamente errónea y conviene entenderla bien:

• AVG(nota_final) calcula 871 / 137 = 6,36: divide entre las matrículas calificadas.
• SUM(nota_final) / COUNT(*) calcula 871 / 143 = 6,09: divide entre todas las matrículas, incluidas las seis sin nota, como si fueran ceros.

Las dos cifras responden a preguntas distintas y ninguna es más correcta que la otra en abstracto: depende de lo que te pidan. «Nota media de los alumnos calificados» es 6,36. «Nota media considerando los no presentados como un cero» es 6,09, y entonces conviene escribirlo explícitamente con AVG(NVL(nota_final, 0)) para que el lector vea la decisión. Lo que nunca debe pasar es tomar una por la otra sin darse cuenta.

1.4 MIN y MAX no son solo para números

MIN y MAX funcionan con texto (orden alfabético según NLS_SORT) y con fechas, donde MIN es la más antigua:

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

1 fila

1.5 Contar condiciones: SUM con CASE

Una técnica muy habitual en informes es contar cuántas filas cumplen una condición dentro del mismo resumen, en vez de lanzar una consulta por cada condición:

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

Fíjate en el denominador del porcentaje: COUNT(nota_final), no COUNT(*). El porcentaje de aprobados se calcula sobre los calificados.

En lugar de SUM(CASE WHEN … THEN 1 ELSE 0 END) también funciona COUNT(CASE WHEN … THEN 1 END): como COUNT ignora los nulos y el CASE sin ELSE devuelve NULL cuando no se cumple, el efecto es el mismo. Usa la forma que te resulte más legible, pero sé coherente dentro de un mismo informe.

1.6 Errores habituales con los agregados

-- (a) Mezclar una columna normal con un agregado sin GROUP BY
SELECT localidad, COUNT(*) FROM alumno;
-- ORA-00937: not a single-group group function

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

-- (c) Anidar agregados sin GROUP BY intermedio
SELECT MAX(AVG(nota_final)) FROM matricula;
-- ORA-00978: nested group function without GROUP BY

El error (a) es el más frecuente: si pides una columna normal junto a un agregado, Oracle no sabe qué localidad poner al lado de un recuento de 32 alumnos. La solución es el GROUP BY del apartado siguiente.

2. Agrupamiento: GROUP BY y HAVING

2.1 Cómo se forman los grupos

GROUP BY divide las filas en grupos que comparten el mismo valor en las columnas indicadas, y después aplica las funciones de agregado a cada grupo por separado. El resultado tiene una fila por grupo.

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

6 filas

Observaciones importantes:

• Hay seis filas porque hay cinco valores distintos de cod_grupo más el grupo de los nulos.
• GROUP BY reúne todos los nulos en un único grupo (al contrario que el operador =, que nunca considera iguales dos nulos). Esos son los 3 alumnos sin grupo asignado.
• La suma de los recuentos es 32: ninguna fila se pierde y ninguna se cuenta dos veces.
• Los NULL aparecen al final porque ORDER BY … ASC los coloca al final en Oracle.

GROUP BY no garantiza el orden del resultado, aunque a veces lo parezca. Si el orden importa, escribe ORDER BY. Es un error clásico confiar en que los grupos salgan ordenados.

2.2 Qué puede aparecer en el SELECT

Esta es la regla que más errores provoca del tema:

En una consulta con GROUP BY, el SELECT solo puede contener:

1. las columnas o expresiones que aparecen en el GROUP BY,
2. funciones de agregado,
3. constantes y expresiones calculadas a partir de 1 y 2.

Cualquier otra columna provoca ORA-00979: not a GROUP BY expression.

-- Incorrecta: nombre no está en el GROUP BY y no es un agregado
SELECT localidad, nombre, COUNT(*)
FROM   alumno
GROUP  BY localidad;
-- ORA-00979: not a GROUP BY expression

La consulta no tiene sentido: el grupo «Alicante» contiene 13 alumnos con 13 nombres distintos; ¿cuál debería mostrar? Tienes tres salidas posibles, según lo que realmente quieras:

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 filas

2.3 Orden de evaluación: WHERE frente a HAVING

Ya conoces el orden lógico de las cláusulas; ahora se completa con GROUP BY y HAVING:

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

flowchart LR
    F["1 · FROM<br/>143 filas"] --> W["2 · WHERE<br/>filtra FILAS"]
    W --> G["3 · GROUP BY<br/>forma grupos"]
    G --> H["4 · HAVING<br/>filtra GRUPOS"]
    H --> S["5 · SELECT<br/>calcula columnas"]
    S --> O["6 · ORDER BY<br/>ordena"]

De este orden se derivan todas las reglas prácticas:

Las dos cláusulas se combinan con frecuencia, y cada una hace un trabajo distinto:

-- Módulos con 3 suspensos o más: quién está suspenso se decide fila a fila
-- (WHERE); cuántos suspensos tiene el grupo se decide despué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 filas

2.4 Agrupar por varias columnas y por expresiones

El GROUP BY puede contener varias columnas: entonces se forma un grupo por cada combinación de valores presente en los datos (nunca se inventan combinaciones que no existan).

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 filas

También se puede agrupar por una expresión, siempre que la misma expresión aparezca en el 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 filas

Repetir el CASE completo es incómodo y propenso a errores. En Oracle no se puede escribir GROUP BY tramo usando el alias, pero sí se puede dar nombre al cálculo con una subconsulta en el FROM o con una cláusula WITH (apartado 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 Subtotales: ROLLUP, CUBE y GROUPING SETS

Un informe suele necesitar, además del detalle por grupo, una fila de totales. GROUP BY ROLLUP(...) la añade automáticamente:

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 filas

En las filas de subtotal, la columna agrupada vale NULL. Si la columna ya tenía valores nulos propios (como cod_grupo en ALUMNO), no podrás distinguir «los alumnos sin grupo» de «el total» mirando la columna: necesitas GROUPING(cod_grupo). Para presentarlo se combina con CASE: CASE WHEN GROUPING(cod_grupo) = 1 THEN 'TOTAL' ELSE NVL(cod_grupo, '(sin grupo)') END.

ROLLUP, CUBE y GROUPING SETS son SQL estándar y existen en Oracle, SQL Server y PostgreSQL; en MySQL solo está WITH ROLLUP. Aquí son ampliación: resuelve primero los informes sin subtotales.

2.6 Laboratorio de agrupamiento

En el laboratorio siguiente trabajas con las 143 matrículas reales de EduGest (hay 6 notas sin poner, dos de ellas en el módulo 0490). Elige la columna de agrupamiento, la función de agregado y, si quieres, una condición HAVING, y observa tres cosas a la vez: qué filas forman cada grupo, qué devuelve el agregado y qué SQL estás construyendo en realidad.

Presta atención especialmente a dos experimentos:

1. Agrupa por id_modulo y compara COUNT(*) con COUNT(nota_final) en el módulo 0490: hay dos matrículas sin nota.
2. Marca la casilla «añadir id_alumno al SELECT»: verás el error ORA-00979, que es el que más veces aparece en los primeros ejercicios del tema.

3. Composiciones internas: INNER JOIN

3.1 Qué problema resuelven

La normalización (UD04) reparte la información para no repetirla: en MATRICULA no se guarda el nombre del alumno, sino su id_alumno. Es el diseño correcto, pero deja un problema práctico: el acta de evaluación necesita el nombre, no el identificador.

Una composición (join) es la operación que vuelve a unir esa información: combina filas de dos o más tablas emparejándolas por una condición, normalmente la igualdad entre una clave ajena y la clave primaria a la que 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 punto de partida: el producto cartesiano

Si escribes dos tablas en el FROM sin condición, Oracle combina cada fila de la primera con cada fila de la segunda. Eso es el producto cartesiano, y en SQL moderno se escribe 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 producto cartesiano de GRUPO y CICLO tiene 24 filas: 6 × 4. Predecir este número es justo lo que hay que aprender a hacer. Las primeras filas, ordenadas, dejan ver 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 filas

De las cuatro filas de cada grupo, solo una tiene sentido: la que empareja el grupo con su ciclo. Una composición interna es exactamente el producto cartesiano más la condición que selecciona esas filas con sentido.

El producto cartesiano crece multiplicando. Con las 143 matrículas y las 46 faltas de EduGest son 6 578 filas; con dos tablas de un millón de filas serían un billón. Si una consulta tarda muchísimo o devuelve un número absurdo de filas, lo primero que hay que sospechar es una condición de composición olvidada.

3.3 Sintaxis JOIN … ON

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

Ejemplo sencillo, cada módulo con el nombre completo de su ciclo:

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 filas

Resultado esperado: 5 filas, las mismas que módulos de ASIR. Una composición por clave ajena hacia una clave primaria no puede multiplicar filas: cada módulo tiene exactamente un ciclo. Esta es la comprobación más útil del tema.

Usa siempre alias de tabla y cualifica todas las columnas. No es una cuestión estética:

• MODULO y CICLO tienen las dos una columna nombre. Sin cualificar, SELECT nombre daría ORA-00918: column ambiguously defined.
• Al leer la consulta meses después, c.nombre dice de dónde sale el dato; nombre no.
• Si alguien añade una columna a una tabla, una consulta sin cualificar puede volverse ambigua de repente y dejar de compilar.

3.4 Composición de tres y cuatro tablas

Las composiciones se encadenan. El método es siempre el mismo: trazar el camino de claves ajenas desde la tabla que tiene el dato que buscas hasta la que tiene el dato que quieres 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 tablas: alumnado de 2DAM con su grupo y el nombre del ciclo.

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 filas

Cuatro tablas: la asignación docente de 2DAM, que cruza IMPARTE con MODULO, GRUPO y 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 filas

Construye las composiciones de una en una. Añade una tabla, ejecuta SELECT COUNT(*), comprueba que el número es el que esperas y pasa a la siguiente. Así, cuando el número se desmadre, sabrás exactamente qué JOIN lo ha causado. La práctica 7.2 está montada sobre esta técnica.

3.5 Composiciones y agrupamientos juntos

Lo habitual en un informe es componer y después resumir. El orden lógico no cambia: primero FROM (con sus composiciones), luego WHERE, luego 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 filas

Falta uno de los cuatro ciclos: SMR no aparece porque no tiene módulos y, por tanto, ninguna fila sobrevive a la composición interna. Ese es precisamente el problema que resuelve el apartado 4.

En el GROUP BY se han puesto c.cod_ciclo y c.nombre, aunque el nombre dependa funcionalmente del código. Oracle exige en el GROUP BY todas las columnas no agregadas del SELECT. Cuando agrupes por una entidad, incluye su clave primaria en el GROUP BY: garantiza que no se fundan dos entidades con el mismo nombre.

3.6 Sintaxis antigua y otras formas de composición

Sintaxis con comas (heredada)

Antes del estándar SQL-92, la condición de composición se escribía en el WHERE:

-- Sintaxis antigua: NO la uses en código nuevo
SELECT mo.codigo, c.nombre
FROM   modulo mo, ciclo c
WHERE  c.cod_ciclo = mo.cod_ciclo
AND    mo.curso = 2;

Devuelve exactamente lo mismo que la versión con JOIN … ON, pero tiene dos problemas graves que explican por qué la norma profesional es no usarla:

1. Mezcla las condiciones de composición con las de filtrado: en una consulta de seis tablas y diez condiciones es imposible ver de un vistazo si falta un emparejamiento.
2. Si olvidas una condición, no hay ningún error: obtienes silenciosamente un producto cartesiano parcial con resultados erróneos.

Debes saber leerla porque aparece en muchísimo código heredado y en documentación antigua de Oracle, pero escribe siempre JOIN … ON.

USING

Cuando las columnas que se emparejan tienen el mismo nombre en las dos tablas, USING abrevia:

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 tiene una peculiaridad que sorprende: la columna común deja de pertenecer a una tabla concreta y no se puede cualificar. SELECT mo.cod_ciclo … USING (cod_ciclo) da ORA-25154: column part of USING clause cannot have qualifier. Hay que escribir cod_ciclo sin alias.

NATURAL JOIN

NATURAL JOIN empareja automáticamente por todas las columnas que tengan el mismo nombre en las dos tablas. Suena cómodo y es peligroso:

SELECT COUNT(*) FROM modulo NATURAL JOIN ciclo;

1 fila

Cero filas. MODULO y CICLO comparten dos nombres de columna: cod_ciclo y nombre. El NATURAL JOIN exige que coincidan los dos, y ningún módulo se llama igual que su ciclo.

No uses NATURAL JOIN en código real. La composición depende de los nombres de las columnas, así que añadir una columna llamada nombre, fecha_alta u observaciones a una tabla puede cambiar en silencio el resultado de consultas que llevaban años funcionando. Escribe siempre la condición explícitamente en el ON.

Condiciones en el ON o en el WHERE

En una composición interna da lo mismo poner una condición de filtrado en el ON o en el WHERE: el resultado es idéntico, y el optimizador de Oracle lo reorganiza igual.

-- Equivalentes (composición 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ón externa no son equivalentes, y la diferencia es una de las trampas más repetidas del módulo: se analiza en el apartado 4.4. Convención recomendada: en el ON, solo lo que empareja; en el WHERE, lo que filtra.

3.7 Laboratorio de composiciones

El laboratorio siguiente compone dos tablas reales de EduGest: los 6 grupos (uno de ellos, 2ASIR, sin tutor asignado) y 7 profesores (dos de los cuales no son tutores de ningún grupo). Cambia el tipo de composición y observa tres cosas: el SQL que se genera, qué filas se conservan y cuántas filas salen.

Antes de tocar nada, predice los números: ¿cuántas filas debería devolver cada tipo de composición? Después compruébalo. Usa también las dos casillas inferiores, que reproducen los dos errores clásicos del tema: el filtro en el WHERE que anula una composición externa y la diferencia entre poner una condición en el ON o en el WHERE.

4. Composiciones externas: LEFT, RIGHT y FULL JOIN

4.1 Qué problema resuelven

Una composición interna solo devuelve las filas que emparejan. Eso hace desaparecer silenciosamente información que muchas veces es la más importante del informe:

En todos estos casos, la fila «sin pareja» es justamente la que hay que detectar y resolver. Una composición externa conserva las filas de una tabla aunque no encuentren pareja en la otra, rellenando con NULL las columnas que faltan.

La palabra OUTER es opcional: LEFT JOIN y LEFT OUTER JOIN son lo mismo.

4.2 LEFT JOIN: el caso 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 filas

Resultado esperado: las 6 filas de GRUPO, porque GRUPO está a la izquierda. La fila de 2ASIR se conserva con todas las columnas de PROFESOR a NULL. Para presentarlo se combina con las funciones 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;

Otro caso clásico, con la composición en el sentido 1:N. Todos los ciclos, con sus grupos:

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 filas

Siete filas: las seis parejas reales más SMR, que aparece una vez con las columnas de grupo nulas.

4.3 COUNT en una composición externa: COUNT(*) frente a COUNT(columna)

Aquí los dos conceptos del tema se cruzan y producen el error más sutil de la unidad:

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 filas

En una composición externa, la fila sin pareja existe (por eso COUNT(*) vale 1 para 2ASIR) pero no tiene datos de la tabla opcional (por eso COUNT(a.id_alumno) vale 0). En un LEFT JOIN, cuenta siempre una columna de la tabla opcional, nunca COUNT(*), si lo que quieres es «cuántos hijos tiene cada padre». Lo mismo vale para SUM, que devolverá NULL en vez de 0: envuélvelo en NVL(SUM(...), 0).

4.4 La trampa del WHERE: cómo anular una composición externa

-- (a) INCORRECTA: el WHERE descarta la fila nula 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 filas: 2ASIR desaparece

-- (b) CORRECTA: la condición sobre la tabla opcional va en el 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 filas: 2ASIR aparece con tutor nulo

La explicación está en el orden de evaluación: el LEFT JOIN crea la fila de 2ASIR con p.id_departamento a NULL; después, el WHERE evalúa NULL = 1, que es UNKNOWN, y descarta la fila. Un WHERE sobre una columna de la tabla opcional convierte un LEFT JOIN en un INNER JOIN.

Regla para recordar: en una composición externa, las condiciones sobre la tabla opcional van en el ON; las condiciones sobre la tabla conservada pueden ir en el WHERE.

Hay una excepción deliberada: WHERE columna_opcional IS NULL, que es justo lo contrario, como se ve en el apartado siguiente.

4.5 Anticomposición: buscar lo que NO existe

Si lo que quieres son precisamente las filas sin pareja, el patrón es composición externa + IS NULL sobre una columna de la tabla opcional. Se llama anti-join:

-- Alumnado sin ninguna 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 filas

Son los tres alumnos que ni tienen grupo ni están matriculados: un dato de calidad de datos que secretaría debe revisar antes de cerrar la matrícula.

Elige para el IS NULL una columna de la tabla opcional que no admita nulos en su tabla (normalmente su clave primaria). Si eligieras una columna que sí admite nulos, no podrías distinguir «no hay pareja» de «hay pareja con ese valor nulo».

4.6 FULL OUTER JOIN

Conserva las filas de las dos tablas. Es la herramienta para conciliar dos listados:

-- Conciliación grupo ↔ tutor: grupos sin tutor y profesorado que no tutoriza
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 filas

Un grupo sin tutor y siete profesores que no son tutores de ningún grupo. Sin el WHERE, la composición devolvería 13 filas: 5 parejas + 1 + 7.

4.7 Nota histórica: el operador (+) de Oracle

Antes de SQL-92, Oracle expresaba las composiciones externas con el operador (+) junto a la columna de la tabla que puede quedar sin datos:

-- Sintaxis propietaria antigua: equivalente 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 sigue admitiendo por compatibilidad, pero la propia documentación recomienda la sintaxis estándar: (+) no es portable, no permite FULL OUTER JOIN, no se puede combinar libremente con OR ni con IN, y en consultas de varias tablas es muy difícil de leer. Debes reconocerla al leer código antiguo y no escribirla.

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ón y otras composiciones

5.1 Autocomposición (self join)

Una tabla también se puede componer consigo misma. Ocurre siempre que una relación se establece entre filas de la misma tabla: el jefe de un empleado, el prerrequisito de un módulo, el profesor que dirige un departamento del que forma parte.

La clave técnica es que hacen falta dos alias distintos para que Oracle (y el lector) sepa de qué «copia» de la tabla se habla.

En EduGest, cada profesor pertenece a un departamento y cada departamento tiene un jefe que es, a su vez, un profesor. Para poner al lado de cada docente el nombre de su jefatura de departamento, PROFESOR aparece dos veces:

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 filas

Observa los dos alias de PROFESOR: p es «el docente» y j es «su jefe de departamento». El LEFT JOIN es necesario porque un departamento puede tener la jefatura vacante.

En una autocomposición, los alias no son opcionales: sin ellos, WHERE id_jefe = id_profesor sería ambiguo y Oracle daría ORA-00918. Elige alias que digan el papel que juega cada copia (p/j, empleado/jefe, modulo/prerrequisito), no t1/t2.

5.2 Autocomposición para comparar filas entre sí

El otro uso de la autocomposición es comparar filas de la misma tabla: duplicados, parejas, coincidencias.

-- Parejas de alumnos del mismo grupo que viven en la misma localidad
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 filas

Dos detalles imprescindibles:

• La condición b.id_alumno > a.id_alumno evita que un alumno se empareje consigo mismo y que cada pareja salga dos veces (A-B y B-A). Si la quitas, las 7 filas se convierten en 22.
• En 1DAM hay dos alumnos distintos que se apellidan «Ferri Baeza» (Adrián y Paula), lo que explica las filas aparentemente repetidas: son parejas diferentes con apellidos iguales. Es un buen recordatorio de que el apellido no identifica a una persona: por eso la clave primaria es id_alumno.

5.3 CROSS JOIN legítimo: construir una rejilla completa

El producto cartesiano del apartado 3.2 parecía solo una fuente de errores, pero tiene un uso profesional importante: generar todas las combinaciones posibles para que en un informe aparezcan también las celdas con valor cero.

Un ejemplo real: el parte trimestral de faltas pide, para cada grupo, las faltas justificadas y las no justificadas. Si se calcula con composiciones normales, los grupos sin ninguna falta simplemente no aparecen, y las combinaciones sin faltas tampoco:

-- Sin rejilla: solo aparecen las combinaciones que existen -> 10 filas
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;

Con CROSS JOIN se construye primero la rejilla «6 grupos × 2 tipos de falta» y después se cuelgan los datos con composiciones externas:

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 filas

Ahora hay 12 filas (6 × 2) en vez de 10, y 2ASIR aparece con ceros explícitos, que es lo que jefatura necesita ver en un parte.

Fíjate en la combinación de técnicas: un CROSS JOIN deliberado para la rejilla, LEFT JOIN para no perder las celdas vacías, la condición f.justificada = t.justificada en el ON (si estuviera en el WHERE perderíamos de nuevo 2ASIR), COUNT de una columna de la tabla opcional y NVL sobre el SUM. Las cuatro reglas de los apartados anteriores actuando juntas.

5.4 Componer con una subconsulta o con una vista

Una composición no exige que los dos operandos sean tablas: puede ser una vista (apartado 7) o una subconsulta en el FROM, también llamada tabla en línea o vista en línea. Esto permite resumir primero y componer después:

-- Resumen por módulo (subconsulta) compuesto con el catálogo de módulos
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 filas

Los siete módulos con nota media inferior a 6. Observa que el código 0484 aparece dos veces, una por ciclo: en EduGest el mismo módulo oficial existe en DAM y en DAW con id_modulo distinto, y por eso la columna cod_ciclo es imprescindible para interpretar el informe.

6. Subconsultas

6.1 Qué problema resuelven

Hay preguntas que no se pueden responder de una vez porque necesitan un dato que a su vez hay que calcular: «los módulos con más horas que la media» exige calcular primero la media. Una subconsulta es una consulta SELECT escrita dentro de otra, entre paréntesis, cuyo resultado usa la consulta exterior.

Según dónde se escriba y cuántas filas devuelva, se comporta de forma distinta:

6.2 Subconsultas de una fila

Se usan con los operadores de comparación normales (=, <>, >, <, >=, <=). La subconsulta debe devolver exactamente una fila y una columna; normalmente lo garantiza una función de agregado sin GROUP BY.

-- Media de horas de los módulos 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 filas

Si una subconsulta de una fila devuelve más de una, Oracle lanza ORA-01427: single-row subquery returns more than one row. Es el error típico de WHERE horas > (SELECT horas FROM modulo WHERE cod_ciclo = 'DAM'): hay 10 módulos de DAM, así que hay 10 valores. La solución es o resumir (AVG, MAX…) o usar un operador de varias filas (ANY, ALL, IN).

Las subconsultas también funcionan en el HAVING, cuando la condición sobre el grupo se compara con un valor global:

-- Módulos cuya nota media supera la media general del centro (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 filas

6.3 Subconsultas de varias filas: IN, ANY y ALL

-- Alumnado matriculado en Bases de datos (código 0484, en cualquier ciclo)
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 filas

-- Módulos de DAW con más horas que el módulo más corto de 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 filas

ANY y ALL son difíciles de leer. Traduce siempre a la forma con agregado antes de escribirlos: > ANY es > (SELECT MIN(...)) y > ALL es > (SELECT MAX(...)). Si dudas, escribe la versión con MIN/MAX: es equivalente y mucho más clara. La única diferencia práctica aparece con subconsultas vacías.

6.4 La trampa de NOT IN con nulos

-- Profesorado que NO es tutor de ningún grupo
SELECT COUNT(*) AS filas
FROM   profesor
WHERE  id_profesor NOT IN (SELECT id_tutor FROM grupo);

1 fila

Cero filas, cuando sabemos que hay siete profesores sin tutoría. No es un error de Oracle: es la lógica de tres valores de la UD06. La subconsulta devuelve seis valores, y uno de ellos es NULL (2ASIR no tiene tutor). Entonces:

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 deja pasar ninguna fila

Hay dos formas correctas de escribirlo:

-- (a) Excluir los nulos 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: inmune a los nulos (recomendada)
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 filas

NOT IN sobre una subconsulta que pueda devolver NULL es un error de diseño esperando a ocurrir. El día en que alguien deje una clave ajena a nulo, la consulta dejará de devolver filas sin dar ningún error. Usa NOT EXISTS o añade IS NOT NULL en la subconsulta. IN (afirmativo) no sufre este problema: con nulos simplemente no empareja.

6.5 Subconsultas correlacionadas y EXISTS

Una subconsulta es correlacionada cuando hace referencia a una columna de la consulta exterior. Conceptualmente se evalúa una vez por cada fila de la consulta exterior, con el valor de esa fila.

-- Matrículas de 2DAW cuya nota supera la media de SU módulo
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 filas

La correlación está en m2.id_modulo = m.id_modulo: m es la fila exterior y m2 recorre todas las matrículas de ese módulo. Sin la correlación, la media sería la general del centro y el resultado otro.

EXISTS es un operador que solo pregunta si la subconsulta devuelve al menos una fila; no mira los valores. Por eso dentro se escribe SELECT 1 (o SELECT *): da lo mismo.

-- Alumnado con al menos 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 filas

Una composición puede duplicar filas si la otra tabla tiene varias coincidencias; EXISTS nunca lo hace, porque solo pregunta si hay alguna. Si una consulta con JOIN te devuelve filas repetidas y lo único que querías era filtrar, cámbiala por EXISTS en lugar de añadir un DISTINCT.

6.6 Dar nombre a los pasos: la cláusula WITH (CTE)

Cuando una consulta necesita dos o tres pasos intermedios, anidar subconsultas la vuelve ilegible. La cláusula WITH, también llamada expresión de tabla común (Common Table Expression, CTE), permite dar nombre a cada paso y escribirlos en el orden en que se piensan.

WITH nombre1 AS (SELECT …),
     nombre2 AS (SELECT … FROM nombre1 …)
SELECT … FROM nombre2 …

La misma consulta del apartado 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;

Devuelve exactamente las 7 filas del apartado 5.4. Lo que cambia es la legibilidad:

Un ejemplo con dos pasos, donde la ventaja es evidente:

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 filas

media_grupo es aquí la media de las medias de los alumnos del grupo: 6,18. No coincide con la media de todas las notas de 2DAM, que es 6,17 (AVG sobre las 31 notas del grupo), porque los alumnos no tienen el mismo número de matrículas y la media de medias pondera igual a todos. La diferencia es pequeña con estos datos, pero es real: es una decisión de cálculo que hay que documentar en el informe, porque en estadística escolar se usan las dos cifras y confundirlas es un error habitual.

WITH es SQL estándar y está en Oracle desde la versión 9i. Oracle admite además CTE recursivas (WITH … UNION ALL con referencia a sí misma) para recorrer jerarquías; es materia de ampliación, igual que la cláusula propietaria CONNECT BY.

7. Vistas con composiciones

7.1 De la consulta útil a la vista

En la UD05 creaste vistas sobre una sola tabla. Su verdadero valor aparece ahora: una vista puede encapsular una composición de varias tablas y ofrecerla al resto del centro como si fuera una tabla sencilla.

7.2 La vista v_acta

El acta de evaluación de un módulo es el informe más usado del centro: alumno, módulo, nota y calificación. Necesita tres tablas (MATRICULA, ALUMNO y MODULO), y la calificación es una regla de negocio que no conviene 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;

Consultarla es como consultar una tabla:

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

7 filas

Y, sobre todo, se puede agrupar como cualquier tabla. El resumen de la sesión de evaluación 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 filas

Compara el tamaño de esta consulta con la que haría falta sin la vista: tres composiciones, el CASE repetido tres veces y la regla de aprobado escrita otra vez. Esa reducción es la razón de ser de las vistas.

Nombra las vistas con un prefijo (v_) y documenta su finalidad en el diccionario de datos:

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 Vistas actualizables

Una vista no almacena datos, pero en algunos casos se puede modificar a través de ella y Oracle traslada el cambio a la tabla base. Para eso, la vista debe tener una tabla con clave preservada: Oracle tiene que poder identificar sin ambigüedad qué fila de qué tabla corresponde a cada fila de la vista.

En una vista con composiciones, solo son modificables las columnas de las tablas cuya clave se preserva:

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

Si quisiéramos permitir al profesorado poner notas a través de una vista, habría que crearla sin WITH READ ONLY y comprobar que MATRICULA conserva su clave:

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;

-- Comprobar qué columnas son actualizables
SELECT column_name, updatable, insertable, deletable
FROM   user_updatable_columns
WHERE  table_name = 'V_NOTAS_1DAM';

Criterio profesional: una vista de informe se crea siempre WITH READ ONLY. Es una decisión de seguridad gratuita que evita modificaciones accidentales y documenta la intención de la vista. Solo se omite cuando la vista existe precisamente para permitir modificaciones controladas, y entonces se añade WITH CHECK OPTION.

7.4 Consultar las vistas en el diccionario

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

-- Ver el SELECT que define una vista
SELECT text FROM user_views WHERE view_name = 'V_ACTA';

-- De qué tablas depende cada vista (útil antes de un ALTER TABLE)
SELECT name, referenced_name, referenced_type
FROM   user_dependencies
WHERE  name = 'V_ACTA';

DROP VIEW v_notas_1dam;

Si se elimina o se modifica una tabla de la que depende una vista, la vista queda inválida (status = 'INVALID' en USER_OBJECTS) y cualquier consulta falla con ORA-04063: view has errors. Antes de un ALTER TABLE o un DROP TABLE, consulta USER_DEPENDENCIES. Oracle no impide borrar una tabla porque tenga vistas encima: las vistas no son restricciones.

8. Múltiples selecciones: UNION, INTERSECT y MINUS

8.1 Qué problema resuelven