1

UNIVERSIDAD DON BOSCO FACULTAD DE ESTUDIOS TECNOLOGICOS

ESCUELA DE COMPUTACION

CICLO 01-2012

GUIA DE LABORATORIO Nº 7

Nombre de la practica: Agrupación y sumarización en SQL Server 2008 Lugar de ejecución: Laboratorio de Informática Tiempo estimado: 3 horas Materia: Base de datos I

I. Objetivos

Que el estudiante sea capaz de:

• Utilizar la palabra clave TOP n para obtener una lista de los primeros n valores especificados de una tabla.

• Generar un único valor de resumen mediante el uso de funciones de agregado.

• Organizar los datos de resumen de una columna mediante el uso de funciones de

agregado con las cláusulas GROUP BY y HAVING.

• Generar datos de resumen de una tabla mediante el uso de funciones de agregado con la cláusula GROUP BY y los operadores ROLLUP o CUBE.

• Generar informes con secciones mediante el uso de cláusulas COMPUTE y COMPUTE

BY.

II. Introducción Teórica

TOP (Transact-SQL) TOP n, especifica que solo se devolverá el primer conjunto de filas del resultado de la consulta. El conjunto de filas puede ser un número o un porcentaje de las filas. La expresión TOP se puede usar en instrucciones SELECT, INSERT, UPDATE, MERGE y DELETE. Aunque la palabra clave TOP n no es un estándar ANSI, resulta útil, por ejemplo, para presentar los productos más vendidos de una compañía. Sintaxis [ TOP (expression) [PERCENT] [ WITH TIES ] ] Argumentos Es la expresión numérica que especifica el número de filas que se devolverán. El valor expression se convierte implícitamente a un valor float si se especificó PERCENT; de lo contrario, se convierte a bigint. TOP (Transact-SQL) TOP n, especifica que solo se devolverá el primer conjunto de filas del resultado de la consulta. El conjunto de filas puede ser un número o un porcentaje de las filas. La expresión TOP se puede usar en instrucciones SELECT, INSERT, UPDATE, MERGE y DELETE. Aunque la palabra clave TOP n no es un estándar ANSI, resulta útil, por ejemplo, para presentar los productos más vendidos de una compañía.

2

Sintaxis [ TOP (expression) [PERCENT] [ WITH TIES ] ] Argumentos Es la expresión numérica que especifica el número de filas que se devolverán. El valor expression se convierte implícitamente a un valor float si se especificó PERCENT; de lo contrario, se convierte a bigint. En instrucciones INSERT, UPDATE, MERGE1y DELETE, se requieren paréntesis que delimiten el valor expression en TOP. Por compatibilidad con versiones anteriores se admite TOP expression sin paréntesis en instrucciones SELECT, pero no se recomienda usar esto. Si la consulta contiene una cláusula ORDER BY, se devolverán las primeras filas de expression o el porcentaje de filas de expression ordenadas por la cláusula ORDER BY. Cuando la consulta no tiene una cláusula ORDER BY, el orden de las filas es arbitrario. PERCENT Indica que la consulta devuelve solo el primer porcentaje de filas de expression del conjunto de resultados. WITH TIES Especifica que las filas adicionales se devolverán del conjunto de resultados base con el mismo valor en las columnas ORDER BY que el que aparece en la última de las filas de TOP n (PERCENT). TOP...WITH TIES solo se puede especificar en instrucciones SELECT y siempre que haya una cláusula ORDER BY especificada. TOP no se puede utilizar conjuntamente con instrucciones UPDATE y DELETE en vistas con particiones. Las filas a las que se hacen referencia en la expresión TOP utilizadas con INSERT, UPDATE, MERGE o DELETE no presentan ningún orden. TOP n devuelve n filas aleatorias. Por ejemplo, la siguiente instrucción de INSERT contiene la cláusula ORDER BY y aún así esta cláusula no afecta a las filas a las que se hace referencia directamente en la instrucción INSERT. INSERT TOP (2) INTO Table2 (ColumnB) SELECT ColumnA FROM Table1 ORDER BY ColumnA; La cláusula ORDER BY de la consulta anterior solo hace referencia a las filas devueltas por la instrucción SELECT anidada. La instrucción INSERT elige cualquiera de las dos filas devueltas por la instrucción SELECT. Para garantizar que se hayan insertado las dos filas superiores de la subconsulta SELECT, vuelva a escribir la consulta de la manera siguiente. INSERT INTO Table2 (ColumnB) SELECT TOP (2) ColumnA FROM Table1 ORDER BY ColumnA; SQL Server permite la actualización de vistas creadas con la cláusula TOP. Dado que la cláusula TOP se incluye en la definición de vista, es posible que algunas filas puedan desaparecer de la vista a causa de una actualización, si el resultado ya no cumple con los requisitos de la expresión TOP. La expresión TOP de una consulta no afecta a las instrucciones que se pueden ejecutar a partir de la activación de un desencadenador. Las tablas insertadas y actualizadas en los desencadenadores solo mostrarán las filas verdaderamente afectadas por las instrucciones INSERT, UPDATE, MERGE o DELETE. Cuando utilice la palabra clave TOP n o TOP n PERCENT, considere los hechos e instrucciones siguientes: En resumen:

