MANUAL 100 Consultas

UNIVERSIDAD NACIONAL DEL ALTIPLANO – PUNO FACULTAD DE INGENIERÍA MECÁNICA ELÉCTRICA, ELECTRÓNICA Y SISTEMAS

ESCUELA PROFESIONAL DE INGENIERÍA DE SISTEMAS

100 CONSULTAS BÁSICAS DE SQL

SENTENCIAS SELECT

Ing. ELMER COYLA IDME

PUNO – PERÚ

2011

Consultas SQL

DEDICATORIA

A

todos los grandes maestros de la Escuela de Post Grado de la Universidad Nacional de Educación “Enrique Guzman y Valle” La Cantuta de la Mención de Docencia Universitaria, a todos los estudiantes de la Escuela Profesional de Ingeniería de Sistemas de la Universidad Nacional del Altiplano de Puno de la Asignatura de Tecnologías de Desarrollo de Aplicaciones Semestre 2007 – I, a los estudiantes el Instituto Superior Pedagógico Público “Alianza Ichuña - Bélgica” de Moquegua Moquegua de la Carrera Profesional de Computación e Informática Promoción 2007 y a los estudiantes del Instituto Superior Tecnológico Público “Alianza Ichuña - Bélgica” de la Carrera Profesional de Computación e Informática, Promoción 2002 – 2003 y 2004, por sus valiosas sugerencias, comprensión y ánimo; sin ustedes no hubiera sido posible la culminación de este Solucionario. El Autor.

-2-

Elmer Coyla Idme

PRESENTACION

E

l objetivo de este documento es guiar su aprendizaje del comando SELECT del SLQ Server, la idea es que, estando conectado al analizador de consultas, lea este material mientras ejercita simultáneamente cada una de las distintas modalidades del comando SELECT. A lo largo de este solucionario se hace referencia a las tablas departamento (DEPT) y empleado (EMP) de una empresa, usadas en los tutoriales de los productos ORACLE. Agradezco muy profundamente la comprensión a todos los futuros profesionales, sobre las deficiencias que pudieran existir en la codificación o razones de error en la digitación, en las diversas consultas, pero al menos hago un alcance a aquellos que verdaderamente lo necesitan y estoy seguro que a de servir de mucho en su formación profesional. Le rogamos que cualquier crítica, sugerencia, o inquietud, la dirija a la siguiente dirección [email protected] [email protected],, que gustosos lo responderé con el ánimo de mejorar en futuras ediciones. Elmer Coyla Idme

-3-

Consultas SQL

ESQUEMA SCOTT

CONTENIDO DE LAS TABLAS

-4-

Elmer Coyla Idme

CONSULTAS 1. Obtener los datos completos de los empleados. Consulta: select * from emp Respuesta: Respuesta:

2. Obtener los datos de los departamentos. Consulta: select * from dept Respuesta: Respuesta:

3. Hallar los datos de los administrativos. Consulta: select empno, ename, mgr, sal from emp where job='administrativo' Respuesta: Respuesta:

4. Hallar los datos de los administrativos. Pero ordenado por nombre. Consulta: select empno, ename, job, mgr, sal from emp where job='administrativo' order by ename asc

-5-

Consultas SQL

Respuesta: Respuesta:

5. El mismo pero modificado de alguna forma la sentencia anterior. Consulta: select empno, ename, job, mgr, sal from emp where job='administrativo' and sal > 1000 order by ename Respuesta: Respuesta:

6. Obtener el nombre y el salario de los empleados. Consulta: select ename,sal from emp Respuesta: Respuesta:

7. Hallar el nombre de los departamentos. Consulta: select dname from dept

-6-

Elmer Coyla Idme


8. Hallar el nombre de los departamentos. Pero ordenado por nombre. Consulta: select dname from dept order by dname asc Respuesta: Respuesta:

9. Lo mismo pero ordenado por la ciudad. Consulta: select dname, loc from dept order by loc asc Respuesta: Respuesta:

10. Hallar los nombres de los departamentos ordenados por su ciudad, pero en orden inverso. Consulta: select dname, loc from dept order by loc desc Respuesta: Respuesta:

11. Obtener el nombre y el empleo de los empleados, ordenados por salario. Consulta: select ename, job, sal from emp order by sal

