Solución ejercicios tema 5. Las subconsultas

Ejercicio 1

SELECT nombre
FROM clientes
WHERE repclie = (SELECT numemp FROM empleados WHERE nombre = 'Alvaro Jaumes' );

Hemos supuesto que no pueden haber dos empleados con el mismo nombre, de lo contrario habría que añadir ANY antes de la subconsulta.

Ejercicio 2

Solución 1
SELECT numemp, nombre, oficina
FROM empleados
WHERE oficina IN ( SELECT oficina FROM oficinas WHERE ventas > objetivo );

Con esta solución buscamos que la oficina del empleado esté en la lista de oficinas que tienen ventas superiores a su objetivo.

Solución 2
SELECT numemp, nombre, oficina
FROM empleados
WHERE EXISTS ( SELECT * FROM oficinas WHERE empleados.oficina = oficinas.oficina AND ventas > objetivo );

Con esta solución buscamos que exista una oficina igual al del empleado y que tenga ventas superiores a su objetivo. El resultado será el mismo que con la solución 1.

Solución 3
SELECT numemp, nombre, oficina
FROM empleados
WHERE oficina = ANY ( SELECT oficina FROM oficinas WHERE ventas > objetivo );

Con esta otra comparamos la oficina del empleado con cada una de las oficinas que tengan ventas superiores a su objetivo, si la oficina del empleado es igual a alguna de esas oficinas aparece el empleado en el resultado. El resultado será el mismo que con la solución 1.

Ejercicio 3

Solución 1
SELECT numemp, nombre, oficina
FROM empleados
WHERE NOT EXISTS ( SELECT * FROM oficinas WHERE empleados.oficina = oficinas.oficina AND dir = 108);

Obtenemos los empleados tales que no exista una oficina igual a la suya que además esté dirigida por el empleado 108, con esta solución sí aparecen los empleados que no tienen oficina.

SELECT numemp, nombre, oficina
FROM empleados
WHERE oficina NOT IN ( SELECT oficina FROM oficinas WHERE dir = 108);

Con la subconsulta obtenemos la lista de las oficinas dirigidas por el empleado 108. Al final se obtienen los empleados cuya oficina no esté en esa lista, es decir salen los empleados asignados a una oficina no dirigida por el 108. Pero no salen los empleados que no tienen oficina asignada ya que su campo oficina es nulo por lo que el resultado de la comparación es nulo, no es verdadero y no se seleccionan. El problema se puede arreglar indicando que también se tienen que seleccionar los empleados con oficina nula:

Solución 2
SELECT numemp, nombre, oficina
FROM empleados
WHERE ( oficina NOT IN ( SELECT oficina FROM oficinas WHERE dir = 108) ) OR ( oficina IS NULL);

Con la subconsulta obtenemos la lista de las oficinas dirigidas por el empleado 108. Al final se obtienen los empleados cuya oficina no esté en esa lista. Pero no salen los empleados que no tienen oficina asignada ya que su campo oficina es nulo por lo que el resultado de la comparación es nulo, no es verdadero y no se seleccionan.

SELECT numemp, nombre, oficina
FROM empleados
WHERE oficina <> ALL ( SELECT oficina FROM oficinas WHERE dir = 108);

Con esta solución tenemos el mismo problema que con NOT IN , cuando la oficina del empleado es nula todos los resultados de las comparaciones individuales son nulos por los que el test ALL da nulo y no se seleccionan los empleados con oficina nula.

Ejercicio 4

SELECT idfab, idproducto, descripcion
FROM productos
WHERE NOT EXISTS (SELECT * FROM pedidos WHERE fab = idfab AND producto = idproducto AND importe >= 25000);

En este caso es más cómodo utilizar NOT EXISTS ya que hay que preguntar por el idfab e idproducto a la vez.

Ejercicio 5

SELECT numclie, nombre
FROM clientes
WHERE repclie IN ( SELECT numemp FROM empleados WHERE nombre = 'Ana Bustamante' )
AND numclie NOT IN ( SELECT clie FROM pedidos WHERE importe > 3000 AND clie IS NOT NULL);

Como utilizamos NOT IN debemos asegurarnos de que la subconsulta no devuelva nulos. En este caso clie es un campo de pedidos que admite nulos por lo que tenemos que añadir en el WHERE de la subconsulta AND clie IS NOT NULL.

Ejercicio 6

SELECT *
FROM oficinas
WHERE EXISTS ( SELECT * FROM empleados WHERE empleados.oficina=oficinas.oficina AND ventas > objetivo * 0.55);

En una subconsulta todos los campos no cualificados se presuponen de la tabla origen de la subconsulta y sólo si no existe ninguna columna con ese nombre, la considera como referencia externa, por eso no es necesario cualificar ventas porque interpreta que es el campo ventas de la tabla empleados.

Ejercicio 7

SELECT *
FROM oficinas
WHERE (objetivo * 0.5) <= ALL ( SELECT ventas FROM empleados WHERE empleados.oficina = oficinas.oficina AND ventas IS NOT NULL);

Si un empleado no tiene ventas queremos que no sea tomado en cuenta, por eso añadimos AND ventas IS NOT NULL. Además esta solución no vale porque salen las oficinas que no tienen empleados.
Hay que añadir una condición para que se consideren sólo las oficinas con empleados como muestra la solución 1.

Solución 1
SELECT *
FROM oficinas
WHERE ((objetivo * 0.5) <= ALL ( SELECT ventas FROM empleados WHERE empleados.oficina = oficinas.oficina AND ventas IS NOT NULL) )
AND ( EXISTS ( SELECT * FROM empleados WHERE empleados.oficina = oficinas.oficina ) );

Solución 2
SELECT *
FROM oficinas
WHERE (objetivo * .5) <= (SELECT MIN(ventas) FROM empleados WHERE empleados.oficina = oficinas.oficina);

Esta es otra posible solución, calculamos la menor venta de los empleados de la oficina y si esta es mayor que el 50% del ojetivo de la oficina quiere decir que todos los empleados de esa oficina tienen ventas iguales o superiores. Si la oficina no tiene empleados, la subconsulta no devuelve ninguna fila y como estamos utilizando una comparación simple el resultado es nulo, luego no salen las oficinas que no tienen empleados. Esta solución es mucho más corta y elegante.

Ejercicio 8

SELECT *
FROM oficinas
WHERE objetivo > ( SELECT SUM(cuota) FROM empleados WHERE empleados.oficina = oficinas.oficina);