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 11 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 283: 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 300: 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 313: 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 327: 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 339: 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 350: 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 373: 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>
  
Línia 413: Línia 408:
 
GROUP BY d.department_name;
 
GROUP BY d.department_name;
 
</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»