-7-

Consultas SQL


12. Idéntico Pero ordenados ahora por empleo y salario. Consulta: select ename, job, sal from emp order by job, sal Respuesta: Respuesta:

13. Idéntico. Pero ordenado inversamente por empleo y normalmente por salario. Consulta: select ename, job, sal from emp order by job desc, sal asc

-8-

Elmer Coyla Idme


14. Obtener los salarios y las comisiones de los empleados empleados del departamento 30. Consulta: select deptno,ename, sal, comm from emp where deptno =30 Respuesta: Respuesta:

15. Idéntico, pero ordenado por comisión. Consulta: select deptno,ename, sal, comm from emp where deptno =30 order by comm asc Respuesta: Respuesta:

16. Obtener las distintas comisiones. Consulta: select distinct comm from emp

-9-

Consultas SQL


17. Hallar las distintas comisiones y los nombres de los empleados. Consulta: select distinct comm, ename from emp where comm is not null Respuesta: Respuesta:

18. Hallar los distintos salarios. Consulta: select distinct sal from emp Respuesta: Respuesta:

19. Obtener comisiones y los números de los departamentos posibles de la empresa, de manera que no se repita. Consulta: select distinct comm, deptno from emp where comm is not null

- 10 -

Elmer Coyla Idme


20. Obtenga los nuevos salarios que resultarían de sumar a los empleados 30 una nueva gratificación de 1000 unidades monetarias. Consulta: select deptno, sal +1000 AS 'NUEVO SALARIO' from emp where deptno=30 Respuesta: Respuesta:

21. Idéntico, pero obteniendo también el salario anterior. Consulta: select deptno, sal, sal +1000 AS 'NUEVO SALARIO' from emp where deptno=30 Respuesta: Respuesta:

22. Hallar los empleados que tienen una comisión superior a la mitad de su salario. Consulta: select ename from emp where comm >(sal/2) Respuesta: Respuesta:

- 11 -

Consultas SQL

23. Hallar los empleados cuya comisión es menor o igual que el 25% del sueldo. Consulta: select ename from emp where comm <=(sal*0.25) Respuesta: Respuesta:

24. Haga que en cada fila figura ‘nombre’ y ‘puesto’ anteponiéndose a su respectivo valor. Consulta: select 'Nombre:', ename, 'Puesto:', job from emp Respuesta: Respuesta:

25. Hallar el salario y la comisión de los empleados cuyo número de empleado supera a 7500. Consulta: select empno,sal, comm from emp where empno>7500

- 12 -

Elmer Coyla Idme


26. Si dividimos a los empleados en dos grupos A y B, empezando los del grupo B en la letra J, J, Obtén los nombres y empleos empleos de los del grupo B, por orden alfabético. Consulta: select ename, job from emp where substring(ename,1,1)>'j' order by ename asc Respuesta: Respuesta:

27. Obtener el salario, la comisión y el salario total (salario + comisión) de los empleados con comisión, ordenado por numero de empleado. Consulta: select sal, comm, (sal+comm) as 'salario total' from emp where comm is not null order by empno asc Respuesta: Respuesta:

- 13 -

Consultas SQL

28. Idéntico, pero para los que no tienen comisión. Consulta: select sal, comm, (sal) as 'salario total' from emp where comm is null order by empno asc Respuesta: Respuesta:

29. Hallar el nombre de los empleados que teniendo un salario superior a 1000, tienen como director al empleado de código 7698. Consulta: select ename, mgr from emp where sal > 1000 and mgr =7698 Respuesta: Respuesta:

30. Hallar el conjunto complementario del resultado del ejercicio anterior. Consulta: select ename, mgr from emp where sal < 1000 and mgr <>7698 Respuesta: Respuesta:

31. Hallar el porcentaje que supone la comisión sobre el salario total ordenado por nombre. Consulta: select ename , sal+comm as 'salario total', comm, round((100*comm/(sal+comm)),0) as 'porcentaje' from emp where comm is not null order by ename

- 14 -

Elmer Coyla Idme


32. Hallar los empleados del departamento 10 cuyo nombre contenga la cadena “LA” Consulta: select ename from emp where deptno = 10 and ename not like '%la%' Respuesta: Respuesta:

