Ayuda ejercicios unidad 6: Las subconsultas

1. Listar los clientes (numclie, nombre) asignados a Juan que no han remitido un pedido superior a 300 euros.

SELECT numclie, nombre

FROM clientes

WHERE repclie IN (SELECT numemp  FROM empleados

				WHERE nombre LIKE 'Juan%')

		  AND NOT EXISTS (SELECT * FROM pedidos

						WHERE numclie = clie AND importe > 300);

2. Listar los empleados (numemp, nombre) mayores de 40 años que dirigen a un vendedor con superávit (ha vendido más que su cuota).

SELECT numemp, nombre

FROM empleados

WHERE edad > 40

		and numemp IN (SELECT jefe FROM empleados WHERE ventas > cuota );

3. Listar los empleados (código de empleado) cuyo importe de pedido medio para productos fabricados por ACI es superior al importe medio global (de todos los pedidos de todos los empleados).

SELECT rep

FROM pedidos

WHERE  fab = 'ACI'

GROUP BY rep

HAVING AVG(importe) > (SELECT AVG(importe) FROM pedidos);

4. Listar los empleados (numemp, nombre, ventas) cuyas ventas son iguales o superiores al objetivo de las oficinas de una determinada ciudad (de todas las oficinas de esa ciudad). Las oficinas con objetivo nulo no se deben de tener en cuenta (como si no existiesen). Y si no hay oficinas en la ciudad no queremos que salga ningún empleado. Intentar primero resolver la consulta utilizando >=ALL.

Probar primero con A Coruña:

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas >= ALL (SELECT objetivo

			FROM oficinas

			WHERE ciudad = 'A Coruña');

Ahora con Pamplona. Pamplona tiene una oficina con objetivo nulo, en este caso no queremos que esa oficina cuente.

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas >= ALL (SELECT objetivo

			FROM oficinas

			WHERE ciudad = 'Pamplona' and objetivo is not null);

Para Barcelona. En este caso no tenemos oficinas en Barcelona por lo que no tiene que salir ningún empleado. Si no queremos que salgan tendremos que añadir una condición:

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas >= ALL (SELECT objetivo

			FROM oficinas

			WHERE ciudad = 'Barcelona' and objetivo is not null)

AND EXISTS (SELECT *

			FROM oficinas 

			WHERE ciudad = 'Barcelona');

Ahora para Madrid. Como en Madrid sólo hay una oficina y no tiene objetivo no tiene que salir ningún empleado.

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas >= ALL (SELECT objetivo

			FROM oficinas

			WHERE ciudad = 'Madrid' and objetivo is not null)

AND EXISTS (SELECT *

			FROM oficinas 

			WHERE ciudad = 'Madrid' and objetivo is not null);

Esta sería la consulta definitiva que nos serviría para cualquier situación. Pero como se ve el modificador ALL puede darnos problemas. Para solucionarlo, realizamos la siguiente consulta.

Intentar resolver la consulta sin utilizar ALL.

SELECT numemp, nombre, ventas

FROM empleados

WHERE ventas >=  (SELECT MAX(objetivo)

			         FROM oficinas

			         WHERE ciudad = 'Madrid');

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

SELECT oficina, ciudad

FROM oficinas

WHERE (objetivo * .5) < = (SELECT MIN(ventas)  

                           FROM empleados WHERE empleados.oficina = oficinas.oficina);