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

De wikiserver
Dreceres ràpides: navegació, cerca
(→‎Solucions Repàs Consultes BBDD HR)
 
(Hi ha 20 revisions intermèdies del mateix usuari que no es mostren)
Línia 11: Línia 11:
 
                           GROUP BY department_id);
 
                           GROUP BY department_id);
 
</pre>
 
</pre>
−
<!-- -->
+
 
−
2) Igual que 9, pero mostrando también el nombre del departamento.
+
2) Igual que 1, pero mostrando también el nombre del departamento.
 
<pre>
 
<pre>
 
SELECT department_id, d.department_name
 
SELECT department_id, d.department_name
Línia 23: Línia 23:
 
                           GROUP BY department_id);
 
                           GROUP BY department_id);
 
</pre>
 
</pre>
−
<!-- -->
+
 
−
3) Igual que 9 pero mostrando los nombres de los empleados.
+
3) Igual que 1 pero mostrando los nombres de los empleados.
 
<pre>
 
<pre>
 
SELECT first_name, last_name, department_id
 
SELECT first_name, last_name, department_id
Línia 36: Línia 36:
 
                                                   GROUP BY department_id));
 
                                                   GROUP BY department_id));
 
</pre>
 
</pre>
−
<!-- -->
+
 
 
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.
 
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.
 
<pre>
 
<pre>
Línia 45: Línia 45:
 
WHERE salary > average.avgsalary;
 
WHERE salary > average.avgsalary;
 
</pre>
 
</pre>
−
<!-- -->
+
 
 
5) Muestra id, first_name, last_name y hire_date de los 10 empleados más antiguos de la empresa.
 
5) Muestra id, first_name, last_name y hire_date de los 10 empleados más antiguos de la empresa.
−
 
 
<pre>
 
<pre>
 
SELECT rownum, first_name, last_name, hire_date
 
SELECT rownum, first_name, last_name, hire_date
Línia 55: Línia 54:
 
WHERE rownum <=10;
 
WHERE rownum <=10;
 
</pre>
 
</pre>
−
<!-- -->
 
  
 
6) Muestra id, first_name, last_name, nombre del departamento y salario de los 5 empleados mejor pagagados de la empresa.
 
6) Muestra id, first_name, last_name, nombre del departamento y salario de los 5 empleados mejor pagagados de la empresa.
−
 
 
<pre>
 
<pre>
 
SELECT rownum, first_name, last_name, salary, department_name
 
SELECT rownum, first_name, last_name, salary, department_name
Línia 66: Línia 63:
 
WHERE rownum <=5;
 
WHERE rownum <=5;
 
</pre>
 
</pre>
−
<!-- -->
+
<!--
−
 
+
NO!7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles.
−
7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles.
 
−
 
 
 
<pre>
 
<pre>
 
select  concat(lpad(' ', level * 3 - 3), last_name) nombre
 
select  concat(lpad(' ', level * 3 - 3), last_name) nombre
Línia 76: Línia 71:
 
start with manager_id is null;
 
start with manager_id is null;
 
</pre>
 
</pre>
−
<!-- -->
 
−
 
−
8) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros.
 
  
 +
NO!8) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros.
 
<pre>
 
<pre>
 
SELECT department_id, job_id, SUM(salary)
 
SELECT department_id, job_id, SUM(salary)
Línia 85: Línia 78:
 
GROUP BY ROLLUP (department_id,job_id);
 
GROUP BY ROLLUP (department_id,job_id);
 
</pre>
 
</pre>
−
<!-- -->
 
−
 
−
9) Igual que 8 pero que aparezca también los costes en función del manager.
 
  
 +
NO!9) Igual que 8 pero que aparezca también los costes en función del manager.
 
<pre>
 
<pre>
 
SELECT department_id, job_id, manager_id, SUM(salary)
 
SELECT department_id, job_id, manager_id, SUM(salary)
Línia 94: Línia 85:
 
GROUP BY ROLLUP ((department_id,job_id),(manager_id));
 
GROUP BY ROLLUP ((department_id,job_id),(manager_id));
 
</pre>
 
</pre>
−
<!-- -->
 
−
 
−
10) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros y los subtotales relativos.
 
  
 +
NO!10) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros y los subtotales relativos.
 
<pre>
 
<pre>
 
SELECT department_id, job_id, SUM(salary)
 
SELECT department_id, job_id, SUM(salary)
Línia 103: Línia 92:
 
GROUP BY CUBE (department_id,job_id);
 
GROUP BY CUBE (department_id,job_id);
 
