Consultas con Agrupamientos en SQL (Structured Query Language)

SQL (Structured Query Language). Lenguaje de consulta estructurado relacional. Sistema gestor de bases de datos

  • Enviado por: Lancelot
  • Idioma: castellano
  • País: España España
  • 7 páginas
publicidad

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