Solución á Práctica DML sobre SCOTT Employees
Revisión del 19:50 15 nov 2021 de Joselv (discusión | contribuciones)
Solución ás consultas
1 -- Consulta 1: Obtener todos los datos de los empleados
2 SELECT * FROM emp;
3
4 -- Conculta 2: Obtener todos los datos de todos los departamentos
5 SELECT * FROM dept;
6
7 -- Consulta 3: Obtener todos los datos de los administrativos (su trabajo es, en ingles, 'CLERK')
8 SELECT * FROM emp
9 WHERE job = 'CLERK';
10
11 -- Consulta 4: Idem, pero ordenado por el nombre
12 SELECT * FROM emp
13 WHERE job = 'CLERK' ORDER BY ename;
14
15 -- Consulta 5: Obten el mismo resultado de la pregunta anterior, preo modificando la sentencia SQL
16 SELECT * FROM emp
17 WHERE job IN ('CLERK') ORDER BY ename;
18
19 -- Consulta 6: Obten el numero, nombre y salario de los empleados
20 SELECT empno, ename, sal
21 FROM emp;
22
23 -- Consulta 7: Lista los nombres de todos los departamentos
24 SELECT dname FROM dept;
25
26 -- Consulta 8: Idem, pero ordenandolos por nombre
27 SELECT dname FROM dept
28 ORDER BY dname;
29
30 -- Consulta 9: Idem, pero ordenandolo por la ciudad (no se debe seleccionar la ciudad en el resultado)
31 SELECT dname FROM dept
32 ORDER BY loc;
33
34 -- Consulta 10: Idem, pero el resultado debe mostrarse ordenado por la ciudad en orden inverso
35 SELECT dname FROM dept
36 ORDER BY loc DESC;
37
38 -- Consulta 11: Obtener el nombre y empleo de todos los empleados, ordenado por salario
39 SELECT ename, job FROM emp
40 ORDER BY sal;
41
42 -- Consulta 12: Obtener el nombre y empleo de todos los empleados, ordenado primero por su trabajo y luego por su salario
43 SELECT ename, job FROM emp
44 ORDER BY job, sal;
45
46 -- Consulta 13: Idem, pero ordenando inversamente por empleo y normalmente por salario
47 SELECT ename, job FROM emp
48 ORDER BY job DESC, sal;
49
50 -- Consulta 14: Obten los salarios y las comisiones de los empleados del departamento 30
51 SELECT sal, comm FROM emp WHERE deptno = 30;
52
53 -- Consulta 15: Idem, pero ordenado por comision
54 SELECT sal, comm FROM emp WHERE deptno = 30 ORDER BY comm;
55
56 -- Consulta 16: (a) Obten las comisiones de todos los empleados
57 SELECT comm FROM emp;
58
59 -- Consulta 16: (b) Obten las comisiones de los empleados de forma que no se repitan
60 SELECT DISTINCT comm FROM emp;
61
62 -- Consulta 17: Obten los distintos pares (empleado, comision)
63 SELECT DISTINCT ename, comm FROM emp;
64
65 -- Consulta 18: Obten los nombres de los empleados y sus salarios, de forma que no se repitan filas
66 SELECT DISTINCT ename, sal FROM emp;
67
68 -- Consulta 19: Obten las comisiones de los empleados y sus numeros de departamento, de forma que no se repitan filas
69 SELECT DISTINCT comm, deptno FROM emp
70 WHERE comm IS NOT NULL;
71
72 -- Consulta 20: Obten los nuevos salarios de los empleados del departamento 30, que resultarian de sumar a su salario una gratifiacion de 1000. Muestra tambien los nombres de los empleados
73 SELECT ename, sal+1000 FROM emp
74 WHERE deptno = 30;
75
76 -- Consulta 21: Lo mismo que la anterior, pero mostrando tambien su salario original, y haz que la columna que almacena el nuevo salario se denomine NUEVO_SALARIO
77 SELECT ename, sal, sal+1000 AS NUEVO_SALARIO
78 FROM emp WHERE deptno = 30;
79
80 -- Consulta 22: Halla los empleados que tienen una comision superior a la mitad de su salario
81 SELECT ename FROM emp
82 WHERE comm > sal/2;
83
84 -- Consulta 23: Halla los empleados cuya comision sea desconocida o menor o igual que el 25% de su salario
85 SELECT ename FROM emp
86 WHERE comm IS NULL OR comm <= sal * 0.25;
87
88 -- Consulta 24: Obten 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
89 SELECT CONCAT("Nombre:",ename) AS Nome, CONCAT("Salario:",sal) AS Salario from emp;
90
91 -- Consulta 25: Hallar el codigo, salario y comision de los empleados cuyo codigo sea mayor que 7500
92 SELECT empno, sal, comm FROM emp
93 WHERE empno > 7500;
94
95 -- Consulta 26: Obten todos los datos de los empleados que esten (considerando una ordenacion ASCII por nombre) a partir de la J, inclusive
96 SELECT * FROM emp
97 WHERE ename >= 'J';
98
99 -- Consulta 27: Obten el salario, comision y salario total (salario+comision) de los empleados con comision, ordenando el resultado por numero de empleado
100 SELECT sal, comm, sal+comm FROM emp
101 WHERE comm > 0
102 ORDER BY empno;
103
104 -- Consulta 28: Lista la misma informacion, pero para los empleados que no tienen comision
105 SELECT sal, comm, sal+comm FROM emp
106 WHERE comm = 0
107 ORDER BY empno;
108
109 -- Consulta 29: Muestra el nombre de los empleados que, teniendo un salario superior a 1000, tengan como jefe al empleado cuyo codigo es 7698
110 SELECT ename FROM emp
111 WHERE sal > 1000 AND mgr = 7698;
112
113 -- Consulta 30: Halla el conjunto complementario del resultado del ejercicio anterior
114 SELECT ename FROM emp
115 WHERE sal <= 1000 OR mgr <> 7698;
116
117 -- Consulta 31: Indica para cada empleado el porcentaje que supone su comision sobre su salario, ordenando el resultado por el nombre del mismo
118 SELECT ename,comm/(sal+comm)*100
119 FROM emp
120 ORDER BY ename;
121
122 -- Consulta 32: Hallar los empleados del departamento 10 cuyo nombre no contiene la cadena LA
123 SELECT ename
124 FROM emp
125 WHERE deptno = 10 AND ename NOT LIKE '%LA%';
126
127 -- Consulta 33: Obten los empleados que no son supervisados por ningun otro
128 SELECT ename FROM emp
129 WHERE mgr IS NULL;
130
131 -- Consulta 34: Obten los nombres de los departamentos que no sean Ventas (SALES) ni investigacion (RESEARCH). Ordena el resultado por la localidad del departamento
132 SELECT dname FROM dept
133 WHERE dname NOT IN ('SALES', 'RESEARCH') ORDER BY loc;
134
135 -- Consulta 35: Deseamos conocer el nombre de los empleados y el codigo 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
136 SELECT ename, deptno FROM emp
137 WHERE job = 'CLERK' AND deptno <> 10 AND sal > 800
138 ORDER BY hiredate;
139
140 -- Consulta 36: Para los empleados que tengan comision, obten sus nombres y el cociente entre su salario y su comision, ordenando el resultado por nombre
141 SELECT ename, sal/comm FROM emp
142 WHERE comm <> 0
143 ORDER BY ename;
144
145 -- Consulta 37: Lista toda la informacion sobre los empleados cuyo nombre completo tenga exactamente 5 caracteres
146 SELECT * FROM emp
147 WHERE LENGTH(ename) = 5;
148
149 -- Consulta 38: Lo mismo, pero para los empleados cuyo nombre tenga al menos 5 letras
150 SELECT * FROM emp
151 WHERE LENGTH(ename) >= 5;
152
153 -- Consulta 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 comision y trabajan en el departamento 30
154 SELECT * FROM emp
155 WHERE (ename LIKE 'A%' AND sal > 1000 ) OR ( comm <> 0 AND deptno = 30 );
156
157 -- Consulta 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 comision no se conozca, el sueldo total debe reflejar solo el salario
158 SELECT ename, sal, NVL(sal+comm,sal) FROM emp
159 ORDER BY sal, sal+comm;
160
161 -- Consulta 41: Obten el nombre, salario y la comision de los empleados que perciben un salario que esta entre la mitad de la comision y la propia comision
162 SELECT ename, sal, comm FROM emp
163 WHERE sal BETWEEN comm/2 AND comm;
164
165 -- Consulta 42: Obten el complementario del anterior
166 SELECT ename, sal, comm FROM emp
167 WHERE sal NOT BETWEEN comm/2 AND comm;
168
169 -- Consulta 43: Lista los nombres y empleos de aquellos empleados cuyo empleo acaba en MAN y cuyo nombre empieza por A
170 SELECT ename, job FROM emp
171 WHERE job LIKE '%MAN' AND ename LIKE 'A%';
172
173 -- Consulta 44: Intenta resolver la pregunta anterior con un predicado simple, es decir, de forma que en la clausula WHERE no haya conectores logicos como AND, OR, etc. Si ayuda a resolver la pregunta, se puede suponer que el nombre del empleo tiene al menos 5 letras
174 SELECT ename, job FROM emp
175 WHERE CONCAT(ename, job) LIKE 'A____%MAN';
176
177 -- Consulta 45: Halla los nombres de los empleados cuyo nombre tiene como maximo 5 caracteres
178 SELECT ename FROM emp
179 WHERE LENGTH(ename) <= 5;
180
181 -- Consulta 46: Suponiendo que el ano proximo 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 proximo y del siguiente, de cada empleado. Indique ademas con SI, NO o NO SE SABE, si el empleado tiene comision. Como en la pregunta 40, si no se conoce la comision, el total se cnsidera igual al salario. Se supone qu no existen comisiones negativas
182 #En Oracle
183 #SELECT ename, NVL(sal+comm,sal) AS SUELDO_ACTUAL, NVL(sal+comm,sal)*0.06+NVL(sal+comm,sal) AS SUELDO_SIGUIENTE, (NVL(sal+comm,sal)*0.06+NVL(sal+comm,sal))*0.07+(NVL(sal+comm,sal)*0.06+NVL(sal+comm,sal)) AS SUELDO_EN_DOS, DECODE(comm,NULL,'NO SE SABE',0,'NO','SI') AS COMISION FROM emp;
184
185 # En MySQL
186 SELECT ename, sal as "Salario", sal + coalesce(comm,0) as "Salario total", sal*1.06 as "Salario 2022", sal*1.07 as "Salario 2023" FROM emp;
187
188 -- Consulta 47: Lista los nombres y fecha de contratacion de aquellos empleados que no son vendedores (SALESMAN)
189 SELECT ename, hiredate FROM emp
190 WHERE job <> 'SALESMAN';
191
192 -- Consulta 48: Obten la informacion 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
193 SELECT * FROM emp
194 WHERE empno IN (7844,7900,7521,7782,7934,7678,7369);
195
196 -- Consulta 49: Ordena los empleados por su codigo de departamento, y luego de manera descendente por su numero de empleado
197 SELECT ename FROM emp
198 ORDER BY deptno, empno DESC;
199
200 -- Consulta 50: Para los empleados que tengan como jefe a un empleado con codigo mayor que el suyo, obten los que reciben de salario mas de 1000 y menos de 2000, o que estan en el departamento 30
201 SELECT ename FROM emp
202 WHERE mgr > empno AND ( (sal BETWEEN 1000 AND 2000) OR deptno = 30 );
203
204 -- Consulta 51: Obten el salario mas alto de la empresa, el total destinado a comisiones y el numero de empleados
205 SELECT MAX(sal), SUM(comm), COUNT(*)
206 FROM emp;
207
208 -- Consulta 52: Halla los datos de los empleados cuyo salario es mayor que el del empleado con codigo 7934, ordenando por el salario
209 SELECT * FROM emp
210 WHERE sal > (SELECT sal FROM emp
211 WHERE empno = 7934)
212 ORDER BY sal;
213
214 -- Consulta 53: Obten informacion en la que se reflejen los nombres, empleos y salarios tanto de los empleados que superan el salario de Allen como del propio Allen
215 SELECT ename, job, sal FROM emp
216 WHERE sal > (SELECT sal FROM emp
217 WHERE ename = 'ALLEN') OR ename = 'ALLEN';
218
219 -- Consulta 54: Halla el nombre del ultimo empleado por orden alfabetico
220 SELECT MAX(ename) FROM emp;
221
222 -- Consulta 55: Halla el salario mas alto, el mas bajo, y la diferencia entre ellos
223 SELECT MAX(sal), MIN(sal), MAX(sal)-MIN(sal) FROM emp;
224
225 -- Consulta 56: Sin conocer los resultados del ejercicio anterior, quienes reciben el salario mas alto y el mas bajo y a cuanto ascienden estos salarios?
226 SELECT ename, sal FROM emp
227 WHERE sal = (SELECT MAX(sal) FROM emp) OR sal = (SELECT MIN(sal) FROM emp);
228
229 -- Consulta 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
230 SELECT emp.deptno, dname, avg(sal) FROM emp JOIN dept ON emp.deptno = dept.deptno
231 WHERE sal<5000 GROUP BY emp.deptno having min(sal)>900;
232
233 -- Consulta 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
234 SELECT emp.ename FROM emp, dept
235 WHERE emp.deptno = dept.deptno AND LENGTH(dept.loc) > 5 ORDER BY dept.loc DESC, emp.ename;
236
237 -- Consulta 59: Halla los empleados cuyo salario supera o coincide con la media del salario de la empresa
238 SELECT ename FROM emp WHERE sal >= (SELECT AVG(sal) FROM emp);
239
240 -- Consulta 60: Obten los empleados cuyo salario supera al de sus companeros de departamento
241 SELECT ename FROM emp e WHERE sal > ALL ( SELECT sal FROM emp c
242 WHERE e.deptno = c.deptno AND e.ename <> c.ename);
243
244 -- Consulta 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?
245 SELECT COUNT(DISTINCT job), count(DISTINCT empno), COUNT(DISTINCT sal), SUM(sal) FROM emp
246 WHERE deptno = 30;
247
248 -- Consulta 62: Cuantos empleados tienen comision?
249 SELECT COUNT(*) FROM emp
250 WHERE comm > 0;
251
252 -- Consulta 63: Cuantos empleados tiene el departamento 20?
253 SELECT COUNT(*) FROM emp
254 WHERE deptno = 20;
255
256 -- Consulta 64: Halla los departamentos que tienen mas de tres empleados, y el numero de empleados de los mismos.
257 SELECT deptno, COUNT(*) FROM emp
258 GROUP BY deptno
259 HAVING COUNT(*) > 3;
260
261 -- Consulta 65: Obten los empleados del departamento 10 que tienen el mismo empleo que alguien del departamento de Ventas. Desconocemos el codigo de dicho departamento
262 SELECT ename FROM emp
263 WHERE deptno = 10 AND job = ANY (SELECT job FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES') );
264
265 -- Alternativa
266 SELECT ename FROM emp
267 WHERE deptno = 10 AND job = ANY (SELECT job FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE dname='SALES');
268
269 -- Consulta 66: Halla los empleados que tienen por lo menos un empleado a su mando, ordenados inversamente por nombre
270 SELECT ename FROM emp
271 WHERE empno = ANY (SELECT mgr FROM emp)
272 ORDER BY ename DESC;
273
274 -- Consulta 67: Obten informacion sobre los empleados que tienen el mismo trabajo que los empleados que trabajan en Chicago
275 SELECT * FROM emp WHERE job = ANY (SELECT job FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc = 'CHICAGO') );
276
277 -- Alternativa
278 SELECT * FROM emp WHERE job = ANY (SELECT job FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE loc='CHICAGO');
279
280 -- Consulta 68: Que empleos distintos encontramos en la empresa, y cuantos empleados desempenan cada uno de ellos?
281 SELECT job, COUNT(*) AS EMPLEADOS FROM emp
282 GROUP BY job;
283
284 -- Consulta 69: Halla la suma de salarios de cada departamento
285 SELECT deptno, SUM(sal) FROM emp
286 GROUP BY deptno;
287
288 -- Consulta 70: Obten todos los departamentos sin empleados
289 SELECT dname FROM dept
290 WHERE deptno <> ALL (SELECT deptno FROM emp);
291
292 -- Consulta 71: Halla los empleados que no tienen a otro empleado a sus ordenes
293 SELECT ename FROM emp
294 WHERE empno <> ALL (SELECT mgr FROM emp WHERE mgr IS NOT NULL);
295
296 -- Consulta 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
297 SELECT d.deptno, dname, COUNT(empno) 'Num Empleados',avg(sal) 'Media salario' FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno GROUP BY e.deptno ORDER BY deptno;
298
299 -- Consulta 73: Halla los empleados del departamento 30, por orden descendiente de comision
300 SELECT ename FROM emp
301 WHERE deptno = 30
302 ORDER BY comm DESC;
303
304 -- Consulta 74: Obten los empleados que trabajan en Dallas o New York
305 SELECT ename FROM emp
306 WHERE deptno = ANY (SELECT deptno FROM dept WHERE loc IN ('DALLAS','NEW YORK'));
307
308 -- Alternativa
309 SELECT ename FROM emp e JOIN dept d ON e.deptno=d.deptno
310 WHERE loc in ('DALLAS','NEW YORK');
311
312 -- Consulta 75: Obten 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
313 SELECT e.ename AS empleado, e2.ename AS jefe FROM emp e LEFT JOIN emp e2 ON e.empno = e2.mgr;
314
315 -- Consulta 76: Lista los empleados que tengan el mayor salario de su departamento, mostrando el nombre del empleado, su salario y el nombre del departamento
316 SELECT ename, sal, dname FROM emp e INNER JOIN dept d ON e.deptno = d.deptno
317 WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = e.deptno);
318
319 -- Consulta 77: Deseamos saber cuantos empleados supervisa cada jefe. Para ello, obten un listado en el que se reflejen el codigo y el nombre de cada jefe, junto al numero de empleados que surpervisa directamente. Como puede haber empleados sin jefe, para estos se indicara solo el numero de ellos, y los valores restantes (codigo y nombre del jefe) se dejaran como nulos
320 SELECT j.empno, j.ename, COUNT(*) FROM emp e INNER JOIN emp j ON e.mgr = j.empno
321 GROUP BY j.empno, j.ename
322 UNION
323 SELECT null, null, COUNT(*) FROM emp
324 WHERE mgr IS NULL;
325
326 -- Consulta 78: Hallar el departamento cuya suma de salarios sea la mas alta, mostrando esta suma de salarios y el nombre del departamento
327 SELECT dname, SUM(sal) AS suma FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
328 GROUP BY dname
329 HAVING SUM(sal) = (SELECT MAX(SUM(sal)) FROM emp GROUP BY deptno);
330
331 -- Consulta 79: Obten los datos de los empleados que cobren los dos mayores salarios de la empresa. (Nota: Procura hacer la consulta de forma que sea facil obtener los empleados de los N mayores salarios)
332 SELECT * FROM emp e
333 WHERE ( (SELECT COUNT(DISTINCT sal) FROM emp WHERE sal > e.sal) < 2 );
334
335 -- Alternativa
336 SELECT * FROM emp
337 ORDER BY sal DESC LIMIT 2;
338
339 -- Consulta 80: Obten las localidades que no tienen departamentos sin empleados, en las que trabajen al menos cuatro empleados. Indica tambien el numero de empleados que trabjan en esas localidades. (Nota: una localidad que no tiene departamentos sin empleados no es lo mismo que una localidad que tiene departamentos con empleados.)
340 SELECT loc, COUNT(DISTINCT empno) AS EMPLEADOS FROM emp e left join dept d ON e.deptno = d.deptno
341 GROUP BY loc, e.deptno
342 HAVING COUNT(DISTINCT empno) >= 4;
