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 231: | Línia 231: | ||
| − | + | <!-- | |
27) Muestra el nombre, apellido i salario mensual de los empleados que cobren mas que Jennifer ordenado por salario descendiente | 27) Muestra el nombre, apellido i salario mensual de los empleados que cobren mas que Jennifer ordenado por salario descendiente | ||
| Línia 378: | Línia 378: | ||
GROUP BY d.department_name; | GROUP BY d.department_name; | ||
</pre> | </pre> | ||
| + | --> | ||
Revisió del 08:22, 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.
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.