</pre>
 
</pre>
−
<!-- -->
 
−
 
−
11) Igual que la consulta 10, pero debe aparecer unas columnas adicionales (valores 1 o 0) indicando de que subtotal se trata.
 
  
 +
NO!11) Igual que la consulta 10, pero debe aparecer unas columnas adicionales (valores 1 o 0) indicando de que subtotal se trata.
 
<pre>
 
<pre>
 
SELECT department_id, job_id, SUM(salary),GROUPING(department_id), GROUPING(job_id)
 
SELECT department_id, job_id, SUM(salary),GROUPING(department_id), GROUPING(job_id)
Línia 112: Línia 99:
 
GROUP BY CUBE (department_id,job_id);
 
GROUP BY CUBE (department_id,job_id);
 
</pre>
 
</pre>
−
<!-- -->
+
-->
−
 
 
 
12) Muestra el id, first_name, department_id, i salari del empleat(s) que guanyen més de cada departament.
 
12) Muestra el id, first_name, department_id, i salari del empleat(s) que guanyen més de cada departament.
−
<!--
 
 
<pre>
 
<pre>
 
SELECT employee_id, first_name, department_id, salary
 
SELECT employee_id, first_name, department_id, salary
Línia 123: Línia 108:
 
WHERE salary = MAXIMS.maxsal;
 
WHERE salary = MAXIMS.maxsal;
 
</pre>
 
</pre>
−
-->
 
−
<!--
 
 
<pre>
 
<pre>
 
SELECT employee_id, first_name, department_id, salary
 
SELECT employee_id, first_name, department_id, salary
Línia 132: Línia 115:
 
               WHERE d.department_id= e.department_id);
 
               WHERE d.department_id= e.department_id);
 
</pre>
 
</pre>
−
-->
 
−
<!--
 
 
<pre>
 
<pre>
 
SELECT employee_id, first_name, department_id, salary
 
SELECT employee_id, first_name, department_id, salary
Línia 142: Línia 123:
  
 
</pre>
 
</pre>
−
-->
+
<!--
−
 
 
 
13) Igual que 12, pero sustituyendo el department_id por el department_name.
 
13) Igual que 12, pero sustituyendo el department_id por el department_name.
−
<!--
 
 
<pre>
 
<pre>
 
SELECT employee_id, first_name, department_name, salary
 
SELECT employee_id, first_name, department_name, salary
Línia 154: Línia 133:
 
WHERE salary = MAXIMS.maxsal;
 
WHERE salary = MAXIMS.maxsal;
 
</pre>
 
</pre>
−
-->
 
  
 
14) Quants departaments hi ha a cada país. Mostreu nom del país i nombre de departaments.
 
14) Quants departaments hi ha a cada país. Mostreu nom del país i nombre de departaments.
 +
<pre>
 +
SELECT c.country_name, count(department_id)
 +
FROM countries c NATURAL JOIN locations NATURAL JOIN departments
 +
GROUP BY c.country_name;
 +
</pre>
  
 
15) Quants empleats treballen a cada país. Mostreu nom del país i nombre de empleats.
 
15) Quants empleats treballen a cada país. Mostreu nom del país i nombre de empleats.
 +
<pre>
 +
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;
 +
</pre>
  
 
16) Mostreu el first_name i last_name, carreg i nom del departament dels 3 empleats que menys temps han estat fent una feina.
 
16) Mostreu el first_name i last_name, carreg i nom del departament dels 3 empleats que menys temps han estat fent una feina.
 +
<pre>
 +
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;
 +
</pre>
  
−
17) Quants empleats de cada grau salarial hi ha a cada departament. Mostreu nom del departament, grau salarial i nombre de empleats.
+
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.
 +
<pre>
 +
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;
 +
</pre>
  
 
18) Quin és l'ofici (job_title) amb un rang salarial més ampli.
 
18) Quin és l'ofici (job_title) amb un rang salarial més ampli.
 +
<pre>
 +
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);
 +
</pre>
  
 
19) Mostreu la següent sortida:
 
19) Mostreu la següent sortida:
Línia 176: Línia 188:
 
...
 
...
 
</pre>
 
</pre>
−
<!--
 
 
<pre>
 
<pre>
 
SELECT RPAD(UPPER(first_name) || ' ' || UPPER (last_name),18,' ') || ' tiene un salario de' ||
 
SELECT RPAD(UPPER(first_name) || ' ' || UPPER (last_name),18,' ') || ' tiene un salario de' ||
Línia 183: Línia 194:
 
FROM employees;
 
FROM employees;
 
</pre>
 
</pre>
−
-->
 
  
 
20) Quants oficis diferents hi ha a cada departament. Mostreu nom del departament i nombre d'oficis.
 
20) Quants oficis diferents hi ha a cada departament. Mostreu nom del departament i nombre d'oficis.
 +
<pre>
 +
SELECT department_name "Nom Departament", COUNT(DISTINCT job_id) "Nombre Oficis"
 +
FROM departments JOIN employees USING(department_id)
 +
GROUP BY department_name;
 +
</pre>
  
 
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.
 
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.
−
<!--
 
 
<pre>
 
<pre>
 
SELECT department_name "Departament", COUNT(employee_id) "Empelats",  
 
SELECT department_name "Departament", COUNT(employee_id) "Empelats",  
Línia 195: Línia 209:
 
GROUP BY department_name;
 
GROUP BY department_name;
 
</pre>
 
</pre>
−
-->
 
  
 
22) Quin cost en salaris, tenint en compte la comissió, té cada país. Mostreu el nom del país i el cost.
 
22) Quin cost en salaris, tenint en compte la comissió, té cada país. Mostreu el nom del país i el cost.
 +
<pre>
 +
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;
 +
</pre>
  
 
23) Quin és el país que té un cost salarial més alt, tenint en compte la comissió.
 
23) Quin és el país que té un cost salarial més alt, tenint en compte la comissió.
 +
<pre>
 +
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;
 +
</pre>
  
 
24) Quin són els tres països amb 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ó.
 +
<pre>
 +
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;
 +
</pre>
 +
  
 
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.
 
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.
Línia 212: Línia 247:
 
...
 
...
 
</pre>
 
</pre>
−
<!--
 
 
<pre>
 
<pre>
 
SELECT e.first_name "Nom", d.department_name "Department", j.job_title "Ofici",  
 
SELECT e.first_name "Nom", d.department_name "Department", j.job_title "Ofici",  
Línia 219: Línia 253:
 
     JOIN departments d ON(d.department_id=e.department_id)  
 
     JOIN departments d ON(d.department_id=e.department_id)  
 
     JOIN jobs j ON (j.job_id = h.job_id)
 
     JOIN jobs j ON (j.job_id = h.job_id)
−
ORDER BY 4, 5;
+
ORDER BY 4 DESC, 5 DESC;
 
</pre>
 
</pre>
−
-->
 
 
26) El mateix que el 25, però mostrant només els 5 que més temps han treballat.
 
26) El mateix que el 25, però mostrant només els 5 que més temps han treballat.
−
 
+
<pre>
−
 
+
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;
 +
</pre>
  
 
27) Muestra el nombre, apellido i salario mensual  de los empleados que cobren mas que Jennifer ordenado por salario descendiente
 
27) Muestra el nombre, apellido i salario mensual  de los empleados que cobren mas que Jennifer ordenado por salario descendiente
Línia 231: Línia 272:
 
   | Nombre i apellido  | Salario Mensual  |  
 
   | Nombre i apellido  | Salario Mensual  |  
 
   +--------------------+-------------------+
 
   +--------------------+-------------------+
−
 
+
<pre>
−
 
+
SELECT e.first_name||' '||e.last_name "Nombre i apellido"s, e.salary"Salario Mensual"
−
  SELECT e.first_name||' '||e.last_name "Nombre i apellido"s, e.salary"Salario Mensual"
+
FROM employees e
−
  FROM employees e
+
WHERE e.salary >ANY (SELECT e.salary
−
  WHERE e.salary >ANY (SELECT e.salary
 
 
                     FROM employees e
 
                     FROM employees e
 
                     WHERE UPPER(e.first_name)='JENNIFER');
 
                     WHERE UPPER(e.first_name)='JENNIFER');
−
+
</pre>
  
 
28) Para cada departamento Muestra el nombre del empleado/s que cobran menos.
 
28) Para cada departamento Muestra el nombre del empleado/s que cobran menos.
  
 
   +------------------------+-------------------+
 
   +------------------------+-------------------+
−
   | Nombre departamentodo | Nombre empleado  |  
+
   | Nombre departamento | Nombre empleado  |  
 
   +------------------------+-------------------+
 
   +------------------------+-------------------+
−
 
+
<pre>
−
 
+
SELECT d.department_name "Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado"
−
  SELECT e.department_id"Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado"
+
FROM employees e JOIN departments d USING(department_id)
−
  FROM e.employees e
+
WHERE (department_id,e.salary) IN (SELECT e.department_id, min(e.salary)
−
  WHERE (e.department.id,e.salary) IN (SELECT e.department_id, min(e.sal)
 
 
                                     FROM employees e
 
                                     FROM employees e
−
                                     GROUP BY e.department);
+
                                     GROUP BY e.department_id);
−
 
+
</pre>
−
 
 
  
 
29) Para cada región  muestra cuantos países hay. Ordena por numero de países ascendentemente.
 
29) Para cada región  muestra cuantos países hay. Ordena por numero de países ascendentemente.
Línia 260: Línia 298:
 
   | Nombre Región  | Numero de paises  |  
 
   | Nombre Región  | Numero de paises  |  
 
   +-----------------+-------------------+
 
   +-----------------+-------------------+
 +
<pre>
 +
SELECT r.region_name "Nombre Region", count(*)"Numero de paises"
 +
FROM regions r  NATURAL JOIN countries c
 +
GROUP BY r.region_name
 +
ORDER BY 2 ASC;
 +
</pre>
  
−
 
+
30) Muestra el nombre de los departamentos, nombre de su jefe i salario mensual, que se encuentran fuera de United States Of America.
−
  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 |  
 
   | Nombre Dep  | Jefe  |  Sueldo Mensual |  
 
   +------------- +-------+-----------------+
 
   +------------- +-------+-----------------+
−
 
+
<pre>
−
  SELECT d.department_name "Nombre Dep", e.first_name "Jefe", e.salary "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
+
FROM countries c NATURAL JOIN locations l NATURAL JOIN departments d JOIN employees e
−
  WHERE c.country_id=l.country_id
+
    ON (d.manager_id = e.employee_id)
−
  AND l.location_id=d.location.id
+
WHERE c.country_name <> 'United States of America';
−
  AND d.manager_id=e.employee_id
+
</pre>
−
  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.
 
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.
Línia 288: Línia 322:
 
   | Nombre pais | Region |  
 
   | Nombre pais | Region |  
 
   +-------------+--------+
 
   +-------------+--------+
−
 
+
<pre>
−
  SELECT c.country_name "Nombre país", r.region_name "Region"
+
SELECT c.country_name "Nombre país", r.region_name "Region"
−
  FROM countries c, regions r
+
FROM countries c NATURAL JOIN regions r
−
  WHERE c.region_id=r.region_id
+
WHERE UPPER(c.country_name) LIKE 'A%'  
−
  AND UPPER(c.country_name) LIKE 'A%'
+
  OR UPPER(c.country_name) LIKE '%A';
−
  OR UPPER(c.country_name) LIKE '%A';
+
</pre>
  
 
32) Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe.
 
32) Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe.
Línia 300: Línia 334:
 
   | Nombre empleado | Nombre jefe |  
 
   | Nombre empleado | Nombre jefe |  
 
   +-----------------+-------------+
 
   +-----------------+-------------+
−
 
+
<pre>
−
  SELECT UPPER(e.first_name) "Nombre empleado", j.first_name "Nombre jefe"  
+
SELECT UPPER(e.first_name) "Nombre empleado", UPPER(j.first_name) "Nombre jefe"  
−
  FROM employees e, employees j
+
FROM employees e JOIN employees j  
−
  WHERE e.manager_id=j.employee_id
+
on (e.manager_id=j.employee_id);
−
 
+
</pre> 
  
 
33) Para cada jefe muestra cuantos empleados tiene a su cargo.
 
33) Para cada jefe muestra cuantos empleados tiene a su cargo.
Línia 311: Línia 345:
 
   | Nombre jefe    | Numero empleados |  
 
   | Nombre jefe    | Numero empleados |  
 
   +-----------------+------------------+
 
   +-----------------+------------------+
−
 
+
<pre>
−
  SELECT h.first_name "Numero del jefe", COUNT(*)"Numero empleados"
+
SELECT j.first_name "Numero del jefe", COUNT(*)"Numero empleados"
−
  FROM employees e, employees h
+
FROM employees e JOIN employees j
−
  WHERE e.manager_id=h.employee_id
+
on (e.manager_id=j.employee_id)
−
  GROUP BY h.first_name
+
GROUP BY j.first_name;
−
 
+
</pre> 
  
 
34) Cantidad de empleados que han ingresado en la empresa en cada año.
 
34) Cantidad de empleados que han ingresado en la empresa en cada año.
Línia 323: Línia 357:
 
   |  Año  | Numero empleados |  
 
   |  Año  | Numero empleados |  
 
   +-------+------------------+
 
   +-------+------------------+
