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);