Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions Repàs consultes HR»

Revisió del 17:18, 11 des 2017

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;

7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles.

select  concat(lpad(' ', level * 3 - 3), last_name) nombre
from employees
connect by prior employee_id = manager_id
start with manager_id is null;

8) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros.

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP (department_id,job_id);

9) Igual que 8 pero que aparezca también los costes en función del manager.

SELECT department_id, job_id, manager_id, SUM(salary)
FROM employees
GROUP BY ROLLUP ((department_id,job_id),(manager_id));

10) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros y los subtotales relativos.

SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE (department_id,job_id);

11) Igual que la consulta 10, pero debe aparecer unas columnas adicionales (valores 1 o 0) indicando de que subtotal se trata.

SELECT department_id, job_id, SUM(salary),GROUPING(department_id), GROUPING(job_id)
FROM employees
GROUP BY CUBE (department_id,job_id);

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

13) Igual que 12, pero sustituyendo el department_id por el department_name.

SELECT employee_id, first_name, department_name, salary
FROM employees NATURAL JOIN (SELECT department_id, MAX(salary) maxsal
                             FROM employees
                             GROUP BY department_id) MAXIMS
               JOIN departments USING (department_id)
WHERE salary = MAXIMS.maxsal;

14) Quants departaments hi ha a cada país. Mostreu nom del país i nombre de departaments.

SELECT c.country_name, count(department_id)
FROM countries c NATURAL JOIN locations NATURAL JOIN departments
GROUP BY c.country_name;

15) Quants empleats treballen a cada país. Mostreu nom del país i nombre de empleats.

SELECT c.country_name, count(e.employee_id)
FROM countries c NATURAL JOIN locations l NATURAL JOIN departments 
     JOIN employees e USING (department_id) 
GROUP BY c.country_name;

16) Mostreu el first_name i last_name, carreg i nom del departament dels 3 empleats que menys temps han estat fent una feina.

17) Quants empleats de cada grau salarial hi ha a cada departament. Mostreu nom del departament, grau salarial i nombre de empleats.

18) Quin és l'ofici (job_title) amb un rang salarial més ampli.

19) Mostreu la següent sortida:

SALARI I COMISSIO
...
44 RANDALL MATOS     té un salari de  $2,600.00
45 PETER VARGAS      té un salari de  $2,500.00
46 JOHN RUSSELL      té un salari de $14,000.00, amb la comissió $19,600.00
47 KAREN PARTNERS    té un salari de $13,500.00, amb la comissió $17,550.00
...

SELECT RPAD(UPPER(first_name) || ' ' || UPPER (last_name),18,' ') || ' tiene un salario de' ||
       TO_CHAR(salary, '$99,999.00') || NVL2(commission_pct,', amb la comissió' || 
       To_CHAR(salary * (1+commission_pct), '$99,999.00'),'') "SALARI I COMISSIO"
FROM employees;

20) Quants oficis diferents hi ha a cada departament. Mostreu nom del departament i nombre d'oficis.

21) Quants empleats hi ha a cada departament, quants cobren comissió i quants no la cobren. Mostreu el nom del departaments. Poseu alies de columnes adequats.

SELECT department_name "Departament", COUNT(employee_id) "Empelats", 
       COUNT(employee_id)-COUNT(commission_pct) "Sense comissió", COUNT(commission_pct) "Amb comissió"
FROM employees JOIN departments USING(department_id)
GROUP BY department_name;

22) Quin cost en salaris, tenint en compte la comissió, té cada país. Mostreu el nom del país i el cost.

23) Quin és el país que té un cost salarial més alt, tenint en compte la comissió.

24) Quin són els tres països amb un cost salarial més alt, tenint en compte la comissió.

25) Mostreu el nom dels empleats, el nom del departament, els mesos sencers treballats i els dies, del historial (job_history). Ordenats de forma descendent per mesos i dies.

Nom         Departament      Ofici                       Mesos     Dies
--------    --------------   ------------------------    ------    -----
Jennifer    Administration   Administration Assistant	 69	   2100
Lex	    Executive	     Programmer                  66        2018
Jennifer    Administration   Public Accountant	         53        1644
...

SELECT e.first_name "Nom", d.department_name "Department", j.job_title "Ofici", 
       Trunc(months_between(h.end_date,h.start_date)) "Mesos", h.end_date-h.start_date "Dies"
FROM job_history h JOIN employees e ON (h.employee_id=e.employee_id) 
     JOIN departments d ON(d.department_id=e.department_id) 
     JOIN jobs j ON (j.job_id = h.job_id)
ORDER BY 4, 5;

26) El mateix que el 25, però mostrant només els 5 que més temps han treballat.

27) Muestra el nombre, apellido i salario mensual de los empleados que cobren mas que Jennifer ordenado por salario descendiente

 +--------------------+-------------------+
 | Nombre i apellido  | Salario Mensual   | 
 +--------------------+-------------------+

