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
 
(Hi ha 14 revisions intermèdies del mateix usuari que no es mostren)
Línia 63: Línia 63:
 
WHERE rownum <=5;
 
WHERE rownum <=5;
 
</pre>
 
</pre>
−
 
+
<!--
−
7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles.
+
NO!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 72: Línia 72:
 
</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 79: Línia 79:
 
</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 86: Línia 86:
 
</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 93: Línia 93:
 
</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 99: 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>
Línia 123: 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>
Línia 196: Línia 196:
  
 
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.
Línia 206: Línia 211:
  
 
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 226: 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 248: Línia 283:
  
 
   +------------------------+-------------------+
 
   +------------------------+-------------------+
−
   | Nombre departamentodo | Nombre empleado  |  
+
   | Nombre departamento | Nombre empleado  |  
 
   +------------------------+-------------------+
 
   +------------------------+-------------------+
 
<pre>
 
<pre>
−
SELECT e.department_id"Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado"
+
SELECT d.department_name "Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado"
−
FROM e.employees e
+
FROM employees e JOIN departments d USING(department_id)
−
WHERE (e.department.id,e.salary) IN (SELECT e.department_id, min(e.sal)
+
WHERE (department_id,e.salary) IN (SELECT e.department_id, min(e.salary)
 
                                     FROM employees e
 
                                     FROM employees e
−
                                     GROUP BY e.department);
+
                                     GROUP BY e.department_id);
 
</pre>
 
</pre>
  
Línia 265: Línia 300:
 
<pre>
 
<pre>
 
SELECT r.region_name "Nombre Region", count(*)"Numero de paises"
 
SELECT r.region_name "Nombre Region", count(*)"Numero de paises"
−
FROM regions r,countries c
+
FROM regions r NATURAL JOIN countries c
−
WHERE r.region_id=c.region_id
 
 
GROUP BY r.region_name  
 
GROUP BY r.region_name  
 
ORDER BY 2 ASC;
 
ORDER BY 2 ASC;
 
</pre>
 
</pre>
  
−
30) Muestra el nombre de los departamentos i nombre de su jefe, que se encuentran fuera de EEUU.
+
30) Muestra el nombre de los departamentos, nombre de su jefe i salario mensual, que se encuentran fuera de United States Of America.
  
 
   +--------------+-------+-----------------+
 
   +--------------+-------+-----------------+
Línia 278: Línia 312:
 
<pre>
 
<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
 
−
  AND c.country_name <> 'United States Of America';  
 
 
</pre>
 
</pre>
  
Línia 292: Línia 324:
 
<pre>
 
<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>
 
</pre>
  
Línia 304: Línia 335:
 
   +-----------------+-------------+
 
   +-----------------+-------------+
 
<pre>
 
<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>   
 
</pre>   
  
Línia 315: Línia 346:
 
   +-----------------+------------------+
 
   +-----------------+------------------+
 
<pre>
 
<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>   
 
</pre>   
  
Línia 338: Línia 369:
 
   +--------------+---------+------------------+
 
   +--------------+---------+------------------+
 
<pre>
 
<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>
 
</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»