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

Revisió del 21:47, 31 oct 2018

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;

NO!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;

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

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

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

NO!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.

SELECT *
FROM (SELECT first_name, last_name, j.job_id, department_name
      FROM employees e JOIN job_history j USING (employee_id) 
           JOIN departments d ON (d.department_id= e.department_id)
      ORDER BY j.end_date-j.start_date)
WHERE ROWNUM<=3;

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

SELECT d.department_name, j.grade_level, COUNT(e.employee_id)
FROM departments d JOIN employees e USING(department_id)
     JOIN job_grades j ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal)
GROUP BY d.department_name, j.grade_level
ORDER BY d.department_name, j.grade_level;

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

SELECT job_title
FROM jobs NATURAL JOIN employees
GROUP BY job_title
HAVING MAX(salary)-MIN(salary) = (SELECT MAX(MAX(salary)-MIN(salary))
                                  FROM employees
                                  GROUP BY job_id);

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.

SELECT department_name "Nom Departament", COUNT(DISTINCT job_id) "Nombre Oficis"
FROM departments JOIN employees USING(department_id)
GROUP BY department_name;

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.

SELECT country_name "Nom pais", SUM(salary*(1+NVL(commission_pct,0))) "Cost salarial"
FROM countries NATURAL JOIN locations NATURAL JOIN departments JOIN employees USING(department_id)
GROUP BY country_name;

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

SELECT country_name "Nom pais"
FROM (SELECT country_name
      FROM countries NATURAL JOIN locations NATURAL JOIN departments JOIN employees USING(department_id)
      GROUP BY country_name
      ORDER BY SUM(salary*(1+NVL(commission_pct,0))) DESC)
WHERE ROWNUM=1;

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

SELECT country_name "Nom pais"
FROM (SELECT country_name
      FROM countries NATURAL JOIN locations NATURAL JOIN departments JOIN employees USING(department_id)
      GROUP BY country_name
      ORDER BY SUM(salary*(1+NVL(commission_pct,0))) DESC)
WHERE ROWNUM<=3;

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 DESC, 5 DESC;

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

SELECT *
FROM (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 DESC, 5 DESC)
WHERE ROWNUM <= 5;

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 departamento  | Nombre empleado   | 
 +------------------------+-------------------+

SELECT d.department_name "Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado"
FROM employees e JOIN departments d USING(department_id) 
WHERE (department_id,e.salary) IN (SELECT e.department_id, min(e.salary)
                                     FROM employees e
                                     GROUP BY e.department_id);

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;