sql aulaclic

SQL Aulaclic GROUP BY.

Alejandro Alonso Taratiel. 1º ASIR.

Base de datos. Prof.: Santiago Blanco.

.

Página - 1 - de 18

ÍNDICE

Ejercicios SQL. ............................................................................................................. - 2 -

Ejercicio_1 ................................................................................................................ - 2 -

Ejercicio_2 ................................................................................................................ - 3 -

Ejercicio_3 ................................................................................................................ - 4 -

Ejercicio_4 ................................................................................................................ - 5 -

Ejercicio_5 ................................................................................................................ - 7 -

Ejercicio_6 ................................................................................................................ - 8 -

Ejercicio_7 .............................................................................................................. - 10 -

Ejercicio_8 .............................................................................................................. - 12 -

Ejercicio_9 .............................................................................................................. - 14 -

Ejercicio_10 ............................................................................................................ - 16 -

Bases de Datos. Alejandro Alonso Taratiel 26/05/2012

Página - 2 - de 18

Ejercicios SQL.

Ejercicio_1 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 ().

Comandos:

SELECT numemp, nombre, ISNULL (SUM (importe), 0) AS [Importe total vendido] FROM empleados LEFT JOIN pedidos ON numemp = rep GROUP BY numemp, nombre

Resultado:

101 Antonio Viguer 266,28 102 Alvaro Jaumes 77,76 103 Juan Rovira 21,00 104 José González 0,00 105 Vicente Pantalla 772,746 106 Luis Antonio 46,08 107 Jorge Gutiérrez 344,32 108 Ana Bustamante 286,33 109 María Sunta 71,05 110 Juan Victor 478,82 111 Juan Gris 0,00 112 Julián Martorell 0,00 113 Juan Gris 0,00 114 Pablo Moreno 0,00

Página - 3 - de 18

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

Comandos:

SELECT rep, clie AS Cliente, SUM (importe) AS [Importe vendido] FROM pedidos GROUP BY rep, clie

Resultado:

106 2101 14,58 101 2102 39,78 105 2103 735,296 102 2106 40,26 110 2107 28,82 101 2108 1,50 109 2108 71,05 107 2109 313,50 103 2111 21,00 105 2111 37,45 108 2112 29,25 110 2112 450,00 101 2113 225,00 108 2114 221,00 106 2117 31,50 108 2118 36,08 102 2120 37,50 107 2124 30,82


Página - 4 - de 18

Ejercicio_3 3. Repetir la consulta anterior pero ahora deben aparecer también los

empleados que no han vendido nada.

Comandos:

SELECT numemp, clie, SUM (importe) AS [Importe vendido] FROM empleados LEFT JOIN pedidos ON numemp = rep GROUP BY numemp, clie ORDER BY numemp, clie

Resultado:

101 2102 39,78 101 2108 1,50 101 2113 225,00 102 2106 40,26 102 2120 37,50 103 2111 21,00 104 NULL NULL 105 2103 735,296 105 2111 37,45 106 2101 14,58 106 2117 31,50 107 2109 313,50 107 2124 30,82 108 2112 29,25 108 2114 221,00 108 2118 36,08 109 2108 71,05 110 2107 28,82 110 2112 450,00 111 NULL NULL 112 NULL NULL 113 NULL NULL 114 NULL NULL

Página - 5 - de 18

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

vendido cada empleado.

Comandos:

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

Resultado:

NULL NULL 2364,386 101 NULL 266,28 101 2102 39,78 101 2108 1,50 101 2113 225,00 102 NULL 77,76 102 2106 40,26 102 2120 37,50 103 NULL 21,00 103 2111 21,00 104 NULL NULL 104 NULL NULL 105 NULL 772,746 105 2103 735,296 105 2111 37,45 106 NULL 46,08 106 2101 14,58 106 2117 31,50 107 NULL 344,32 107 2109 313,50 107 2124 30,82 108 NULL 286,33 108 2112 29,25 108 2114 221,00 108 2118 36,08 109 NULL 71,05 109 2108 71,05 110 NULL 478,82 110 2107 28,82 110 2112 450,00 111 NULL NULL 111 NULL NULL 112 NULL NULL 112 NULL NULL 113 NULL NULL 113 NULL NULL


Página - 6 - de 18

114 NULL NULL 114 NULL NULL

Página - 7 - de 18

Ejercicio_5 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.

Comandos:

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

Resultado:


Página - 8 - de 18

Ejercicio_6 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.

Comandos:

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

Resultado:

NULL NULL 2364,386 Total empleado Total Final 101 NULL 266,28 Total empleado 101 2102 39,78 101 2108 1,50 101 2113 225,00 102 NULL 77,76 Total empleado 102 2106 40,26 102 2120 37,50 103 NULL 21,00 Total empleado 103 2111 21,00 104 NULL NULL 104 NULL NULL Total empleado 105 NULL 772,746 Total empleado 105 2103 735,296 105 2111 37,45 106 NULL 46,08 Total empleado 106 2101 14,58 106 2117 31,50 107 NULL 344,32 Total empleado 107 2109 313,50 107 2124 30,82 108 NULL 286,33 Total empleado 108 2112 29,25 108 2114 221,00 108 2118 36,08 109 NULL 71,05 Total empleado 109 2108 71,05 110 NULL 478,82 Total empleado 110 2107 28,82 110 2112 450,00 111 NULL NULL

