Ejercicios Prácticos de Consultas SQL con Joins y Agregación en Bases de Datos Relacionales

Ejercicios Prácticos de Consultas SQL Avanzadas

A continuación, se presentan las correcciones y optimizaciones de una serie de consultas SQL enfocadas en la manipulación de datos entre las tablas EMP y DEPT, utilizando diferentes tipos de uniones (joins) y funciones de agregación.

1. Mostrar Nombre de Empleado y Departamento

Objetivo: Obtener el nombre del empleado y el nombre del departamento al que pertenece.

-- 1 MOSTRAR EL NOMBRE DE EMPLEADO Y EL NOMBRE DEL DPTO AL QUE PERTENECEN
SELECT 
    E.ENAME, 
    D.DNAME
FROM 
    EMP E, 
    DEPT D;

2. Producto Cartesiano Filtrado por Inicial del Nombre

Objetivo: Mostrar el producto cartesiano de las tablas EMP y DEPT, filtrando únicamente los registros donde el nombre del empleado empiece por 'M'.

-- 2 MOSTRAR EL PRODUCTO CARTESIANO DE LAS TABLAS EMP Y DEPT, MOSTRANDO UNICAMENTE LOS REGISTROS
-- QUE EL NOMBRE DE EMPLEADO EMPIECE POR M.
SELECT 
    E.ENAME, 
    D.DNAME
FROM 
    EMP E, 
    DEPT D
WHERE 
    E.ENAME LIKE 'M%';

3. Filtrado por Longitud Combinada de Nombres

Objetivo: Mostrar las filas del producto cartesiano que cumplan que la suma de las longitudes de los nombres de empleado y departamento sean mayores que 10 caracteres.

-- 3 MOSTRAR LAS FILAS DEL PRODUCTO CARTESIANO QUE CUMPLAN QUE LA SUMA DE LAS
-- LONGITUDES DE LOS NOMBRES DE EMPLEADO Y DEPARTAMENTO SEAN MAYORES QUE 10 CARACTERES.
SELECT
    E.ename AS "EMPLEADO",
    D.dname AS "DPTO"
FROM 
    emp E, 
    dept D
WHERE 
    LENGTH(E.ename || D.dname) > 10;

4. Listado de Partidos entre Managers

Objetivo: Organizar un torneo entre todos los managers. Mostrar un listado de los partidos con el nombre de los managers (Local vs. Visitante).

-- 4 SE PRETENDE ORGANIZAR UN TORNEO ENTRE TODOS LOS MANAGER. MOSTRAR UN LISTADO
-- DE LOS PARTIDOS CON EL NOMBRE DE LOS MANAGER.
SELECT
    E1.ENAME AS "LOCAL",
    E2.ENAME AS "VISITANTE"
FROM 
    EMP E1, 
    EMP E2
WHERE 
    E1.JOB = 'MANAGER' 
    AND E2.JOB = 'MANAGER' 
    AND E1.ENAME <> E2.ENAME; -- Evita que un manager juegue contra sí mismo

5. Parejas Manager-Empleado con Suma de Salarios Restringida

Objetivo: Mostrar las parejas formadas por un manager y un empleado cualquiera cuya suma de salarios sea menor que 3500.

-- 5 MOSTRAR LAS PAREJAS FORMADAS POR UN MANAGER Y UN EMPLEADO CUALQUIERA CUYA
-- SUMA DE SALARIOS SEA MENOR QUE 3500
SELECT
    E1.ename AS "MANAGER",
    E2.ename AS "EMPLEADO"
FROM 
    emp E1, 
    emp E2
WHERE 
    E1.job = 'MANAGER' 
    AND E1.sal + E2.sal < 3500 
    AND E1.ename <> E2.ename; -- Se asume que no deben ser la misma persona, aunque el requisito no lo especifica explícitamente.

6. Combinaciones de Nombres de Departamento (CROSS JOIN)

Objetivo: Mostrar las combinaciones de nombres de departamento usando CROSS JOIN.

-- 6 MOSTRAR LAS COMBINACIONES DE NOMBRES DE DPTO, USANDO CROSS JOIN
SELECT
    D1.DNAME,
    D2.DNAME
FROM 
    DEPT D1 
CROSS JOIN 
    DEPT D2;

7. Informe Detallado de Empleados y Departamentos (NATURAL JOIN)

Objetivo: Mostrar nombre de empleado, salario, nombre de departamento y localización, utilizando NATURAL JOIN.

-- 7 MOSTRAR NOMBRE DE EMPLEADO, SALARIO, NOMBRE DE DPTO Y LOCALIZACION
SELECT
    E.ENAME AS "NOMB EMP",
    E.SAL AS "SALARIO",
    D.DNAME AS "NOMB DPTO",
    D.LOC AS "LOCALIZACION"
