Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions Repàs consultes HR»
(→Solucions Repàs Consultes BBDD HR) |
|||
(Hi ha 10 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 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 | + | FROM regions r NATURAL JOIN countries c |
− | |||
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 | + | 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 | + | 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> | </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 | + | FROM countries c NATURAL JOIN regions r |
− | WHERE | + | WHERE 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 | + | FROM employees e JOIN employees j |
− | + | on (e.manager_id=j.employee_id); | |
</pre> | </pre> | ||
Línia 350: | Línia 346: | ||
+-----------------+------------------+ | +-----------------+------------------+ | ||
<pre> | <pre> | ||
− | SELECT | + | SELECT j.first_name "Numero del jefe", COUNT(*)"Numero empleados" |
− | FROM employees e | + | FROM employees e JOIN employees j |
− | + | on (e.manager_id=j.employee_id) | |
− | GROUP BY | + | GROUP BY j.first_name; |
</pre> | </pre> | ||
Línia 373: | Línia 369: | ||
+--------------+---------+------------------+ | +--------------+---------+------------------+ | ||
<pre> | <pre> | ||
− | SELECT e.first_name "Nombre empleado", | + | SELECT e.first_name "Nombre empleado",j.job_title "puesto", |
− | FROM employees e | + | 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> | </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);