Ćwiczenia z sql-a, cz3 Grupowanie I podsumowywanie. Funkcje agregujące



Pobieranie 19.98 Kb.
Data03.05.2016
Rozmiar19.98 Kb.
Ćwiczenia z SQL-a, cz3
Grupowanie i podsumowywanie. Funkcje agregujące

Zadanie 1


W T-SQL można łatwo ograniczyć liczbę wierszy w wyniku. Służy do tego polecenie TOP. Można ograniczyć wynik podając dokładną liczbę wierszy lub procent (w stosunku do tego co by zapytanie zwróciło gdyby nie było polecenia TOP)

Wykonaj i przeanalizuj poniższe przykłady.

a)

SELECT orderid, productid, quantity



FROM [order details]

ORDER BY quantity DESC

b) Teraz pięć pierwszych: SELECT TOP 5 ...

c) Teraz 25% procent: SELECT TOP 25 PERCENT ...

d) Teraz z wiązaniem tych wierszy, które mają taką samą wartość jak wartość pola ostatniego wiersza: SELECT TOP 5 WITH TIES ...


Funkcje, które obliczają pewne wartości z grupy danych nazywamy funkcjami agregującymi (czasami podsumowującymi). Przykładami mogą być średnia (arytmetyczna), suma czy odchylenie standardowe.

Najprostszy przypadek grupy wierszy (rekordów) to cała tabela. Można również tworzyć mniejsze grupy używając w zapytaniu odpowiedniej klauzuli GROUP.



Zadanie 2


Obliczyć średnią wartość ceny jednostkowej (unit price) z wszystkich produktów (tabela products). Należy użyć funkcji agregującej AVG (od ang. avarage – średnia).
a)

SELECT AVG(unitprice)

FROM products

b) To samo, ale z nazwaniem kolumny

SELECT AVG(unitprice) AS ‘Średnia’

FROM products



Zadanie 3


Aby policzyć ile jest wierszy w grupie można użyć funkcji COUNT. Ponieważ w tym przypadku nie jest istotne, które pole bierzemy pod uwagę, więc najlepiej użyć konstrukcji COUNT(*). Gdy używamy konstrukcji COUNT(kolumna), to nie są zliczane te wiersze, które posiadają wartość NULL w tej kolumnie!.

Ile jest zamówień w tabeli Zamówienia (Orders)?

SELECT COUNT(*)

FROM Orders

Zadanie 4


Należy uważać z konstrukcją COUNT(pole). Nie zawsze zliczy ona ile jest wierszy w zestawieniu. Dzieje się tak dlatego, że funkcja COUNT nie zlicza tych pól, które zwierają wartość NULL. Wykonaj poniższe zapytania analizując dokładnie wyniki
SELECT lastname, reportsto

FROM employees


SELECT lastname, reportsto

FROM employees

WHERE reportsto IS NULL

SELECT COUNT(*) FROM employees




SELECT COUNT(reportsto) FROM employees

Instrukcja GROUP BY

Jest to bardzo ważna instrukcja i należy ją dobrze opanować. Generalnie umożliwia ona grupowanie danych według różnych kryteriów. Na utworzonych grupach można wykonywać funkcje agregujące. Np. pracownicy w firmie są przydzieleni do różnych działów. Powstaje naturalne pytanie: jaka jest średnia płaca w poszczególnych działach. Zadanie takie można łatwo rozwiązać stosując właśnie instrukcję GROUP BY.

Zadanie 5


Dokonać podsumowania, które obliczy jaka jest całkowita liczba zamówień dla poszczególnych produktów. Aby na razie nie komplikować zadania, interesuje nas tylko numer produktu (productid). Wszystkie informacje znajdują się oczywiście w tabeli Szczegóły sprzedaży (Order details).
a)

SELECT productid, SUM( quantity)

FROM [Order details]

GROUP BY productid


b) Dodaj własną nazwę, np. ‘Całkowita Liczba’, dla pola (kolumny), która jest wyliczana w podpunkcie a).

Zadanie 6


Czasami musimy stosować grupowanie i złączenie w tym samym zapytaniu.

Policzyć ile było wszystkich zamówień w poszczególnych kategoriach towarów. Należy wykorzystać pole categoryid z tabeli Products i quantity z Order details (baza Northwind).


SELECT categoryid, SUM(quantity)

FROM [order details] AS OD INNER JOIN Products AS P

ON OD.productid = P.productid

GROUP BY categoryid




Kolejnym ważnym elementem związanym z grupowaniem jest instrukcja HAVING. Jest ona podobna do instrukcji WHERE w tym, że służy do definiowania warunków. Ale można jej używać tylko z instrukcją GROUP. Warunek zdefiniowany w HAVING jest stosowany po utworzeniu grup. Jeżeli występuje Warunek zdefiniowany w WHERE to jest on stosowany przed utworzeniem grup.

