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)
 
(22 revisions intermèdies per 2 usuaris que no es mostren)
Línia 1: Línia 1:
 
==Solucions Repàs Consultes BBDD 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.
 +
<pre>
 +
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);
 +
</pre>
  
−
1. Muestra el nombre, apellido i salario mensual  de los empleados que cobren mas que Jennifer ordenado por salario descendiente
+
2) Igual que 1, pero mostrando también el nombre del departamento.
 +
<pre>
 +
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);
 +
</pre>
 +
 
 +
3) Igual que 1 pero mostrando los nombres de los empleados.
 +
<pre>
 +
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));
 +
</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.
 +
<pre>
 +
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;
 +
</pre>
 +
 
 +
5) Muestra id, first_name, last_name y hire_date de los 10 empleados más antiguos de la empresa.
 +
<pre>
 +
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;
 +
</pre>
 +
 
 +
6) Muestra id, first_name, last_name, nombre del departamento y salario de los 5 empleados mejor pagagados de la empresa.
 +
<pre>
 +
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;
 +
</pre>
 +
<!--
 +
NO!7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles.
 +
<pre>
 +
select  concat(lpad(' ', level * 3 - 3), last_name) nombre
 +
from employees
 +
connect by prior employee_id = manager_id
 +
start with manager_id is null;
 +
</pre>
 +
 
 +
NO!8) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros.
 +
<pre>
 +
SELECT department_id, job_id, SUM(salary)
 +
FROM employees
 +
GROUP BY ROLLUP (department_id,job_id);
 +
</pre>
 +
 
 +
NO!9) Igual que 8 pero que aparezca también los costes en función del manager.
 +
<pre>
 +
SELECT department_id, job_id, manager_id, SUM(salary)
 +
FROM employees
 +
GROUP BY ROLLUP ((department_id,job_id),(manager_id));
 +
</pre>
 +
 
 +
NO!10) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros y los subtotales relativos.
 +
<pre>
 +
SELECT department_id, job_id, SUM(salary)
 +
FROM employees
 +
GROUP BY CUBE (department_id,job_id);
 +
</pre>
 +
 
 +
NO!11) Igual que la consulta 10, pero debe aparecer unas columnas adicionales (valores 1 o 0) indicando de que subtotal se trata.
 +
<pre>
 +
SELECT department_id, job_id, SUM(salary),GROUPING(department_id), GROUPING(job_id)
 +
FROM employees
 +
GROUP BY CUBE (department_id,job_id);
 +
</pre>
 +
-->
 +
12) Muestra el id, first_name, department_id, i salari del empleat(s) que guanyen més de cada departament.
 +
<pre>
 +
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;
 +
</pre>
 +
<pre>
 +
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);
 +
</pre>
 +
<pre>
 +
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);
 +
 
 +
</pre>
 +
<!--
 +
13) Igual que 12, pero sustituyendo el department_id por el department_name.
 +
<pre>
 +
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;
 +
</pre>
 +
 
 +
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.
 +
<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.
 +
<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. 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.
 +
<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:
 +
<pre>
 +
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
 +
...
 +
</pre>
 +
<pre>
 +
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;
 +
</pre>
 +
 
 +
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.
 +
<pre>
 +
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;
 +
</pre>
 +
 
 +
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ó.
 +
<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ó.
 +
<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.
 +
<pre>
 +
Nom        Departament      Ofici                      Mesos    Dies
 +
--------    --------------  ------------------------    ------    -----
 +
Jennifer    Administration  Administration Assistant 69   2100
 +
Lex     Executive     Programmer                  66        2018
 +
Jennifer    Administration  Public Accountant         53        1644
 +
...
 +
</pre>
 +
<pre>
 +
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;
 +
</pre>
 +
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
  
 
   +--------------------+-------------------+
 
   +--------------------+-------------------+
 
   | 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>
  
−
2. 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.
−
3. Para cada región  muestra cuantos países hay. Ordena por numero de países ascendentemente.
 
  
 
   +-----------------+-------------------+
 
   +-----------------+-------------------+
 
   | 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;
 
−
 
 
−
 
 
−
4. 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"
 +
FROM countries c NATURAL JOIN locations l NATURAL JOIN departments d JOIN employees e
 +
    ON (d.manager_id = e.employee_id)
 +
WHERE c.country_name <> 'United States of America';
 +
</pre>
  
−
  SELECT d.department_name "Nombre Dep", e.first_name "Jefe", e.salary "Sueldo Mensual"
+
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.
−
  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';
 
−
 
 
−
 
 
−
5. 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 |  
 
   | Nombre pais | Region |  
 
   +-------------+--------+
 
   +-------------+--------+
 +
<pre>
 +
SELECT c.country_name "Nombre país", r.region_name "Region"
 +
FROM countries c NATURAL JOIN regions r
 +
WHERE UPPER(c.country_name) LIKE 'A%'
 +
  OR UPPER(c.country_name) LIKE '%A';
 +
</pre>
  
−
  SELECT c.country_name "Nombre país", r.region_name "Region"