−
 
+
<pre>
−
  SELECT TO_CHAR(e.hire_date,'YYYY') "Año", COUNT(*)
+
SELECT TO_CHAR(e.hire_date,'YYYY') "Año", COUNT(*)
−
  FROM employees e
+
FROM employees e
−
  GROUP BY TO_CHAR(e.hire_date,'YYYY');  
+
GROUP BY TO_CHAR(e.hire_date,'YYYY');  
−
 
+
</pre> 
  
 
35) Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto.
 
35) Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto.
Línia 334: Línia 368:
 
   |  Nombre emp  |  Puesto | Meses trabajados |
 
   |  Nombre emp  |  Puesto | Meses trabajados |
 
   +--------------+---------+------------------+
 
   +--------------+---------+------------------+
−
 
+
<pre>
−
  SELECT e.first_name "Nombre empleado",jh.job_id "puesto", ROUND(MONTHS_BETWEEN(jh.start-date,NVL(jh.end-date,SYSDATE))"Meses trabajados"
+
SELECT e.first_name "Nombre empleado",j.job_title "puesto",  
−
  FROM employees e, jobs j, job_hystory jh  
+
      ROUND(MONTHS_BETWEEN(jh.end_date,jh.start_date)) "Meses trabajados"
−
  WHERE e.job_id=j.job_id
+
FROM employees e NATURAL JOIN jobs j JOIN job_history jh USING(job_id);
−
  AND e.employee_id=jh.employee_id;
+
</pre>
−
 
 
−
 
 
  
 
36) El nombre de los jefes de cada departamento y su telefono.
 
36) El nombre de los jefes de cada departamento y su telefono.
Línia 347: Línia 379:
 
   |  Nombre departamento  |  Nombre jefe    | Telefono jefe |
 
   |  Nombre departamento  |  Nombre jefe    | Telefono jefe |
 
   +------------------------+------------------+---------------+
 
   +------------------------+------------------+---------------+
−
 
+
<pre>
−
  SELECT d.department_name "Nombre departamento", e.first_name "Nombre jefe", e.phone_number "telefono jefe"
+
SELECT d.department_name "Nombre departamento", e.first_name "Nombre jefe", e.phone_number "telefono jefe"
−
  FROM employees e, departments d
+
FROM employees e, departments d
−
  WHERE d.manager_id=e.employee_id;  
+
WHERE d.manager_id=e.employee_id;  
−
 
+
</pre>
−
 
 
  
 
37) El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones)
 
37) El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones)
Línia 359: Línia 390:
 
   |  Nombre departamento  |  Coste año YYYY  |   
 
   |  Nombre departamento  |  Coste año YYYY  |   
 
   +-----------------------+------------------+
 
   +-----------------------+------------------+
−
 
+
<pre>
−
  SELECT d.department_name "Nombre departamento", SUM((e.salary*12)+NVL(e.comm))"Coste año 2015"
+
SELECT d.department_name "Nombre departamento", SUM((e.salary*12)+NVL(e.comm))"Coste año 2015"
−
  FROM departments d, employees e
+
FROM departments d, employees e
−
  WHERE d.department_id=e.department_id
+
WHERE d.department_id=e.department_id
−
  GROUP BY d.department_name;  
+
GROUP BY d.department_name;  
−
 
+
</pre>
  
 
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.
 
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.
Línia 371: Línia 402:
 
   |  Nombre departamento  |  Sueldo medio  | Salario maximo  |  
 
   |  Nombre departamento  |  Sueldo medio  | Salario maximo  |  
 
   +------------------------+-----------------+-----------------+
 
   +------------------------+-----------------+-----------------+
−
 
+
<pre>
−
 
+
SELECT d.department_name "Nombre departamento", ROUND(AVG(e.salary)) "Suedo medio", MAX(e.salary) "Salario máximo"
−
  SELECT d.department_name "Nombre departamento", ROUND(AVG(e.salary)) "Suedo medio", MAX(e.salary) "Salario máximo"
+
FROM departments d, employees e
−
FROM departments d, employees e
+
WHERE d.department_id=e.department_id
−
WHERE d.department_id=e.department_id
+
GROUP BY d.department_name;
−
GROUP BY d.department_name;
+
</pre>
 +
-->

Revisió de 19:11, 4 nov 2020

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);
Obtingut de «http://wikiserver.infomerce.es/index.php?title=M2_-_Bases_de_dades_/_Exercicis_UF2:_Llenguatges_SQL_/_Solucions_Repàs_consultes_HR&oldid=14182»