• Especifique el intervalo de valores en la cláusula ORDER BY. Si no utiliza una cláusula ORDER BY, Microsoft® SQL Server devuelve las filas que cumplen la cláusula WHERE sin ningún orden concreto.

• Utilice un entero sin signo a continuación de la palabra clave TOP.

• Si la palabra clave TOP n PERCENT produce un número no entero de filas, SQL Server

redondea la cantidad no entera al siguiente valor entero.

• Utilice la cláusula WITH TIES para incluir las filas iguales en el conjunto de resultados. Las filas iguales se producen cuando hay dos o más filas con valores iguales a los de la última fila devuelta según la cláusula ORDER BY. Por lo tanto, el conjunto de resultados puede incluir cualquier número de filas.

3

Funciones de agregado Las funciones de agregado realizan un cálculo sobre un conjunto de valores y devuelven un solo valor. Si exceptuamos la función COUNT, todas las funciones de agregado ignoran los valores NULL. Las funciones de agregado se suelen utilizar con la cláusula GROUP BY de la instrucción SELECT. Todas las funciones de agregado son deterministas. Esto significa que las funciones de agregado devuelven el mismo valor cada vez que se las llama con un conjunto específico de valores de entrada. Para obtener más información acerca del determinismo de las funciones, vea Funciones deterministas y no deterministas. La cláusula OVER puede seguir a todas las funciones de agregado, excepto CHECKSUM. Las funciones de agregado sólo se pueden utilizar como expresiones en:

• La lista de selección de una instrucción SELECT (en una subconsulta o en la consulta externa).

• Cláusulas COMPUTE o COMPUTE BY.

• Cláusulas HAVING.

Transact-SQL proporciona las siguientes funciones de agregado:

Las funciones que calculan promedios y sumas se llaman funciones de agregado. Cuando se ejecuta una función de agregado, SQL Server resume los valores de toda una tabla o de grupos de columnas de una tabla, y produce un valor por cada conjunto de filas para las columnas especificadas:

• Las funciones de agregado se pueden utilizar en la instrucción SELECT o en combinación con la cláusula GROUP BY.

• Con la excepción de la función COUNT(*), todas las funciones de agregado devuelven

NULL si ninguna fila cumple la cláusula WHERE. La función COUNT(*) devuelve el valor cero si ninguna fila cumple la cláusula WHERE.

El tipo de datos de una columna determina las funciones que se pueden utilizar con ella. La tabla siguiente describe las relaciones entre las funciones y los tipos de datos.

4

Uso de la cláusula GROUP BY Utilice la cláusula GROUP BY en columnas o expresiones para organizar filas en grupos y para resumir dichos grupos. Por ejemplo, utilice la cláusula GROUP BY para determinar la cantidad de cada producto pedida en todos los pedidos. Cuando utilice la cláusula GROUP BY, considere los hechos e instrucciones siguientes:

• SQL Server produce una columna de valores por cada grupo definido.

• SQL Server sólo devuelve filas por cada grupo especificado; no devuelve información de detalle.

• Todas las columnas que se especifican en la cláusula GROUP BY tienen que estar

incluidas en la lista de selección.

• Si incluye una cláusula WHERE, SQL Server sólo agrupa las filas que cumplen las condiciones de la cláusula WHERE.

