Subconsultas y ejercicios

Las subconsultas 

• Una subconsulta es una sentencia SELECT que aparece dentro de otra sentencia SELECT que llamaremos consulta principal.

•  Una subconsulta tiene la misma sintaxis que una sentencia SELECT normal exceptuando que aparece encerrada entre paréntesis, no puede contener la cláusula ORDER BY, ni puede ser la UNION de varias sentencias SELECT, además tiene algunas restricciones en cuanto a número de columnas según el lugar donde aparece en la consulta principal.

Anidar subconsultas 

Las subconsultas pueden anidarse de forma que una subconsulta aparezca en la clausula WHERE(por ejemplo) de otra subconsulta que a su vez forma parte de otra consulta principal. En la practica, una consulta consume mucho mas tiempo y memoria cuando se incrementa el numero de niveles de anidamiento. La consulta resulta también mas difícil de leer, comprender y mantener cuando contiene mas de uno o dos niveles de subconsultas. 

Ejemplo: 

      SELECT numemp, nombre
FROM empleados
WHERE numemp=(SELECT rep FROM pedidos WHERE clie=(SELECT numclie FROM clientes WHERE nombre ='Julia ramona'))

      En este ejemplo, por cada linea de pedido se calcula la subconsulta de clientes, y esto se repite por cada empleado, en el caso de tener 10 filas de empleados y 200 filas de pedidos(Tablas realmente pequeñas), la subconsulta mas interna se ejecutaría 2000 veces(10x200)

Las condiciones asociadas a las subconsultas son las siguientes:

El test de comparación con subconsulta

el test ANY 

el test ALL 

el test IN

el test EXIST 

1. Ejercicios:

1. Listar los nombres de los clientes que tienen asignado el representante Alvaro Jaumes (suponiendo que no pueden haber representantes con el mismo nombre).
2. Listar los vendedores (numemp, nombre, y nº de oficina) que trabajan en oficinas "buenas" (las que tienen ventas superiores a su objetivo).
3. Listar los vendedores que no trabajan en oficinas dirigidas por el empleado 108.
4. Listar los productos (idfab, idproducto y descripción) para los cuales no se ha recibido ningún pedido de 25000 o más.
5. Listar los clientes asignados a Ana Bustamante que no han remitido un pedido superior a 3000 pts.

6. Listar las oficinas en donde haya un vendedor cuyas ventas representen más del 55% del objetivo de su oficina.

7. Listar las oficinas en donde todos los vendedores tienen ventas que superan al 50% del objetivo de la oficina.

8. Listar las oficinas que tengan un objetivo mayor que la suma de las cuotas de sus vendedores.

         Soluciones: 

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

2-

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

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

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

3-

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

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

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

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

4-

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

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);

6-

 SELECT * FROM oficinas
WHERE EXISTS ( SELECT * FROM empleados WHERE ventas > objetivo * 0.55);

7-

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

Solución 1 SELECT * FROM oficinas
WHERE ((objetivo * 0.5) <= ALL ( SELECT ventas FROM empleados WHERE empleados.oficina = oficinas.oficina  ) ) 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);

8. 

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

Base de datos: