Diferència entre revisions de la pàgina «M2 - Bases de dades / Exercicis UF2: Llenguatges SQL / Solucions Repàs consultes HR»
De wikiserver
| (Hi ha 3 revisions intermèdies del mateix usuari que no es mostren) | |||
| Línia 63: | 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. | NO!7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles. | ||
<pre> | <pre> | ||
| 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 369: | 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 409: | 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);