33. Obtener los empleados que no son supervisados supervisados por ningún otro. otro. Consulta: select ename, mgr from emp where mgr is null Respuesta: Respuesta:

34. Obtener los nombres de los departamentos que no sean “SALES” ni “RESEARCH”, “RESEARCH”, ordenados por localidad. Consulta: select dname from dept where not dname='ventas' and not dname ='investigacion' order by loc Respuesta: Respuesta:

35. Deseamos conocer el nombre y departamento de los administrativos que no trabajan en el departamento 10 y cuyo salario es superior a 800, ordenados por la fecha de incorporación. Consulta: select ename, deptno, sal , hiredate from emp where job='administrativo' and deptno<>10 and sal > 800 order by hiredate asc

- 15 -

Consultas SQL


36. Para los empleados que tienen comisión obtener obtener sus nombres en orden alfabético y el cociente entre salarios y comisión. Consulta: select ename, comm , sal/comm from emp where comm is not null order by ename Respuesta: Respuesta:

37. Obtener información información de empleados cuyos cuyos nombre contiene exactamente exactamente 5 caracteres. Consulta: select ename from emp where len(ename)=5 Respuesta: Respuesta:

38. Idéntico, pero cuyo nombre contengan al menos cinco caracteres. Consulta: select ename from emp where len(ename)>=5

- 16 -

Elmer Coyla Idme


39. Hallar los datos de los empleados para los que, su nombre comiencen con A y su salario es mayor que 1000 Consulta: select ename, job, mgr, sal, deptno from emp where (ename like '[a]%' and sal>1000) Respuesta: Respuesta:

40. Hallar el nombre y el salario total de todos los empleados y ordenados por este ultimo y por su propio salario. Consulta: select ename, sal+comm as 'salario total' , sal from emp order by sal+comm, sal Respuesta: Respuesta:

- 17 -

Consultas SQL

41. Obtener los nombres, salarios y comisión de los empleados que perciben un salario situado entre la mitad de la comisión y la propia comisión. Consulta: select ename, sal, comm from emp where sal between comm/2 and comm order by comm Respuesta: Respuesta:

42. El complementario del anterior. Consulta: select ename, sal, comm from emp where sal not between comm/2 and comm order by comm Respuesta: Respuesta:

43. Intentar lograr los nombres y empleos de los empleados tales que su empleo acaba en “IVO” y su nombre empieza con A. Consulta: select ename, job from emp where ename like '[a]%'and job like '%ivo' Respuesta: Respuesta:

44. Si te ha resultado muy fácil el anterior prueba a intentar con un predicado simple. Supón que el nombre de un empleo tiene al menos cinco caracteres. Consulta: select ename, job from emp where len(job)> 5 and ename like '[a]%'and job like '%ivo' Respuesta: Respuesta:

- 18 -

Elmer Coyla Idme

45. Hallar los nombres de los empleados que tienen como máximo cinco caracteres en su nombre. Consulta: select ename from emp where len(ename)<=5 Respuesta: Respuesta:

46. Suponiendo que el año próximo la subida del total percibido por ejemplo es del 6% y el siguiente de 7% hallar los nombres y salarios totales actuales y futuros de todos los empleados, indicando para cada uno si tienen o no comisión. Consulta: select ename, comm, sal+comm as 'Salario total', (sal+comm)+(0.06*(sal+comm)) as '6%', (sal+comm)+(0.07*(sal+comm)) as '7%' from emp where comm is not null union select ename, comm,sal, sal+(0.06*sal),sal+(0.07*sal) from emp where comm is null Respuesta: Respuesta:

- 19 -

Consultas SQL

47. Hallar el nombre y la fecha de ingreso de los empleados que no son vendedores. Consulta: select ename, hiredate from emp where job <> 'vendedor' Respuesta: Respuesta:

48. Obtener la información disponible de los empleados de numero uno de los siguientes; 7844, 7900, 7521, 7782 y 7369, pero no uno de entre: 7902, 74 99 ni 7878. el interés del ejercicio radico en que el predicado no debe ser muy extenso. Consulta: select ename from emp where empno in (7844,7900,7521,7782,7934,7678,7369) and empno not in (7902,7839,7499,7878) Respuesta: Respuesta:

