Uso de Subconsultas para Resolver Consultas (PL/SQL)

Usando Subconsulta para Resolver un Problema

Supongamos que se quiere escribir una consulta para saber quién gana un salario mayor que el salario de Abel.
Para resolver este problema, se necesitan dos consultas: una para ver cuánto gana Abel, y una segunda consulta para encontrar quién gana más de esa cantidad.
Este problema se puede resolver con la combinación de las dos consultas, colocando una consulta dentro de otra consulta. La consulta interna (o subconsulta) devuelve un valor que es utilizado por la consulta externa (o consulta principal).
El uso de una subconsulta es equivalente a realizar dos consultas secuenciales y usar el resultado de la primera consulta como el valor de búsqueda en la segunda consulta.

Una subconsulta es una instrucción SELECT que está en una cláusula de otra instrucción SELECT. Se pueden construir poderosas declaraciones mediante el uso de subconsultas. Pueden ser muy útil cuando se necesita seleccionar filas de una tabla con una condición que depende de los datos de la misma tabla.

Se pueden colocar subconsultas en una cláusula SQL como:

• Cláusula WHERE.
• Cláusula HAVING.
• Cláusula FROM.

En la sintaxis:
operador_comparación : corresponde a una condición de comparación que pueden ser: >, =, >=, <, <>, <= (operadores de una sola fila) o IN, ANY, ALL (operadores de múltiples filas).

-- Ejemplo

SELECT last_name, salary
FROM employees
WHERE salary > 11000       
            (SELECT salary
                FROM employees
                WHERE last_name = 'Abel');

En el ejemplo, la consulta interna o subconsulta es la primera que se resuelve y obtiene el salario del empleado Abel. Posteriormente la consulta externa obtiene a todos los empleados que posean un salario mayor al valor obtenido en la subconsulta que en este caso es 11000.

• Las subconsultas deben ser escritas entre paréntesis.
• Colocar las subconsultas en el lado derecho de la condición de comparación para mejorar la legibilidad. Sin embargo, las subconsultas pueden aparecer en cualquier lado de el operador de comparación.
• La cláusula ORDER BY de la subconsulta no es necesaria.
• Se deben utilizar operadores de una sola fila con subconsultas que retornan una sola fila y operadores de varias filas con subconsultas que retornan varias filas.

Tipos de Subconsultas

Subconsultas de una sola fila: consultas que devuelven una sola fila desde la sentencia SELECT interna.

Subconsultas de múltiples (varias) filas: consultas que devuelven más de una fila desde la sentencia SELECT interna.

NOTA: También hay subconsultas de varias columnas, que son consultas que devuelven más de una columna en la sentencia SELECT interna

Subconsultas de una Fila

Una subconsulta de una sola fila es la que devuelve una fila desde la sentencia SELECT interna. Este tipo de subconsulta utiliza un operador de una sola fila:

• = : igual que
• > : mayor que
• >= : mayor o igual que
• < : menor que



• <= : menor o igual que

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE job_id = ST_CLERK
               (SELECT job_id
                FROM employees
                WHERE employee_id = 141);

En la sentencia, se resuelve la subconsulta para obtener el trabajo del empleado 141. El valor que retorna la subconsulta (ST_CLERK) es el que utiliza la condición de comparación de la consulta principal para mostrar a los empleados que poseen el trabajo ST_CLERK (el trabajo del empleado 141).

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE job_id = ST_CLERK
               (SELECT job_id
                FROM employees
                WHERE employee_id = 141)
AND salary > 2600
             (SELECT salary
              FROM employees
              WHERE employee_id = 143);

La sentencia del ejemplo, retorna información de los empleados cuyo trabajo es igual al trabajo de empleado 141 (ST_CLERK) y su salario es mayor al salario que posee el empleado 143 (2600).

Subconsultas de una Fila y Funciones de Grupo

Se pueden mostrar datos de una consulta principal mediante el uso de una función de grupo en una subconsulta de una sola fila. La subconsulta está entre paréntesis y se coloca después de la condición de comparación.

-- Ejemplo

SELECT last_name, job_id, salary
FROM employees
WHERE salary = 2100
               (SELECT MIN(salary)
                FROM employees);
    
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > 2100
                     (SELECT MIN(salary)
                      FROM employees
                      WHERE department_id = 50)
ORDER BY MIN(salary);

En la sentencia del primer ejemplo, primero se resuelve la subconsulta que obtiene el salario mínimo que existe en la tabla EMPLOYEES (2100). El valor obtenido de la subconsulta es el que utiliza la condición de comparación de la consulta principal. Es decir, la consulta principal muestra a los empleados que poseen un salario igual a 2100. En la sentencia del segundo ejemplo, primero se resuelve la subconsulta que obtiene el salario mínimo del departamento 50 (2100). El valor obtenido de la subconsulta es el que utiliza la condición de comparación de la consulta principal. Es decir, la consulta principal muestra los departamentos que poseen un salario mínimo mayor a 2100. Como la condición de comparación está basada en el resultado de una función de grupo se debe utilizar la cláusula HAVING.

-- ¿Cuál es el error en la Sentencia?

SELECT employee_id, last_name, salary
FROM employees
WHERE salary = (SELECT MIN(salary)
                 FROM employees
                 GROUP BY department_id);

Un error común con subconsultas se produce cuando la subconsulta retorna más de una fila y la condición de comparación es de una sola fila. En la sentencia que se muestra, la subconsulta contiene una cláusula GROUP BY, lo que implica que la subconsulta devolverá varias filas, una para cada grupo que encuentre. En este caso, los resultados de la subconsulta son 4400, 6000, 2500, 4200, 7000, 17000, y 8300. La consulta externa toma esos resultados y los utiliza en su cláusula WHERE. La cláusula WHERE contiene un operador de igualdad (=) que es un operador de comparación de una sola fila por lo tanto espera sólo un valor. El operador = no puede aceptar más de un valor de la subconsulta y, por lo tanto, genera el error.