• En la lista de columnas de la cláusula GROUP BY puede haber hasta 8.060 bytes.

• No utilice la cláusula GROUP BY en columnas que contengan varios valores nulos, porque los valores nulos se procesan como otro grupo.

• Utilice la palabra clave ALL con la cláusula GROUP BY para presentar todas las filas que

tengan valores nulos en las columnas de agregado, independientemente de si las filas cumplen la condición de la cláusula WHERE.

5

Ejemplo:

Uso de la cláusula GROUP BY con la cláusula HAVING Utilice la cláusula HAVING en columnas o expresiones para establecer condiciones en los grupos incluidos en un conjunto de resultados. La cláusula HAVING establece condiciones en la cláusula GROUP BY de una forma muy similar a como interactúa la cláusula WHERE con la instrucción SELECT. Cuando utilice la cláusula HAVING, considere los hechos e instrucciones siguientes:

• Utilice la cláusula HAVING sólo con la cláusula GROUP BY para restringir los agrupamientos. El uso de la cláusula HAVING sin la cláusula GROUP BY no tiene sentido.

• En una cláusula HAVING puede haber hasta 128 condiciones. Cuando utilice varias

condiciones, tiene que combinarlas con operadores lógicos (AND, OR o NOT).

• Puede hacer referencia a cualquiera de las columnas que aparezcan en la lista de selección.

• No utilice la palabra clave ALL con la cláusula HAVING, porque la cláusula HAVING pasa

por alto la palabra clave ALL y sólo devuelve los grupos que cumplen la cláusula HAVING.

6

Generación de valores de agregado dentro de los conjuntos de resultados. Utilice la cláusula GROUP BY con los operadores ROLLUP y CUBE para generar valores de agregado dentro de los conjuntos de resultados. Los operadores ROLLUP o CUBE pueden ser útiles para obtener información de referencias cruzadas dentro de una tabla sin tener que escribir secuencias de comandos adicionales. Cuando utilice los operadores ROLLUP o CUBE, use la función GROUPING para identificar los valores de detalle y de resumen dentro del conjunto de resultados. Uso de la cláusula GROUP BY con el operador ROLLUP

Utilice la cláusula GROUP BY con el operador ROLLUP para resumir valores de grupos. La cláusula GROUP BY y el operador ROLLUP proporcionan datos en un formato relacional estándar. Cuando utilice la cláusula GROUP BY con el operador ROLLUP, considere los hechos e instrucciones siguientes:

• SQL Server procesa los datos de derecha a izquierda en la lista de columnas especificadas en la cláusula GROUP BY. Después, SQL Server aplica la función de agregado a cada grupo.

• SQL Server agrega al conjunto de resultados una fila que presenta los cálculos

acumulados, como un total o un promedio acumulado. Dichos cálculos acumulados se indican en el conjunto de resultados con un valor NULL.

• Cuando utiliza el operador ROLLUP puede tener hasta 10 expresiones de agrupación.

• Con el operador ROLLUP no se puede utilizar la palabra clave ALL.

• Cuando utilice el operador ROLLUP, asegúrese de que las columnas que siguen a la

cláusula GROUP BY tienen una relación significativa en su entorno de trabajo.

7

Uso de la cláusula GROUP BY con el operador CUBE

Utilice la cláusula GROUP BY con el operador CUBE para crear y resumir todas las combinaciones posibles de los grupos en función de la cláusula GROUP BY. Utilice la cláusula GROUP BY con el operador ROLLUP para proporcionar datos en un formato relacional estándar. Cuando utilice la cláusula GROUP BY con el operador CUBE, considere los hechos e instrucciones siguientes:

• Si tiene n columnas o expresiones en la cláusula GROUP BY, SQL Server devuelve las 2n-1 combinaciones posibles en el conjunto de resultados.

• Los valores NULL del conjunto de resultados indican que dichas filas concretas son el

resultado del operador CUBE.

• Cuando utilice el operador CUBE, puede incluir hasta 10 expresiones de agrupamiento.

• Con el operador CUBE no se puede utilizar la palabra clave ALL.

• Cuando utilice el operador CUBE, asegúrese de que las columnas que siguen a la cláusula GROUP BY tienen una relación significativa en su entorno de trabajo.

8

Uso de las cláusulas COMPUTE y COMPUTE BY