+
32) Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe.
−
  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';
 
−
 
 
−
6. Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe.
 
  
 
   +-----------------+-------------+
 
   +-----------------+-------------+
 
   | Nombre empleado | Nombre jefe |  
 
   | Nombre empleado | Nombre jefe |  
 
   +-----------------+-------------+
 
   +-----------------+-------------+
 +
<pre>
 +
SELECT UPPER(e.first_name) "Nombre empleado", UPPER(j.first_name) "Nombre jefe"
 +
FROM employees e JOIN employees j
 +
on (e.manager_id=j.employee_id);
 +
</pre> 
  
−
  SELECT UPPER(e.first_name) "Nombre empleado", j.first_name "Nombre jefe"
+
33) Para cada jefe muestra cuantos empleados tiene a su cargo.
−
  FROM employees e, employees j
 
−
  WHERE e.manager_id=j.employee_id
 
−
 
 
−
 
 
−
7. Para cada jefe muestra cuantos empleados tiene a su cargo.
 
  
 
   +-----------------+------------------+
 
   +-----------------+------------------+
 
   | Nombre jefe    | Numero empleados |  
 
   | Nombre jefe    | Numero empleados |  
 
   +-----------------+------------------+
 
   +-----------------+------------------+
 +
<pre>
 +
SELECT j.first_name "Numero del jefe", COUNT(*)"Numero empleados"
 +
FROM employees e JOIN employees j
 +
on (e.manager_id=j.employee_id)
 +
GROUP BY j.first_name;
 +
</pre> 
  
−
  SELECT h.first_name "Numero del jefe", COUNT(*)"Numero empleados"
+
34) Cantidad de empleados que han ingresado en la empresa en cada año.
−
  FROM employees e, employees h
 
−
  WHERE e.manager_id=h.employee_id
 
−
  GROUP BY h.first_name
 
−
 
 
−
 
 
−
8. Cantidad de empleados que han ingresado en la empresa en cada año.
 
  
 
   +-------+------------------+
 
   +-------+------------------+
 
   |  Año  | Numero empleados |  
 
   |  Año  | Numero empleados |  
 
   +-------+------------------+
 
   +-------+------------------+
 +
<pre>
 +
SELECT TO_CHAR(e.hire_date,'YYYY') "Año", COUNT(*)
 +
FROM employees e
 +
GROUP BY TO_CHAR(e.hire_date,'YYYY');
 +
</pre> 
  
−
  SELECT TO_CHAR(e.hire_date,'YYYY') "Año", COUNT(*)
+
35) Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto.
−
  FROM employees e
 
−
  GROUP BY TO_CHAR(e.hire_date,'YYYY');
 
−
 
 
−
 
 
−
9. Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto.
 
  
 
   +--------------+---------+------------------+
 
   +--------------+---------+------------------+
 
   |  Nombre emp  |  Puesto | Meses trabajados |
 
   |  Nombre emp  |  Puesto | Meses trabajados |
 
   +--------------+---------+------------------+
 
   +--------------+---------+------------------+
 +
<pre>
 +
SELECT e.first_name "Nombre empleado",j.job_title "puesto",
 +
      ROUND(MONTHS_BETWEEN(jh.end_date,jh.start_date)) "Meses trabajados"
 +
FROM employees e NATURAL JOIN jobs j JOIN job_history jh USING(job_id);
 +
</pre>
  
−
  SELECT e.first_name "Nombre empleado",jh.job_id "puesto", ROUND(MONTHS_BETWEEN(jh.start-date,NVL(jh.end-date,SYSDATE))"Meses trabajados"
+
36) El nombre de los jefes de cada departamento y su telefono.
−
  FROM employees e, jobs j, job_hystory jh
 
−
  WHERE e.job_id=j.job_id
 
−
  AND e.employee_id=jh.employee_id;
 
−
 
 
−
 
 
−
 
 
−
10. El nombre de los jefes de cada departamento y su telefono.
 
  
 
   +------------------------+------------------+---------------+
 
   +------------------------+------------------+---------------+
 
   |  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"
 +
FROM employees e, departments d
 +
WHERE d.manager_id=e.employee_id;
 +
</pre>
  
−
  SELECT d.department_name "Nombre departamento", e.first_name "Nombre jefe", e.phone_number "telefono jefe"
+
37) El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones)
−
  FROM employees e, departments d
 
−
  WHERE d.manager_id=e.employee_id;
 
−
 
 
−
 
 
−
 
 
−
11. 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  |   
 
   |  Nombre departamento  |  Coste año YYYY  |   
 
   +-----------------------+------------------+
 
   +-----------------------+------------------+
 +
<pre>
 +
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;
 +
</pre>
  
−
  SELECT d.department_name "Nombre departamento", SUM((e.salary*12)+NVL(e.comm))"Coste año 2015"
+
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.
−
  FROM departments d, employees e
 
−
  WHERE d.department_id=e.department_id
 
−
  GROUP BY d.department_name;
 
−
 
 
−
 
 
−
12. 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  |  
 
   |  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", 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 1;
+
</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»