|
|
Línia 90: |
Línia 90: |
| FROM employees); | | FROM employees); |
| </pre> | | </pre> |
− |
| |
− | '''Adicionales'''
| |
− |
| |
− | 9) Muestra los departamentos (departament_id) en los que sus empleados tienen un salario superior a todos los empleados de otro departamento.
| |
− | <pre>
| |
− | 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);
| |
− | </pre>
| |
− |
| |
− | 10) Igual que 9, pero mostrando también el nombre del departamento.
| |
− | <pre>
| |
− | 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);
| |
− | </pre>
| |
− |
| |
− | 11) Igual que 9 pero mostrando los nombres de los empleados.
| |
− | <pre>
| |
− | 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));
| |
− | </pre>
| |
− |
| |
− | 12)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>
| |
− | 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;
| |
− | </pre>
| |
− |
| |
− | 13) Muestra id, first_name, last_name y hire_date de los 10 empleados más antiguos de la empresa.
| |
− | <!--
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | -->
| |
− |
| |
− | 14) Muestra id, first_name, last_name, nombre del departamento y salario de los 5 empleados mejor pagagados de la empresa.
| |
− | <!--
| |
− | <pre>
| |
− | 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;
| |
− | </pre>
| |
− | -->
| |
− |
| |
− | 15) Muestra la estructura jerárquica de la empresa identando los diferentes niveles.
| |
− | <!--
| |
− | <pre>
| |
− | select concat(lpad(' ', level * 3 - 3), last_name) nombre
| |
− | from employees
| |
− | connect by prior employee_id = manager_id
| |
− | start with manager_id is null;
| |
− | </pre>
| |
− | -->
| |
− |
| |
− | 16) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros.
| |
− | <!--
| |
− | <pre>
| |
− | SELECT department_id, job_id, SUM(salary)
| |
− | FROM employees
| |
− | GROUP BY ROLLUP (department_id,job_id);
| |
− | </pre>
| |
− | -->
| |
− |
| |
− | 17) Igual que 16 pero que aparezca también los costes en función del manager.
| |
− | <!--
| |
− | <pre>
| |
− | SELECT department_id, job_id, manager_id, SUM(salary)
| |
− | FROM employees
| |
− | GROUP BY ROLLUP ((department_id,job_id),(manager_id));
| |
− | </pre>
| |
− | -->
| |
− |
| |
− | 18) Muestra los costes totales de los salarios por departamento y oficio. Deben aparacer los registros y los subtotales relativos.
| |
− | <!--
| |
− | <pre>
| |
− | SELECT department_id, job_id, SUM(salary)
| |
− | FROM employees
| |
− | GROUP BY CUBE (department_id,job_id);
| |
− | </pre>
| |
− | -->
| |
− |
| |
− | 19) Igual que la consulta 18, pero debe aparecer unas columnas adicionales (valores 1 o 0) indicando de que subtotal se trata.
| |
− | <!--
| |
− | <pre>
| |
− | SELECT department_id, job_id, SUM(salary),GROUPING(department_id), GROUPING(job_id)
| |
− | FROM employees
| |
− | GROUP BY CUBE (department_id,job_id);
| |
− | </pre>
| |
− | -->
| |
− |
| |
− | 20) 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);
| |
− |
| |
− | -->
| |
− |
| |
− | 21) Igual que 20, 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;
| |
− | -->
| |
Revisió del 11:44, 4 des 2017
1) El departamento de recursos humanos necesita una consulta que solicite al usuario el apellido de un empleado. A continuación, la consulta muestra el apellido y la fecha de contratación de cualquier empleado en el mismo departamento del empleado cuyo nombre se proporciona (excepto ese empleado). Por ejemplo, si el usuario introduce Zlotkey , se buscarán todos los empleados que trabajen con Zlotkey (excepto Zlotkey).
UNDEFINE Enter_name
SELECT last_name, hire_date
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
WHERE last_name = '&&Enter_name')
AND last_name <> '&Enter_name';
2) Cree un informe que muestre el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio. Ordene los resultados en orden ascendente de salario.
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary)
FROM employees)
ORDER BY salary;
3) Escriba una consulta que muestre el número de empleado y el apellido de todos los empleados que trabajan en un departamento con un empleado cuyo apellido contiene una "u". Guarde la sentencia SQL en un archivo denominado lab_07_03.sql .
Ejecute la consulta.
SELECT employee_id, last_name
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE last_name like '%u%');
4) El departamento de recursos humanos necesita un informe que muestre el apellido,
número de departamento e ID de cargo de todos los empleados cuyo ID de ubicación
de departamento sea 1700.
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700);
Modifique la consulta para que se solicite al usuario un ID de ubicación. Guarde estos datos en un archivo denominado lab_07_04.sql .
SELECT last_name, department_id, job_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = &Enter_location);
5) Cree un informe para el departamento de recursos humanos que muestre el apellido y el salario de cada empleado que realice informes para Steven King.
SELECT last_name, salary
FROM employees
WHERE manager_id = (SELECT employee_id
FROM employees
WHERE first_name='Steven' AND last_name = 'King');
6) Cree un informe para el departamento de recursos humanos que muestre el número de departamento, apellido e ID de cargo de cada empleado del departamento Executive.
SELECT department_id, last_name, job_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE department_name = 'Executive');
7) Cree un informe que muestre una lista de todos los empleados cuyo salario sea mayor que el salario de los empleados del departamento 60.
SELECT last_name FROM employees
WHERE salary > ANY (SELECT salary
FROM employees
WHERE department_id=60);
Si tiene tiempo, realice el siguiente ejercicio:
8) Modifique la consulta del archivo lab_07_03.sql para mostrar el número de empleado, apellido y salario de todos los empleados que ganan más del salario medio y que trabajan en un departamento con cualquier empleado cuyo apellido contiene una "u". Vuelva a guardar lab_07_03.sql como lab_07_08.sql . Ejecute la
sentencia en el archivo lab_07_08.sql .
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM employees
WHERE last_name like '%u%')
AND salary > (SELECT AVG(salary)
FROM employees);