Las cláusulas COMPUTE y COMPUTE BY generan filas de resumen adicionales en un formato no relacional que no se adaptan al estándar ANSI. Aunque presentan datos útiles, su salida no está adaptada para generar conjuntos de resultados que se vayan a utilizar en otras aplicaciones. Por ejemplo, puede que le interese utilizar COMPUTE y COMPUTE BY para presentar informes básicos y rápidos, o para comprobar los resultados de las aplicaciones que esté desarrollando. Sin embargo, otras herramientas, como Crystal Reports o Microsoft Access, ofrecen características más completas para la generación de informes. Si utiliza las cláusulas COMPUTE y COMPUTE BY, considere los hechos siguientes:

• En las cláusulas COMPUTE o COMPUTE BY no se pueden incluir tipos de datos text, ntext o image.

• No se puede ajustar el formato del conjunto de resultados. Por ejemplo, si utiliza la función

de agregado SUM, SQL Server presenta la palabra sum en el conjunto de resultados. No se puede cambiar para que presente resumen.

Generación de informes con valores de detalle y de resumen para una columna La cláusula COMPUTE produce filas de detalle y un solo valor de agregado para una columna. Cuando utilice la cláusula COMPUTE, considere los hechos y directrices siguientes:

• En una misma instrucción se pueden utilizar varias cláusulas COMPUTE con la cláusula COMPUTE BY.

• SQL Server requiere que se especifiquen las mismas columnas en la lista de selección que

en la cláusula COMPUTE.

• No utilice la instrucción SELECT INTO en la misma instrucción que la cláusula COMPUTE, puesto que las instrucciones que incluyen COMPUTE no generan una salida relacional.

Generación de informes con valores de detalle y de resumen para subconjuntos de grupos La cláusula COMPUTE BY genera filas de detalle y varios valores de resumen. Los valores de resumen se generan cuando cambian los valores de la columna. Utilice la cláusula COMPUTE BY con datos que se puedan clasificar con facilidad. Cuando utilice la cláusula COMPUTE BY, considere los hechos e instrucciones siguientes:

• Para que las filas estén agrupadas, con la cláusula COMPUTE BY tiene que utilizar una cláusula ORDER BY.

9

• Especifique los nombres de las columnas después de la cláusula COMPUTE BY para

determinar qué valores de resumen son generados por SQL Server.

• Las columnas que aparezcan después de la cláusula COMPUTE BY tienen que ser idénticas a un subconjunto de las columnas que aparezcan después de la cláusula ORDER BY. Tienen que estar en el mismo orden (de izquierda a derecha), comenzar con la misma expresión y no saltar ninguna de las expresiones.

III. Requerimientos

• Microsoft SQL Server 2008 R2 • Guía Número 7

IV. Procedimiento

Ejercicio 1. Obtener los primeros n valores de un conjunto de resultados Ejercicio 1.A.

1. Hacer uso de la base de datos Northwind

2. Utilizar la palabra clave TOP n para buscar los cinco productos más vendidos en un mismo pedido. Los valores iguales siguientes quedan excluidos del conjunto de resultados. Digitar la siguiente consulta:

Ejercicio 1.B.

3. Utiliza la palabra clave TOP n y la cláusula WITH TIES para buscar los cinco productos más vendidos dentro de un mismo pedido.

WITH TIES Especifica que las filas adicionales se devolverán del conjunto de resultados base con el mismo valor en las columnas ORDER BY que el que aparece en la última de las filas de TOP n , solo se puede especificar en instrucciones SELECT y siempre que haya una cláusula ORDER BY especificada.

10

El conjunto de resultados presenta un total de 10 productos, ya que se incluyen las filas adicionales que tienen los mismos valores que la última fila incluida. Compare el conjunto de resultados siguiente con el del Ejercicio 1.A. Ejercicio 1.C.

4. En el ejemplo siguiente se obtiene el 10% de los pedidos que tienen las ventas más altas y los devuelve en orden descendente de acuerdo a las cantidades vendidas.

PERCENT Indica que la consulta devuelve solo el primer porcentaje de filas del conjunto de resultados.

5. Se obtiene un total de 216 filas. Ejercicio 1.D. Usar TOP con variables

6. En el ejemplo siguiente se utiliza una variable para obtener los 4 primeros empleados enumerados en la tabla Employees