Subconsultas de Múltiples Filas

Las subconsultas que devuelven más de una fila se llaman subconsultas de varias o múltiples filas. Se deben utilizar operadores de varias filas, en lugar de un operador de una sola fila, con una subconsulta de varias filas ya que esperan uno o más valores para comparar.

• IN : igual a cualquier miembro de la lista.
• ANY : compara el valor con cada valor devuelto por la subconsulta.
• ALL : compara el valor con todos los valores devueltos por la subconsulta.

-- Ejemplo

SELECT employee_id, job, salary
FROM employees
WHERE salary IN 2100,2500,4200,4400, … ,17000
             (SELECT MIN(salary)
              FROM employees
              GROUP BY department_id);

En el ejemplo, para poder obtener los empleados cuyo salario sea igual a alguno de los salarios mínimos que existen en cada departamento se debe utilizar el operador de comparación IN.

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE salary < ANY 9000,6000,4800 y 4200
               (SELECT DISTINCT salary
                FROM employees
                WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'
ORDER BY employee_id;

En la sentencia primero se resuelve la subconsulta que obtiene los diferentes salarios entre los empleados que poseen el trabajo IT_PROG (9000, 6000, 4800 y 4200). Por lo tanto la consulta principal retorna a los empleados que posean un salario menor a cualquiera de los salarios obtenidos en la subconsulta y que además posean un trabajo distinto a IT_PROG.

-- Ejemplo

SELECT employee_id, job_id, salary
FROM employees
WHERE salary < ALL 9000,6000,4800 y 4200
               (SELECT DISTINCT salary
                FROM employees
                WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG'
ORDER BY employee_id;

En la sentencia primero se resuelve la subconsulta que obtiene los diferentes salarios entre los empleados que poseen el trabajo IT_PROG (9000, 6000, 4800 y 4200). Por lo tanto la consulta principal retorna a los empleados que posean un salario menor a todos los salarios obtenidos en la subconsulta y que además posean un trabajo distinto a IT_PROG.

Guia:

/* 1.- La empresa Norteamericana "FACE TO FACE" lo ha contratado a Ud. para desarrollar su nuevo Sistema de Personal.  Como primera etapa se 
requiere que, a partir del  Modelo que se presenta, pueda satisfacer los requerimientos de información que se solicitan a continuación: 
  a) Se ha detectado que dado el trabajo que desempeñan, existen empleados que no les corresponde percibir el mismo salario del empleado 204. 
  Dado esto, se requiere saber la identificación, nombre (nombre y apellido concatenados), trabajo y el salario de estos empleados (incluyendo 
  al empleado 204). La información se debe  mostrar como se visualiza en el ejemplo:  */

SELECT EMPLOYEE_ID "IDENTIFICACION EMPLEADO", FIRST_NAME || ' ' || LAST_NAME EMPLEADO, JOB_ID, SALARY SALARIO 
FROM EMPLOYEES
WHERE SALARY = (SELECT SALARY
                FROM EMPLOYEES
                WHERE EMPLOYEE_ID = 204);
                
  /* b) La empresa desea mejorar la situación económica de los empleados cuyo salario es menor al salario promedio entre todos los empleados. 
  Inicialmente la idea es aumentar los salarios de estos empleados según el tiempo que llevan trabajando en la empresa a la fecha actual (puede 
  ser según los días, meses o años que lleva trabajando). Para ello, se requiere saber nombre, apellido, salario actual, total de días, total 
  de meses y años que llevan contratados los empleados que se encuentran en esta condición. La información se debe mostrar en el formato del 
  ejemplo  y ordena en forma ascendente por salario e  identificación del empleado (la información que se muestra se obtuvo en Febrero del 2014) */

SELECT EMPLOYEE_ID EMPLEADO, TO_CHAR(SALARY,'$99,999') "SALARIO ACTUAL", TO_CHAR(HIRE_DATE,'DD/MM/YYYY') "FECHA CONTRATO", 
       ROUND(SYSDATE - HIRE_DATE) "DIAS TRABAJADOS", ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)) "MESES CONTRATADOS",
       ROUND(MONTHS_BETWEEN(SYSDATE,HIRE_DATE) / 12) "AÑOS TRABAJADOS"
FROM EMPLOYEES
WHERE SALARY < (SELECT AVG(SALARY)
                FROM EMPLOYEES)
ORDER BY SALARY, EMPLOYEE_ID;  

  /* c) Dado la gran diferencia en la distribución actual de los empleados en los diferentes departamentos, se efectuará una nueva redistribución 
  con el objetivo de corregir esta situación. Por lo tanto, la primera medida será cambiar algunos empleados del departamento que tenga más 
  empleados. Para ello, se requiere saber el nombre del departamento y el total de empleados del departamento que cumpla con esa condición como se 
  muestra en el ejemplo: */
  
SELECT d.DEPARTMENT_NAME DEPARTAMENTO, COUNT(e.EMPLOYEE_ID) "TOTAL DE EMPLEADOS"
FROM EMPLOYEES e JOIN DEPARTMENTS d USING(DEPARTMENT_ID)
GROUP BY d.DEPARTMENT_NAME
HAVING COUNT(e.EMPLOYEE_ID) = (SELECT MAX(COUNT(e.EMPLOYEE_ID)) 
                               FROM EMPLOYEES e JOIN DEPARTMENTS d USING(DEPARTMENT_ID)
                               GROUP BY d.DEPARTMENT_NAME);