Fundamentos de Consultas SQL: Ejercicios Prácticos de Agregación y Joins

Repaso de Consultas SQL

Este documento presenta una serie de ejercicios prácticos de SQL, cubriendo desde la inserción de datos hasta consultas complejas que involucran múltiples tablas, agregación y subconsultas. Los ejercicios se dividen en dos secciones principales: Gestión de Alojamientos y Gestión de Recursos Humanos (HR).

1. Configuración Inicial de Datos (DML)

A continuación, se muestran las sentencias INSERT utilizadas para poblar las tablas ALOJAMIENTO, HABITACIONES, PERSONAL, ACTIVIDADES y ACT_ALO.

INSERT INTO ALOJAMIENTO VALUES (1, 'ALOJA', 'C/ Los Borrowers', 676879623, 'JABY');
INSERT INTO ALOJAMIENTO VALUES (2, 'LA HUERTA', 'C/ El Tuerto', 676899923, 'FONSO');
INSERT INTO HABITACIONES VALUES (1, 1, 'SIMPLE', 'SI', 40, 2);
INSERT INTO HABITACIONES VALUES (1, 1, 'SIMPLE', 'SI', 40, 2);
INSERT INTO PERSONAL VALUES ('71883579V', 4 , 'JESUS', 'MONTOYA', 'C/ Andalucía Nº 9', 2);
INSERT INTO ACTIVIDADES VALUES (1, 'PUENTING', 'TIRARSE DESDE UN PUENTE', 2);
INSERT INTO ACTIVIDADES VALUES (2, 'SENDERISMO', 'Caminar por el barranco', 2);
INSERT INTO ACT_ALO VALUES (1, 2, 'SABADO');

2. Consultas sobre Gestión de Alojamientos

1) Nombre y descripción de las actividades que se realizan en el alojamiento denominado La Huerta.

SELECT C.NOMBRE, C.DESCRIPCION 
FROM ACT_ALO A, ALOJAMIENTO B, ACTIVIDADES C 
WHERE A.CODIGO_ALO = B.COD_ALO 
  AND A.CODIGO_ACT = C.CODIGO_ACT 
  AND B.NOMBRE = 'LA HUERTA';

2) Nombre de los alojamientos que tienen habitaciones dobles y realizan actividades de senderismo.

SELECT A.NOMBRE 
FROM ALOJAMIENTO A, HABITACIONES B, ACTIVIDADES C, ACT_ALO D 
WHERE A.COD_ALO = B.CODIGO_ALO 
  AND D.CODIGO_ACT = C.CODIGO_ACT 
  AND B.TIPO = 'DOBLE' 
  AND C.NOMBRE = 'SENDERISMO';

3) Nombre del alojamiento y el nombre del personal.

Nota: La consulta original estaba repetida. Se proporciona la consulta correcta para unir Alojamiento y Personal.

SELECT A.NOMBRE AS NombreAlojamiento, P.NOMBRE AS NombrePersonal 
FROM ALOJAMIENTO A, PERSONAL P 
WHERE A.COD_ALO = P.CODIGO_ALO;

4) Nombre del alojamiento y el número de habitaciones.

SELECT NOMBRE, COUNT(*) 
FROM ALOJAMIENTO, HABITACIONES 
WHERE COD_ALO = CODIGO_ALO 
GROUP BY NOMBRE;

5) Nombre de los alojamientos y el número de personas que trabajan en ellos.

SELECT A.NOMBRE, COUNT(*) AS "Nº Personas" 
FROM ALOJAMIENTO A, PERSONAL B 
WHERE A.COD_ALO = B.CODIGO_ALO 
GROUP BY A.NOMBRE;

6) Nombre de los alojamientos y el nombre de las personas que trabajan en ellos.

Nota: La consulta proporcionada originalmente cuenta las personas, agrupadas por alojamiento. Si se deseara el nombre de cada persona, se omitiría la función COUNT y el GROUP BY.

SELECT A.NOMBRE, COUNT(*) AS "Nº Personas" 
FROM ALOJAMIENTO A, PERSONAL B 
WHERE A.COD_ALO = B.CODIGO_ALO 
GROUP BY A.NOMBRE;

3. Consultas sobre Esquema de Recursos Humanos (HR Schema)

7) ¿Cuántos oficios diferentes existen?

SELECT COUNT(DISTINCT job_id) FROM employees;

-- O, alternativamente, consultando la tabla de oficios:
SELECT COUNT(job_id) FROM jobs;

8) ¿Cuántos oficios diferentes tiene el departamento 60?

SELECT COUNT(DISTINCT job_id) 
FROM employees 
WHERE department_id = 60;

9) Nombre del departamento y contar los empleados.

SELECT department_name, COUNT(employee_id) 
FROM departments A, employees B 
WHERE A.department_id = B.department_id 
GROUP BY department_name;

10) Número de oficios diferentes por departamento.

SELECT department_name, COUNT(DISTINCT job_id) 
FROM departments A, employees B 
WHERE A.department_id = B.department_id 
GROUP BY department_name;

11) Nombre del empleado, oficio, fecha de inicio y fecha de fin (historial laboral).

SELECT 
    first_name AS Empleado, 
    job_title AS Oficio, 
    start_date AS Desde, 
    end_date AS Hasta 
FROM employees A, jobs B, job_history C 
WHERE A.job_id = B.job_id 
  AND B.job_id = C.job_id;

12) Mostrar las ciudades ubicadas en Europa.

SELECT city, country_name, region_name 
FROM countries C, locations L, regions R 
WHERE C.country_id = L.country_id 
  AND R.region_id = C.region_id 
  AND C.region_id = 1;

13) Empleados activos en Roma.

SELECT COUNT(EMPLOYEE_ID) AS "EMPLEADOS ACTIVOS EN ROMA" 
FROM EMPLOYEES E, DEPARTMENTS D 
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID 
  AND LOCATION_ID = (SELECT LOCATION_ID FROM LOCATIONS WHERE CITY = 'ROMA');

14) Nombre y apellidos, antigüedad (años) y oficio actual del empleado.

SELECT 
    first_name AS Nombre, 
    last_name AS Apellidos, 
    TRUNC(MONTHS_BETWEEN(SYSDATE, hire_date) / 12) AS Antigüedad_Años, 
    job_title AS Oficio 
FROM employees E, jobs J 
WHERE E.job_id = J.job_id;