Ejercicio 2. Uso de funciones de agregado

1. Haciendo uso siempre de la base de datos Northwind Ejercicio 2.A. Función AVG

2. Función AVG, devuelve el promedio de los valores de un grupo. Los valores NULL se pasan por alto.

3. En este ejemplo se calcula el precio promedio por unidad de todos los productos de la

tabla products.

Ejercicio 2.B. Función SUM

4. Función SUM, devuelve la suma de todos los valores o sólo de los valores DISTINCT 2 de la expresión. SUM sólo puede utilizarse con columnas numéricas. Los valores Null se pasan por alto.

11

5. En este ejemplo se suman todas las filas de la columna quantity de la tabla order details.

Ejercicio 2.C. Función COUNT

6. Función COUNT, devuelve el número de elementos de un grupo. COUNT siempre devuelve un valor de tipo de datos int.

7. En este ejemplo se presenta el número de empleados de la tabla Employees.

8. Digite la siguiente consulta:

9. Y observara los siguientes resultados:

10. Este ejemplo presenta el número de empleados de la tabla Employees que no tienen un valor nulo en la columna Region.

Ejercicio 2.D. Función MAX y MIN

11. La función MAX, devuelve el valor máximo de la expresión. Y la función MIN, devuelve el valor mínimo de la expresión.

Como observara los resultados muestran 4 resultados nulos en el campo Region.

12

12. En el siguiente ejemplo, se muestra como obtener el precio mayor de un producto

13. En el siguiente ejemplo, se muestra como obtener el precio menor de un producto

Ejercicio 3. Uso del GROUP BY Ejercicio 3.A

1. En este ejemplo se devuelve información acerca de los pedidos de la tabla order details. La consulta agrupa y presenta cada identificador de producto y calcula la cantidad total pedida. La cantidad total se calcula con la función de agregado SUM y presenta un valor para cada producto del conjunto de resultados, y al final se ordena la información con el ORDER BY.

Ejercicio 3.B

2. Este ejemplo agrega una cláusula WHERE a la consulta del Ejercicio 3A. Esta consulta restringe las filas al producto cuyo identificador esta dentro del rango 10 y 25 y, después, agrupa dichas filas y calcula la cantidad total pedida. Compare este conjunto de resultados con el del Ejercicio 3A.

Ejercicio 3.C

3. Este ejemplo muestra la agrupación de 2 campos que se encuentran en la tabla Customers

13

Ejercicio 3.D. Uso de la clausula HAVING

4. En este ejemplo se presentan todos los grupos de productos de la tabla order details que tienen pedidos de 30 unidades o más y las filas se ordenan en forma ascendente según la suma de las cantidades.

Ejercicio 3.E. Uso de la clausula ROLLUP

5. En este ejemplo se presentan todas las filas de la tabla Order details y los valores de cantidades de resumen de cada producto.

ROLLUP Genera filas de agregado mediante la cláusula GROUP BY simple, más filas de subtotal o de superagregado, así como una fila de total general.

6. En este ejemplo se devuelve información acerca de los pedidos de la tabla order details. Esta consulta contiene una instrucción SELECT con una cláusula GROUP BY sin el operador ROLLUP. El ejemplo devuelve la lista de las cantidades totales de cada producto solicitadas en cada pedido, para los pedidos cuyo orderid sea menor que 10250.

7. En este ejemplo se agrega el operador ROLLUP a la instrucción del ejemplo anterior. El conjunto de resultados incluye la cantidad total para:

a. Cada producto en cada pedido (también devuelto por la cláusula GROUP BY sin el operador ROLLUP).

b. Todos los productos de cada pedido.

c. Todos los productos de todos los pedidos (total final).

14

Observe en el conjunto de resultados que la fila que contiene NULL en las columnas productid y orderid representa la cantidad total final de todos los pedidos para todos los productos. Las filas que contienen NULL en la columna productid representan la cantidad total de un producto en el pedido de la columna orderid. Ejercicio 3.F. Uso de la clausula CUBE

8. En este ejemplo se devuelve un resultado que proporciona la cantidad de cada producto en cada pedido, la cantidad total de todos los productos de cada pedido, la cantidad total de cada producto en todos los pedidos y la cantidad total final de todos los productos en todos los pedidos.

