Práctica DML sobre SCOTT Employees

Introdución

Partimos da base de datos scott de traballadores. Tamén disponible eiquí.

O modelo E/R:

E o relacional (para base de datos Oracle; para MySQL hai pequenos cambios nos tipos de datos):

Todos os campos excepto as claves primarias admiten valores nulos. Nos seguientes casos:

• COMM: Se ten un valor nulo, indica que o empregado non ten comisión
• MGR: Se ten un valor nulo, indica que o empregado non ten xefe

Este é o script de xeneración

 1 #Eliminado de táboas e database se xa existen
 2 #drop table emp;
 3 #drop table dept;
 4 #drop database company;
 5 
 6 #Crea base de datos
 7 create database company;
 8 
 9 use company;
10 
11 #Crea táboas con campos
12 create table dept(
13   deptno int(2),
14   dname  varchar(14),
15   loc    varchar(13),
16   primary key (deptno)
17 );
18  
19 create table emp(
20   empno    int(4),
21   ename    varchar(10),
22   job      varchar(9),
23   mgr      int(4),
24   hiredate date,
25   sal      decimal(7,2),
26   comm     decimal(7,2),
27   deptno   int(2),
28   primary key (empno),
29   foreign key (deptno) references dept (deptno)
30 );
31 
32 #Carga de datos con DML
33 insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
34 insert into dept values(20, 'RESEARCH', 'DALLAS');
35 insert into dept values(30, 'SALES', 'CHICAGO');
36 insert into dept values(40, 'OPERATIONS', 'BOSTON');
37  
38 insert into emp values( 7839, 'KING', 'PRESIDENT', null, str_to_date('17-11-1981', '%d-%m-%Y'), 5000, null, 10);
39 insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, str_to_date('1-5-1981','%d-%m-%Y'), 2850, null, 30);
40 insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, str_to_date('9-6-1981','%d-%m-%Y'), 2450, null, 10);
41 insert into emp values( 7566, 'JONES', 'MANAGER', 7839, str_to_date('2-4-1981','%d-%m-%Y'), 2975, null, 20);
42 insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, str_to_date('13-07-87','%d-%m-%Y'), 3000, null, 20);
43 insert into emp values( 7902, 'FORD', 'ANALYST', 7566, str_to_date('3-12-1981','%d-%m-%Y'), 3000, null, 20);
44 insert into emp values( 7369, 'SMITH', 'CLERK', 7902, str_to_date('17-12-1980','%d-%m-%Y'), 800, null, 20);
45 insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('20-2-1981','%d-%m-%Y'), 1600, 300, 30);
46 insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, str_to_date('22-2-1981','%d-%m-%Y'), 1250, 500, 30);
47 insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('28-9-1981','%d-%m-%Y'), 1250, 1400, 30);
48 insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, str_to_date('8-9-1981','%d-%m-%Y'), 1500, 0, 30);
49 insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, str_to_date('13-7-1987', '%d-%m-%Y'), 1100, null, 20);
50 insert into emp values( 7900, 'JAMES', 'CLERK', 7698, str_to_date('3-12-1981','%d-%m-%Y'), 950, null, 30);
51 insert into emp values( 7934, 'MILLER', 'CLERK', 7782, str_to_date('23-1-1982','%d-%m-%Y'), 1300, null, 10);

Consultas

Elabora as queries que se piden a continuación:

1. Obtén todos los datos de los empleados

2. Obtén todos los datos de todos los departamentos

3. Obtén todos los datos de los administrativos (su trabajo es, en ingles, CLERK)

4. Ídem, pero ordenado por el nombre

5. Obtén el mismo resultado de la pregunta anterior, pero modificando la sentencia

6. Obtén el numero, nombre y salario de los empleados

7. Lista los nombres de todos los departamentos

8. Ídem, pero ordenándolos por nombre

9. Ídem, pero ordenándolo por la ciudad (no se debe seleccionar la ciudad en el resultado)

10. Ídem, pero el resultado debe mostrarse ordenado por la ciudad en orden inverso

11. Obtén el nombre y empleo de todos los empleados, ordenado por salario

12. Obtén el nombre y empleo de todos los empleados, ordenado primero por su trabajo y luego por su salario

13. Ídem, pero ordenando inversamente por empleo y normalmente por salario

14. Obtén los salarios y las comisiones de los empleados del departamento 30