FROM 
    EMP E 
NATURAL JOIN 
    DEPT D;

8. Empleados que Trabajan en New York

Objetivo: Obtener el nombre y salario de los empleados que trabajan en 'NEW YORK'.

-- 8 NOMBRE Y SALARIO DE LOS EMPLEADOS QUE TRABAJAN EN NEW YORK
SELECT
    E.ENAME,
    E.SAL
FROM 
    EMP E 
NATURAL JOIN 
    DEPT D
WHERE 
    D.LOC = 'NEW YORK';

9. Conteo de Empleados por Ciudad

Objetivo: Contar cuántos empleados trabajan en cada ciudad.

-- 9 CUANTOS EMPLEADOS TRABAJAN EN CADA CIUDAD?
SELECT
    D.LOC AS "CIUDAD",
    COUNT(*) AS "NUM EMP"
FROM 
    EMP E 
NATURAL JOIN 
    DEPT D
GROUP BY 
    D.LOC;

10. Ciudades con Más de Tres Empleados

Objetivo: Mostrar en qué ciudades trabajan más de tres empleados.

-- 10 MOSTRAR EN QUE CIUDADES TRABAJAN MAS DE TRES EMPLEADOS.
SELECT
    D.LOC AS "CIUDAD",
    COUNT(*) AS "NUM EMP"
FROM 
    EMP E 
NATURAL JOIN 
    DEPT D
GROUP BY 
    D.LOC
HAVING 
    COUNT(*) > 3;

11. Empleados Contratados en 1981

Objetivo: Mostrar nombre, oficio y nombre del departamento de los empleados que entraron en la compañía en 1981.

-- 11 MOSTRAR NOMBRE, OFICIO Y NOMBRE DEL DEPARTAMENTO DE LOS EMPLEADOS QUE ENTRARON
-- EN LA COMPAÑÍA EN 1981.
SELECT
    E.ENAME AS "NOMB EMP",
    E.JOB AS "PUESTO",
    D.DNAME AS "NOMB DEPT"
FROM 
    EMP E 
JOIN 
    DEPT D USING (DEPTNO)
WHERE 
    TO_CHAR(E.HIREDATE, 'YYYY') = '1981';

12. Conteo de 'CLERK' por Departamento

Objetivo: Mostrar un informe que muestre el nombre del dpto y cuántos 'CLERK' hay en él.

-- 12 MOSTRAR UN INFORME QUE MUESTRE EL NOMBRE DEL DPTO Y CUANTOS CLERK HAY EN EL.
SELECT
    D.DNAME AS "NOMB DPTO",
    COUNT(*) AS "NUM CLERK"
FROM 
    EMP E 
JOIN 
    DEPT D USING(DEPTNO)
WHERE 
    E.JOB = 'CLERK'
GROUP BY 
    D.DNAME;

13. Conteo de Managers y Analysts por Localidad

Objetivo: Mostrar para cada localidad cuántos 'MANAGER' y cuántos 'ANALYST' hay.

-- 13 MOSTRAR PARA CADA NOMBRE DE DPTO CUANTOS MANAGER Y CUANTOS ANALYST HAY
SELECT
    D.LOC AS "LOCALIDAD",
    E.JOB AS "OFICIO",
    COUNT(*) AS "NUM EMP"
FROM 
    EMP E 
JOIN 
    DEPT D USING (DEPTNO)
WHERE 
    E.JOB IN ('MANAGER', 'ANALYST')
GROUP BY 
    D.LOC, E.JOB
ORDER BY 
    D.LOC;

14. Conteo de Empleados por Oficio y Localidad

Objetivo: Mostrar para cada localidad cuántos empleados hay de cada oficio.

-- 14 MOSTRAR PARA CADA NOMBRE DE DPTO CUANTOS EMPLEADOS HAY DE CADA OFICIO
SELECT
    D.LOC AS "LOCALIDAD",
    E.JOB AS "OFICIO",
    COUNT(*) AS "NUM EMP"
FROM 
    EMP E 
JOIN 
    DEPT D ON (E.DEPTNO = D.DEPTNO)
GROUP BY 
    D.LOC, E.JOB
ORDER BY 
    D.LOC;

15. Relación Empleado y su Jefe (Auto-Join)

Objetivo: Mostrar el nombre del empleado y el nombre de su jefe.

-- 15 MOSTRAR EL NOMBRE DEL EMPLEADO Y EL NOMBRE DE SU JEFE
SELECT
    EMPLEADO.ENAME AS "EMPLEADO",
    JEFE.ENAME AS "SUPERVISOR"
FROM 
    EMP EMPLEADO 
JOIN 
    EMP JEFE ON (EMPLEADO.MGR = JEFE.EMPNO);