===== Ejercicios SQL de parcial y final =====

Se agradece a Pablo Musumeci, Martín Buchwald y Florencia Bosch que contribuyeron en la resolución de muchos de estos ejercicios.

==== Punto I ====

Dado el esquema

<code>Vuelo(VueloNro, Desde, Hacia, Distancia)
Aeronave (aID, aNombre, Rango)
Certificado(eID, aID)
Empleado(eID, eNombre, Sueldo)</code>

La relación ''Empleado'' contiene datos de todos los empleados de la compañía. En la relación ''Certificado'' solo figuran los pilotos certificados para volar una determinada aeronave.
Listar los nombres y sueldos de los empleados que no son pilotos pero que su sueldo es superior al promedio de los pilotos.


==== Punto II ====

Dado el esquema

<code>Vuelo(VueloNro, Desde, Hacia, Distancia)
Aeronave (aID, aNombre, Rango)
Certificado(eID, aID)
Empleado(eID, eNombre, Sueldo)</code>

La relación ''Empleado'' contiene datos de todos los empleados de la compañía, entre ellos los pilotos. En la relación ''Certificado'' solo figuran los pilotos certificados para volar una determinada aeronave.
Listar los nombres de los empleados que son certificados solo en aviones con rango de crucero superior a 2000 km. y al menos en un avión Boeing.

==== Punto III ====

Dado el siguiente esquema de base de datos:

<code>P_Info(NroParte, SubparteDe, Descripcion)
UsadoEn(NroParte, TipoAvion, CantUsa)
EnStock(NroParte, Ciudad, Cantidad)</code>

¿Para cuáles partes el total en stock es al menos la cantidad usada por un B737, y por qué monto el total excede la cantidad usada?

==== Punto IV ====

Dado el siguiente esquema de base de datos:

<code>
Estudiante(eNro, eNombre, carrera, anioCursada, edad)
Curso(cNombre, horario, aula, pID)
Cursa(eNro, cNombre)
Profesor(pID, pNombre, departamento)
</code>

Hallar los nombres de los estudiantes que cursan el máximo número de cursos.

==== Punto V ====

Dado el siguiente esquema de base de datos:

<code>
Encuentros(nroEncuentro, fecha, nombreHipo)
Carreras(nroEncuentro, nroCarrera, tipoPista, estadoPista)
Caballos(nombreCaballo, fechaNac, pelaje)
Corredores(nroEncuentro, nroCarrera, nombreCaballo, nroPosicion)
</code>

Obtener los datos de aquellos caballos que hayan conseguido llegar a la meta primero en más de 3 oportunidades.

==== Punto VI ====

Dado el siguiente esquema de base de datos:

<code>
Encuentros(nroEncuentro, fecha, nombreHipo)
Carreras(nroEncuentro, nroCarrera, tipoPista, estadoPista)
Caballos(nombreCaballo, fechaNac, pelaje)
Corredores(nroEncuentro, nroCarrera, nombreCaballo, nroPosicion)
</code>

Obtener la cantidad de veces que, en carreras de San Isidro, hayan competido los caballos //Matuk// y //Laca// juntos.

==== Punto VII ====

Dado el siguiente esquema de base de datos:

<code>
Estudiante(eNro, eNombre, carrera, anioCursada, edad)
Curso(cNombre, horario, aula, pID)
Cursa(eNro, cNombre)
Profesor(pID, pNombre, departamento)
</code>

Escribir una consulta SQL que permita, para cada valor de edad que aparece en **Estudiante**, hallar el valor de año que aparece más frecuentemente. Por ejemplo, si hay más estudiantes de 20 años de edad en 2do año que en cualquier otro año para estudiantes de 20 años, listar el par **(20,2do)**. Nota: No deben listarse duplicados.

==== Punto VIII ====

Dada la relación 
<code>R(A)</code>