Ejercicio 3.G. Uso de la clausula GROUPING SETS Una cláusula GROUP BY que utiliza GROUPING SETS puede generar un conjunto de resultados equivalente al generado por una cláusula UNION ALL3

9. El siguiente ejemplo muestra el total de proveedores por País y Ciudad

10. Ahora digite la siguiente consulta y compare con los resultados de la anterior

15

Ejercicio 4. Uso de las clausulas COMPUTE Y COMPUTE BY

1. En este ejemplo se presentan todas las filas de la tabla order details y se genera el total final de todos los productos pedidos.

2. Observara, que cuando se ejecuta la consulta en los resultados se despliega otro al final donde el encabezado es sum

3. En este ejemplo se presentan todas las filas de la tabla order details, se genera el total pedido para cada producto y el total final de todos los productos pedidos.

V. Ejercicio complementario

Haciendo uso de la base de datos Northwind realice las siguientes consultas:

1. Seleccionar los datos de la tabla Customers y ordene los datos por el campo

CompanyName en orden ascendente.

2. Mostrar cuantos Clientes hay por cada Compañía (CompanyName)

16

3. Seleccionar los datos ProductName y UnitPrice de la tabla Products donde el UnitPrice se

encuentre entre los datos 25 y 150

4. Seleccionar los nombres de la compañía (CompanyName) de los proveedores (Suppliers)

que comiencen con la letra “L”

5. Seleccionar los empleados (Employees) donde el titulo (Title) contenga la palabra “Sales”

6. Seleccionar los datos ProductName y UnitPrice de la tabla Products donde el UnitPrice sea

menor que 75

7. Seleccionar los campos: OrderID, CustomerID, EmployeeID , OrderDate y ShippedDate de

la tabla Orders, donde la fecha del pedido (OrderDate) sea del año 1996.

Implemente la función YEAR

8. Mostrar los datos de la tabla EmployeeTerritories y ordenarlos por el campo TerritoryID en

forma descendente

9. Se desea conocer cuántos empleados hay por cada Territorio, utilice la tabla

EmployeeTerritories

10. Mostrar los datos de la tabla Territories

11. Se desea conocer cuántos territorios hay por cada región (RegionID), utilice la tabla

Territories

12. Mostrar de la tabla Order Details aquellos pedidos en donde las unidades sumen más de

50 y ordenar los datos en forma descendente según la suma de estas cantidades.

Haciendo uso de la base de datos AdventureWorks realice las siguientes consultas: Utilizando la tabla Sales.SalesOrderDetail realice las siguientes consultas:

1. Mostrar la suma de las unidades vendidas (OrderQty) por cada orden (SalesOrderID)

2. Mostrar el promedio de ventas (LineTotal) por cada orden (SalesOrderID)

3. Mostrar la venta (LineTotal) máxima por cada orden (SalesOrderID)

4. Mostrar la venta (LineTotal) mínima por cada orden (SalesOrderID)

Utilizando la tabla Sales.Store realice las siguientes consultas:

5. Mostrar los datos de la tabla y ordenarlos por el campo SalesPersonID

6. Mostrar la cantidad de tiendas (CustomerID) que atendió cada vendedor (SalesPersonID)

Haciendo uso de la base de datos Library realice las siguientes consultas:

17

1. Mostrar cuantas reservaciones (isbn) han realizado cada miembro (member_no).

Utilizando la tabla reservation

2. Mostrar la cantidad de títulos de libros que se han traducido al ARABIC (translation),

utilizando la tabla item

VI. Análisis de resultados

1. Crear la siguiente base de datos:

18

Deberá crear todas las restricciones que cree necesarias para cada una de las tablas. Agregar la siguiente información a las tablas:

19

A continuación deberá realizar diferentes consultas con distintos niveles de dificultad, utilizando en algunas de ellas diferentes funciones asociadas con la sentencia SELECT:

1) Se desea un listado de todos los alumnos

2) Se desea conocer cuántos estudiantes hay por cada género;

3) Se desea conocer la nota promedio del alumno con carnet “mb960240” del grupo 01 de Base de Datos (BDD1).

4) Se desea conocer la nota mayor de las 3 evaluaciones para cada alumno

2. Investigar sobre: CHECKSUM Colocar ejemplos