M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions Repàs consultes HR
Solucions Repàs Consultes BBDD HR
1) Muestra los departamentos (departament_id) en los que sus empleados tienen un salario superior a todos los empleados de otro departamento.
SELECT department_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING min(salary) > ANY (SELECT max(salary) FROM employees GROUP BY department_id);
2) Igual que 1, pero mostrando también el nombre del departamento.
SELECT department_id, d.department_name FROM employees e JOIN departments d USING (department_id) WHERE department_id IS NOT NULL GROUP BY department_id, d.department_name HAVING min(salary) > ANY (SELECT max(salary) FROM employees GROUP BY department_id);
3) Igual que 1 pero mostrando los nombres de los empleados.
SELECT first_name, last_name, department_id FROM employees WHERE department_ID in (SELECT department_id FROM employees WHERE department_id IS NOT NULL GROUP BY department_id HAVING min(salary) > ANY (SELECT max(salary) FROM employees GROUP BY department_id));
4)Muestra id, last_name, salary i la media de salarios del departamento de su departamento para todos aquellos empleados que ganen más que la media de su departamento.
SELECT employee_id, last_name, salary, average.avgsalary FROM employees NATURAL JOIN (SELECT department_id, trunc(AVG(salary)) avgsalary FROM employees GROUP BY department_id) average WHERE salary > average.avgsalary;
5) Muestra id, first_name, last_name y hire_date de los 10 empleados más antiguos de la empresa.
SELECT rownum, first_name, last_name, hire_date FROM (SELECT first_name, last_name, hire_date FROM employees ORDER BY hire_date) WHERE rownum <=10;
6) Muestra id, first_name, last_name, nombre del departamento y salario de los 5 empleados mejor pagagados de la empresa.
SELECT rownum, first_name, last_name, salary, department_name FROM (SELECT first_name, last_name, salary, department_id FROM employees ORDER BY salary DESC) JOIN departments USING(department_id) WHERE rownum <=5;
12) Muestra el id, first_name, department_id, i salari del empleat(s) que guanyen més de cada departament.
SELECT employee_id, first_name, department_id, salary FROM employees NATURAL JOIN (SELECT department_id, MAX(salary) maxsal FROM employees GROUP BY department_id) MAXIMS WHERE salary = MAXIMS.maxsal;
SELECT employee_id, first_name, department_id, salary FROM employees e WHERE salary = (SELECT MAX(salary) FROM employees d WHERE d.department_id= e.department_id);
SELECT employee_id, first_name, department_id, salary FROM employees WHERE (salary, department_id) IN (SELECT MAX(salary), department_id FROM employees GROUP BY department_id);