usando SQL convencional escribir una consulta para hallar el valor de la mediana de A (el valor tal que la mitad de los valores son más grandes y la mitad son
más chicos). Considerar que no hay duplicados de A en R, que la cantidad de elementos es impar y no se permiten nulos.

==== Punto XIX ====

Dada la relación 
<code>R(A)</code>

siendo A números que pueden estar repetidos y ninguno es NULL escribir una consulta SQL que devuelva la moda de A (el valor más frecuente).

==== Punto X ====

Sean los siguientes esquemas relacionales:

<code>
Estudiante (Enro, Enombre, Carrera, Anio_cursa, Edad)
Curso (Cnombre, Horario, Aula, Pid)
Cursa (Enro, Cnombre)
Profesor(Pid, Pnombre, departamento)
</code>

Hallar los nombres de todos los profesores que enseñan en todas las aulas en las que se dicta algún curso.

==== Punto XI ====

Dada la consulta sobre R(A, B, C, D), asumiendo que A, B, C y D son enteros:

<code sql>
SELECT […]
FROM R
GROUP BY A,B;
</code>

¿Cuál de las siguientes puede aparecer en la posición marcada como [...]?

<code>
I. Min(C+D)
II. A,B
III. C,D
</code>

a) II solamente

b) I y II solamente

c) I, II y III

d) Ninguno de los anteriores

==== Punto XII ====

Sean las consultas:
Q1:

<code sql>
 SELECT a FROM R r1 WHERE EXISTS ( SELECT * FROM R WHERE a = r1.b );
</code>

Q2:
<code sql>
 SELECT a FROM R WHERE b =  ANY ( SELECT a FROM R );
</code>

¿Cuál de las siguientes opciones es verdadera?

a) Los resultados son iguales.

b) El resultado de Q1 está siempre contenido en el resultado de Q2.

c) El resultado de Q2 está siempre contenido en el resultado de Q1.

d) Los resultados son distintos.

Observación: Pueden haber NULLs y filas duplicadas.

==== Punto XIII ====

Dada la relación Lista(e,i) donde ’e’ (elemento) es un entero, no se repite y no es NULL, e ’i’ es la posición en la lista, se pide hacer una consulta SQL para hallar la mediana de “e” (la mediana es un valor que indica que la mitad de los números son mayores que la mediana y la otra mitad es menor). Se puede asumir que la cantidad de tuplas es impar.

===== Resolución =====

En todos los casos la resolución debe ser tan concisa como resulte posible.

==== Punto I ====
<code sql>
SELECT e.eNombre, e.Sueldo
FROM Empleado e
WHERE e.eID NOT IN (SELECT c.eid
                    FROM Certificado c) 
      AND e.Sueldo > (SELECT AVG(e2.Sueldo) 
                      FROM Empleado e2 
                      WHERE e2.eID IN (SELECT c2.eID 
                                       FROM Certificado c2));
</code>
                                       
==== Punto II ====

**Hipótesis**: el atributo **Rango** dentro de **Aeronave** ya está en kilómetros.

<code sql>
SELECT e.ENombre
FROM Empleado e
WHERE e.EId NOT IN (SELECT c.EId
                   FROM Certificado c, Aeronave a
                   WHERE c.Aid = a.Aid and a.Rango <= 2000)
     AND e.Eid IN (SELECT c.EId
                   FROM Certificado c, Aeronave a
                   WHERE c.Aid = a.Aid and a.Anombre = 'Boeing');
</code>

==== Punto III ====

**Hipótesis**: un mismo producto puede tener stock en varias Ciudades.

**Hipótesis**: la clave de **UsadoEn** es <NroParte,TipoAvion>.

