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) |
(→Solucions Repàs Consultes BBDD HR) |
||
Línia 11: | Línia 11: | ||
GROUP BY department_id); | GROUP BY department_id); | ||
</pre> | </pre> | ||
− | + | ||
− | 2) Igual que | + | 2) Igual que 1, pero mostrando también el nombre del departamento. |
<pre> | <pre> | ||
SELECT department_id, d.department_name | SELECT department_id, d.department_name | ||
Línia 23: | Línia 23: | ||
GROUP BY department_id); | GROUP BY department_id); | ||
</pre> | </pre> | ||
− | + | ||
− | 3) Igual que | + | 3) Igual que 1 pero mostrando los nombres de los empleados. |
<pre> | <pre> | ||
SELECT first_name, last_name, department_id | SELECT first_name, last_name, department_id | ||
Línia 36: | Línia 36: | ||
GROUP BY department_id)); | GROUP BY department_id)); | ||
</pre> | </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. | 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> | <pre> | ||
Línia 45: | Línia 45: | ||
WHERE salary > average.avgsalary; | WHERE salary > average.avgsalary; | ||
</pre> | </pre> | ||
− | + | ||
5) Muestra id, first_name, last_name y hire_date de los 10 empleados más antiguos de la empresa. | 5) Muestra id, first_name, last_name y hire_date de los 10 empleados más antiguos de la empresa. | ||
− | |||
<pre> | <pre> | ||
SELECT rownum, first_name, last_name, hire_date | SELECT rownum, first_name, last_name, hire_date | ||
Línia 55: | Línia 54: | ||
WHERE rownum <=10; | WHERE rownum <=10; | ||
</pre> | </pre> | ||
− | |||
6) Muestra id, first_name, last_name, nombre del departamento y salario de los 5 empleados mejor pagagados de la empresa. | 6) Muestra id, first_name, last_name, nombre del departamento y salario de los 5 empleados mejor pagagados de la empresa. | ||
− | |||
<pre> | <pre> | ||
SELECT rownum, first_name, last_name, salary, department_name | SELECT rownum, first_name, last_name, salary, department_name | ||
Línia 66: | Línia 63: | ||
WHERE rownum <=5; | WHERE rownum <=5; | ||
</pre> | </pre> | ||
− | |||
7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles. | 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 76: | Línia 71: | ||
start with manager_id is null; | start with manager_id is null; | ||
</pre> | </pre> | ||
− | |||
8) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros. | 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 85: | Línia 78: | ||
GROUP BY ROLLUP (department_id,job_id); | GROUP BY ROLLUP (department_id,job_id); | ||
</pre> | </pre> | ||
− | |||
9) Igual que 8 pero que aparezca también los costes en función del manager. | 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 94: | Línia 85: | ||
GROUP BY ROLLUP ((department_id,job_id),(manager_id)); | GROUP BY ROLLUP ((department_id,job_id),(manager_id)); | ||
</pre> | </pre> | ||
− | |||
10) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros y los subtotales relativos. | 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 103: | Línia 92: | ||
GROUP BY CUBE (department_id,job_id); | GROUP BY CUBE (department_id,job_id); | ||
</pre> | </pre> | ||
− | |||
11) Igual que la consulta 10, pero debe aparecer unas columnas adicionales (valores 1 o 0) indicando de que subtotal se trata. | 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 112: | 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> | ||
SELECT employee_id, first_name, department_id, salary | SELECT employee_id, first_name, department_id, salary | ||
Línia 123: | Línia 108: | ||
WHERE salary = MAXIMS.maxsal; | WHERE salary = MAXIMS.maxsal; | ||
</pre> | </pre> | ||
− | |||
− | |||
<pre> | <pre> | ||
SELECT employee_id, first_name, department_id, salary | SELECT employee_id, first_name, department_id, salary | ||
Línia 132: | Línia 115: | ||
WHERE d.department_id= e.department_id); | WHERE d.department_id= e.department_id); | ||
</pre> | </pre> | ||
− | |||
− | |||
<pre> | <pre> | ||
SELECT employee_id, first_name, department_id, salary | SELECT employee_id, first_name, department_id, salary | ||
Línia 142: | 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> | ||
SELECT employee_id, first_name, department_name, salary | SELECT employee_id, first_name, department_name, salary | ||
Línia 154: | Línia 133: | ||
WHERE salary = MAXIMS.maxsal; | WHERE salary = MAXIMS.maxsal; | ||
</pre> | </pre> | ||
− | |||
14) Quants departaments hi ha a cada país. Mostreu nom del país i nombre de departaments. | 14) Quants departaments hi ha a cada país. Mostreu nom del país i nombre de departaments. | ||
Línia 176: | Línia 154: | ||
... | ... | ||
</pre> | </pre> | ||
− | |||
<pre> | <pre> | ||
SELECT RPAD(UPPER(first_name) || ' ' || UPPER (last_name),18,' ') || ' tiene un salario de' || | SELECT RPAD(UPPER(first_name) || ' ' || UPPER (last_name),18,' ') || ' tiene un salario de' || | ||
Línia 183: | Línia 160: | ||
FROM employees; | FROM employees; | ||
</pre> | </pre> | ||
− | |||
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. | ||
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. | ||
− | |||
<pre> | <pre> | ||
SELECT department_name "Departament", COUNT(employee_id) "Empelats", | SELECT department_name "Departament", COUNT(employee_id) "Empelats", | ||
Línia 195: | Línia 170: | ||
GROUP BY department_name; | GROUP BY department_name; | ||
</pre> | </pre> | ||
− | |||
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. | ||
Línia 212: | Línia 186: | ||
... | ... | ||
</pre> | </pre> | ||
− | |||
<pre> | <pre> | ||
SELECT e.first_name "Nom", d.department_name "Department", j.job_title "Ofici", | SELECT e.first_name "Nom", d.department_name "Department", j.job_title "Ofici", | ||
Línia 221: | Línia 194: | ||
ORDER BY 4, 5; | ORDER BY 4, 5; | ||
</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. | ||
Línia 231: | Línia 203: | ||
| 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" | |
− | + | FROM employees e | |
− | + | 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> | |
28) 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. | ||
Línia 245: | Línia 216: | ||
| Nombre departamentodo | Nombre empleado | | | Nombre departamentodo | Nombre empleado | | ||
+------------------------+-------------------+ | +------------------------+-------------------+ | ||
− | + | <pre> | |
− | + | SELECT e.department_id"Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado" | |
− | + | FROM e.employees e | |
− | + | 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); | ||
− | + | </pre> | |
− | |||
29) Para cada región muestra cuantos países hay. Ordena por numero de países ascendentemente. | 29) Para cada región muestra cuantos países hay. Ordena por numero de países ascendentemente. | ||
Línia 260: | Línia 229: | ||
| 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,countries c | |
− | + | WHERE r.region_id=c.region_id | |
− | + | GROUP BY r.region_name | |
− | + | ORDER BY 2 ASC; | |
− | + | </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 i nombre de su jefe, que se encuentran fuera de EEUU. | ||
Línia 274: | Línia 242: | ||
| 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, locations l, departments d, employees e | |
− | + | WHERE c.country_id=l.country_id | |
AND l.location_id=d.location.id | AND l.location_id=d.location.id | ||
AND d.manager_id=e.employee_id | AND d.manager_id=e.employee_id | ||
AND c.country_name <> 'United States Of America'; | AND c.country_name <> 'United States Of America'; | ||
− | + | </pre> | |
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. | 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. | ||
Línia 288: | Línia 256: | ||
| Nombre pais | Region | | | Nombre pais | Region | | ||
+-------------+--------+ | +-------------+--------+ | ||
− | + | <pre> | |
− | + | SELECT c.country_name "Nombre país", r.region_name "Region" | |
− | + | FROM countries c, regions r | |
− | + | WHERE c.region_id=r.region_id | |
AND 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> | ||
32) Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe. | 32) Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe. | ||
Línia 300: | Línia 269: | ||
| Nombre empleado | Nombre jefe | | | Nombre empleado | Nombre jefe | | ||
+-----------------+-------------+ | +-----------------+-------------+ | ||
− | + | <pre> | |
− | + | SELECT UPPER(e.first_name) "Nombre empleado", j.first_name "Nombre jefe" | |
− | + | FROM employees e, employees j | |
− | + | WHERE e.manager_id=j.employee_id | |
− | + | </pre> | |
33) Para cada jefe muestra cuantos empleados tiene a su cargo. | 33) Para cada jefe muestra cuantos empleados tiene a su cargo. | ||
Línia 311: | Línia 280: | ||
| Nombre jefe | Numero empleados | | | Nombre jefe | Numero empleados | | ||
+-----------------+------------------+ | +-----------------+------------------+ | ||
− | + | <pre> | |
− | + | SELECT h.first_name "Numero del jefe", COUNT(*)"Numero empleados" | |
− | + | FROM employees e, employees h | |
− | + | WHERE e.manager_id=h.employee_id | |
− | + | GROUP BY h.first_name | |
− | + | </pre> | |
34) Cantidad de empleados que han ingresado en la empresa en cada año. | 34) Cantidad de empleados que han ingresado en la empresa en cada año. | ||
Línia 323: | Línia 292: | ||
| 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> | |
35) Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto. | 35) Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto. | ||
Línia 334: | Línia 303: | ||
| Nombre emp | Puesto | Meses trabajados | | | Nombre emp | Puesto | Meses trabajados | | ||
+--------------+---------+------------------+ | +--------------+---------+------------------+ | ||
− | + | <pre> | |
− | + | SELECT e.first_name "Nombre empleado",jh.job_id "puesto", ROUND(MONTHS_BETWEEN(jh.start-date,NVL(jh.end-date,SYSDATE))"Meses trabajados" | |
− | + | FROM employees e, jobs j, job_hystory jh | |
− | + | WHERE e.job_id=j.job_id | |
AND e.employee_id=jh.employee_id; | AND e.employee_id=jh.employee_id; | ||
− | + | </pre> | |
− | |||
36) El nombre de los jefes de cada departamento y su telefono. | 36) El nombre de los jefes de cada departamento y su telefono. | ||
Línia 347: | Línia 315: | ||
| 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> | |
− | |||
37) El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones) | 37) El costo de cada departamento en el ultimo año (suma de salario *12 de todos los empleados mas comisiones) | ||
Línia 359: | Línia 326: | ||
| 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> | |
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. | 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. | ||
Línia 371: | Línia 338: | ||
| 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" | |
− | + | FROM departments d, employees e | |
− | + | WHERE d.department_id=e.department_id | |
− | + | GROUP BY d.department_name; | |
− | + | </pre> |
Revisió del 13:45, 5 des 2017
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;
7) Muestra la estructura jerárquica de la empresa identando los diferentes niveles.
select concat(lpad(' ', level * 3 - 3), last_name) nombre from employees connect by prior employee_id = manager_id start with manager_id is null;
8) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros.
SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY ROLLUP (department_id,job_id);
9) Igual que 8 pero que aparezca también los costes en función del manager.
SELECT department_id, job_id, manager_id, SUM(salary) FROM employees GROUP BY ROLLUP ((department_id,job_id),(manager_id));
10) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros y los subtotales relativos.
SELECT department_id, job_id, SUM(salary) FROM employees GROUP BY CUBE (department_id,job_id);
11) Igual que la consulta 10, pero debe aparecer unas columnas adicionales (valores 1 o 0) indicando de que subtotal se trata.
SELECT department_id, job_id, SUM(salary),GROUPING(department_id), GROUPING(job_id) FROM employees GROUP BY CUBE (department_id,job_id);
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);
13) Igual que 12, pero sustituyendo el department_id por el department_name.
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;
14) Quants departaments hi ha a cada país. Mostreu nom del país i nombre de departaments.
15) Quants empleats treballen a cada país. Mostreu nom del país i nombre de empleats.
16) Mostreu el first_name i last_name, carreg i nom del departament dels 3 empleats que menys temps han estat fent una feina.
17) Quants empleats de cada grau salarial hi ha a cada departament. Mostreu nom del departament, grau salarial i nombre de empleats.
18) Quin és l'ofici (job_title) amb un rang salarial més ampli.
19) Mostreu la següent sortida:
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 ...
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;
20) Quants oficis diferents hi ha a cada departament. Mostreu nom del departament i nombre d'oficis.
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.
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;
22) Quin cost en salaris, tenint en compte la comissió, té cada país. Mostreu el nom del país i el cost.
23) Quin és el país que té 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ó.
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.
Nom Departament Ofici Mesos Dies -------- -------------- ------------------------ ------ ----- Jennifer Administration Administration Assistant 69 2100 Lex Executive Programmer 66 2018 Jennifer Administration Public Accountant 53 1644 ...
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, 5;
26) El mateix que el 25, però mostrant només els 5 que més temps han treballat.
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 | +--------------------+-------------------+
SELECT e.first_name||' '||e.last_name "Nombre i apellido"s, e.salary"Salario Mensual" FROM employees e WHERE e.salary >ANY (SELECT e.salary FROM employees e WHERE UPPER(e.first_name)='JENNIFER');
28) Para cada departamento Muestra el nombre del empleado/s que cobran menos.
+------------------------+-------------------+ | Nombre departamentodo | Nombre empleado | +------------------------+-------------------+
SELECT e.department_id"Nombre departamento", e.first_name ||' '|| e.last_name "Nombre empleado" FROM e.employees e WHERE (e.department.id,e.salary) IN (SELECT e.department_id, min(e.sal) FROM employees e GROUP BY e.department);
29) Para cada región muestra cuantos países hay. Ordena por numero de países ascendentemente.
+-----------------+-------------------+ | Nombre Región | Numero de paises | +-----------------+-------------------+
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;
30) Muestra el nombre de los departamentos i nombre de su jefe, que se encuentran fuera de EEUU.
+--------------+-------+-----------------+ | Nombre Dep | Jefe | 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 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';
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.
+-------------+--------+ | Nombre pais | Region | +-------------+--------+
SELECT c.country_name "Nombre país", r.region_name "Region" 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';
32) Muestra el nombre de cada empleado en mayúsculas junto con el de su respectivo jefe.
+-----------------+-------------+ | Nombre empleado | Nombre jefe | +-----------------+-------------+
SELECT UPPER(e.first_name) "Nombre empleado", j.first_name "Nombre jefe" FROM employees e, employees j WHERE e.manager_id=j.employee_id
33) Para cada jefe muestra cuantos empleados tiene a su cargo.
+-----------------+------------------+ | Nombre jefe | Numero empleados | +-----------------+------------------+
SELECT h.first_name "Numero del jefe", COUNT(*)"Numero empleados" FROM employees e, employees h WHERE e.manager_id=h.employee_id GROUP BY h.first_name
34) Cantidad de empleados que han ingresado en la empresa en cada año.
+-------+------------------+ | Año | Numero empleados | +-------+------------------+
SELECT TO_CHAR(e.hire_date,'YYYY') "Año", COUNT(*) FROM employees e GROUP BY TO_CHAR(e.hire_date,'YYYY');
35) Para cada empleado y cada puesto ocupado los meses que ha trabajado en dicho puesto.
+--------------+---------+------------------+ | Nombre emp | Puesto | Meses trabajados | +--------------+---------+------------------+
SELECT e.first_name "Nombre empleado",jh.job_id "puesto", ROUND(MONTHS_BETWEEN(jh.start-date,NVL(jh.end-date,SYSDATE))"Meses trabajados" FROM employees e, jobs j, job_hystory jh WHERE e.job_id=j.job_id AND e.employee_id=jh.employee_id;
36) El nombre de los jefes de cada departamento y su telefono.
+------------------------+------------------+---------------+ | Nombre departamento | Nombre jefe | Telefono jefe | +------------------------+------------------+---------------+
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;
37) 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 | +-----------------------+------------------+
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;
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.
+------------------------+-----------------+-----------------+ | Nombre departamento | Sueldo medio | Salario maximo | +------------------------+-----------------+-----------------+
SELECT d.department_name "Nombre departamento", ROUND(AVG(e.salary)) "Suedo medio", MAX(e.salary) "Salario máximo" FROM departments d, employees e WHERE d.department_id=e.department_id GROUP BY d.department_name;