📊 2. SALARY STATISTICS
1️⃣ Salaries Between 60k And 80k
SELECT emp_no, salary
FROM salaries
WHERE salary BETWEEN 60000 AND 80000;
2️⃣ Salary Min/Max And Employee Count Per Department
-- Opción 1: Mostrar el salario mínimo y máximo por empleado
SELECT emp_no, MIN(salary) AS salario_minimo, MAX(salary) AS salario_maximo
FROM salaries
GROUP BY emp_no;
-- Opción 2: Obtener el salario mínimo y máximo por departamento
SELECT d.dept_no, MIN(s.salary) AS salario_minimo, MAX(s.salary) AS salario_maximo
FROM salaries s
JOIN dept_emp d ON s.emp_no = d.emp_no
GROUP BY d.dept_no;
3️⃣ Average/Min/Max Salary By Job Title
SELECT T.title, MAX(S.salary) AS salario_max,
MIN(S.salary) AS salario_min, AVG(S.salary) AS salario_promedio
FROM salaries S
JOIN titles T ON S.emp_no = T.emp_no
GROUP BY T.title;
4️⃣ Total Salary Per Department
SELECT DP.dept_name AS 'Departamento', SUM(S.salary) AS 'Total del salario'
FROM salaries S
JOIN dept_emp DE ON S.emp_no = DE.emp_no
JOIN departments DP ON DE.dept_no = DP.dept_no
WHERE S.to_date = '9999-01-01'
GROUP BY DP.dept_name
ORDER BY 2 DESC;
5️⃣ Salaries Above Company Average
SELECT E.emp_no, E.first_name, E.last_name, S.salary
FROM salaries S
JOIN employees E ON S.emp_no = E.emp_no
WHERE S.salary > (
SELECT AVG(salary) FROM salaries
);
6️⃣ Salary Classification With CASE
SELECT e.emp_no, e.first_name, e.last_name, s.salary,
CASE
WHEN s.salary > 80000 THEN 'Alto salario'
WHEN s.salary BETWEEN 50000 AND 80000 THEN 'Salario medio'
ELSE 'Bajo salario'
END AS categoria_salarial
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.to_date = '9999-01-01';
7️⃣ Salary Above Department Average
SELECT D.emp_no AS "Número empleado", S.salary AS "Salario"
FROM dept_emp D
JOIN salaries S ON D.emp_no = S.emp_no
WHERE S.to_date = '9999-01-01'
AND S.salary > (
SELECT AVG(S2.salary)
FROM salaries S2
JOIN dept_emp D2 ON S2.emp_no = D2.emp_no
WHERE D2.dept_no = D.dept_no
);
8️⃣ Highest-Paid Employee Per Department
SELECT *
FROM (
SELECT E.first_name, E.last_name, DP.dept_name, S.salary,
DENSE_RANK() OVER (PARTITION BY DP.dept_name ORDER BY S.salary DESC) AS ranking
FROM employees E
JOIN dept_emp DE ON E.emp_no = DE.emp_no
JOIN departments DP ON DE.dept_no = DP.dept_no
JOIN salaries S ON E.emp_no = S.emp_no
WHERE DE.to_date = '9999-01-01'
) AS AliasSalario
WHERE ranking = 1;
9️⃣ Top 3 Salaries By Department
SELECT *
FROM (
SELECT E.first_name, E.last_name, DP.dept_name, S.salary,
DENSE_RANK() OVER (PARTITION BY DP.dept_name ORDER BY S.salary DESC) AS ranking
FROM employees E
JOIN dept_emp DE ON E.emp_no = DE.emp_no
JOIN departments DP ON DE.dept_no = DP.dept_no
JOIN salaries S ON E.emp_no = S.emp_no
WHERE S.to_date = '9999-01-01'
) AS AliasSalario
WHERE ranking <= 3;
🔟 Salaries Above 90th Percentile
WITH SalariosPercentil AS (
SELECT salary, NTILE(100) OVER (ORDER BY salary) AS percentil
FROM salaries
)
SELECT S.emp_no, E.first_name, E.last_name, S.salary
FROM salaries S
JOIN employees E ON S.emp_no = E.emp_no
WHERE S.salary > (
SELECT salary FROM SalariosPercentil
WHERE percentil = 90
LIMIT 1
);
1️⃣1️⃣ Previous Salary Using LAG
WITH SalarioAnterior AS (
SELECT
s.emp_no, e.first_name, e.last_name, s.salary, s.to_date,
LAG(s.salary) OVER (PARTITION BY s.emp_no ORDER BY s.from_date) AS salario_anterior
FROM salaries s
JOIN employees e ON s.emp_no = e.emp_no
)
SELECT emp_no, first_name, last_name, salary, salario_anterior
FROM SalarioAnterior
WHERE to_date = '9999-01-01' AND salario_anterior IS NOT NULL;
1️⃣2️⃣ Highest Salary Per Department (CTE)
WITH SalariosAltos AS (
SELECT DP.dept_name AS Nombre_Departamento, MAX(S.salary) AS Salario_maximo
FROM salaries S
JOIN dept_emp D ON S.emp_no = D.emp_no
JOIN departments DP ON D.dept_no = DP.dept_no
GROUP BY DP.dept_no
)
SELECT Nombre_Departamento AS Departamento, Salario_maximo AS 'Salario máximo'
FROM SalariosAltos
ORDER BY Salario_maximo DESC;