4.9 - CONSULTAS ANIDADAS.
CONSULTAS ANIDADAS
Podemos crear concultas anidadas en los que algún valor o valores de la consulta padre es devuelto por otra consulta hijo.
La consulta anidada puede devolver uno o más datos.
CASO 1° - La consulta anidada devuelve un sólo dato.
En este caso se usarán los operadores =, <, <=, >=, > asociados a la consulta anidada.
EJEMPLO 47:
Nombre de las asignaturas de la titulación "130110" cuyos costes básicos sobrepasen el coste básico promedio por
asignatura en esa titulación.
SELECT Nombre FROM ASIGNATURA
WHERE CosteBasico > (SELECT AVG(CosteBasico)
FROM ASIGNATURA
WHERE IdTitulacion = "130110")
AND IdTitulacion = "130110";
CASO 2º - La consulta anidada devuelve más de un valor.
Si la consulta anidada devuelve más de un valor usaremos los operadores IN, NOT IN o los operadores anteriores asociados
a cuantificadores como ALL y ANY o también el cuantificador EXITS. Estos cuantificadores se verán más adelante a partir
del ejemplo 54.
EJEMPLO 48:
Nombre de las titulaciones que tengan al menos una asignatura de 4.5 créditos.
SELECT Nombre
FROM TITULACION
WHERE IdTitulacion IN (SELECT DISTINCT IdTitulacion
FROM ASIGNATURA
WHERE Creditos = 4.5);
Otra forma:
SELECT DISTINCT [Link]
FROM TITULACION, ASIGNATURA
WHERE [Link] = [Link]
AND Creditos = 4.5;
EJEMPLO 49:
Mostrar el identificador de los alumnos matriculados en cualquier asignatura excepto la "150212" y la "130113". (Es el
ejemplo 19 corregido).
Ver ejemplo 19.
SELECT IdAlumno
FROM ALUMNO
WHERE IdAlumno NOT IN (SELECT IdAlumno
FROM ALUMNO_ASIGNATURA
WHERE IdAsignatura = "150212" OR IdAsignatura = "130113");
EJEMPLO 50:
Id de los alumnos matriculados en la asignatura "150212" pero no en la "130113".
SELECT IdAlumno
FROM ALUMNO
WHERE IdAlumno IN (SELECT IdAlumno
FROM ALUMNO_ASIGNATURA
WHERE IdAsignatura = "150212")
AND IdAlumno NOT IN (SELECT IdAlumno
FROM ALUMNO_ASIGNATURA
WHERE IdAsignatura = "130113");
EJEMPLO 51:
Nombre de las asignaturas que tienen más créditos que "Seguridad Vial". (Es igual que el ejemplo 38).
Ver ejemplo 38.
SELECT Nombre
FROM ASIGNATURA
WHERE Creditos > (SELECT Creditos
FROM ASIGNATURA
WHERE Nombre = "Seguridad Vial");
EJEMPLO 52:
Id de los alumnos matriculados en todas las asignaturas de 1º de Empresariales.
SELECT IdAlumno
FROM ASIGNATURA, ALUMNO_ASIGNATURA, TITULACION
WHERE ALUMNO_ASIGNATURA.IdAsignatura = [Link]
AND [Link] = [Link]
AND Curso = "1"
GROUP BY IdAlumno
HAVING COUNT(IdAlumno) = (SELECT COUNT(IdAsignatura)
FROM ASIGNATURA, TITULACION
WHERE [Link] = [Link]
AND [Link] = "Empresariales"
AND Curso = "1");
CONSULTAS AVANZADAS: CUANTIFICADORES ALL, ANY Y EXISTS
En relacción con consultas anidadas existen 3 cuantificadores ALL y ANY y EXITS.
CUANTIFICADOR ALL.
Se utilizará de forma conjunta con una consulta anidada que devuelva más de un valor y un operador de comparación que
no sea la igualdad.
Sirve para seleccionar los valores que cumplan una determinada condición para todos los valores de la consulta anidada.
(No se suele utilizar con igualdad).
EJEMPLO 53:
Nombre de las asignaturas que tengan más créditos.
SELECT Nombre
FROM ASIGNATURA
WHERE Creditos >= ALL (SELECT Creditos
FROM ASIGNATURA);
Otra forma:
SELECT Nombre
FROM ASIGNATURA
WHERE Creditos = (SELECT MAX(Creditos)
FROM ASIGNATURA);
EJEMPLO 54:
Personas que viven en la ciudad que vive más gente.
SELECT *
FROM PERSONA
WHERE Ciudad IN (SELECT Ciudad
FROM PERSONA
GROUP BY Ciudad
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM PERSONA
GROUP BY Ciudad));
CUANTIFICADOR ANY.
En conjunción con una consulta anidada que devuelve varios valores, sirve para determinar cuando una determinada
condición se verifica para al menos uno de los valores que devuelve la subconsulta. (No se suele utilizar con desigualdad).
EJEMPLO 54B:
Personas que viven en cualquier ciudad excepto en la que vive más gente.
SELECT *
FROM PERSONA
WHERE Ciudad IN (SELECT Ciudad
FROM PERSONA
GROUP BY Ciudad
HAVING COUNT(*) < ANY (SELECT COUNT(*)
FROM PERSONA
GROUP BY Ciudad));
CUANTIFICADOR EXISTS.
En conjunción con una consulta anidada sirve para determinar si existen filas en dicha subconsulta. Podemos utilizar
análogamente NOT EXISTS.
Este tipo de consultas se llaman correlacionadas ya que hay una relacción entre la consulta principal y la subconsulta.
EJEMPLO 55:
Lista de asignaturas en las que no se ha matriculado nadie.
SELECT *
FROM ASIGNATURA
WHERE NOT EXISTS (SELECT *
FROM ALUMNO_ASIGNATURA
WHERE ALUMNO_ASIGNATURA.IdAsignatura = [Link]);
EJEMPLO 56:
Ciudades en las que vive algún profesor y también algún alumno.
SELECT DISTINCT Ciudad
FROM PERSONA AS PP, PROFESOR
WHERE [Link] = [Link]
AND EXISTS (SELECT *
FROM PERSONA AS PA, ALUMNO
WHERE [Link] = [Link]
AND [Link] = [Link]);
Otra forma:
SELECT DISTINCT [Link]
FROM PERSONA AS PP, PROFESOR, PERSONA AS PA, ALUMNO
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link];
EJEMPLO 57:
Ciudades en las que vive algún profesor pero ningún alumno.
SELECT DISTINCT Ciudad
FROM PERSONA AS PP, PROFESOR
WHERE [Link] = [Link]
AND NOT EXISTS (SELECT *
FROM PERSONA AS PA, ALUMNO
WHERE [Link] = [Link]
AND [Link] = [Link]);
OPERADORES DE TIPO CONJUNTO
Si entendemos las tablas como un conjunto de filas, podemos entonces aplicar las operaciones básicas de
conjuntos:UNIÓN, INTERSECCIÓN y SUSTRACCIÓN siendo sus equivalentes en SQL UNION, INTERSECT y MINUS.
(Las dos últimas no funcionan en ACCESS pero sí en ORACLE).
Suponiendo que aplicamos alguno de los operadores anteriores a dos tablas A y B, éstas deben ser compatibles, es decir el
número y el tipo de las columnas debe ser el mismo.
No es necesario que el nombre de las columnas coincida en ambas tablas.
El nombre de la columna resultado AUB es el mismo que A.
Se pueden aplicar estos operadores a más de dos tablas a la vez en una misma consulta.
OPERADOR UNION.
El operador UNION se utilizará para crear una consulta de unión entre dos o más consultas o tablas independientes.
El resultado de esta consulta devuelve la suma de todas las filas de las consultas o tablas implicadas omitiendo aquéllas que
estén repetidas. Podemos forzar que las tuplas repetidas sean devueltas utilizando el predicado ALL.
EJEMPLO 58:
Mostrar el DNI de alumnos y profesores.
SELECT DNI
FROM ALUMNO
UNION
SELECT DNI
FROM PROFESOR;
Lo mismo pero con filas repetidas:
SELECT DNI
FROM ALUMNO
UNION ALL
SELECT DNI
FROM PROFESOR;
EJEMPLO 59:
Mostrar las personas que no son ni profesores ni alumnos.
SELECT *
FROM PERSONA
WHERE DNI NOT IN (SELECT DNI
FROM ALUMNO
UNION
SELECT DNI
FROM PROFESOR);
La consulta anterior no es aceptada por ACCESS. Una variante que devuelve el mismo resultado es la siguiente:
SELECT *
FROM PERSONA
WHERE DNI NOT IN (SELECT DNI
FROM ALUMNO)
AND DNI NOT IN (SELECT DNI
FROM PROFESOR);
EJEMPLO 60:
Id de los alumnos matriculados en la asignatura "150212" o en la "130113" o en ambas. (Igual que el ejemplo 17).
Ver ejemplo 17.
SELECT IdAlumno
FROM ALUMNO_ASIGNATURA
WHERE IdAsignatura = "150212"
UNION
SELECT IdAlumno
FROM ALUMNO_ASIGNATURA
WHERE IdAsignatura = "130113";
OPERADOR INTERSECT.
EJEMPLO 61:
DNI de los profesores que también son alumnos. (Igual que el ejemplo 40).
Ver ejemplo 40.
SELECT DNI
FROM PROFESOR
INTERSECT
SELECT DNI
FROM ALUMNO;
La consulta anterior no funciona en ACCESS.
OPERADOR MINUS.
EJEMPLO 62:
DNI de personas que no son profesores ni alumnos. (Igual que el ejemplo 59).
Ver ejemplo 59.
SELECT DNI
FROM PERSONA
MINUS
(SELECT DNI
FROM PROFESOR
UNION
SELECT DNI
FROM ALUMNO);
La consulta anterior no funciona en ACCESS.
EJEMPLO 63:
Lista de asignaturas en las que no se ha matriculado nadie. (Igual que el ejemplo 55).
Ver ejemplo 55.
SELECT *
FROM ASIGNATURA
MINUS
SELECT ASIGNATURA.*
FROM ASIGNATURA, ALUMNO_ASIGNATURA
WHERE ALUMNO_ASIGNATURA.IdAsignatura = [Link];
EJEMPLO 64:
Ciudades en las que vive algún profesor y también algún alumno. (Igual que el ejemplo 56).
Ver ejemplo 56.
SELECT Ciudad
FROM PROFESOR,PERSONA
WHERE [Link] = [Link]
INTERSECT
SELECT Ciudad
FROM ALUMNO, PERSONA
WHERE [Link] = [Link];
EJEMPLO 65:
Ciudades en las que vive algún profesor pero ningún alumno. (Igual que el ejemplo 57).
Ver ejemplo 57.
SELECT Ciudad
FROM PROFESOR,PERSONA
WHERE [Link] = [Link]
MINUS
SELECT Ciudad
FROM ALUMNO, PERSONA
WHERE [Link] = [Link];
FUNCIONES DE AGRUPACIÓN
SUM - Calcula la suma de los valores de una columna.
AVG - Calcula la media de los valores de una columna.
MAX - Calcula el máximo de los valores de una columna.
MIN - Calcula el mínimo de los valores de una columna.
COUNT - Calcula número de filas de una columna.
EJEMPLO 5:
Mostrar la suma de todos los créditos de las asignaturas.
SELECT SUM(Creditos)
FROM ASIGNATURA;
EJEMPLO 6:
Media de coste básico.
SELECT AVG(CosteBasico)
FROM ASIGNATURA;
EJEMPLO 7:
Coste básico de la asignatura más cara.
SELECT MAX(CosteBasico)
FROM ASIGNATURA;
EJEMPLO 8:
Coste básico de la asignatura más barata.
SELECT MIN(CosteBasico)
FROM ASIGNATURA;
EJEMPLO 9:
Cuantos costes básicos hay.
SELECT COUNT(CosteBasico)
FROM ASIGNATURA;
EJEMPLO 10:
Cuantos costes básicos distintos hay.
SELECT COUNT(DISTINCT(CosteBasico))
FROM ASIGNATURA;
Nota: Válido para ORACLE, no para ACCESS.
EJEMPLO 11:
Cuantas asignaturas hay.
SELECT COUNT(*)
FROM ASIGNATURA;
RENOMBRAMIENTO DE CAMPOS
Podemos renombrar un campo con la instrucción AS en ACCESS o
dejando un espacio en blanco en ORACLE.
EJEMPLO 12:
Suma de los créditos de las asignaturas llamando a la columna
resultante "TotalCreditos".
ACCESS:
SELECT SUM(Creditos) AS "TotalCreditos"
FROM ASIGNATURA;
ORACLE:
SELECT SUM(Creditos) "TotalCreditos"
FROM ASIGNATURA;
SENTENCIA DE AGRUPACIÓN GROUP BY
Para agrupar valores utilizaremos la clausula GROUP BY.
EJEMPLO 41:
Cuantos alumnos hay matriculados en cada asignatura.
SELECT IdAsignatura, COUNT(IdAlumno) AS NUM
FROM ALUMNO_ASIGNATURA
GROUP BY IdAsignatura;
Nota: Todos los campos que aparecen en el SELECT junto a una orden de agrupación deben aparecer en el GROUP BY.
EJEMPLO 41 B:
Idem que el ejemplo 41 pero mostrando el nombre de cada asignatura.
SELECT Nombre, COUNT(IdAlumno) AS NUM
FROM ASIGNATURA, ALUMNO_ASIGNATURA
WHERE [Link] = ALUMNO_ASIGNATURA.IdAsignatura
GROUP BY [Link], Nombre;
EJEMPLO 42:
Cual es el coste básico total en cada titulación. Crear otra variante mostrando el nombre de la titulación.
SELECT SUM(CosteBasico) AS COSTE_TOTAL
FROM ASIGNATURA
GROUP BY IdTitulacion;
SELECT [Link] AS NOMBRE_TIT, SUM(CosteBasico) AS COSTE_TOTAL
FROM ASIGNATURA, TITULACION
WHERE [Link] = [Link]
GROUP BY [Link];
Nota: Si en el enunciado aparece la palabra cada va ir asociada a GROUP BY y si aparece la palabra en total va ir
asociada a SUM.
EJEMPLO 43:
Supongamos que cada matrícula adicional supone un 10% de aumento sobre el coste básico, es decir un 10% en 2ª
matrícula, un 20% en 3ª matrícula, etc.. Mostrar cuanto ha pagado cada alumno en total por su matrícula.
SELECT IdAlumno, SUM(CosteBasico*(1+(NumeroMatricula-1)*0.1)) AS CosteTotalMatricula
FROM ASIGNATURA, ALUMNO_ASIGNATURA
WHERE [Link] = ALUMNO_ASIGNATURA.IdAsignatura
GROUP BY IdAlumno;
CONDICIONES SOBRE EL AGRUPAMIENTO - HAVING
Para filtrar los registros combinados por GROUP BY debemos utilizar la sentencia HAVING que funciona de una manera
similar a como lo hace la sentencia WHERE con los registros construidos por la sentencia FROM.
EJEMPLO 44:
Coste medio de las asignaturas de cada titulación, para aquellas titulaciones en el que el coste total de la 1ª matrícula sea
mayor que 60 euros.
SELECT IdTitulacion, AVG(CosteBasico) AS CosteMedio
FROM ASIGNATURA
GROUP BY IdTitulacion
HAVING SUM(CosteBasico) > 60;
EJEMPLO 45:
Id de los alumnos matriculados en las asignaturas "150212" y "130113" a la vez.
SELECT IdAlumno
FROM ALUMNO_ASIGNATURA
WHERE IdAsignatura = "150212" OR IdAsignatura = "130113"
GROUP BY IdAlumno
HAVING COUNT(IdAlumno) = 2;
EJEMPLO 46:
Id de los alumnos matriculados en las asignatura "150212" ó "130113", en una o en otra pero no en ambas a la vez.
SELECT IdAlumno
FROM ALUMNO_ASIGNATURA
WHERE IdAsignatura = "150212" OR IdAsignatura = "130113"
GROUP BY IdAlumno
HAVING COUNT(IdAlumno) = 1;
