Administración de sistemas informáticos
Consultas con Agrupamientos en SQL (Structured Query Language)
4. CONSULTAS CON AGRUPAMIENTO DE FILAS.
Hallar para cada departamento el salario medio, el mínimo, el máximo y la media aritmética de éstos.
SELECT AVG (SALAR),MIN (SALAR),MAX (SALAR)
FROM TEMPLE
GROUP BY NUMDE
Hallar por departamentos la edad en años cumplidos del empleado más viejo del departamento que tiene comisión. Ordenar el resultado por edades.
SELECT NUMDE, MAX(CONVERT(INT,(DATEDIFF(DAY,FECNA,GETDATE())/365.25)))
FROM TEMPLE
WHERE COMIS<>0
GROUP BY NUMDE
Agrupando por departamento y número de hijos, hallar cuántos empleados hay en cada grupo.
SELECT NUMDE,NUMHI,COUNT (NOMEM) [NUMEROS DE EMPLEADOS]
FROM TEMPLE
GROUP BY NUMDE,NUMHI
ORDER BY NUMDE,NUMHI
Hallar el salario máximo y el mínimo para cada grupo de empleados con igual número de hijos y que tienen al menos uno, y sólo si hay más de un empleado en el grupo y el salario máximo de éste excede a 200000 ptas.
SELECT NUMHI,MAX(SALAR)[SALARIO MAXIMO],MIN(SALAR)[SALARIO MINIMO]
FROM TEMPLE
GROUP BY NUMHI
HAVING NUMHI>=1 AND COUNT(NUMEMP)>1 AND MAX(SALAR)>200
Hallar el salario medio por departamento para aquellos departamentos cuyo salario máximo es inferior al salario medio de todos los empleados.
SELECT NUMDE,AVG(SALAR) AS [SALARIO MEDIO]
FROM TEMPLE
GROUP BY NUMDE
HAVING MAX(SALAR)< (SELECT AVG(SALAR)
FROM TEMPLE)
Hallar el salario medio y la edad media en años para cada grupo de empleados con igual comisión y para los que no la tengan.
SELECT COMIS,AVG(SALAR)AS [SALARIO MEDIO],AVG(CONVERT(INT,DATEDIFF(DAY,FECNA,GETDATE())/365.25)) AS [EDAD MEDIA]
FROM TEMPLE
GROUP BY COMIS
Para los departamentos en los que hay algún empleado cuyo salario sea mayor que 400000 ptas. al mes hallar el número de empleados y la suma de sus salarios, comisiones y número de hijos.
SELECT NUMDE AS [NUMERO DE DEPARTAMENTO],
COUNT(NUMEMP)[CANTIDAD DE EMPLEADOS],
SUM(SALAR)AS [SUMA SALARIOS],
SUM(COMIS)AS [SUMA COMISIONES],
SUM(NUMHI)AS [SUMA NUMHI]
FROM TEMPLE
GROUP BY NUMDE,SALAR,COMIS,NUMHI
HAVING NUMDE IN(SELECT NUMDE
FROM TEMPLE
WHERE SALAR>400)
100 1 380 0 2
100 1 450 0 0
100 1 720 0 6
110 1 200 0 1
110 1 215 0 1
110 1 480 50 2
121 1 190 0 1
121 1 300 0 4
121 1 310 0 3
121 1 440 0 0
122 1 175 0 0
122 1 380 0 0
122 1 405 0 2
122 1 450 0 1
130 1 290 0 5
130 1 400 0 0
130 1 420 0 0
SELECT NUMDE AS [NUMERO DE DEPARTAMENTO],
COUNT(NUMEMP)[CANTIDAD DE EMPLEADOS],
SUM(SALAR)AS [SUMA SALARIOS],
SUM(COMIS)AS [SUMA COMISIONES],
SUM(NUMHI)AS [SUMA NUMHI]
FROM TEMPLE
WHERE NUMDE IN(SELECT NUMDE
FROM TEMPLE
WHERE SALAR>400)
GROUP BY NUMDE
100 3 1550 0 8
110 3 895 50 4
121 4 1240 0 8
122 4 1410 0 3
130 3 1110 0 5
Para los departamentos en los que la antigüedad media de sus empleados supera a la edad media de la empresa, hallar el salario mínimo, el medio y el máximo.
Select numde'Numero de Departamento', MIN(salar)'Salario Minimo', max(salar)'Salario Maximo', avg(salar)'Salario Medio'
from TEMPLE
group by NUMDE
having AVG(CONVERT(int,datediff(day,FECNA,getdate())/365.25))> (select avg((CONVERT(int,datediff(day,FECNA,getdate())/365.25))) from temple)
order by NUMDE asc
Para los departamentos en los que haya algún empleado con más de 10 años de antigüedad y tales que la media de hijos por cada uno de estos empleados sea superior a 1, hallar el salario medio de estos empleados.
select numde 'Nº de Departamento', AVG(salar) 'Salario Medio'
from temple
group by numde
having numde = some (select numde
from TEMPLE
where NUMHI >1 and CONVERT(int,datediff(day,FECIN,getdate())/365.25)> 10)
Agrupando por número de hijos, hallar la media por hijo del total de salario y comisión.
SELECT numhi AS [NUMERO DE HIJOS],SUM((salar+comis)/numhi)AS[MEDIA DEL SALARIO POR HIJOS]
FROM TEMPLE
GROUP BY NUMHI
HAVING NUMHI>0
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)as media
FROM Temple
WHERE comis>0
group by NUMDE
union
select numde, SUM(COMIS)/COUNT(*)
from TEMPLE
group BY numde
Para cada extensión telefónica, hallar cuántos empleados la usan y el salario medio de éstos.
SELECT EXTEL,COUNT (NUMEMP) [CANTIDAD DE EMPLEADOS],AVG (SALAR)AS [SALARIO MEDIO]
FROM TEMPLE
GROUP BY EXTEL
Para cada extensión telefónica y cada departamento, hallar cuántos empleados la usan y el salario medio de éstos.
SELECT EXTEL,NUMDE,COUNT (NUMEMP) [CANTIDAD DE EMPLEADOS],AVG (SALAR)AS [SALARIO MEDIO]
FROM TEMPLE
GROUP BY EXTEL,NUMDE
ORDER BY EXTEL,NUMDE
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
HAVING MAX(EXTEL)>=ALL(SELECT MAX(EXTEL)
FROM TEMPLE)
Para cada extensión telefónica, hallar el número de departamentos a los que sirve.
SELECT DISTINCT EXTEL,NUMDE
FROM TEMPLE
ORDER BY EXTEL,NUMDE
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(NUMEMP)/COUNT(DISTINCT EXTEL)AS [PROMEDIO POR EXTENSION]
FROM TEMPLE
GROUP BY NUMDE
HAVING NUMDE IN (SELECT DISTINCT(NUMDE)
FROM TEMPLE
WHERE COMIS IS NOT NULL)
Para los departamentos en los que algún empleado tiene comisión, hallar cuántos empleados con comisión hay en promedio por cada extensión telefónica.
SELECT NUMDE,COUNT(NOMEM)/COUNT(DISTINCT EXTEL)
FROM TEMPLE
WHERE COMIS IS NOT NULL
GROUP BY NUMDE
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.
SELECT NUMDE,COUNT (DISTINCT EXTEL)AS[CANTIDAD EXT. TELEFONICAS]
FROM TEMPLE
GROUP BY NUMDE
HAVING COUNT (DISTINCT EXTEL)>2 AND COUNT(DISTINCT NOMEM)<4 ANDCOUNT(NUMEMP)<4 AND COUNT(NUMEMP)>1
ORDER BY NUMDE
Para los departamentos cuyo salario medio supera al de la empresa, hallar cuántas extensiones telefónicas tienen.
SELECT NUMDE, COUNT(DISTINCT EXTEL)AS [CANTIDAD DE EXTENSIONES]
FROM TEMPLE
GROUP BY NUMDE
HAVING AVG(SALAR)>(SELECT AVG(SALAR)
FROM TEMPLE)
Para cada centro, hallar los presupuestos medios de los departamentos dirigidos en propiedad y en funciones, excluyendo del resultado el número de centro.
SELECT AVG(PRESU)[PRESUPUESTO MEDIO],TIDIR
FROM TDEPTO
GROUP BY NUMCE,TIDIR
Hallar el máximo valor de la suma de los salarios de los departamentos.
SELECT (SUM(SALAR))AS [SUMA SALARIO MAXIMO],NUMDE
FROM TEMPLE
GROUP BY NUMDE
HAVING SUM(SALAR)>=ALL(SELECT SUM(SALAR)
FROM TEMPLE
GROUP BY NUMDE)
COMO SE PUEDE INTERPRETAR DE DOS MANERAS (O ASÍ LO CREO YO) LO HE HECHO DE AMBAS MANERAS.
COMO VES, REALMENTE ES EL MISMO RESULTADO
Descargar
| Enviado por: | Lancelot |
| Idioma: | castellano |
| País: | España |
Todos los derechos reservados.