15. Ídem, pero ordenado por comisión

16. Obtén las comisiones de todos los empleados y las comisiones de los empleados de forma que no se repitan (dos consultas)

17. Obtén los distintos pares (empleado, comisión)

18. Obtén los nombres de los empleados y sus salarios, de forma que no se repitan filas

19. Obtén las comisiones de los empleados y sus números de departamento, de forma que no se repitan filas

20. Obtén los nuevos salarios de los empleados del departamento 30, que resultarían de sumar a su salario una gratificación de 1000. Muestra también los nombres de los empleados

21. Lo mismo que la anterior, pero mostrando también su salario original, y haz que la columna que almacena el nuevo salario se denomine NUEVO_SALARIO

22. Halla los empleados que tienen una comisión superior a la mitad de su salario

23. Halla los empleados cuya comisión sea desconocida o menor o igual que el 25% de su salario

24. Obtén una lista de nombres de empleados y sus salarios, de forma que en la salida aparezca en todas las filas "Nombre:" y "Salario:" antes del respectivo campo. Hazlo de forma que selecciones exactamente 2 expresiones

25. Hallar el código, salario y comisión de los empleados cuyo código sea mayor que 7500

26. Obtén todos los datos de los empleados que estén (considerando una ordenación ASCII por nombre) a partir de la J, inclusive

27. Obtén el salario, comisión y salario total (salario+comision) de los empleados con comisión, ordenando el resultado por numero de empleado

28. Lista la misma información, pero para los empleados que no tienen comisión

29. Muestra el nombre de los empleados que, teniendo un salario superior a 1000, tengan como jefe al empleado cuyo código es 7698

30. Halla el conjunto complementario del resultado del ejercicio anterior

31. Indica para cada empleado el porcentaje que supone su comisión sobre su salario, ordenando el resultado por el nombre del mismo

32. Hallar los empleados del departamento 10 cuyo nombre no contiene la cadena LA

33. Obtén los empleados que no son supervisados por ningún otro

34. Obtén los nombres de los departamentos que no sean Ventas (SALES) ni investigación (RESEARCH). Ordena el resultado por la localidad del departamento

35. Deseamos conocer el nombre de los empleados y el código del departamento de los administrativos (CLERK) que no trabajan en el departamento 10, y cuyo salario es superior a 800, ordenado por fecha de contratacion

36. Para los empleados que tengan comisión, obtén sus nombres y el cociente entre su salario y su comisión, ordenando el resultado por nombre

37. Lista toda la información sobre los empleados cuyo nombre completo tenga exactamente 5 caracteres

38. Lo mismo, pero para los empleados cuyo nombre tenga al menos 5 letras

39. Halla los datos de los empleados que, o bien su nombre empieza por A y su salario es superior a 1000, o bien reciben comisión y trabajan en el departamento 30

40. Halla el nombre, el salario y el sueldo total de todos los empleados, ordenando el resultado primero por salario y luego por el sueldo total. En el caso de que la comisión no se conozca, el sueldo total debe reflejar solo el salario

41. Obtén el nombre, salario y la comisión de los empleados que perciben un salario que esta entre la mitad de la comisión y la propia comisión

42. Obtén el complementario del anterior

43. Lista los nombres y empleos de aquellos empleados cuyo empleo acaba en MAN y cuyo nombre empieza por A

44. Intenta resolver la pregunta anterior con un predicado simple, es decir, de forma que en la clausula WHERE no haya conectores lógicos como AND, OR, etc. Si ayuda a resolver la pregunta, se puede suponer que el nombre del empleo tiene al menos 5 letras

45. Halla los nombres de los empleados cuyo nombre tiene como máximo 5 caracteres

46. Suponiendo que el ano próximo la subida del sueldo total de cada empleado sera del 6% y el siguiente del 7%, halla los nombres y el salario total actual, del ano próximo y del siguiente, de cada empleado. Indique además con SI, NO o NO SE SABE, si el empleado tiene comisión. Como en la pregunta 40, si no se conoce la comisión, el total se considera igual al salario. Se supone que no existen comisiones negativas

47. Lista los nombres y fecha de contratacion de aquellos empleados que no son vendedores (SALESMAN)