[[http://sqlfiddle.com/#!2/03f07a/1|SQL Fiddle]]

<code sql>
SELECT E.NroParte, SUM(E.Cantidad) - U.CantUsa AS 'EXCEDENTE'
FROM EnStock E, UsadoEn U
WHERE E.NroParte = U.NroParte AND
       U.TipoAvion = 'B737'
GROUP BY E.NroParte, U.CantUsa
HAVING SUM(E.Cantidad) >= U.CantUsa;
</code>

==== Punto IV ====

[[http://sqlfiddle.com/#!2/95a3a/1|SQL Fiddle]]

<code sql>
SELECT DISTINCT e.eNombre
FROM Estudiante e
WHERE e.eNro IN (SELECT c.eNro
                 FROM Cursa c
                 GROUP BY c.eNro
                 HAVING COUNT(C.Cnombre) >= ALL (SELECT COUNT(C.Cnombre)
                                                 FROM Cursa c2
                                                 GROUP BY c2.eNro));
</code>                    
                                                 
==== Punto V ====

[[http://sqlfiddle.com/#!2/3d4657/1|SQL Fiddle]]

<code sql>
SELECT *
FROM Caballos
WHERE NombreCaballo IN (SELECT NombreCaballo 
			FROM Corredores
			WHERE NroPosicion = 1
			GROUP BY NombreCaballo
			HAVING COUNT(*) >= 3);
</code> 

==== Punto VI ====

<code sql>
SELECT COUNT(*)
FROM Encuentros e, Corredores c1, Corredores c2
WHERE e.nroEncuentro = c1.nroEncuentro AND
      c1.nroEncuentro = c2.nroEncuentro AND
      e.nombreHipo = 'San Isidro' AND
      c1.nroCarrera = c2.nroCarrera AND
      c1.nombreCaballo = 'Matuk' AND
      c2.nombreCaballo = 'Laca';
</code>                       

==== Punto VII ====

<code sql>
SELECT e.edad, e.anioCursada
FROM Estudiante e
GROUP BY e.edad, e.anioCursada
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
                        FROM Estudiante e2
                        WHERE e2.edad = e.edad
                        GROUP BY e2.anioCursada);                    
</code>

==== Punto VIII ====

[[http://sqlfiddle.com/#!2/825cc1/1|SQL Fiddle]]

<code sql>
-- Returns Median(R)
SELECT A
FROM R R1 
WHERE ((SELECT COUNT(A) FROM R R2 WHERE R1.A > R2.A) = 
       (SELECT COUNT(A) FROM R R2 WHERE R1.A < R2.A)); 
</code>      

==== Punto XIX ====

[[http://sqlfiddle.com/#!2/cf8221/1|SQL Fiddle]]

<code sql>
-- Returns Mode(R)
SELECT A
FROM R
GROUP BY A
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
                        FROM R R1
                        GROUP BY R1.A);
</code> 

==== Punto X ====

La consulta original puede pensarse como "Hallar los nombres de todos los profesores para los cuales no exista un aula en la cual no dicten clases."

<code sql>
SELECT P.Pnombre
FROM Profesor P
WHERE NOT EXISTS (SELECT C.Aula
                  FROM Curso C1
                  WHERE NOT EXISTS (SELECT *
                                    FROM Curso C2
                                    WHERE C2.Aula = c1.Aula AND
                                          C2.Pid = P.Pid));
</code>

==== Punto XI ====

En una cláusula SELECT con GROUP BY solo pueden aparecer dos cosas:

1. TODOS los atributos del GROUP BY, y funciones agregadas

ó

2. SOLO funciones agregadas.

Así que la respuesta correcta es la (b): Min(C+D) y A,B.

==== Punto XII ====

La respuesta correcta es la (a): los resultados son iguales.

==== Punto XIII ====

**Hipótesis**: la lista está ordenada.

**Hipótesis**: la posición de los elementos se cuentan a partir del 1.

[[http://sqlfiddle.com/#!2/d0de9/1|SQL Fiddle]]

<code sql>
SELECT e
FROM Lista
WHERE i = (SELECT AVG(lista.i)
             FROM Lista lista);
</code>