🧑💼 1. EMPLOYEE ANALYSIS
1️⃣ Show First And Last Names Of Employees
SELECT first_name, last_name
FROM employees;2️⃣ Employees Whose Last Name Starts With “S”
SELECT emp_no, first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';
3️⃣ Names With Exactly 5 Characters
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE '_____'; -- 5 guiones bajos representan 5 caracteres exactos
4️⃣ First 10 Employees By Hire Date
SELECT emp_no, first_name, last_name, hire_date
FROM employees
ORDER BY hire_date ASC
LIMIT 10;
5️⃣ Employees Hired On The Same Day
SELECT hire_date, COUNT(emp_no) AS empleados_contratados
FROM employees
GROUP BY hire_date
ORDER BY hire_date;
6️⃣ Years In Which Employees Were Hired
SELECT YEAR(hire_date) AS anio_contratacion, COUNT(emp_no) AS total_contratados
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY anio_contratacion;
7️⃣ Hired Before 1995 Or In Department d007
SELECT e.emp_no, e.first_name, e.last_name, e.hire_date, d.dept_no
FROM employees e
JOIN dept_emp d ON e.emp_no = d.emp_no
WHERE e.hire_date < '1995-01-01' OR d.dept_no = 'd007';
8️⃣ Peak Hiring Day
SELECT hire_date, COUNT(emp_no) AS contrataciones
FROM employees
GROUP BY hire_date
ORDER BY contrataciones DESC
LIMIT 1;
9️⃣ Days Worked Since Hire Date
SELECT emp_no, first_name, last_name, hire_date,
DATEDIFF(CURDATE(), hire_date) AS dias_trabajados
FROM employees;
🔟 Employee Count By Gender And Department
SELECT
Dp.dept_name,
COUNT(CASE WHEN E.gender = 'M' THEN 1 END) AS hombres,
COUNT(CASE WHEN E.gender = 'F' THEN 1 END) AS mujeres
FROM employees E
JOIN dept_emp D ON E.emp_no = D.emp_no
JOIN departments Dp ON D.dept_no = Dp.dept_no
WHERE D.to_date = '9999-01-01'
GROUP BY Dp.dept_name
ORDER BY dept_name;
1️⃣1️⃣ ROW_NUMBER, RANK By Hire Date
SELECT *, ROW_NUMBER() OVER (ORDER BY hire_date ASC) AS 'Orden contratacion'
FROM employees;
SELECT *, RANK() OVER (ORDER BY hire_date ASC) AS 'Orden contratacion'
FROM employees;
SELECT *, DENSE_RANK() OVER (ORDER BY hire_date ASC) AS 'Orden contratacion'
FROM employees;
1️⃣2️⃣ REGEXP Pattern Matching On Names
SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name REGEXP 'aa+';
SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name REGEXP 'Is..c';
SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name REGEXP '[x-z]';
SELECT emp_no, first_name, last_name
FROM employees
WHERE first_name REGEXP 'e{2}';
1️⃣3️⃣ Trigger To Prevent Future Hire Dates
DELIMITER //
CREATE TRIGGER trg_prevent_future_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.hire_date > CURDATE() THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error: No se puede insertar un empleado con fecha futura';
END IF;
END //
DELIMITER ;
-- Prueba del trigger (debería lanzar error)
-- INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date)
-- VALUES (10002, '1995-07-20', 'Ana', 'Gómez', 'F', '2030-01-01');