48. Obtén la información disponible de los empleados cuyo numero es uno de los siguientes: 7844, 7900, 7521, 7782, 7934, 7678, 7369, pero que no sea uno de los siguientes: 7902, 7839, 7499 ni 7878. La sentencia no debe complicarse innecesariamente y debe dar el resultado correcto independientemente de los empleados almacenados en la base de datos

49. Ordena los empleados por su código de departamento, y luego de manera descendente por su numero de empleado

50. Para los empleados que tengan como jefe a un empleado con código mayor que el suyo, obtén los que reciben de salario mas de 1000 y menos de 2000, o que estan en el departamento 30

51. Obtén el salario mas alto de la empresa, el total destinado a comisiones y el numero de empleados

52. Halla los datos de los empleados cuyo salario es mayor que el del empleado con código 7934, ordenando por el salario

53. Obtén información en la que se reflejen los nombres, empleos y salarios tanto de los empleados que superan el salario de Allen como del propio Allen

54. Halla el nombre del ultimo empleado por orden alfabético

55. Halla el salario mas alto, el mas bajo, y la diferencia entre ellos

56. Sin conocer los resultados del ejercicio anterior, quienes reciben el salario mas alto y el mas bajo y a cuanto ascienden estos salarios?

57. Considerando empleados con salario menor de 5000, halla la media de los salarios de los departamentos cuyo salario minimo supera a 900. Muestra tambien el codigo y el nombre de los departamentos

58. Que empleados trabajan en ciudades de mas de cinco letras? Ordena el resultado inversamente por ciudades y normalmente por los nombres de los empleados

59. Halla los empleados cuyo salario supera o coincide con la media del salario de la empresa

60. Obtén los empleados cuyo salario supera al de sus compañeros de departamento

61. Cuantos empleos diferentes, cuantos empleados y cuantos salarios diferentes encontramos en el departamento 30, y a cuanto asciende la suma de salarios de dicho departamento?

62. Cuantos empleados tienen comisión?

63. Cuantos empleados tiene el departamento 20?

64. Halla los departamentos que tienen mas de tres empleados, y el numero de empleados de los mismos.

65. Obtén los empleados del departamento 10 que tienen el mismo empleo que alguien del departamento de Ventas. Desconocemos el código de dicho departamento

66. Halla los empleados que tienen por lo menos un empleado a su mando, ordenados inversamente por nombre

67. Obtén informacion sobre los empleados que tienen el mismo trabajo que los empleados que trabajan en Chicago

68. Que empleos distintos encontramos en la empresa, y cuantos empleados desempeñan cada uno de ellos?

69. Halla la suma de salarios de cada departamento

70. Obtén todos los departamentos sin empleados

71. Halla los empleados que no tienen a otro empleado a sus ordenes

72. Cuantos empleados hay en cada departamento y cual es la media anual del salario de cada uno? Indique el nombre del departamento para clarificar el resultado

73. Halla los empleados del departamento 30, por orden descendiente de comisión

74. Obtén los empleados que trabajan en Dallas o New York

75. Obtén un listado en el que se reflejen los empleados y los nombres de sus jefes. En el listado deben aparecer todos los empleados, aunque no tengan jefe, poniendo un nulo el nombre de este

76. Lista los empleados que tengan el mayor salario de su departamento, mostrando el nombre del empleado, su salario y el nombre del departamento

77. Deseamos saber cuantos empleados supervisa cada jefe. Para ello, obtén un listado en el que se reflejen el código y el nombre de cada jefe, junto al numero de empleados que supervisa directamente. Como puede haber empleados sin jefe, para estos se indicara solo el numero de ellos, y los valores restantes (código y nombre del jefe) se dejaran como nulos

78. Hallar el departamento cuya suma de salarios sea la mas alta, mostrando esta suma de salarios y el nombre del departamento

79. Obtén los datos de los empleados que cobren los dos mayores salarios de la empresa. (Nota: Procura hacer la consulta de forma que sea fácil obtener los empleados de los N mayores salarios)

80. Obtén las localidades que no tienen departamentos sin empleados, en las que trabajen al menos cuatro empleados. Indica también el numero de empleados que trabajan en esas localidades. (Nota: una localidad que no tiene departamentos sin empleados no es lo mismo que una localidad que tiene departamentos con empleados.)

Pistas

• Consultas con group by: 64,68,69
• Subconsultas: 52,53,56,59,66,70,71

Solución

Podes consultar a solución á Práctica DML sobre SCOTT Employees