Página - 9 - de 18

111 NULL NULL Total empleado 112 NULL NULL 112 NULL NULL Total empleado 113 NULL NULL 113 NULL NULL Total empleado 114 NULL NULL 114 NULL NULL Total empleado


Página - 10 - de 18

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

Comandos:

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

Resultado:

Total Final Total empleado NULL NULL 2364,386 Total empleado 101 NULL 266,28 101 2102 39,78 101 2108 1,50 101 2113 225,00 Total empleado 102 NULL 77,76 102 2106 40,26 102 2120 37,50 Total empleado 103 NULL 21,00 103 2111 21,00 104 NULL NULL Total empleado 104 NULL NULL Total empleado 105 NULL 772,746 105 2103 735,296 105 2111 37,45 Total empleado 106 NULL 46,08 106 2101 14,58 106 2117 31,50 Total empleado 107 NULL 344,32 107 2109 313,50 107 2124 30,82 Total empleado 108 NULL 286,33 108 2112 29,25 108 2114 221,00 108 2118 36,08 Total empleado 109 NULL 71,05 109 2108 71,05 Total empleado 110 NULL 478,82 110 2107 28,82 110 2112 450,00 111 NULL NULL Total empleado 111 NULL NULL 112 NULL NULL


Total empleado 112 NULL NULL 113 NULL NULL Total empleado 113 NULL NULL 114 NULL NULL Total empleado 114 NULL NULL



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

Comandos:


'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

Resultado:

Total Final NULL Total empleado 2364,386

101 2102 39,78 101 2108 1,50 101 2113 225,00 101 Total empleado 266,28 102 2106 40,26 102 2120 37,50 102 Total empleado 77,76 103 2111 21,00 103 Total empleado 21,00 104 NULL NULL 104 Total empleado NULL 105 2103 735,296 105 2111 37,45 105 Total empleado 772,746 106 2101 14,58 106 2117 31,50 106 Total empleado 46,08 107 2109 313,50 107 2124 30,82 107 Total empleado 344,32 108 2112 29,25 108 2114 221,00 108 2118 36,08 108 Total empleado 286,33 109 2108 71,05 109 Total empleado 71,05 110 2107 28,82 110 2112 450,00 110 Total empleado 478,82 111 NULL NULL 111 Total empleado NULL 112 NULL NULL


112 Total empleado NULL 113 NULL NULL 113 Total empleado NULL 114 NULL NULL 114 Total empleado NULL



Ejercicio_9 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.

Comandos:


'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

Resultado:

Total Final NULL Total empleado 2364,386 101 2102 39,78 101 2108 1,50 101 2113 225,00 101 Total empleado 266,28 102 2106 40,26 102 2120 37,50 102 Total empleado 77,76 103 2111 21,00 103 Total empleado 21,00 104 0,00 104 Total empleado 0,00 105 2103 735,296 105 2111 37,45 105 Total empleado 772,746 106 2101 14,58 106 2117 31,50 106 Total empleado 46,08 107 2109 313,50 107 2124 30,82 107 Total empleado 344,32 108 2112 29,25 108 2114 221,00 108 2118 36,08 108 Total empleado 286,33 109 2108 71,05 109 Total empleado 71,05 110 2107 28,82 110 2112 450,00 110 Total empleado 478,82


111 0,00 111 Total empleado 0,00 112 0,00 112 Total empleado 0,00 113 0,00 113 Total empleado 0,00 114 0,00 114 Total empleado 0,00



Ejercicio_10 10. Lo rematamos para que la fila del Total final no muestre los valores

"NULL" ni "Total empleado". En cambio, los cambiare mos por tres puntos. Tampoco mostraremos el encabezado "Agrupa numemp".

Comandos:


'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

Resultado:

Total Final ... ... 2364,386

101 2102 39,78 101 2108 1,50 101 2113 225,00 101 Total empleado 266,28 102 2106 40,26 102 2120 37,50 102 Total empleado 77,76 103 2111 21,00 103 Total empleado 21,00 104 0,00 104 Total empleado 0,00 105 2103 735,296 105 2111 37,45 105 Total empleado 772,746 106 2101 14,58 106 2117 31,50 106 Total empleado 46,08 107 2109 313,50 107 2124 30,82 107 Total empleado 344,32 108 2112 29,25 108 2114 221,00 108 2118 36,08 108 Total empleado 286,33 109 2108 71,05


109 Total empleado 71,05 110 2107 28,82 110 2112 450,00 110 Total empleado 478,82 111 0,00 111 Total empleado 0,00 112 0,00 112 Total empleado 0,00 113 0,00 113 Total empleado 0,00 114 0,00 114 Total empleado 0,00

sql aulaclic

Documents

alejandro alonso taratiel

bases de datos

ejercicios sql

total empleado

total final

rollup order

importe vendido

case grouping