Databases

T01-011-02 Subconsultas en cláusulas distintas al WHERE (opcional)

Enunciado

Como dijimos anteriormente, podemos utilizar subconsultas en cláusulas como HAVING o FROM.

La utilización de una subconsulta en una clásula HAVING, no difiere de la ya explicada en la cláusula WHERE, por lo que nos centraremos, en este apartado a su utilización en la clásula FROM.

Subcobsultas en FROM.

Recordemos que, en la clásula FROM, debemos hacer referencia a las tablas que vamos a utilizar en la consulta.

...
FROM HR.employees JOIN HR.departments USING...

En el ejemplo anterior employees y departments son tablas del esquema HR.

Observemos también que el resultado de las sentencias SELECT se muestran en formato tabla, lo que las convierte en objetos que se pueden referenciar en la cláusula FROM, siempre y cuando le asignemos un alias de tabla.

Ejemplo:

Vamos a construir una sentencia que muestre los empleados que tienen una antigüedad mayor que los mejor pagados de su departamento.

Igual que haría cualquier persona que no dispusiera de una base de datos, buscaría en primer lugar quiénes son los mejor pagados de cada departamento y cuál es su fecha de contratación:

SELECT FIRST_NAME, DEPARTMENT_ID, HIRE_DATE
FROM HR.employees
WHERE (DEPARTMENT_ID, SALARY) IN (
    SELECT DEPARTMENT_ID, MAX(SALARY)
    FROM HR.EMPLOYEES
    GROUP BY DEPARTMENT_ID
)
ORDER BY DEPARTMENT_ID

El resultado sería una tabla como la siguiente:

FIRST_NAME DEPARTMENT_ID HIRE_DATE
Jennifer 10 17-SEP-03
Michael 20 17-FEB-04
Den 30 07-DEC-02
... ... ...

En contenidos posteriores del curso, veremos que esta tabla la podríamos incluso guardar en la base de datos. De momento, únicamente le vamos a poner un alias (MejorPagados), para poder referenciarla.

Disponiendo de esa tabla, ya podemos buscar a los empleados con una antigüedad mayor que los MejorPagados de su departamento:

SELECT DISTINCT EMP.FIRST_NAME, EMP.LAST_NAME, EMP.DEPARTMENT_ID, EMP.HIRE_DATE
FROM HR.EMPLOYEES EMP JOIN  MEJORPAGADOS ON (EMP.DEPARTMENT_ID = MEJORPAGADOS.DEPARTMENT_ID)
WHERE EMP.HIRE_DATE < MEJORPAGADOS.HIRE_DATE
ORDER BY EMP.DEPARTMENT_ID, EMP.LAST_NAME

Ya! ya! Es cierto que si ejecutamos la consulta anterior, el resultado sería: ORA-00942: table or view does not exist

Lo único que nos queda hacer es poner la consulta que utilizamos para obtener la tabla de los MejorPagados, como la subconsulta que define dicha tabla:

SELECT DISTINCT EMP.FIRST_NAME, EMP.LAST_NAME, EMP.DEPARTMENT_ID, EMP.HIRE_DATE
FROM HR.EMPLOYEES EMP JOIN (
    SELECT FIRST_NAME, DEPARTMENT_ID, HIRE_DATE
    FROM HR.employees
    WHERE (DEPARTMENT_ID, SALARY) IN (
        SELECT DEPARTMENT_ID, MAX(SALARY)
        FROM HR.EMPLOYEES
        GROUP BY DEPARTMENT_ID
    )
    ORDER BY DEPARTMENT_ID
) MEJORPAGADOS ON (EMP.DEPARTMENT_ID = MEJORPAGADOS.DEPARTMENT_ID)
WHERE EMP.HIRE_DATE < MEJORPAGADOS.HIRE_DATE
ORDER BY EMP.DEPARTMENT_ID, EMP.LAST_NAME

Ahora sí se obtienen los datos que buscábamos:

FIRST_NAME LAST_NAME DEPARTMENT_ID HIRE_DATE
Sarah Bell 50 04-FEB-04
Alexis Bull 50 20-FEB-05
... ... ... ...
David Austin 60 25-JUN-05
Ellen Abel 80 11-MAY-04
Janette King 80 30-JAN-04
... ... ... ...

Las posibilidades de anidar subconsultas son infinitas (o casi) y nos permiten resolver consultas acercándonos poco a poco a la solución.

Ejercicio:

En el esquema HR aparecen empleados cuyo salario coincide con el mínimo establecido para el trabajo que realizan. A la consulta correspondiente a estos empleados la vamos a nombrar como MinimamentePagados.

Averigua quiénes son los jefes de los departamentos en los que trabajan estos empleados por si se les solicitara que les incrementaran el salario. Ordena a estos jefes de departamento según el nombre de su departamento.

Tipo

SELECT

Solución


												
SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_NAME
FROM HR.EMPLOYEES JOIN HR.DEPARTMENTS ON(EMPLOYEES.EMPLOYEE_ID = DEPARTMENTS.MANAGER_ID) JOIN (
    SELECT DISTINCT JOB_TITLE, DEPARTMENT_ID
    FROM HR.EMPLOYEES JOIN HR.JOBS USING(JOB_ID)
    WHERE SALARY = MIN_SALARY
) MINIMAMENTEPAGADOS ON (DEPARTMENTS.DEPARTMENT_ID = MINIMAMENTEPAGADOS.DEPARTMENT_ID)
ORDER BY DEPARTMENT_NAME

Prueba

Librerías (Onfly databases)