Ayuda ejercicios unidad 5: Consultas de resumen

Ejercicio 1: Funciones de agregado

1. ¿Cuántas oficinas tenemos en Valencia?

SELECT COUNT(*) AS Valencianas

FROM oficinas

WHERE ciudad = 'Valencia';

2. Hallar cuántos pedidos hay de más de 250 euros.

SELECT COUNT(*) AS [Superiores a 250]

FROM pedidos

WHERE importe > 250;

3. ¿Cuántos títulos (cargos) de empleados se usan?

SELECT COUNT(DISTINCT titulo) AS [Cuántos títulos]

FROM empleados;

4. ¿Entre qué cuotas se mueven los empleados?

SELECT MIN(cuota) AS [Cuota mínima], MAX(cuota) AS [Cuota máxima]

FROM empleados;

Ejercicio 2: Agrupamiento de filas: GROUP BY

1. De cada vendedor (todos) queremos saber su nombre y el importe total vendido. En caso de que el importe sea NULL, cámbialo por 0,00 con la función ISNULL().

SELECT numemp, nombre, ISNULL(SUM(importe),0) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, nombre;

2. De cada empleado, obtener el importe vendido a cada cliente.

SELECT rep, clie AS Cliente, SUM(importe) AS [Importe vendido]

FROM pedidos

GROUP BY rep, clie;

3. Repetir la consulta anterior pero ahora deben aparecer también los empleados que no han vendido nada.

SELECT numemp, clie, SUM(importe) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie

ORDER BY numemp,clie;

4. Repetir la consulta pero ahora debe aparecer también el total de cuánto ha vendido cada empleado.

SELECT numemp, clie, SUM(importe) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie WITH ROLLUP

ORDER BY numemp,clie;

5. En los resultados anteriores no se distinguen bien las líneas que corresponden a totales. Modificar la consulta para indicar con un 1 si es una fila de totales y con un 0 si no lo es.

SELECT numemp, clie, SUM(importe) AS [Importe vendido], GROUPING(clie) AS [Agrupa clie], GROUPING(numemp) AS [Agrupa numemp]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie WITH ROLLUP

ORDER BY numemp,clie;

6. Ahora modifica la consulta para que las filas de totales aparezcan más claras, substituyendo el 1 de Agrupa clie por "Total empleado", el 1 de Agrupa numemp por Total final y el valor 0 por espacio en blanco.

SELECT numemp, clie, SUM(importe) AS [Importe vendido], 

   CASE GROUPING(clie) WHEN 0 THEN ' ' WHEN 1 THEN 'Total empleado' END AS [Agrupa clie], 

   CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [Agrupa numemp]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie WITH ROLLUP

ORDER BY numemp,clie;

7. Ahora coloca las columnas Agrupa delante de las demás columnas.

SELECT  CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [Agrupa numemp], 

        CASE GROUPING(clie) WHEN 0 THEN ' ' WHEN 1 THEN 'Total empleado' END AS [Agrupa clie], 

        numemp, clie, SUM(importe) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie WITH ROLLUP

ORDER BY numemp,clie;

8. Ahora queremos que "Total empleado" aparezca en la columna clie.

SELECT  CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [Agrupa numemp], 

        numemp, 

        CASE GROUPING(clie) WHEN 0 THEN CONVERT(CHAR(4),clie)  WHEN 1 THEN 'Total empleado' END AS [Clie], 

        SUM(importe) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie WITH ROLLUP

ORDER BY numemp,clie;

9. El empleado 104 (y otros) no ha vendido a nadie y por eso sale en la columna clie la palabra NULL, queremos que en estos casos no aparezca nada (se deje en blanco), y el importe si es NULL que salga un cero.

SELECT  CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [Agrupa numemp], 

        numemp, 

        CASE WHEN GROUPING(clie) = 1 THEN 'Total empleado' WHEN clie IS NULL THEN ' ' ELSE CONVERT(CHAR(4),clie) END AS [Clie],

        ISNULL(SUM(importe),0) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie WITH ROLLUP

ORDER BY numemp,clie;

10. Lo rematamos para que la fila del Total final no muestre los valores "NULL" ni "Total empleado". En cambio, los cambiaremos por tres puntos. Tampoco mostraremos el encabezado "Agrupa numemp".

SELECT CASE GROUPING(numemp) WHEN 0 THEN ' ' ELSE 'Total Final' END AS [ ], 

        ISNULL(CONVERT(CHAR(3),numemp),'... ') AS [numemp], 

        CASE WHEN GROUPING(clie) = 1 AND GROUPING(numemp) = 0 

                  THEN 'Total empleado' WHEN GROUPING(clie) = 1 

             AND GROUPING(numemp) = 1 THEN '...' 

             WHEN clie IS NULL THEN ' ' ELSE CONVERT(CHAR(4),clie) END AS [Clie], 

        ISNULL(SUM(importe),0) AS [Importe vendido]

FROM empleados LEFT JOIN pedidos ON numemp = rep

GROUP BY numemp, clie WITH ROLLUP

ORDER BY numemp,clie;

 Cursos Informática Gratuitos