Zadanie 7


Interesują nas teraz tylko te produkty, których ogólna sprzedaż jest np. większa od 30. Oto zapytanie, które wykona odpowiednią selekcję
SELECT productid, SUM(quantity)

FROM [Order details]

GROUP BY productid

HAVING SUM(quantity) > 1000




Podzapytania (subqueries)

Podzapytanie jest to instrukcja SELECT, która jest zagnieżdżona wewnątrz innej instrukcji SELECT. W języku T-SQL występuje kilka wariantów podzapytań.



Zadnie 8


Chcemy wyświetlić nazwy tylko tych towarów z tabeli Products (baza Northwind), których cena jednostkowa jest mniejsza od średniej ceny towaru. Zauważmy, że zapytanie

SELECT AVG(UnitPrice) FROM Products

zwraca średnią cenę jednostkową produktów z tabeli Products. Jeżeli zapytanie zwraca pojedynczą wartość możemy go użyć w głównym zapytaniu. W tym przypadku podzapytanie znajdzie się w klauzuli WHERE głównego zapytania



SELECT ProductName


FROM Products

WHERE UnitPrice < (SELECT AVG(UnitPrice) FROM Products)


Zauważmy, że wewnętrzne zapytanie musi być otoczone nawiasami ().

Zadanie 9


Wyświetlić wszystkie produkty z tabeli Products, których cena jest większa od jednej czwartej ceny najdroższego produktu.
SELECT * FROM Products

WHERE UnitPrice > 0.25*(SELECT MAX(UnitPrice) FROM Products)




Zadanie 10


W T-SQL’u można użyć podzapytania praktycznie w każdym wyrażeniu. W takiej jednak sytuacji na ogół podzapytanie musi być takie, aby zwracało pojedynczą wartość. Powiedzmy, że chcemy aby zapytanie zwróciło nazwę produktu (ProductName), cenę jednostkową (UnitPrice), średnią cenę produktu oraz różnicę pomiędzy ceną a średnią. Oto zapytanie
SELECT ProductName, UnitPrice,

(SELECT AVG(UnitPrice) FROM Products) AS 'Średnia',

UnitPrice - (SELECT AVG(UnitPrice) FROM Products) AS 'Różnica'

FROM Products


Zadanie 12


Mamy zapytanie, które jest bardzo podobne do poprzedniego, ale tym razem występuje dodakowo warunek WHERE CategoryId = 3. Jaki będzie efekt: czy średnia będzie liczona teraz w tej kategorii czy z całej tabeli?
SELECT ProductName, UnitPrice,

(SELECT AVG(UnitPrice) FROM Products) AS 'Średnia',

UnitPrice - (SELECT AVG(UnitPrice) FROM Products) AS 'Różnica'

FROM products

WHERE CategoryId = 3

Dotychczasowe przykłady podzapytań były takie, że nie było związku pomiędzy aktualnym rekordem (wierszem) przetwarzanym przez główne zapytanie a rekordami przetwarzanymi przez podzapytanie. Gdy taka zależność zachodzi mówimy o podzapytaniu skorelowanym (powiązanym)

Zadanie 13

Napisać zapytanie, które zwróci listę, w której będą występowały produkty (z tabeli Order details) oraz obok nich liczby największego zamówienia, jakie kiedykolwiek zostało złożone dla danego produktu. Zrobić dwie wersje. Pierwsza będzie zwracała tylko identyfikator produktu (ProductID), a druga jego nazwę. Widać, że w pierwszy przypadku wystarczy wykorzystać tylko tabelę Order Details, a w drugim aż trzy: Order Details, Orders i Products i wykonać odpowiednie złączenie.




Wersja I

Proszę zwrócić uwagę na korelację, uzyskaną przez odpowiedni warunek WHERE.

SELECT ProductId, quantity

FROM [Order Details] AS OD1

WHERE quantity = ( SELECT MAX(quantity) FROM [Order Details] AS OD2

WHERE OD1.ProductId = OD2.ProductID )

Zadanie 14


Napisać zapytanie, które z bazy Northwind zwróci tych klientów, którzy zamówili więcej niż 20 sztuk towaru o numerze 23. Wykorzystać podzapytania.

SELECT orderid, customerid

FROM orders AS O

WHERE 20 < (SELECT quantity FROM [orders details] AS OD



WHERE O.ordered = OD.ordered AND OD.productid = 23)




©absta.pl 2019
wyślij wiadomość

    Strona główna