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) |
|||
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. |
Revisió del 08:40, 15 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.
SELECT c.country_name, count(department_id) FROM countries c NATURAL JOIN locations NATURAL JOIN departments GROUP BY c.country_name;
15) Quants empleats treballen a cada país. Mostreu nom del país i nombre de empleats.
SELECT c.country_name, count(e.employee_id) FROM countries c NATURAL JOIN locations l NATURAL JOIN departments JOIN employees e USING (department_id) GROUP BY c.country_name;
16) Mostreu el first_name i last_name, carreg i nom del departament dels 3 empleats que menys temps han estat fent una feina.
SELECT * FROM (SELECT first_name, last_name, j.job_id, department_name FROM employees e JOIN job_history j USING (employee_id) JOIN departments d ON (d.department_id= e.department_id) ORDER BY j.end_date-j.start_date) WHERE ROWNUM<=3;
17) Quants empleats de cada grau salarial hi ha a cada departament. Mostreu nom del departament, grau salarial i nombre de empleats. Ordenat per nom de departament i grau salarial.
SELECT d.department_name, j.grade_level, COUNT(e.employee_id) FROM departments d JOIN employees e USING(department_id) JOIN job_grades j ON (e.salary BETWEEN j.lowest_sal AND j.highest_sal) GROUP BY d.department_name, j.grade_level ORDER BY d.department_name, j.grade_level;
18) Quin és l'ofici (job_title) amb un rang salarial més ampli.
SELECT job_title FROM jobs NATURAL JOIN employees GROUP BY job_title HAVING MAX(salary)-MIN(salary) = (SELECT MAX(MAX(salary)-MIN(salary)) FROM employees GROUP BY job_id);
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.
SELECT department_name "Nom Departament", COUNT(DISTINCT job_id) "Nombre Oficis" FROM departments JOIN employees USING(department_id) GROUP BY department_name;
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.
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;
23) Quin és el país que té un cost salarial més alt, tenint en compte la comissió.
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;
24) Quin són els tres països amb un cost salarial més alt, tenint en compte la comissió.
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;
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.