SELECT e.first_name||' '||e.last_name "Nombre i apellido"s, e.salary"Salario Mensual"
FROM employees e
WHERE e.salary >ANY (SELECT e.salary
                     FROM employees e
                     WHERE UPPER(e.first_name)='JENNIFER');

28) Para cada departamento Muestra el nombre del empleado/s que cobran menos.

 +------------------------+-------------------+
 | Nombre departamentodo  | Nombre empleado   | 
 +------------------------+-------------------+

SELECT e.department_id"Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado"
FROM e.employees e
WHERE (e.department.id,e.salary) IN (SELECT e.department_id, min(e.sal)
                                     FROM employees e
                                     GROUP BY e.department);

29) Para cada región muestra cuantos países hay. Ordena por numero de países ascendentemente.

 +-----------------+-------------------+
 | Nombre Región   | Numero de paises  | 
 +-----------------+-------------------+

SELECT r.region_name "Nombre Region", count(*)"Numero de paises"
FROM regions r,countries c
WHERE r.region_id=c.region_id
GROUP BY r.region_name 
ORDER BY 2 ASC;

30) Muestra el nombre de los departamentos i nombre de su jefe, que se encuentran fuera de EEUU.

 +--------------+-------+-----------------+
 | Nombre Dep   | Jefe  |  Sueldo Mensual | 
 +------------- +-------+-----------------+

SELECT d.department_name "Nombre Dep", e.first_name "Jefe", e.salary "Sueldo Mensual"
FROM countries c, locations l, departments d, employees e
WHERE c.country_id=l.country_id
  AND l.location_id=d.location.id
  AND d.manager_id=e.employee_id
  AND c.country_name <> 'United States Of America'; 

31) Mostrar el nombre de los países que empiezan o termina en una “a" junto con el nombre de la region a la que pertencen.

 +-------------+--------+
 | Nombre pais | Region | 
 +-------------+--------+

SELECT c.country_name "Nombre país", r.region_name "Region"
FROM countries c, regions r
WHERE c.region_id=r.region_id
  AND UPPER(c.country_name) LIKE 'A%'
  OR UPPER(c.country_name) LIKE '%A';

32) Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe.

 +-----------------+-------------+
 | Nombre empleado | Nombre jefe | 
 +-----------------+-------------+

SELECT UPPER(e.first_name) "Nombre empleado", j.first_name "Nombre jefe" 
FROM employees e, employees j
WHERE e.manager_id=j.employee_id

33) Para cada jefe muestra cuantos empleados tiene a su cargo.

 +-----------------+------------------+
 | Nombre jefe     | Numero empleados | 
 +-----------------+------------------+

SELECT h.first_name "Numero del jefe", COUNT(*)"Numero empleados"
FROM employees e, employees h
WHERE e.manager_id=h.employee_id
GROUP BY h.first_name

34) Cantidad de empleados que han ingresado en la empresa en cada año.

 +-------+------------------+
 |  Año  | Numero empleados | 
 +-------+------------------+

SELECT TO_CHAR(e.hire_date,'YYYY') "Año", COUNT(*)
FROM employees e
GROUP BY TO_CHAR(e.hire_date,'YYYY'); 

35) Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto.

 +--------------+---------+------------------+
 |  Nombre emp  |  Puesto | Meses trabajados |
 +--------------+---------+------------------+

SELECT e.first_name "Nombre empleado",jh.job_id "puesto", ROUND(MONTHS_BETWEEN(jh.start-date,NVL(jh.end-date,SYSDATE))"Meses trabajados"
FROM employees e, jobs j, job_hystory jh 
WHERE e.job_id=j.job_id
  AND e.employee_id=jh.employee_id;

36) El nombre de los jefes de cada departamento y su telefono.

 +------------------------+------------------+---------------+
 |  Nombre departamento   |  Nombre jefe     | Telefono jefe |
 +------------------------+------------------+---------------+

SELECT d.department_name "Nombre departamento", e.first_name "Nombre jefe", e.phone_number "telefono jefe"
FROM employees e, departments d
WHERE d.manager_id=e.employee_id; 

37) El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones)

 +------------------------+-----------------+
 |  Nombre departamento  |  Coste año YYYY  |   
 +-----------------------+------------------+

SELECT d.department_name "Nombre departamento", SUM((e.salary*12)+NVL(e.comm))"Coste año 2015"
FROM departments d, employees e
WHERE d.department_id=e.department_id
GROUP BY d.department_name; 

38) Para cada departamento el promedio de sueldo, el salario maximo i el mínimo pero sólo mostrar los departamentos con promedio superior a la media de todos los departamentos.

 +------------------------+-----------------+-----------------+ 
 |  Nombre departamento   |  Sueldo medio   | Salario maximo  | 
 +------------------------+-----------------+-----------------+

SELECT d.department_name "Nombre departamento", ROUND(AVG(e.salary)) "Suedo medio", MAX(e.salary) "Salario máximo"
FROM departments d, employees e
WHERE d.department_id=e.department_id
GROUP BY d.department_name;