Subconsultas 2

/*PAG 123 EMILIA HA DICHO QUE LAS CONSULTAS NO PUEDEN ESTAR EN LA MISMA LINEA*/

/*Pregunta de emilia, sacar los empleados cuyo salario sea igual que el minimo del departamento*/
SELECT EMP_NO, APELLIDO
FROM EMPLE
WHERE SALARIO = (SELECT MIN(SALARIO) FROM EMPLE WHERE DEPT_NO=30);

/*En uno de los departamentos deberiamos tener 2 empleados con el salario mínimo, si no lo tenemos insertamos un nuevo empleado"/

/*Hacer select del minimo salario de cada departamento con GROUP BY*/
SELECT DEPT_NO, MIN(SALARIO)
FROM EMPLE
GROUP BY DEPT_NO;

/*pedimos todos los empleados que tengan el minimo salario en cada departamento, incluimos IN para que devuelva mas de un valor*/
SELECT *
FROM EMPLE
WHERE (DEPT_NO, SALARIO)
IN (SELECT DEPT_NO, MIN(SALARIO) FROM EMPLE GROUP BY DEPT_NO);

/*SUBCONSULTA CORRELACIONADA (apartado D): pedimos lo mismo que en la anterior pero de distinta forma, en el libro pide el maximo, pero yo soy mas chulo =)*/
SELECT *
FROM EMPLE E
WHERE SALARIO = (SELECT MIN(SALARIO) FROM EMPLE WHERE DEPT_NO = E.DEPT_NO);
/*debemos poner un alias E a la tabla EMPLE y así al comparar el numero de departamento lo compara con el numero de departamento del empleado*/

/*EJERCICIO 12 PAG. 121 - CONSULTAR DATOS de empleados que trabajen en madrid y barcelona*/

/*primero seleccionamos los numeros de dept que esten en madrid y barcelona*/
SELECT DEPT_NO
FROM DEPART
WHERE LOC IN ('MADRID', 'BARCELONA');

/*seleccionamos los datos de los empleados de los 2 departamentos que nos da la consulta anterior*/
SELECT *
FROM EMPLE
WHERE DEPT_NO IN(20,30);

/*unimos las dos consultas anteriores poniendo la primera como subconsulta de la segunda*/
SELECT *
FROM EMPLE
WHERE DEPT_NO IN (SELECT DEPT_NO FROM DEPART WHERE LOC IN ('MADRID', 'BARCELONA'));

/*apellidos y oficios de todos los empleados del departamento 20 cuyo trabajo sea idéntico al de cualquiera
de los empleados del departamento 'VENTAS'*/
SELECT APELLIDO, OFICIO
FROM EMPLE
WHERE DEPT_NO=20 AND OFICIO IN (SELECT OFICIO  FROM EMPLE  WHERE DEPT_NO =(SELECT DEPT_NO FROM DEPART WHERE DNOMBRE='VENTAS'));

/*apellido de los empleados con el mismo oficio y salario que 'GIL'. En esta consulta se introduce una variante:
hasta ahora las subconsultas nos devolvían una columna, aunque pueden devolver más de una. En este caso, la subconsulta
devuelve dos columnas, el oficio y el salario*/
SELECT APELLIDO, SALARIO
FROM EMPLE
WHERE (OFICIO, SALARIO) = (SELECT OFICIO, SALARIO FROM EMPLE WHERE APELLIDO ='GIL');
/*si tenemos 2 gil en la tabla nos dará error al devolvernos varios valores,
podemos borrar uno de los gil para que no de error. OTRA SOLUCION: podemos no borrar gil y poner una clausula ANY para que seleccione cualquiera de los gil*/
DELETE FROM EMPLE WHERE EMP_NO=7788;

/*ahora provamos esta select con solo 1 gil para ver que nos devuelve los mismos datos que la anterior consulta*/
SELECT APELLIDO, SALARIO FROM EMPLE
 WHERE OFICIO= (SELECT OFICIO FROM EMPLE WHERE APELLIDO ='GIL')
 AND SALARIO= (SELECT SALARIO FROM EMPLE WHERE APELLIDO ='GIL');
/*insertaremos de nuevo un gil para ver las consecuencias, basicamente volverá a dar el mismo error usemos cualquiera de las 2 consultas.*/
INSERT INTO EMPLE VALUES(5656,'GIL','ANALISTA',3333,'12/12/12',3000,1,20);

/*probamos las 2 select de antes con  ANY a ver si sale bien*/
SELECT APELLIDO, SALARIO
FROM EMPLE
WHERE (OFICIO, SALARIO) = ANY (SELECT OFICIO, SALARIO FROM EMPLE WHERE APELLIDO ='GIL');


SELECT APELLIDO, SALARIO FROM EMPLE
 WHERE OFICIO= ANY (SELECT OFICIO FROM EMPLE WHERE APELLIDO ='GIL')
 AND SALARIO= ANY (SELECT SALARIO FROM EMPLE WHERE APELLIDO ='GIL');
 /*si un empleado tiene el mismo salario que un gil y el empleo de otro gil saldrá en la lista, y no deberia salir*/