Automatyka Przemysłowa

dr inż. Jakub Kołota

Moduł III - Grupowanie i podsumowywanie danych

Tabele wykorzystywane w przykładach: HumanResources.Employee oraz Person.Address

Zadanie nr 1

 Uruchom wirtualną maszynę 2778A-NY-SQL-01
 Zaloguj się podając login: Administrator , hasło: Pa$$w0rd

Zadanie nr 2

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl średnią VacationHours z aliasem ‘AverageVacationHours’ oraz sumę SickLeaveHours z

aliasem ‘TotalSickLeave Hours ’ z tabeli HumanResources.Employee

 Wyniki ogranicz dla JobTitle zawierającego tekst ‘Vice President’

Zadanie nr 3

 Połącz się z bazą danych AdventureWorks2008
 Zlicz wszystkie wiersze z tabeli HumanResources.Employee używając funkcji COUNT()

Zadanie nr 4

 Połącz się z bazą danych AdventureWorks2008
 Zlicz wszystkie wiersze z tabeli Person.Address używając funkcji COUNT(), dla których kolumna

AddressLine2 ma wartość NULL

 W celu rozwiązania zadania użyj funkcji ISNULL()

Zadanie nr 5

 Połącz się z bazą danych AdventureWorks2008
 Zlicz wszystkie wiersze z tabeli Person.Address dla których kolumna AddressLine2 nie ma

wartości NULL

 W celu rozwiązania zadania użyj tylko funkcji COUNT()
 Po zapoznaniu się z wynikami dodaj klauzulę DISTINCT wewnątrz funkcji COUNT () i wykonaj

zapytanie ponownie odnotowując różnicę

Tabele wykorzystywane w przykładach: Sales.SalesOredrDetail, Sales.SalesPerson,
Production.ProductInventory oraz Production.Product

Zadanie nr 6

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość ProductID, średnią z DaysToManufacture z aliasem

‘AvgDaysToManufacture’ z tabeli Production.Product

Zadanie nr 7

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość Color, średnią z ListPrice z aliasem ‘AvgListPrice’ z tabeli Production.Product
 Wyniki ogranicz do ProductNumber równego ‘FR-R72R-58’
 Po zapoznaniu się z wynikami dodaj klauzulę ALL wewnątrz instrukcji GROUP BY i wykonaj

zapytanie ponownie odnotowując różnicę

Zadanie nr 8

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość ProductID, średnią z OrderQty z aliasem ‘AverageQuantity’, sumę z

LineTotal z aliasem ‘Total’ z tabeli Sales.SalesOrderDetail

 Wyniki pogrupuj względem ProductID i wybierz grupy mające sumę z LineTotal większą niż

$1000000 i średnią z OrderQty mniejszą niż 3

Zadanie nr 9

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość ProductID, Shelf, sumę z Quantity z aliasem ‘QtySum’ z tabeli

Production.ProductInventory

 Wyniki pogrupuj względem ROLLUP( ProductID, Shelf)

Zadanie nr 10

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość SalesOrderID, ProductID, sumę z OrderQty z aliasem ‘SumQuantity’ z tabeli

Sales.SalesOrderDetail

 Wyniki pogrupuj względem CUBE( SalesOrderID, ProductID)
 Wyniki posortuj względem SalesOrderID, ProductID

Zadanie nr 11

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość SalesQuota, sumę z SalesYTD z aliasem ‘TotalSalesYTD’, z tabeli

Sales.SalesPerson

 Wyniki pogrupuj względem CUBE(SalesQuota)
 Po zapoznaniu się z wynikami dodaj kolumnę GROUPING(SalesQuota) z aliasem ‘Grouping’

określając przynależność kolumny SalesQuota do grupy . Wykonaj zapytanie ponownie
odnotowując zasadę działania funkcji GROUPING()

Zadanie nr 12

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość ProductID, Shelf, sumę z Quantity z aliasem ‘TotalQuantity’, przynależność

do grupy kolumny Shelf z aliasem ‘ShelfGrouping’ z tabeli Production.ProductInventory

 Wyniki pogrupuj względem CUBE(ProductID, Shelf)

Zadanie nr 13

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość SalesOrderID, UnitPrice, UnitPriceDiscount z tabeli Sales.SalesOrderDetail
 Wyniki posortuj względem kolumny SalesOrderID
 Dodaj klauzulę podsumowującą COMPUTE liczącą sumę z UnitPrice i sumę z UnitPriceDiscount

Zadanie nr 14

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl numer wiersza funkcją ROW_NUMBER() porządkując ją względem SalesYTD malejąco z

aliasem ‘Row Number’, BusinessEntityID, SalesYTD z tabeli Sales.SalesPerson

 Wyniki ogranicz dla TerritoryID różnych od NULL i SalesYTD różnego od zera
 Podpowiedź : struktura funkcji ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)

Zadanie nr 15

 Połącz się z bazą danych AdventureWorks2008
 Użyj funkcji rankingującej RANK() partycjonując względem LocationID i porządkując ją względem

Quantity malejąco z aliasem ‘Rank’, ProductID, LocationID, Quantity z tabeli
Production.ProductInventory

 Wyniki posortuj względem ‘Rank’
 Podpowiedź : struktura funkcji RANK() OVER (PARTITION BY x ORDER BY y)

Zadanie nr 16

 Połącz się z bazą danych AdventureWorks2008
 Zmodyfikuj rozwiązanie poprzedniego zadania używając funkcji DENSE_RANK() zamiast funkcji

RANK() - wykonaj zapytanie ponownie odnotowując różnicę

Zadanie nr 17

 Połącz się z bazą danych AdventureWorks2008
 Użyj funkcji rankingującej NTILE(4), tworzącej cztery grupy i porządkującej je względem SalesYTD

malejąco z aliasem ‘Quartile’, SalesYTD, BusinessEntityID z tabeli Sales.SalesPerson

 Wyniki ogranicz dla TerritoryID różnych od NULL i SalesYTD różnego od zera
 Podpowiedź : struktura funkcji NTILE(x) OVER (PARTITION BY y ORDER BY z)

Zadanie nr 18

 Połącz się z bazą danych AdventureWorks2008
 Wyświetl zawartość Name, Red, Blue, Black z zapytania

SELECT SafetyStockLevel, Color, Name FROM Production.Product

poddanego operacji PIVOT względem sumy z SefetyStockLevel dla Color w zbiorze (Red, Blue,
Black)

 Wyniki ogranicz dla Name zawierających ‘Helmet’
 Wyniki posortuj względem kolumny Name

Zadanie nr 20

 Połącz się z bazą danych AdventureWorks2008
 Wykonaj poniższy przykład prezentujący operację UNPIVOT

SELECT Name, Attribute, Value FROM Production.Product AS P
UNPIVOT
(Value FOR Attribute IN (StandardCost, ListPrice)) AS UnPVT

Bardziej złożona wersja rozwiązania z rzutowaniem typów:

SELECT Name, Attribute, Value FROM
(

SELECT Name,

CAST (ProductLine as SQL_Variant) ProductLine,
CAST (StandardCost as Sql_variant) StandardCost,
CAST (ListPrice as sql_variant) ListPrice

FROM Production.Product) AS P
UNPIVOT
(Value FOR Attribute IN (ProductLine, StandardCost, ListPrice)) AS UnPVT
Order By Name Desc