🎯 3. HR INSIGHTS & PROMOTIONS
1️⃣ Employees Who Worked In Multiple Departments
SELECT emp_no, COUNT(DISTINCT dept_no) AS num_departamentos
FROM dept_emp
GROUP BY emp_no
HAVING COUNT(DISTINCT dept_no) > 1;
2️⃣ Employees In Overlapping Departments
SELECT DISTINCT d1.emp_no
FROM dept_emp d1
JOIN dept_emp d2
ON d1.emp_no = d2.emp_no
AND d1.dept_no <> d2.dept_no
AND d1.from_date < d2.to_date
AND d2.from_date < d1.to_date;
3️⃣ Number Of Distinct Titles Per Employee
SELECT E.emp_no AS 'Nº empleado', E.first_name AS 'Nombre', E.last_name AS 'Apellido',
COUNT(*) AS 'Nº titulos'
FROM titles T
JOIN employees E ON T.emp_no = E.emp_no
GROUP BY T.emp_no, E.first_name, E.last_name;
4️⃣ Employees With More Than 3 Salary Changes
-- Versión sin CTE:
SELECT emp_no, COUNT(*) AS 'Numero_sueldos'
FROM salaries
GROUP BY emp_no
HAVING Numero_sueldos > 2;
-- Versión con CTE:
WITH NumeroSueldos AS (
SELECT emp_no, COUNT(*) AS 'Numero_sueldos'
FROM salaries
GROUP BY emp_no
)
SELECT * FROM NumeroSueldos
WHERE Numero_sueldos > 2;
5️⃣ Employees With Same Initial And Current Salary
WITH SalaryHistory AS (
SELECT emp_no, salary, to_date,
FIRST_VALUE(salary) OVER (PARTITION BY emp_no ORDER BY from_date) AS first_salary
FROM salaries
)
SELECT DISTINCT emp_no
FROM SalaryHistory
WHERE salary = first_salary AND to_date = '9999-01-01';
6️⃣ Average Duration Of Job Titles
WITH Time_Titles AS (
SELECT emp_no, title, DATEDIFF(to_date, from_date) AS 'Tiempo_trabajando'
FROM titles
WHERE to_date <> '9999-01-01'
)
SELECT title, AVG(Tiempo_trabajando)
FROM Time_Titles
GROUP BY title;
7️⃣ Salary Increase >15% (LAG)
WITH SalaryChanges AS (
SELECT emp_no, salary,
LAG(salary) OVER (PARTITION BY emp_no ORDER BY from_date) AS prev_salary
FROM salaries
)
SELECT DISTINCT emp_no
FROM SalaryChanges
WHERE salary > prev_salary * 1.15;
8️⃣ Employees With More Than 2 Title Changes
WITH EmpleadosConTitulos AS (
SELECT E.emp_no, E.first_name, E.last_name, COUNT(DISTINCT T.title) AS num_titulos
FROM employees E
JOIN titles T ON E.emp_no = T.emp_no
GROUP BY E.emp_no
)
SELECT *
FROM EmpleadosConTitulos
WHERE num_titulos > 2
ORDER BY num_titulos DESC;
9️⃣ View With Salary And Title History
CREATE VIEW Titulos_Salarios_Empleados AS
SELECT E.emp_no AS 'Nº empleado', E.first_name AS 'Nombre', E.last_name AS 'Apellido',
S.salary AS 'Salario', T.title AS 'Título'
FROM employees E
JOIN salaries S ON E.emp_no = S.emp_no
JOIN titles T ON E.emp_no = T.emp_no
WHERE T.to_date = '9999-01-01';
SELECT * FROM Titulos_Salarios_Empleados;
🔟 Stored Procedure To Update Salary
DELIMITER //
CREATE PROCEDURE UpdateSalary (
IN p_emp_no INT,
IN p_new_salary INT,
IN p_reason VARCHAR(255)
)
BEGIN
DECLARE old_salary INT;
START TRANSACTION;
SELECT salary INTO old_salary
FROM salaries
WHERE emp_no = p_emp_no AND to_date = '9999-01-01'
LIMIT 1;
SAVEPOINT before_update;
UPDATE salaries
SET to_date = CURDATE()
WHERE emp_no = p_emp_no AND to_date = '9999-01-01';
IF ROW_COUNT() = 0 THEN
ROLLBACK TO SAVEPOINT before_update;
SELECT 'Error: No se encontró el salario actual' AS message;
ELSE
INSERT INTO salaries (emp_no, salary, from_date, to_date)
VALUES (p_emp_no, p_new_salary, CURDATE(), '9999-01-01');
IF ROW_COUNT() = 0 THEN
ROLLBACK TO SAVEPOINT before_update;
SELECT 'Error: No se pudo insertar el nuevo salario' AS message;
ELSE
COMMIT;
SELECT 'Salario actualizado correctamente' AS message;
END IF;
END IF;
END //
DELIMITER ;
-- Llamar al procedimiento
-- CALL UpdateSalary(10001, 92000, 'Aumento anual');
1️⃣1️⃣ Trigger To Log Salary Changes
DELIMITER //
CREATE TRIGGER trigger_actualizar_salario
AFTER INSERT ON salaries
FOR EACH ROW
BEGIN
INSERT INTO prueba_updates_salarios (emp_no, old_salary, new_salary, reason)
VALUES (
NEW.emp_no,
(SELECT salary FROM salaries WHERE emp_no = NEW.emp_no ORDER BY to_date DESC LIMIT 1),
NEW.salary,
'Aumento anual'
);
END //
DELIMITER ;