Examen BBDD - SQL

--1
SELECT APELLIDO, SALARIO, DEPT_NO
FROM EMPLE
WHERE EMP_NO IN (SELECT DIR
                 FROM EMPLE) AND SALARIO>169000 ORDER BY APELLIDO DESC;
--2
SELECT APELLIDO, OFICIO, SALARIO, DEPT_NO
FROM EMPLE
WHERE DEPT_NO IN (SELECT DEPT_NO FROM EMPLE WHERE APELLIDO='GIL')
AND OFICIO IN (SELECT OFICIO FROM EMPLE WHERE APELLIDO='GIL')
AND APELLIDO!='GIL';
--3
SELECT APELLIDO, OFICIO, SALARIO, DEPT_NO
FROM EMPLE
WHERE FECHA_ALT BETWEEN '01/01/1981' AND '31/12/1981'
AND SALARIO IN (SELECT MAX(SALARIO) FROM EMPLE E, DEPART D WHERE E.DEPT_NO=D.DEPT_NO GROUP BY E.OFICIO);
--4 corregir
SELECT APELLIDO, OFICIO, SALARIO, DEPT_NO FROM EMPLE WHERE SALARIO> ANY(SELECT AVG(SALARIO) FROM EMPLE GROUP BY DEPT_NO);
--5
/*SELECT APELLIDO "<apellido_jefe>" FROM EMPLE WHERE EMP_NO IN (SELECT DIR FROM EMPLE);
SELECT APELLIDO FROM EMPLE WHERE EMP_NO NOT IN (SELECT NVL(DIR,0) FROM EMPLE);*/

SELECT DISTINCT A.APELLIDO "<apellido_jefe>", 'trabaja para', B.APELLIDO "<apellido_emple>" FROM EMPLE A, EMPLE B WHERE A.EMP_NO=B.DIR;

-6
--A)
SELECT NOMBRE FROM ASIGNATURAS
WHERE COD IN (SELECT COD FROM NOTAS)
AND COD NOT IN (SELECT COD FROM NOTAS WHERE  NOTA>=5) ORDER BY NOMBRE;
--B)
SELECT NOMBRE
FROM ASIGNATURAS S, NOTAS N, ALUMNOS U
WHERE S.cod=n.cod AND U.DNI=N.DNI AND NOTA<5
GROUP BY NOMBRE;

--7

--A)
SELECT LAST_NAME
FROM EMPLOYEES
WHERE department_id IN (SELECT DEPARTMENT_ID
                     FROM departments
                     WHERE LOCATION_ID IN (SELECT LOCATION_ID
                                        FROM locations
                                        WHERE CITY='Seattle'));
--B)
SELECT LAST_NAME
FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D
WHERE D.location_id=l.location_id AND D.department_id=e.department_id AND CITY='Seattle';
--C)
SELECT LAST_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE D.department_id=e.department_id AND d.location_id=(SELECT location_id
                                                         FROM LOCATIONS
                                                         WHERE CITY='Seattle');
--8
UPDATE EMPLE (SALARIO, COMISION)
SET SALARIO = (SELECT NVL(MIN(SALARIO),0)
                FROM EMPLE
                GROUP BY DEPT_NO), COMISION = (SELECT (MAX(COMISION)/2)
                                                FROM EMPLE)
                                                WHERE SALARIO> (SELECT NVL(AVG(SALARIO),0)
                                                               FROM EMPLE GROUP BY DEPT_NO);

--9

INSERT INTO EMPLE SELECT DISTINCT 9999, 'DIAZ', 'ANALISTA', 7566,
'03/03/2013', 2000, 120, DEPT_NO
FROM EMPLE WHERE DEPT_NO= (SELECT DEPT_NO FROM EMPLE GROUP BY DEPT_NO
HAVING COUNT(*)= (SELECT MIN(COUNT(*)) FROM EMPLE GROUP BY DEPT_NO));

--10

  DELETE FROM EMPLE
       WHERE DEPT_NO=(SELECT DEPT_NO
                      FROM EMPLE
                      WHERE SALARIO=(SELECT MAX(AVG(SALARIO))
                                     FROM EMPLE GROUP BY DEPT_NO));