49. Hallar los salarios totales de todos los empleados. Consulta: select ename, comm+sal as 'Salario Final' from emp where comm is not null union select ename, sal from emp where comm is null

- 20 -

Elmer Coyla Idme


50. Ordena los empleados por su departamento y luego de manera descendente por su número. Consulta: select ename, deptno, empno from emp order by deptno, empno desc Respuesta: Respuesta:

51. Para los empleados que tienen como director a algún otro con número mayor que el suyo, obtener los que reciben el salario mas de 1000 y menos de 2000. Consulta: select ename, mgr,empno, sal, deptno from emp where mgr>empno and sal between 1000 and 2000

- 21 -

Consultas SQL


52. Obtén el salario más alto de la empresa, empresa, el total destinado destinado a comisiones y el número de empleados. Consulta: select max(sal) as 'Maximo Salario', sum(comm) as 'Total de comisiones', count(empno) as 'Total de Empleados' from emp Respuesta: Respuesta:

53. Hallar los datos de los empleados cuyo salario es mayor que el del empleado de código 7934 ordenando por su propio salario Consulta: select ename, sal from emp where sal >(select sal from emp where empno=7934) order by sal asc Respuesta: Respuesta:

54. Obtén información en la que se refleja en nombre, empleo y salarios salarios tanto de los que superan el salario de Allen como del propio Allen. Consulta: select ename, job,sal from emp where sal >(select sal from emp where ename='allen')

- 22 -

Elmer Coyla Idme


55. Hallar el nombre del último empleado por orden alfabético. Consulta: select ename from emp where ename>= all(select ename from emp) Respuesta: Respuesta:

56. Halla el salario más alto, el más bajo y la diferencia entre ellos. Consulta: select max(sal)as 'salario maximo', min(sal) as 'salario minimo', (max(sal)min(sal)) as 'diferencia' from emp Respuesta: Respuesta:

57. Sin conocer los resultado del ejercicio anterior, ¿Quiénes reciben el salario mas alto y mas bajo y a cuanto ascienden?. Consulta: select ename, sal from emp where sal >= all(select sal from emp) union select ename, sal from emp where sal <= all(select sal from emp) Respuesta: Respuesta:

58. Hallar la media de los salarios de los departamentos cuyo salario mínimo supera a 900, considerando los salarios inferiores a 5000. además se nenecita el código y el nombre de los departamentos. Consulta: select e.deptno, d.dname, avg(sal) as 'Salario promedio' from emp e, dept d where e.deptno=d.deptno and e.deptno not in(select deptno from emp where sal <=900) group by d.dname, e.deptno

- 23 -

Consultas SQL


59. ¿Qué empleados trabajan en ciudades que tienen mas de cinco letras?. Ordena el resultado inversamente por ciudades y normalmente por nombres de empleados. Consulta: select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno and len(d.loc)>5 order by d.loc desc , e.ename Respuesta: Respuesta:

60. Hallar los empleados cuyo salario supera o coincide con la media del salario de la empresa. Consulta: select ename, sal from emp where sal >= all (select avg(sal) from emp) Respuesta: Respuesta:

- 24 -

Elmer Coyla Idme

61. Obtén los empleados cuyos salarios supera al de sus compañeros de departamento. Consulta: select e.ename, e.sal, e.deptno from emp e where e.sal >= all(select sal from emp where deptno=e.deptno) order by sal Respuesta: Respuesta:

62. ¿Cuántos empleados diferentes, empleados y diferentes salarios encontramos en el departamento 30, y a que cantidad asciende la suma de los salarios de dicho departamento? Consulta: select count(distinct job) as 'Empleos', count(ename) as 'Empleados', count(distinct sal) as 'Salarios', sum(sal) as 'Total de salarios' from emp where deptno=30 Respuesta: Respuesta:

63. ¿Cuántos empleados tienen comisión? Consulta: select count(comm) as 'Cantidad de empleados con comision' from emp Respuesta: Respuesta:

64. ¿Cuántos empleados tienen el departamento 20? Consulta: select count(e.empno) as 'Cant. de Emp del Dept 10' from emp e, dept d where e.deptno=d.deptno and d.deptno=10 Respuesta: Respuesta:

- 25 -

Consultas SQL

65. Halla los departamentos que tienen más de tres empleados y el número de empleados de los mismos. Consulta: select e.deptno, d.dname, count(e.ename)as 'Cant de Empleados' from emp e, dept d where d.deptno=e.deptno and 3<(select count(ename) from emp where deptno=d.deptno) group by dname, e.deptno Respuesta: Respuesta:

66. Halla los empleados que tienen por lo menos un empleado a su mando, ordenando inversamente por nombre. Consulta: select ename from emp where empno in(select mgr from emp) order by ename desc Respuesta: Respuesta:

67. Halla la suma de los salarios de cada departamento. Consulta: select d.dname, sum(e.sal) as 'suma de salarios' from emp e, dept d where e.deptno=d.deptno group by d.dname Respuesta: Respuesta:

- 26 -

Elmer Coyla Idme

68. Obtén todos los departamentos departamentos sin sin empleados. Consulta: select distinct d.deptno,d.dname from emp e, dept d where d.deptno not in(select e.deptno from emp e, dept d where e.deptno=d.deptno) Respuesta: Respuesta:

69. ¿Cuántos empleos hay en cada departamento y cual es la media del salario de cada uno?. Consulta: select count(e.ename), avg(e.sal) from emp e, dept d where e.deptno=d.deptno group by d.deptno Respuesta: Respuesta:

70. Halla los empleados del departamento 30 por orden descendente de comisión. Consulta: select ename, deptno, comm from emp where deptno=30 order by comm desc Respuesta: Respuesta:

71. Obtener los empleados que trabajan trabajan en Dallas o en New New York. Consulta: select ename from emp where deptno in(select deptno from dept where loc='dallas' or loc='new york')

- 27 -

Consultas SQL


72. Hállense los empleados con salario mayor de cada departamento. Se debe incluir el salario y el nombre del departamento. Consulta: select e.ename, e.sal, d.dname from emp e, dept d where d.deptno=e.deptno and e.sal>= all(select sal from emp where deptno=e.deptno) Respuesta: Respuesta:

73. Seleccionar los empleos que hay en cada departamento, ordenados por departamento. Consulta: select deptno, job from emp order by 1 Respuesta: Respuesta:

- 28 -

Elmer Coyla Idme

74. Calcular el salario anual a percibir por cada empleado. Consulta: select ename, sal *4*12 as 'Salario Anual' from emp Respuesta: Respuesta:

75. Seleccionar aquellos empleados que hayan entrado antes del 1/1/82. Consulta: select ename, hiredate from emp where hiredate < '1/1/82' Respuesta: Respuesta:

76. Seleccionar los empleados cuyo salario sea superior al de ADAMS. ADAMS. Consulta: select ename from emp where sal >(select sal from emp where ename = 'adams')

- 29 -

Consultas SQL


77. Seleccionar los empleados que trabajan en el mismo departamento que CLARK. Consulta: select ename from emp where deptno=(select deptno from emp where ename = 'clark') Respuesta: Respuesta:

78. Encontrar a los empleados cuyo jefe es BLAKE. Consulta: select ename from emp where mgr=(select empno from emp where ename='blake') Respuesta: Respuesta:

79. Seleccionar aquellos empleados que tienen comisión. Consulta: select ename from emp where comm is not null

- 30 -

Elmer Coyla Idme


80. Seleccionar los empleados que trabajan en Chicago. Consulta: select ename from emp where deptno in(select deptno from dept where loc='chicago') Respuesta: Respuesta:

81. Seleccionar los empleados que ganen más que alguno de los Vendedores. Consulta: select ename from emp where sal > some(select sal from emp where job='vendedor') Respuesta: Respuesta:

82. Seleccionar aquellos empleados que hayan entrado en 1981. Consulta: select ename, hiredate from emp where hiredate between '1/1/1981'and'31/12/1981'

- 31 -

Consultas SQL


83. Seleccionar aquellos empleados cuyo nombre tenga tenga como como segunda segunda letra una ‘D’. Consulta: select ename from emp where ename like '_d%' Respuesta: Respuesta:

