Transparencias Teoría de Prácticas– SQL como DML 2010
EJERCICIOS
Los corregidos están en azul:
– Nombre de los empleados que trabajan en el departamento 121
[SQL]
SELECT Nomem
FROM Temple
WHERE numde=121
[/SQL]
– Extraer todos los datos del departamento 121
SELECT *
FROM tdepto
WHERE numde=121
– Obtener los nombres y sueldos de los empleados con más de 3 hijos por orden alfabetico
SELECT Nomem, salar
FROM Temple W
HERE numhi>3
ORDER BY Nomem
– Obtener la comisión, departamentos y nombre de los empleados cuyo salario es inferior a 1900 euros, clasificándolos por departamento en orden creciente, y por comisión en orden decreciente dentro de cada departamento.
SELECT comis, numde, Nomem
FROM Temple
WHERE salar<1900
ORDER BY numde, comis desc;
– Igual que la anterior, pero las columnas resultantes han de llamarse comision, depto y empleado
SELECT comis as comision, numde as depto, Nomem as empleado
FROM Temple
WHERE salar<1900
ORDER BY numde, comis desc;
– Números de los departamentos donde trabajan empleados con salario inferior a 2500
SELECT numde
FROM Temple
WHERE salar<2500
– Obtener los valores diferentes de comisiones que hay en el departamento 110
SELECT distinct comis
FROM Temple
WHERE numde=110
– Hallar las combinaciones diferentes de valores de salario y comision en el departamento 111, por orden de salario y comisión crecientes.
SELECT distinct salar, comis
FROM Temple
WHERE numde=111
ORDER BY salar,comis
-Obtener los nombres de los emleados cuya comision es superior o igual al 50% de su salario, por orden alfabético
SELECT Nomem
FROM Temple
WHERE comis>=(salar/2)
ORDER BY 1
-Obtener por orden alfabético los nombres de los empleados cuyo salario supera al máximo salario de los empleados del departamento 123.
SELECT Nomem
FROM Temple
WHERE salar> all(SELECT salar FROM Temple WHERE numde=’123′)
ORDER BY 1;
-Obtener por orden alfabético los nombres de los empleados cuyo sueldo supera en tres veces y media o más al mínimo salario de los empleados del departamento 122
SELECT Nomem
FROM Temple
WHERE salar/3.5> some (SELECT salar FROM Temple WHERE numde=’122′)
ORDER BY 1;
EJERCICIOS PREDICADOS
1- Para todos los empleados que tienen Comisión, hallar sus Salarios mensuales totales incluyendo ésta. Ordenarlos por orden alfabético. Hallar también el porcentaje de su Salario total que supone la Comisión.
SELECT Nomem, (Salar + Comis)*12 as Salario_total, Salar/Comis as porcentaje
FROM Temple
WHERE Comis is not null and Comis<>0
ORDER BY Nomem;
2- Mostrar nombres y presupuestos de los departamentos 111 y 112, de tal manera que aparezcan en la misma fila.
SELECT uno.Nomde, uno.Presu, dos.Nomde, dos.Presu
FROM Tdepto uno, Tdepto dos
WHERE uno.Numde=’111′ and dos.Numde=’112’;
3- Obtener los nombres de los departamentos que no dependen funcionalmente de otros.
SELECT Numde
FROM Tdepto
WHERE depde is null;
4- Para los empleados que no tienen Comisión obtener por orden alfabético el nombre y el cociente entre su Salario y el número de hijos.
SELECT Nomem, Salar/Numhi as cociente
FROM Temple
WHERE (Comis is null or Comis=0) and Numhi>0
ORDER BY 1;
5- Obtener por orden alfabético los nombres de los empleados cuyo Salario supera al máximo Salario de los empleados del departamento 122.
SELECT Nomem
FROM Temple
WHERE Salar> all (SELECT Salar FROM Temple WHERE Numde=122)
ORDER BY 1;
6- Obtener por orden alfabético los nombres de los empleados cuyo Salario supera en tres veces y media o más al mínimo Salario de los empleados del departamento 122.
SELECT Nomem
FROM Temple
WHERE Salar>= some (SELECT Salar*3.5 FROM Temple WHERE Numde=122)
ORDER BY 1;
7- Obtener por orden alfabético los nombres de los empleados cuyo Salario supera en tres veces y media o más al mínimo Salario de los empleados del departamento 123. Razonar el resultado.
SELECT Nomem
FROM Temple
WHERE Salar/3.5>= some (SELECT Salar FROM Temple WHERE Numde=123)
ORDER BY 1;
No sale ninguna fila porque el departamento 123 no existe.
8- Obtener los nombres y Salarios de los empleados cuyo Salario coincide con la Comisión de algún otro o la suya propia. Ordenarlos alfabéticamente.
SELECT unique t.Nomem,t.Salar
FROM Temple t, Temple td
WHERE t.Salar=td.Comis
ORDER BY 1;
9- Obtener por orden alfabético los nombres y Salarios de los empleados cuyo Salario es inferior a la Comisión más alta existente.
SELECT Nomem,Salar
FROM Temple
WHERE Salar< (SELECT max(Comis) FROM Temple)
ORDER BY 1;
SELECT Nomem,Salar
FROM Temple
WHERE Salar< all(SELECT Comis FROM Temple)
ORDER BY 1;
10- Obtener por orden alfabético los nombres y Salarios de los empleados cuyo Salario es inferior al cuádruplo de la Comisión más baja existente.
SELECT Nomem,Salar
FROM Temple
WHERE Salar<4*(SELECT min(Comis) FROM Temple)
ORDER BY 1,2;
SELECT Nomem,Salar
FROM Temple
WHERE Salar< some(SELECT Comis*4 FROM Temple)
ORDER BY 1,2;
11- Obtener por orden alfabético los nombres de los empleados cuyo Salario está entre 2500 y 3000 euros.
SELECT Nomem,Salar
FROM Temple
WHERE Salar between 2500 and 3000
ORDER BY 1,2;
12- Obtener por orden alfabético los nombres y los Salarios de los empleados cuyo Salario dividido por su número de hijos cumpla una, o ambas, de las dos condiciones siguientes
a) Que sea inferior a 1200 euros
b) Que sea superior al doble de su Comisión Ejercicio de examen
SELECT Nomem,Salar
FROM Temple
WHERE (((Salar/Numhi<1200) or ((Salar/Numhi)>(2*Comis))) and Numhi>0
ORDER BY 1;
13- En la fiesta de Reyes se desea organizar un espectáculo para los hijos de los empleados, que se representará en dos días diferentes. El primer día asistirán los empleados cuyo apellido empiece por las letras desde A hasta L, ambas inclusive. El segundo día se cursarán invitaciones para el resto. A cada empleado se le asignarán tantas invitaciones gratuitas como hijos tenga y dos más. Además en la fiesta se entregará a cada empleado un obsequio por hijo. Obtener una lista por orden alfabético de los nombres a quienes hay que invitar el primer día de la representación, incluyendo también cuántas invitaciones corresponden a cada nombre y cuántos regalos hay que preparar para él. (Obsérvese que si dos empleados están casados, esta consulta calculará dos veces el número de invitaciones familiar si los hijos figuran en la tabla tanto en la fila del marido como de la esposa).
SELECT Nomem,Numhi+2 as invitaciones, Numhi as obsequios
FROM Temple
WHERE Nomem between ‘A%’ and ‘M%’;
14- Obtener por orden alfabético los nombres de los empleados cuyo primer apellido es Mora o empieza por Mora
SELECT Nomem
FROM Temple
WHERE Nomem like ‘MORA%’
ORDER BY 1;
15- Obtener por orden alfabético los nombres de los empleados cuyo nombre de pila empieza por Valeriana.
SELECT Nomem
FROM Temple
WHERE Nomem like ‘%, VALERIANA%’
ORDER BY 1;
16- Obtener por orden alfabético los nombres de los empleados cuyo apellido tenga siete letras.
SELECT Nomem
FROM Temple
WHERE Nomem like ‘_ _ _ _ _ _ _,%’;
17- Obtener por orden alfabético los nombres de los empleados cuyo apellido tenga seis o más letras.
SELECT Nomem
FROM Temple
WHERE Nomem like ‘_ _ _ _ _ _%,%’;
18- Obtener los nombres de los empleados cuyo apellido tenga tres letras o menos.
SELECT Nomem
FROM Temple
WHERE Nomem not like ‘_ _ _ _%,%’;
19- Obtener por orden alfabético los nombres de los empleados cuyo apellido termina en EZ y su nombre de pila termina en O y tiene al menos tres letras.
SELECT Nomem
FROM Temple
WHERE Nomem like ‘%EZ, _ _O’ ORDER BY 1;
20- Se desea hacer un regalo de un 1 % del Salario a los empleados en el día de su onomástica. Hallar por orden alfabético los nombres y cuantía de los regalos en euros para los que celebren su santo el día de San Honorio.
SELECT Nomem, Salar, Salar*0.01 as regalo
FROM Temple
WHERE Nomem like ‘%, %HONORI_ %’ (Para José Honorio De Todos los Santos)
ORDER BY 1;
21- Obtener por orden alfabético los nombres de los empelados que trabajan en el mismo departamento que Pilar Gálvez o Dorotea Flor
SELECT Nomem, Numde
FROM Temple
WHERE Numde in (SELECT Numde FROM Temple WHERE Nomem like ‘GALVEZ, PILAR’ or Nomem like ‘FLOR, DOROTEA’)
ORDER BY 1;
Ó
SELECT Nomem, Numde
FROM Temple
WHERE Numde in (SELECT Numde FROM Temple WHERE Nomem in (‘GALVEZ, PILAR’, ‘FLOR, DOROTEA’))
ORDER BY 1;
Ó
SELECT Nomem, Numde
FROM Temple
WHERE Numde = SOME (SELECT Numde FROM Temple WHERE Nomem in (‘GALVEZ, PILAR’, ‘FLOR, DOROTEA’))
ORDER BY 1;
(“IN” es igual o equivalente a “ = SOME”)
22- Obtener una lista por orden alfabético de los empleados cuyo Salario coincida con el de alguno de los empleados del departamento 100. Resolver de dos maneras diferentes.
a) SELECT Nomem, Numde, Salar
FROM Temple
WHERE Salar IN (SELECT Salar FROM Temple WHERE Numde=100)
ORDER BY 1;
b) SELECT Nomem, Numde, Salar
FROM Temple
WHERE Salar = SOME (SELECT Salar FROM Temple WHERE Numde=100)
ORDER BY 1;
23- Obtener los nombres de los centros de trabajo si hay alguno que esté en la calle Atocha.
SELECT nomce, señas
FROM Tcentr
WHERE exists (SELECT nomce FROM Tcentr WHERE señas like ‘%ATOCHA%’);
24- Obtener por orden alfabético los nombres y Salarios de los empleados del departamento 111 que tienen Comisión si hay alguno de ellos cuya Comisión supere al 15 % de su Salario.
SELECT Nomem, Salar
FROM Temple
WHERE Numde=111 and Comis is not null and exists
(SELECT Comis FROM Temple WHERE Comis>Salar*0.15 and Numde= 111 and Comis is not null)
ORDER BY 1;
25- Obtener por orden alfabético los nombres y Comisiones de los empleados del departamento 110 si hay en él algún empleado que tenga Comisión.
SELECT Nomem, Comis
FROM Temple
WHERE Numde=110 and exists (SELECT * FROM Temple WHERE Comis<>0 or Comis<>null) ORDER BY 1;
26- Obtener los nombres, Salarios y fechas de ingreso de los empleados que, o bien ingresaron después de 1.1.88, bien tienen un Salario inferior a 2000 euros. Clasificarlos por fecha y nombre.
SELECT Nomem, Salar,fecin
FROM Temple
WHERE fecin >’1.1.88′ or Salar <2000
ORDER BY 3, 1;
27 – Obtener por orden alfabético los nombres de los departamentos que no sean de Dirección ni de Sectores.
SELECT Nomde
FROM Tdepto
WHERE Nomde<>’Direccion’ and Nomde<>’Sectores’
ORDER BY 1;
28- Obtener por orden alfabético los nombres y Salarios de los empleados que o bien no tienen hijos y ganan más de 2000 euros, o bien tienen hijos y ganan menos de 3000 euros.
SELECT Nomem,Salar
FROM Temple
WHERE (Numhi=0 and Salar>2000) or
(Numhi<>0 and Salar<3000)
ORDER BY 1;
SELECT Nomem,Salar
FROM Temple
WHERE (Numhi is null and Salar>2000) or
(Numhi is not null and Salar<3000)
ORDER BY 1;
29- Hallar por orden alfabético los nombres y Salarios de empleados de los departamentos 110 y 111 que o bien no tengan hijos o bien su Salario por hijo supere a 1000 euros, si hay alguno sin Comisión en los departamentos 111 ó 112.
SELECT Nomem, Salar
FROM Temple
WHERE (Numde= 110 or Numde= 111) and (Numhi = 0 or (Salar/Numhi) <1000) and exists (SELECT 1 FROM Temple WHERE Comis is null and Numde IN (111, 112))
ORDER BY 1;
30- Hallar por orden alfabético los nombres de departamentos que o bien tienen directores en funciones o bien en propiedad y su Presupuesto anual excede a 50.000 euros o bien no dependen de ningún otro.
SELECT Nomde
FROM Tdepto
WHERE tidir=’P’ or tidir=’F’ and (Presu >50000 or depde is null)
ORDER BY 1;
EJERCICIOS DE AGREGACION Y AGRUPAMIENTO
1- Hallar el salario máximo para el conjunto de empleados del departamento 100.
SELECT max(salar)
FROM Temple
WHERE numde=100;
2- Obtener por orden alfabético los salarios y nombres de los empleados cuyo salario se diferencia con el máximo en menos de un 40% de éste.
SELECT numem, salar
FROM Temple
WHERE salar>0.6*(SELECT max(salar) FROM Temple));
3- Hallar el número de empleados de la empresa.
SELECT count(*)
FROM Temple;
4- Hallar el número de empleados y de extensiones telefónicas del departamento 112.
SELECT count (*) as numero_de_empleados, count(distinct extel) as
extensiones_telefonicas
FROM Temple
WHERE numde=112;
5- Hallar el número de empleados del departamento 112, así como cuántas comisiones hay y la suma y media de sus comisiones.
SELECT count(*) as numero_de_empleados, count(comis) as comisiones,
avg(comis) as media_comisiones, sum(comis) as suma_comisiones
FROM Temple
WHERE numde=112;
6- Hallar cuántas comisiones diferentes hay y su valor medio.
SELECT count (distinct comis),avg(comis)
FROM Temple;
7- Hallar la media del número de hijos de los empleados del departamento 123.
SELECT count(*)
FROM Temple
WHERE fecna<’01/01/29′;
8- Hallar para cada departamento que depende del 100 su número y su presupuesto, junto con la media del presupuesto de todos los departamentos.
SELECT numde,presu,(SELECT avg(presu) FROM tdepto)
FROM tdepto
WHERE depde=100;
9 – Obtener por orden alfabético los nombres de los empleados cuyo salario supera en tres veces y media o más al mínimo salario de los empleados del departamento 123. Razonar el resultado.
Utilizar funciones de agregación y agrupación.
Select nomem
From temple
Where salar > 3.5 *(select min (salar) from temple where numde=123)
Order by 1;
10- Hallar cuántos departamentos hay y el presupuesto anual medio de ellos.
SELECT count(*),avg(presu)
FROM tdepto
11- Como la pregunta anterior, pero para los departamentos que no tienen director en
propiedad.
SELECT count(*),avg(presu)
FROM tdepto
WHERE tidir<>’P’;
12- Hallar por orden de número de empleado el nombre y salario total (salario más comisión) de los empleados cuyo salario total supera al salario mínimo en 3000 euros mensuales.
SELECT numem,Nomem, salar+nvl(comis,0)
FROM Temple
WHERE ((salar+nvl(comis,0))-(SELECT min(salar) FROM Temple))>3000
ORDER BY numem;
De años anteriores
10- Resolver el ejercicio 7 del boletín de predicados utilizando funciones de agregación.
SELECT Nomem
FROM Temple
WHERE salar>= 3.5*(SELECT min(salar) FROM Temple WHERE numde=123)
ORDER BY 1;
11- Resolver el ejercicio 9 del boletín de predicados utilizando funciones de agregación.
SELECT Nomem,salar
FROM Temple
WHERE salar<(SELECT max(comis) FROM Temple)
ORDER BY 1;
12- Resolver el ejercicio 10 del boletín de predicados utilizando funciones de agregación.
SELECT Nomem,salar
FROM Temple
WHERE salar> 4*(SELECT min(comis) FROM Temple)
ORDER BY 1;
13- Hallar la masa salarial anual (salario más comisión) de la empresa (se suponen 14 pagas anuales).
SELECT (sum(salar)+sum(comis))*14 as masa_salarial_anual
FROM Temple;
Esto está mal porque los valores nulos de comis no los cuenta.
Correcto:
SELECT sum(salar + nvl(comis,0))*14
FROM Temple;
Nvl (arg1,arg2) -> Si arg1 distinto de nulo devuelve arg1. Si arg1 igual a nulo devuelve arg2.
14- Hallar el salario medio de los empleados cuyo salario no supera en más de 20 % al salario mínimo de los empleados que tienen algún hijo y su salario medio por hijo es mayor que 1000 euros.
SELECT avg(salar)
FROM Temple
WHERE salar<1.2*(SELECT min(salar) FROM Temple WHERE numhi<>0 and (salar/numhi)>1000);
15- Hallar la diferencia entre el salario más alto y el más bajo.
SELECT max(salar)-min(salar) as diferencia
FROM Temple;
16- Hallar el presupuesto medio de los departamentos cuyo presupuesto supera al presupuesto medio de los departamentos.
SELECT avg(presu)
FROM tdepto
WHERE presu >(SELECT avg(presu) FROM tdepto);
17- Hallar el número medio de hijos por empleado para todos los empleados que no tienen más de dos hijos.
SELECT avg(numhi)
FROM Temple
WHERE numhi<3
18- Hallar el número de empleados de los departamentos 100 y 110.
SELECT count(NUMDE),numde
FROM Temple
WHERE numde in(100,110)
group BY numde;
19- Agrupando por departamento y número de hijos, hallar cuántos empleados hay en cada grupo para los departamentos 100 y 110.
SELECT count(*),numde, numhi
FROM Temple
WHERE numde in(100,110)
group BY numde, numhi
ORDER BY numhi;
20- Para los departamentos en los que hay algún empleado cuyo salario sea mayor que 4000 euros al mes hallar el número de empleados y la suma de sus salarios, comisiones y número de hijos.
SELECT count(numem),sum(salar),sum(comis),sum(numhi),numde
FROM Temple
group BY numde having numde in(SELECT distinct (numde) FROM Temple WHERE salar>4000);
21- Agrupando por número de hijos, hallar la media por hijo del salario total (salario y comisión).
SELECT numhi, sum((salar+nvl(comis,0))/numhi)
FROM Temple
WHERE numhi>0
group BY numhi
ORDER BY 1
24- Para cada departamento, hallar la media de la comisión con respecto a los empleados que la reciben y con respecto al total de empleados.
SELECT numde, avg(comis), sum(comis)/cont(*) as mediatotal
FROM Temple
WHERE comis is not null
group BY numde
23- Para cada extensión telefónica hallar cuántos empleados la usan y el salario medio de éstos.
SELECT count(numem),avg(salar),extel
FROM Temple
group BY extel
ORDER BY extel ;
24- Para cada extensión telefónica y cada departamento hallar cuántos empleados la usan y el salario medio de éstos.
SELECT count(numem),avg(salar),extel,numde
FROM Temple
group BY extel,numde
ORDER BY extel;
25- Hallar los números de extensión telefónica mayores de los diversos departamentos, sin incluir los números de éstos.
SELECT max(extel)
FROM Temple
group BY numde
ORDER BY 1;
26- Para cada extensión telefónica hallar el número de departamentos a los que sirve.
SELECT extel,count(numde)
FROM Temple
group BY extel
ORDER BY 1;
27- Para los departamentos en los que algún empleado tiene comisión, hallar cuántos empleados hay en promedio por cada extensión telefónica.
SELECT numde,count(*)/count(distinct extel)
FROM Temple
Where numde in (SELECT distinct (numde) FROM Temple WHERE comis is not null)
Group by numde;
Hacerlo conhaving!!!!
28- Para los empleados que tienen comisión, hallar para los departamentos cuántos empleados hay en promedio por cada extensión telefónica.
SELECT numde,count(*)/count(distinct extel)
FROM Temple
WHERE comis is not null
group BY numde;
29- Hallar los departamentos que tienen más de 2 extensiones teléfónicas.
SELECT numde, count(distinct(extel))
FROM Temple
group BY numde
having count (distinct(extel)) > 2
30- Hallar los departamentos cuyo salario medio es mayor a 1500
SELECT numde,
FROM Temple
group BY numde
having avg(salar)> 1500
31- Hallar los departamentos cuyo valor mayor de extensión telefónica es superior a 150.
SELECT numde,
FROM Temple
group BY numde
having MAX(extel) > 150
Si pudieramos una select subordinada escalar having(Select max extel from temple)>150 lo que hariamos es coger las extensiones mayores de toda la tabla temple. En la primera esta agrupado por grupos.
Correlacionada:
SELECT numde,
FROM Temple
group BY numde
having MAX(extel) > 150
32- Para los departamentos cuyo salario medio supera al de la empresa, hallar cuántas extensiones telefónicas tienen.
SELECT numde, count(distinct extel)
FROM Temple
group BY numde having avg(salar)>(SELECT avg(salar) FROM Temple);
33- Para cada centro hallar los presupuestos medios de los departamentos dirigidos en propiedad y en funciones, excluyendo del resultado el número del centro.
SELECT avg(presu),tidir
FROM tdepto
group BY numce,tidir;
34- Obtener por orden creciente los números de extensiones telefónicas de los departamentos que tienen más de dos y que son compartidas por menos de 4 empleados, excluyendo las que no son compartidas.
Solución 1:
SELECT extel
FROM Temple
WHERE numde in (SELECT numde FROM Temple group BY numde having count (distinct extel) > 2)
GROUP BY extel
having count (numem) BETWEEN 2 AND 3
ORDER BY 1;
Solución 2:
SELECT extel
FROM Temple
WHERE numde in (SELECT numde FROM Temple group BY numde having count (distinct extel) > 2) and extel in (SELECT extel FROM Temple group BY extel having count (numem) < 4 and count(numem)>1)
ORDER BY 1;
Solución 3:
SELECT extel
FROM Temple
group BY extel
WHERE numde in(SELECT numde FROM Temple group BY numde having count(distinct extel) > 2) having count(*)> 1 and count (*) < 4
ORDER BY 1;
35- Hallar el máximo valor de la suma de los salarios de los departamentos.
SELECT sum(salar),numde
FROM Temple
group BY numde
having sum(salar)>=all(SELECT sum(salar)FROM Temple group BY numde);
EJERCICIOS CONSULTAS SOBRE VARIAS TABLAS
1- Averiguar los nombres de los departamentos que tienen un presupuesto inferior a 100.000 euros, así como el nombre del centro de trabajo donde se encuentran ubicados.
SELECT Nomde, c.nomce, presu
FROM tcentr c, tdepto d
WHERE c.numce=d.numce and d.presu<100000
ORDER BY 3;
2- Hallar el salario máximo para el conjunto de empleados del departamento FINANZAS.
SELECT max(e.salar)
FROM Temple e, tdepto d
WHERE d.Nomde=’FINANZAS’ and e.numde=d.numde;
3- Obtener por orden alfabético los salarios, número de empleado y nombre de departamento de los empleados cuyo salario se diferencia con el máximo en menos de un 40% de éste.
SELECT numem, salar,d.Nomde
FROM Temple e,tdepto d
WHERE salar>0.6*(SELECT max(salar) FROM Temple) and d.numde=e.numde;
4- Hallar el número de empleados y de extensiones telefónicas del departamento PERSONAL.
SELECT count(*), count(distinct extel)
FROM Temple e, tdepto d
WHERE e.numde=d.numde and d.Nomde=’PERSONAL’;
5- Hallar el número de empleados del departamento PERSONAL, así como cuántas comisiones hay y la suma y media de sus comisiones.
SELECT count(e.numem),count(e.comis),sum(e.comis),avg(e.comis)
FROM Temple e, tdepto d
WHERE e.numde=d.numde and d.Nomde=’PERSONAL’;
6- Hallar la media del número de hijos de los empleados del departamento PROCESO DE DATOS.
SELECT avg(numhi)
FROM Temple e, tdepto d
WHERE e.numde=d.numde and Nomde=’PROCESO DE DATOS’;
7- Hallar para cada departamento que depende del depto DIRECC. COMERCIAL su número y su presupuesto, junto con la media del presupuesto de todos los departamentos.
SELECT t1.numde,t1.presu, (SELECT avg(presu) FROM tdepto)
FROM tdepto t1, tdepto t2
WHERE t1.depde=t2.numde and t2.Nomde=’DIRECC. COMERCIAL’
8- Hallar por orden de número de empleado el nombre del departamento, nombre del empleado y salario total (salario más comisión) de los empleados cuyo salario total supera al salario mínimo en 1000 euros mensuales.
SELECT Nomde,numem,Nomem, salar+nvl(comis,0)
FROM Temple e, tdepto d
WHERE e.numde=d.numde and (salar+nvl(comis,0))>(SELECT min(salar) FROM Temple)+1000 ORDER BY numem;
9- Si el departamento 122 está ubicado en la calle Alcalá, obtener por orden alfabético los nombres de aquellos empleados cuyo salario supere al salario medio de su departamento.
SELECT e.Nomem
FROM Temple e
WHERE e.salar>(SELECT avg(salar)FROM Temple e2 WHERE e2.numde=e.numde) and exists(SELECT * FROM tdepto d, tcentr c WHERE d.numde=122 and c.señas like ‘%ALCALA%’ and d.numce=c.numce);
- Para cada departamento con presupuesto inferior a 60.000 euros, hallar el nombre del centro donde está ubicado y el máximo salario de sus empleados, si éste excede a 2000 euros. Clasificar alfabéticamente por nombre de departamento.
Solucion 1:
SELECT d.Nomde,c.nomce,max(salar)
FROM Temple e, tdepto d, tcentr c
WHERE d.numce=c.numce and e.numde=d.numde and presu<60000
group BY d.Nomde,c.nomce having max(e.salar)>2000;
11- Hallar por orden alfabético los nombres de los departamentos que dependen de los que tienen un presupuesto inferior a 50.000 euros.
SELECT t1.Nomde
FROM tdepto t1, tdepto t2
WHERE t1.depde=t2.numde and 2.presu<50000
ORDER BY 1;
12- Para los departamentos cuyo presupuesto anual supera a 60 000 euros, hallar cuántos empleados hay en promedio por cada extensión telefónica.
Solucion1:
SELECT count(numem)/count(distinct extel),d.Nomde
FROM Temple e, tdepto d
WHERE e.numde=d.numde and d.presu>60000
group BY d.Nomde;
Solucion2:
SELECT count(numem)/count(distinct extel
FROM Temple
WHERE numde in (Select numde from tdepto where presu>60000)
group BY Nomde;
Solucion3:
SELECT count(numem)/count(distinct extel
FROM Temple
WHERE Exist (Select 1 from tdepto where tdepto.numde= temple.numde and presu>60000)
group BY Nomde;
13- Obtener por orden alfabético los nombres de empleados cuyo apellido empieza por G y trabajan en un departamento ubicado en algún centro de trabajo de la calle Alcalá.
SELECT e,Nomem
FROM Temple e,tdepto d,tcentr c
WHERE e.numde=d.numde and d.numce=c.numce and e.Nomem like ‘G%’ and c.señas like ‘%ALCALA%’;
14- Hallar por orden alfabético los distintos nombres de los empleados que son directores en funciones.
Solucion 1:
SELECT Nomem
FROM Temple e,tdepto d
WHERE e.numem=d.direc and d.tidir=’F’
ORDER BY 1;
Solucion 2:
SELECT Nomem
FROM Temple
WHERE numem in (select direc from tdepto where tdir= ‘F’)
ORDER BY 1;
15- Para todos los departamentos que no sean de dirección ni de sectores, hallar número de departamento y sus extensiones telefónicas, por orden creciente de departamento y, dentro de éste, por número de extensión creciente.
SELECT d.numde,e.extel
FROM tdepto d, Temple e
WHERE e.numde=d.numde and d.Nomde not like ‘%SECTOR%’ and d.Nomde not like ‘%DIRECCION%’
ORDER BY 1,2;
16- A los distintos empleados que son directores en funciones se les asignará una gratificación del 5 % de su salario. Hallar por orden alfabético los nombres de estos empleados y la gratificación correspondiente a cada uno.
SELECT e.Nomem,(e.salar*1.05) as gratificacion
FROM Temple e, tdepto d
WHERE e.numem=d.direc and d.tidir=’F’
ORDER BY 1;
17- Hallar por orden alfabético los nombres de los empleados cuyo director de departamento es Marcos Pérez, bien en propiedad o bien en funciones, indicando cuál es el caso para cada uno de ellos.
SELECT distinct(e.Nomem) ,(SELECT d2.tidir FROM tdepto d2 WHERE d2.numde=e.numde) as tipo
FROM Temple e,tdepto d
WHERE e.numde=d.numde and d.direc= (SELECT numem FROM Temple WHERE Nomem like ‘PEREZ, MARCOS’)
ORDER BY 1;
18- Hallar por orden alfabético los nombres de los empleados que dirigen departamentos de los que dependen otros departamentos, indicando cuántos empleados hay en total en éstos.
SELECT e.Nomem
FROM Temple e, tdepto d
WHERE e.numde=d.numde and d.direc=e.numem and d.numde in (SELECT d1.numde FROM tdepto d1,tdepto d2 WHERE d2.depde=d1.numde )
ORDER BY 1;