Ejercicios Empresa:

Dada la base de datos que se muestra en la figura:

erDiagram 
    EMPLEADO {
        char(3) cdemp PK
        varchar(30) nombre
        date fecha_ingreso
        decimal salario
        char(3) cdjefe FK
        char(2) cddep FK
    }
    
    DEPARTAMENTO {
        char(2) cddep PK
        varchar(30) nombre
        varchar(20) ciudad
    }
    
    PROYECTO {
        char(3) cdpro PK
        varchar(30) nombre
        char(2) cddep FK
    }
    
    TRABAJA {
        char(3) cdemp FK
        char(3) cdpro FK
        int nhoras
    }

    EMPLEADO ||--o{ EMPLEADO : "es jefe de"
    EMPLEADO }|--|| DEPARTAMENTO : "pertenece a"
    DEPARTAMENTO ||--o{ PROYECTO : "gestiona"
    EMPLEADO ||--o{ TRABAJA : "trabaja en"
    PROYECTO ||--o{ TRABAJA : "es asignado a"

Implementar las siguientes consultas:

  1. Creación de tablas, con las siguientes restricciones:
    • En la tabla empleado:
      • fecha ingreso y cdjefe tendrán por defecto NULL.
      • Salario tendrá 2 dígitos decimales y cuatro enteros, sin signo.
    • En la tabla trabaja:
      • nhoras solo acepta valores entre 0 y 20.
  2. Inserta los siguientes valores a cada tabla:

    • En la tabla departamento:

        ('01','Contabilidad-1','Almería'),
  ('02','Ventas','Sevilla'),
  ('03','I+D','Málaga'),
  ('04','Gerencia','Córdoba'),
  ('05','Administración','Córdoba'),
  ('06','Contabilidad-2','Córdoba'),
  ('07','Marketing','Granada')

    • En la tabla empleado:

      ('A11','Esperanza Amarillo','1993-09-23',3000,NULL,'04'),
('A03','Pedro Rojo','1995-03-07',2000,'A11','01'),
('C01','Juan Rojo','1997-02-03',1800,'A03','01'),
('B02','María Azul','1996-01-09',1450,'A03','01'),
('A07','Elena Blanco','1994-04-09',2000,'A11','02'),
('B06','Carmen Violeta','1997-02-03',2200,'A07','02'),
('C05','Alfonso Amarillo','1998-12-03',2000,'B06','02'),
('B09','Pablo Verde','1998-10-12',1600,'A11','03'),
('C04','Ana Verde',NULL,2000,'A07','02'),
('C08','Javier Naranja',NULL,1680,'B09','03'),
('A10','Dolores Blanco','1998-11-15',1900,'A11','04'),
('B12','Juan Negro','1997-02-03',1900,'A11','05'),
('A13','Jesús Marrón','1999-02-21',2200,'A11','05'),
('A14','Manuel Amarillo','2000-09-01',2000,'A11',NULL)

    • En la tabla proyecto:

      ('GRE','Gestión de residuos','03'),
('DAG','Depuración de aguas','03'),
('AEE','Análisis económico energías','04'),
('MES','Marketing de energía solar','02')

    • En la tabla trabaja:

      ('C01','GRE',10),
('C08','GRE',54),
('C01','DAG',5),
('C08','DAG',150),
('B09','DAG',100),
('A14','DAG',10),
('A11','AEE',15),
('C04','AEE',20),
('A11','MES',0),
('A03','MES', 0)
  3. Consulta 1: De la tabla EMPLEADO, el código, nombre y salario de los empleados ordenados por nombre ascendentemente y por salario de manera descendente.
  4. Consulta 2: Todos los nombres de departamentos menos los de Sevilla o Granada.
  5. Consulta 3: Número de empleados con departamento asignado.
  6. Consulta 4: Media de horas trabajadas en proyectos de la tabla trabaja.
  7. Consulta 5: Total de empleados de cada departamento que ganan un salario superior a 1500€, pero que sólo se mostraran los departamentos con más de 2 empleados con salario superior a 1500€.
  8. Consulta 6: Nombre de cada empleado de la base de datos junto al nombre de su jefe.
  9. Consulta 7: Nombre del departamento con más empleados.
  10. Consulta 8: Empleado que más horas ha trabajado en cada proyecto.
  11. Consulta 9: Nombre de los departamentos que NO tienen empleados que ingresaron antes de 2005.
  12. Consulta 10: Datos de los empleados cuyo nombre comienza por “A”
  1. Creación de la base de datos y las tablas:
CREATE TABLE empleado (
    cdemp CHAR(3) PRIMARY KEY,                       -- Código del empleado (clave primaria)
    nombre VARCHAR(30) NOT NULL,                     -- Nombre del empleado (no nulo)
    fecha_ingreso DATE DEFAULT NULL,                 -- Fecha de ingreso (por defecto NULL)
    salario DECIMAL(6,2) UNSIGNED,                   -- Salario con precisión adecuada
    cdjefe CHAR(3) DEFAULT NULL,                     -- Código del jefe (por defecto NULL)
    cddep CHAR(2),                                   -- Código del departamento
    FOREIGN KEY (cdjefe) REFERENCES empleado(cdemp) ON DELETE SET NULL,
    FOREIGN KEY (cddep) REFERENCES departamento(cddep) ON DELETE SET NULL
);

CREATE TABLE trabaja (
    cdemp CHAR(3),                                           -- Código del empleado
    cdpro CHAR(3),                                           -- Código del proyecto
    nhoras INT(11) CHECK (nhoras >= 0 AND nhoras <= 20),     -- Horas trabajadas (entre 0 y 20)
    PRIMARY KEY (cdemp, cdpro),                              -- Clave primaria compuesta
    FOREIGN KEY (cdemp) REFERENCES empleado(cdemp)           -- Clave foránea que referencia a la tabla empleado
);

CREATE TABLE departamento (
    cddep CHAR(2) PRIMARY KEY,                              -- Código del departamento (clave primaria)
    nombre VARCHAR(30) NOT NULL,                            -- Nombre del departamento (no nulo)
    ciudad VARCHAR(20)                                      -- Ciudad donde se ubica el departamento
);

CREATE TABLE proyecto (
    cdpro char(3) NOT NULL,
    nombre varchar(30) NOT NULL,
    cddep char(2) DEFAULT NULL
);
  1. Inserts de datos
# Tabla Departamento
INSERT INTO `departamento` (`cddep`, `nombre`, `ciudad`) VALUES
    ('01', 'Contabilidad-1', 'Almería'),
    ('02', 'Ventas', 'Sevilla'),
    ('03', 'I+D', 'Málaga'),
    ('04', 'Gerencia', 'Córdoba'),
    ('05', 'Administración', 'Córdoba'),
    ('06', 'Contabilidad-2', 'Córdoba'),
    ('07', 'Marketing', 'Granada');

# Tabla Empleado
INSERT INTO `empleado` (`cdemp`, `nombre`, `fecha_ingreso`, `salario`, `cdjefe`, `cddep`) VALUES
    ('A03', 'Pedro Rojo', '1995-03-07', 2000.00, 'A11', '01'),
    ('A07', 'Elena Blanco', '1994-04-09', 2000.00, 'A11', '02'),
    ('A10', 'Dolores Blanco', '1998-11-15', 1900.00, 'A11', '04'),
    ('A11', 'Esperanza Amarillo', '1993-09-23', 3000.00, NULL, '04'),
    ('A13', 'Jesús Marrón', '1999-02-21', 2200.00, 'A11', '05'),
    ('A14', 'Manuel Amarillo', '2000-09-01', 2000.00, 'A11', NULL),
    ('B02', 'María Azul', '1996-01-09', 1450.00, 'A03', '01'),
    ('B06', 'Carmen Violeta', '1997-02-03', 2200.00, 'A07', '02'),
    ('B09', 'Pablo Verde', '1998-10-12', 1600.00, 'A11', '03'),
    ('B12', 'Juan Negro', '1997-02-03', 1900.00, 'A11', '05'),
    ('C01', 'Juan Rojo', '1997-02-03', 1800.00, 'A03', '01'),
    ('C04', 'Ana Verde', NULL, 2000.00, 'A07', '02'),
    ('C05', 'Alfonso Amarillo', '1998-12-03', 2000.00, 'B06', '02'),
    ('C08', 'Javier Naranja', NULL, 1680.00, 'B09', '03');

# Tabla Proyecto
INSERT INTO `proyecto` (`cdpro`, `nombre`, `cddep`) VALUES
    ('AEE', 'Análisis económico energías', '04'),
    ('DAG', 'Depuración de aguas', '03'),
    ('GRE', 'Gestión de residuos', '03'),
    ('MES', 'Marketing de energía solar', '02');

# Tabla Trabaja
INSERT INTO `trabaja` (`cdemp`, `cdpro`, `nhoras`) VALUES
    ('A03', 'MES', 0),
    ('A11', 'AEE', 15),
    ('A11', 'MES', 0),
    ('A14', 'DAG', 10),
    ('B09', 'DAG', 100),
    ('C01', 'DAG', 5),
    ('C01', 'GRE', 10),
    ('C04', 'AEE', 20),
    ('C08', 'DAG', 150),
    ('C08', 'GRE', 54);

De acuerdo con las restricciones del campo nhoras donde solo aceptaba valores entre 0 y 20, estas inserciones devolverán error, por lo que será necesario modificar la tabla trabaja:

ALTER table trabaja modify nhoras int(11) check (nhoras >=0);

🔹 Consultas Básicas

  1. Seleccionar todos los empleados 
    SELECT * FROM empleado;
  2. Seleccionar nombre y salario de los empleados 
    SELECT nombre, salario FROM empleado;
  3. Filtrar empleados con salario mayor a 3000 
    SELECT * FROM empleado WHERE salario > 3000;
  4. Buscar empleados con nombre que empiece con ‘A’ 
    SELECT * FROM empleado WHERE nombre LIKE 'A%';
  5. Ordenar empleados por salario descendente 
    SELECT * FROM empleado ORDER BY salario DESC;

🔹 Funciones Agregadas

  1. Contar el número de empleados 
    SELECT COUNT(*) AS total_empleados FROM empleado;
  2. Calcular el salario promedio 
    SELECT AVG(salario) AS salario_promedio FROM empleado;
  3. Obtener el salario máximo y mínimo 
    SELECT MAX(salario) AS salario_max, MIN(salario) AS salario_min FROM empleado;
  4. Sumar los salarios de todos los empleados 
    SELECT SUM(salario) AS total_salarios FROM empleado;
  5. Agrupar empleados por departamento y contar cuántos hay en cada uno 
    SELECT cddep, COUNT(*) AS cantidad FROM empleado GROUP BY cddep;

🔹 Subconsultas y Joins

  1. Encontrar empleados con el salario más alto 
    SELECT * FROM empleado WHERE salario = (SELECT MAX(salario) FROM empleado);
  2. Listar empleados con su jefe (auto-relación) 
    SELECT e1.nombre AS empleado, e2.nombre AS jefe
FROM empleado e1
LEFT JOIN empleado e2 ON e1.cdjefe = e2.cdemp;
  3. Mostrar empleados con su departamento 
    SELECT e.nombre, d.nombre AS departamento
FROM empleado e
JOIN departamento d ON e.cddep = d.cddep;

🔹 Consultas con Condiciones Avanzadas

  1. Empleados con salario entre 2000 y 5000 
    SELECT * FROM empleado WHERE salario BETWEEN 2000 AND 5000;
  2. Empleados sin jefe asignado 
    SELECT * FROM empleado WHERE cdjefe IS NULL;
  3. Mostrar los 5 empleados con mayor salario 
    SELECT * FROM empleado ORDER BY salario DESC LIMIT 5;

🔹 Operaciones con Modificaciones

  1. Aumentar el salario de todos los empleados en 10% 
    UPDATE empleado SET salario = salario * 1.10;
  2. Eliminar empleados con salario menor a 1500 
    DELETE FROM empleado WHERE salario < 1500;
  3. Insertar un nuevo empleado 
    INSERT INTO empleado (cdemp, nombre, fecha_ingreso, salario, cdjefe, cddep)
VALUES ('E21', 'Carlos López', '2023-05-10', 3500.00, 'E05', 'D01');
  4. Crear una vista de empleados y sus departamentos 
    CREATE VIEW vista_empleados AS
SELECT e.nombre, e.salario, d.nombre AS departamento
FROM empleado e
JOIN departamento d ON e.cddep = d.cddep;

🔹 Consultas con JOIN entre Tablas

  1. Listar empleados junto con su departamento 
    SELECT e.nombre AS empleado, d.nombre AS departamento
FROM empleado e
JOIN departamento d ON e.cddep = d.cddep;
  2. Mostrar empleados y sus jefes (auto-relación en empleado) 
    SELECT e1.nombre AS empleado, e2.nombre AS jefe
FROM empleado e1
LEFT JOIN empleado e2 ON e1.cdjefe = e2.cdemp;
  3. Listar empleados y los proyectos en los que trabajan 
    SELECT e.nombre AS empleado, p.nombre AS proyecto, t.nhoras
FROM empleado e
JOIN trabaja t ON e.cdemp = t.cdemp
JOIN proyecto p ON t.cdpro = p.cdpro;
  4. Departamentos con sus proyectos asignados 
    SELECT d.nombre AS departamento, p.nombre AS proyecto
FROM departamento d
JOIN proyecto p ON d.cddep = p.cddep;
  5. Empleados, sus departamentos y los proyectos en los que trabajan 
    SELECT e.nombre AS empleado, d.nombre AS departamento, p.nombre AS proyecto
FROM empleado e
JOIN departamento d ON e.cddep = d.cddep
JOIN trabaja t ON e.cdemp = t.cdemp
JOIN proyecto p ON t.cdpro = p.cdpro;

🔹 Consultas con LEFT JOIN y RIGHT JOIN

  1. Mostrar empleados y proyectos, incluyendo los empleados sin proyectos asignados 
    SELECT e.nombre AS empleado, p.nombre AS proyecto
FROM empleado e
LEFT JOIN trabaja t ON e.cdemp = t.cdemp
LEFT JOIN proyecto p ON t.cdpro = p.cdpro;
  2. Mostrar proyectos y empleados, incluyendo proyectos sin empleados asignados 
    SELECT p.nombre AS proyecto, e.nombre AS empleado
FROM proyecto p
LEFT JOIN trabaja t ON p.cdpro = t.cdpro
LEFT JOIN empleado e ON t.cdemp = e.cdemp;
  3. Mostrar empleados sin departamento asignado 
    SELECT e.nombre
FROM empleado e
LEFT JOIN departamento d ON e.cddep = d.cddep
WHERE d.cddep IS NULL;
  4. Mostrar departamentos sin empleados asignados 
    SELECT d.nombre
FROM departamento d
LEFT JOIN empleado e ON d.cddep = e.cddep
WHERE e.cdemp IS NULL;
  5. Mostrar proyectos sin empleados trabajando en ellos 
    SELECT p.nombre
FROM proyecto p
LEFT JOIN trabaja t ON p.cdpro = t.cdpro
WHERE t.cdemp IS NULL;

    🔹 Consultas con GROUP BY y Agregaciones

  6. Contar empleados por departamento 
    SELECT d.nombre AS departamento, COUNT(e.cdemp) AS total_empleados
FROM departamento d
LEFT JOIN empleado e ON d.cddep = e.cddep
GROUP BY d.nombre;
  7. Total de horas trabajadas por cada empleado 
    SELECT e.nombre AS empleado, SUM(t.nhoras) AS total_horas
FROM empleado e
JOIN trabaja t ON e.cdemp = t.cdemp
GROUP BY e.nombre;
  8. Salario promedio por departamento 
    SELECT d.nombre AS departamento, AVG(e.salario) AS salario_promedio
FROM departamento d
JOIN empleado e ON d.cddep = e.cddep
GROUP BY d.nombre;
  9. Número de empleados que trabajan en cada proyecto 
    SELECT p.nombre AS proyecto, COUNT(t.cdemp) AS total_empleados
FROM proyecto p
JOIN trabaja t ON p.cdpro = t.cdpro
GROUP BY p.nombre;
  10. Departamento con el mayor número de empleados 
    SELECT d.nombre AS departamento, COUNT(e.cdemp) AS total_empleados
FROM departamento d
JOIN empleado e ON d.cddep = e.cddep
GROUP BY d.nombre
ORDER BY total_empleados DESC
LIMIT 1;

    🔹 Consultas con HAVING, LIMIT y ORDER BY

  11. Proyectos con más de 3 empleados asignados 
    SELECT p.nombre AS proyecto, COUNT(t.cdemp) AS total_empleados
FROM proyecto p
JOIN trabaja t ON p.cdpro = t.cdpro
GROUP BY p.nombre
HAVING total_empleados > 3;
  12. Los 5 empleados con más horas trabajadas en proyectos 
    SELECT e.nombre AS empleado, SUM(t.nhoras) AS total_horas
FROM empleado e
JOIN trabaja t ON e.cdemp = t.cdemp
GROUP BY e.nombre
ORDER BY total_horas DESC
LIMIT 5;
  13. Los 3 departamentos con el salario promedio más alto 
    SELECT d.nombre AS departamento, AVG(e.salario) AS salario_promedio
FROM departamento d
JOIN empleado e ON d.cddep = e.cddep
GROUP BY d.nombre
ORDER BY salario_promedio DESC
LIMIT 3;
  14. Empleados que trabajan en más de un proyecto 
    SELECT e.nombre AS empleado, COUNT(t.cdpro) AS total_proyectos
FROM empleado e
JOIN trabaja t ON e.cdemp = t.cdemp
GROUP BY e.nombre
HAVING total_proyectos > 1;
  15. Empleados que trabajan en proyectos del departamento “Ventas” 
    SELECT e.nombre AS empleado, p.nombre AS proyecto, d.nombre AS departamento
FROM empleado e
JOIN trabaja t ON e.cdemp = t.cdemp
JOIN proyecto p ON t.cdpro = p.cdpro
JOIN departamento d ON p.cddep = d.cddep
WHERE d.nombre = 'Ventas';

Consultas de EXAMEN

  • Consulta 1: De la tabla EMPLEADO, el código, nombre y salario de los empleados ordenados por nombre ascendentemente y por salario de manera descendente.

      SELECT nombre FROM departamento WHERE ciudad NOT IN ('Sevilla', 'Granada');
  • Consulta 2: Todos los nombres de departamentos menos los de Sevilla o Granada. 
      SELECT cdemp, nombre, salario FROM empleado ORDER BY nombre ASC, salario DESC;

  • Consulta 3: Número de empleados con departamento asignado.

      SELECT COUNT(*) AS num_empl_dep FROM empleado WHERE cddep IS NOT NULL;
  • Consulta 4: Media de horas trabajadas en proyectos de la tabla trabaja: 
      SELECT AVG(nhoras) AS media_horas_trabajadas FROM trabaja;
  SELECT AVG(nhoras) AS media_horas_trabajadas FROM trabaja WHERE nhoras > 0; --Excluimos los que trabajan 0 horas
  • Consulta 5: Total de empleados de cada departamento que ganan un salario superior a 1500€, pero que sólo se mostraran los epartamentos con más de 2 empleados con salario superior a 1500€. 
      SELECT cddep, COUNT(*) AS total_empleados FROM empleado WHERE salario > 1500
  GROUP BY cddepHAVING COUNT(*) > 2;
  • Consulta 6: Nombre de cada empleado de la base de datos junto al nombre de su jefe. 
      SELECT e.nombre AS nombre_empleado, j.nombre AS nombre_jefe FROM empleado e INNER JOIN empleado j ON e.cdjefe = j.cdemp; -- Si queremos ver los q no tienen jefe usamos LEFT JOIN
  • Consulta 7: Nombre del departamento con más empleados. 
      SELECT d.nombre FROM departamento d JOIN empleado e ON d.cddep = e.cddep
  GROUP BY d.nombre HAVING COUNT(*) = (SELECT COUNT(*)FROM empleado GROUP BY cddep ORDER BY COUNT(*) DESC LIMIT 1);

  • Consulta 8: Empleado que más horas ha trabajado en cada proyecto.

      SELECT 
      t.cdpro,
      t.cdemp,
      e.nombre AS nombre_empleado,
      t.nhoras
  FROM trabaja t
  JOIN empleado e ON t.cdemp = e.cdemp
  JOIN (
      SELECT 
          cdpro, 
          MAX(nhoras) AS max_horas
      FROM trabaja
      WHERE nhoras > 0  -- Excluir casos con 0 horas
      GROUP BY cdpro
      ) max_horas_por_proyecto ON t.cdpro = max_horas_por_proyecto.cdpro 
                              AND t.nhoras = max_horas_por_proyecto.max_horas;
  • Consulta 9: Nombre de los departamentos que NO tienen empleados que ingresaron antes de 2005.
    • Opcion 1: Usando una subconsulta con la cláusula NOT EXISTS 
      SELECT d.nombre
FROM departamento d
WHERE NOT EXISTS (
    SELECT 1
    FROM empleado e
    WHERE e.cddep = d.cddep
    AND e.fecha_ingreso < '2005-01-01'
);

    • Opcion 2: Otra forma de resolverlo es usando un LEFT JOIN y filtrando los departamentos que no tienen coincidencias en la subconsulta:

      SELECT d.nombre
FROM departamento d
LEFT JOIN (
    SELECT DISTINCT cddep
    FROM empleado
    WHERE fecha_ingreso < '2005-01-01'
) empleados_antes_2005 ON d.cddep = empleados_antes_2005.cddep
WHERE empleados_antes_2005.cddep IS NULL;
  • Consulta 10: Datos de los empleados cuyo nombre comienza por “A” 
      SELECT * FROM empleado WHERE LOWER(nombre) LIKE 'a%';

Diego J. Gonzalez