84. Seleccionar el salario mínimo, máximo y medio de los empleados agrupados por empleo, pero sólo aquellos cuya media sea superior a 4000. Consulta: select job, min(sal), max(sal), avg(sal) from emp group by job having avg(sal)> 4000 Respuesta: Respuesta:

85. El salario medio y mínimo de cada puesto, mostrando en el resultado aquellos cuyo salario medio esté por encima de 1500. Consulta: select job, avg(sal), min(sal) from emp group by job having avg(sal)>1500 Respuesta: Respuesta:

- 32 -

Elmer Coyla Idme

86. ¿Cuántos empleados trabajan en Chicago?. Consulta: select count(*) from emp, dept where emp.deptno=dept.deptno and loc='chicago' Respuesta: Respuesta:

87. ¿Qué empleados trabajan en Dallas?. Consulta: select ename from emp, dept where emp.deptno=dept.deptno and loc='dallas' Respuesta: Respuesta:

88. Nombre de todos los empleados, empleo, departamento y localidad. Consulta: select ename, job, emp.deptno, loc from emp, dept where emp.deptno=dept.deptno Respuesta: Respuesta:

89. Seleccionar el nombre de cada empleado, y el número y el nombre de su jefe. Consulta: select e1.ename, e1.mgr, e2.ename from emp e1, emp e2 where e2.empno=e1.mgr order by e1.ename

- 33 -

Consultas SQL


90. Mostrar el nombre del empleado, su trabajo, el nombre y el código del departamento en el que trabaja. Consulta: select ename,job, dname, dept.deptno from emp, dept where emp.deptno=dept.deptno Respuesta: Respuesta:

91. Nombres de los empleados que trabajan en Chicago. Consulta: select ename from emp e where exists(select * from dept d where e.deptno=d.deptno and loc='chicago')

- 34 -

Elmer Coyla Idme


92. Visualice el número de los departamentos que tengan más de tres empleados asignados. Consulta: select distinct deptno from emp e1 where 3<(select count(*) from emp e2 where e1.deptno=e2.deptno) Respuesta: Respuesta:

93. Listar el nombre de los empleados que ganan menos que sus supervisores. Consulta: select ename from emp e1 where sal<(select sal from emp e2 where e2.empno=e1.mgr) Respuesta: Respuesta:

- 35 -

Consultas SQL

94. Encontrar el ename, dname, job y sal de los empleados que trabajen en el mismo departamento que ‘turner’ y su salario sea mayor que la media del salario del departamento 10. Consulta: select ename, dname, job, sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=(select deptno from emp where ename='turner') and sal > (select avg(sal) from emp where deptno =30) Respuesta: Respuesta:

95. Listar el nombre, trabajo, departamento, localidad y salario de aquellos empleados que tengan un salario mayor de 2000 y trabajen en ‘Dallas’ o ’New York’. Consulta: select ename,job,emp.deptno, loc, sal from emp,dept where emp.deptno=dept.deptno and sal>2000 and loc in ('dallas','new york') Respuesta: Respuesta:

96. Mostrar el nombre, cargo y sueldo para todos los empleados excepto los gerentes. Consulta: select ename,job, sal from emp where job!='gerente' Respuesta: Respuesta:

- 36 -

Elmer Coyla Idme

97. Listar el nombre, sueldo mensual, sueldo diario (basado en un mes de 22 días hábiles), y sueldo diario redondeado al dólar entero más cercano, para Allen y Jones. Consulta: select ename,sal,sal/22, round(sal/22,0) from emp where ename in('allen','jones') Respuesta: Respuesta:

98. Mostrar el salario promedio anual para todos los cargos que tengan más de dos empleados. Consulta: select job, 12*avg(sal) from emp group by job having count(*) > 2 Respuesta: Respuesta:

99. Mostrar cada departamento, seguido de su número de empleados. Consulta: select deptno, count(*) from emp group by deptno Respuesta: Respuesta:

- 37 -

Consultas SQL

100. Mostrar el número de empleados para cada cargo en cada departamento. Consulta: select deptno, job, count(*) from emp group by deptno, job Respuesta: Respuesta:

- 38 -

Elmer Coyla Idme

“El que no sabe es un imbécil. El que sabe y calla es un criminal…”. Bertolt Brecht

- 39 -

MANUAL 100 Consultas

Recommend Documents