Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej
publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną,
fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym
powoduje naruszenie praw autorskich niniejszej publikacji.

Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi
bądź towarowymi ich właścicieli.

Autor oraz Wydawnictwo HELION dołożyli wszelkich starań, by zawarte w tej książce informacje
były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie,
ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz
Wydawnictwo HELION nie ponoszą również żadnej odpowiedzialności za ewentualne szkody
wynikłe z wykorzystania informacji zawartych w książce.

Redaktor prowadzący: Michał Mrowiec

Projekt okładki: Studio Gravite / Olsztyn
Obarek, Pokoński, Pazdrijowski, Zaprucki

Wydawnictwo HELION
ul. Kościuszki 1c, 44-100 GLIWICE
tel. [phone]
e-mail:

[e-mail]

WWW:

http://helion.pl (księgarnia internetowa, katalog książek)

Drogi Czytelniku!
Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres
http://helion.pl/user/opinie/sqljak
Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję.

Kody źródłowe wybranych przykładów dostępne są pod adresem:
ftp://ftp.helion.pl/przyklady/sqljak.zip

ISBN: 978-83-283-1283-8

Copyright © Helion 2015

Printed in Poland.

•

Kup książkę

•

Poleć książkę

•

Oceń książkę

•

Księgarnia internetowa

•

Lubię to! » Nasza społeczność

Spis treĈci

Wstöp .............................................................................................. 5

PodziĊkowania .................................................................................................................. 5

Rozdziaä 1. Klauzula SELECT .............................................................................. 7

ûwiczenia ......................................................................................................................... 8
Zadania do samodzielnego wykonania ........................................................................... 46
Rozwiązania zadaĔ ......................................................................................................... 56

Rozdziaä 2. Funkcje wbudowane ....................................................................... 61

ûwiczenia ....................................................................................................................... 61
Zadania do samodzielnego wykonania ........................................................................... 79
Rozwiązania zadaĔ ......................................................................................................... 86

Rozdziaä 3. Zäñczenia ....................................................................................... 91

Rodzaje záączeĔ .............................................................................................................. 91
Záączenie wewnĊtrzne INNER JOIN .............................................................................. 92
Záączenie zewnĊtrzne lewostronne LEFT OUTER JOIN ............................................... 93
Záączenie zewnĊtrzne prawostronne RIGHT OUTER JOIN .......................................... 93
Peáne záączenie zewnĊtrzne FULL OUTER JOIN .......................................................... 94
Záączenie krzyĪowe CROSS JOIN ................................................................................. 95
ûwiczenia ....................................................................................................................... 96
Zadania do samodzielnego wykonania ......................................................................... 121
Rozwiązania zadaĔ ....................................................................................................... 128

Rozdziaä 4. Funkcje agregujñce ...................................................................... 133

ûwiczenia ..................................................................................................................... 133
Zadania do samodzielnego wykonania ......................................................................... 158
Rozwiązania zadaĔ ....................................................................................................... 166

Rozdziaä 5. Operacje na zbiorach .................................................................... 173

Operator UNION ALL ................................................................................................. 173
Operator EXCEPT ........................................................................................................ 174
Operator INTERSECT ................................................................................................. 175
ûwiczenia ..................................................................................................................... 176
Zadania do samodzielnego wykonania ......................................................................... 187
Rozwiązania zadaĔ ....................................................................................................... 190

Poleć książkę

Kup książkę

4

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Rozdziaä 6. Podzapytania ............................................................................... 193

ûwiczenia ..................................................................................................................... 193
Zadania do samodzielnego wykonania ......................................................................... 212
Rozwiązania zadaĔ ....................................................................................................... 217

Rozdziaä 7. CASE, CAST i CONVERT ............................................................... 223

CASE ............................................................................................................................ 223
CAST i CONVERT ...................................................................................................... 224
ûwiczenia ..................................................................................................................... 225
Zadania do samodzielnego wykonania ......................................................................... 231
Rozwiązania zadaĔ ....................................................................................................... 233

Rozdziaä 8. Zadania ....................................................................................... 235

Tabela Reader ............................................................................................................... 235
Tabela Genre ................................................................................................................ 236
Tabela Book ................................................................................................................. 237
Tabela BookCopy ......................................................................................................... 237
Tabela BookRating ....................................................................................................... 238
Tabela Employee .......................................................................................................... 239
Tabela Loan .................................................................................................................. 239
Tabela Parameter .......................................................................................................... 240
Zadania do samodzielnego wykonania ......................................................................... 241
Rozwiązania zadaĔ ....................................................................................................... 266

Skorowidz ................................................................................... 287

Poleć książkę

Kup książkę

Rozdziaä 4.

Funkcje agregujñce

Funkcje agregujące są to funkcje, które jako parametr przyjmują zbiór wartoĞci i zwra-
cają w wyniku pojedynczą wartoĞü. Ten parametr podajemy zazwyczaj w postaci na-
zwy kolumny, a obliczenia są wykonywane na wszystkich wartoĞciach wystĊpujących
w tej kolumnie.

W tym rozdziale omówimy nastĊpujące funkcje agregujące:



COUNT

— zlicza liczbĊ wierszy,



SUM

— oblicza sumĊ wartoĞci,



MIN

— znajduje najniĪszą wartoĞü,



MAX

— znajduje najwyĪszą wartoĞü,



AVG

— oblicza Ğrednią wartoĞü.

çwiczenia

ûwiczenia bĊdą wykonywane na tych samych tabelach

Employee

,

Department

i

City

,

co üwiczenia z rozdziaáu 3.

çwiczenie 4.1

Policz wszystkich pracowników.

Oczekiwany wynik

Rysunek 4.1.
Oczekiwany wynik
üwiczenia 4.1

Poleć książkę

Kup książkę

134

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Rozwiñzanie

Aby policzyü wszystkich pracowników, skorzystamy z funkcji agregującej

COUNT

, którą

wywoáamy z uĪyciem parametru

*

:

SELECT COUNT(*) as LiczbaPracowników
FROM Employee

Funkcja

COUNT

wywoáana z uĪyciem parametru

*

zlicza wszystkie wiersze w danej tabeli.

JeĞli natomiast jako parametr podamy nazwĊ kolumny, zostaną zliczone wszystkie war-
toĞci w tej kolumnie, które nie są wartoĞciami

NULL

.

Jako przykáad weĨmy tabelĊ 4.1 o nazwie

Sïownik

.

Tabela 4.1. Sáownik

Id

Nazwa

1

AAA

3

BBB

4

NULL

5

CCC

Dla zapytania:

SELECT COUNT(*)
FROM Sđownik

otrzymamy wynik

4

—zostaáy zliczone wszystkie wiersze.

Dla zapytania:

SELECT COUNT(Nazwa)
FROM Sđownik

otrzymamy wynik

3

— zostaáy zliczone te wiersze, które w kolumnie

Nazwa

mają warto-

Ğci inne niĪ

NULL

.

çwiczenie 4.2

Policz wszystkich pracowników, którzy nie mają drugiego imienia.

Oczekiwany wynik

Rysunek 4.2.
Oczekiwany wynik
üwiczenia 4.2

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

135

Rozwiñzanie

Aby znaleĨü pracowników, którzy nie mają drugiego imienia, naleĪy wyĞwietliü te
wszystkie wiersze z tabeli

Employee

, których wartoĞü w kolumnie

SecondName

jest warto-

Ğcią

NULL

. NastĊpnie naleĪy — za pomocą funkcji agregującej

COUNT

— policzyü liczbĊ

zwróconych wierszy:

SELECT COUNT(*) as LiczbaPracownikow
FROM Employee
WHERE SecondName IS NOT NULL

To zadanie moĪna rozwiązaü równieĪ w inny sposób.

Tak jak pokazaáam w poprzednim üwiczeniu — jeĪeli funkcja

COUNT

jako parametr

przyjmuje nazwĊ kolumny, wówczas zlicza tylko te wartoĞci z tej kolumny, które nie
są wartoĞciami

NULL

.

PowyĪsze zapytanie moĪna wiĊc równieĪ zapisaü w nastĊpujący sposób:

SELECT COUNT(SecondName)

FROM Employee

çwiczenie 4.3

Policz wszystkich aktywnych pracowników.

Oczekiwany wynik

Rysunek 4.3.
Oczekiwany wynik
üwiczenia 4.3

Rozwiñzanie

JeĞli uĪywamy funkcji agregujących, nic nie stoi na przeszkodzie, aby uĪywaü rów-
nieĪ poznanych juĪ wczeĞniej elementów jĊzyka — w tym wypadku warunków, których
uĪyjemy w sekcji

WHERE

.

Aby policzyü wszystkich aktywnych pracowników, uĪyjemy funkcji agregującej

COUNT(*)

dla wierszy, które speániają warunek

Active = 1

.

Zapytanie wiĊc bĊdzie wyglądaáo nastĊpująco:

SELECT COUNT(*) as LiczbaAktywnychPracowników
FROM Employee
WHERE Active = 1

Poleć książkę

Kup książkę

136

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

çwiczenie 4.4

Policz, ilu pracowników pracuje w dziale IT.

Oczekiwany wynik

Rysunek 4.4.

Oczekiwany wynik
üwiczenia 4.4

Rozwiñzanie

Informacje o pracownikach mamy w tabeli

Employee

, natomiast informacje o dziale,

w którym pracują, mamy w tabeli

Department

. Musimy wiĊc záączyü te dwie tabele oraz

zdefiniowaü warunek tak, aby zostaáy wyĞwietlone dane tylko tych pracowników, którzy
są zatrudnieni w dziale IT:

SELECT *

FROM Employee

INNER JOIN Department ON Employee.DepartmentId = Department.Id

WHERE Department.Name = 'IT'

Teraz wystarczy juĪ tylko zliczyü wiersze za pomocą funkcji agregującej

COUNT(*)

:

SELECT COUNT(*) as LiczbaPracowników

FROM Employee

INNER JOIN Department ON Employee.DepartmentId = Department.Id

WHERE Department.Name = 'IT'

çwiczenie 4.5

Policz wszystkich pracowników, którzy pracują w dziaáach znajdujących siĊ w Warszawie.

Oczekiwany wynik

Rysunek 4.5.

Oczekiwany wynik
üwiczenia 4.5

Rozwiñzanie

Informacje o pracownikach znajdują siĊ w tabeli

Employee

, dane dziaáów — w tabeli

Department

, dane miast — w tabeli

City

. Musimy wiĊc poáączyü te trzy tabele. NastĊpnie

definiujemy warunek wyĞwietlający tylko wiersze, w których

Name

ma wartoĞü

Warsaw

,

i zliczamy wiersze za pomocą funkcji agregującej

COUNT

:

SELECT COUNT(*) as LiczbaPracownikow

FROM Employee

INNER JOIN Department ON Employee.DepartmentId = Department.Id

INNER JOIN City ON Department.CityId = City.Id

WHERE City.Name = 'Warsaw'

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

137

çwiczenie 4.6

Policz, ile pracodawca wydaje miesiĊcznie na pensje. ZauwaĪ, Īe pracodawca páaci
pensje tylko aktywnym pracownikom.

Oczekiwany wynik

Rysunek 4.6.
Oczekiwany wynik
üwiczenia 4.6

Rozwiñzanie

ĩeby policzyü, ile pracodawca wydaje miesiĊcznie na pensje, musimy policzyü sumĊ
zarobków wszystkich aktywnych pracowników.

Aktywnych pracowników znajdziemy, uĪywając zapytania:

SELECT *
FROM Employee
WHERE Active = 1

Aby obliczyü sumĊ, skorzystamy z funkcji agregującej

SUM

, która jako parametr przyj-

muje nazwĊ kolumny i zlicza sumĊ wartoĞci z tej kolumny. Aby wyĞwietliü sumĊ za-
robków, uĪyjemy funkcji

SUM

w sekcji

SELECT

, zamiast

*

:

SELECT SUM(Salary) as SumaPensji
FROM Employee
WHERE Active = 1

çwiczenie 4.7

Policz, ile pracodawca wydaje rocznie na pensje.

Oczekiwany wynik

Rysunek 4.7.
Oczekiwany wynik
üwiczenia 4.7

Rozwiñzanie

Aby policzyü roczną sumĊ zarobków, naleĪy pomnoĪyü miesiĊczne zarobki przez

12

.

Zapytanie bĊdzie wiĊc wyglądaáo nastĊpująco:

SELECT SUM(Salary) * 12 as RocznaSumaPensji
FROM Employee
WHERE Active = 1

Poleć książkę

Kup książkę

138

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

MoĪna je równieĪ zapisaü w taki sposób:

SELECT SUM(Salary * 12) as RocznaSumaPensji
FROM Employee
WHERE Active = 1

çwiczenie 4.8

ZnajdĨ minimalne zarobki spoĞród wszystkich pracowników.

Oczekiwany wynik

Rysunek 4.8.
Oczekiwany wynik
üwiczenia 4.8

Rozwiñzanie

Aby znaleĨü minimalne zarobki, uĪyjemy funkcji

MIN

, która jako parametr przyjmuje

nazwĊ kolumny i zwraca najmniejszą wartoĞü spoĞród wszystkich w tej kolumnie.

Zapytanie bĊdzie wiĊc wyglądaáo nastĊpująco:

SELECT MIN(Salary) as MinimalneZarobki
FROM Employee

çwiczenie 4.9

Policz, z ilu liter skáada siĊ najkrótsze imiĊ pracownika spoĞród zatrudnionych.

Oczekiwany wynik

Rysunek 4.9.
Oczekiwany wynik
üwiczenia 4.9

Rozwiñzanie

LiczbĊ liter w imieniu moĪemy policzyü za pomocą funkcji

LEN

:

SELECT FirstName, LEN(FirstName) as LiczbaLiter

FROM Employee

Po wykonaniu tego zapytania otrzymamy wynik widoczny na rysunku 4.10.

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

139

Rysunek 4.10.
Wynik obliczenia
liczby liter w imionach
wszystkich pracowników

Na rysunku widzimy, Īe spoĞród wyĞwietlonych dáugoĞci imion najkrótsze z nich ma
4 znaki. Aby napisaü zapytanie, które znajdzie najkrótsze imiĊ, musimy uĪyü funkcji

MIN

, jako parametr podając wynik funkcji

LEN(FirstName)

:

SELECT MIN(LEN(FirstName)) as NajmniejLiter
FROM Employee

çwiczenie 4.10

Policz, ile lat áącznie przepracowali w firmie aktywni pracownicy.

Oczekiwany wynik

Rysunek 4.11.
Oczekiwany wynik
üwiczenia 4.10

Rozwiñzanie

Aby wyĞwietliü, ile lat przepracowali firmie aktywni pracownicy, uĪyjemy funkcji

DATEDIFF

— do policzenia róĪnicy lat pomiĊdzy datą zatrudnienia a datą dzisiejszą:

SELECT DATEDIFF(year,HireDate,GETDATE())
FROM Employee
WHERE Active = 1

Aby obliczyü sumĊ tych lat, uĪyjemy funkcji

SUM

, jako argument podając wynik funkcji

DATEDIFF

:

SELECT SUM(DATEDIFF(year,HireDate,GETDATE()))
FROM Employee
WHERE Active = 1

Poleć książkę

Kup książkę

140

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

çwiczenie 4.11

SprawdĨ, jaka jest róĪnica pomiĊdzy minimalną a maksymalną pensją.

Oczekiwany wynik

Rysunek 4.12.

Oczekiwany wynik
üwiczenia 4.11

Rozwiñzanie

Aby znaleĨü wysokoĞü minimalnej pensji, uĪyjemy poznanej juĪ funkcji

MIN

.

SELECT MIN(Salary)

FROM Employee

Aby znaleĨü wysokoĞü maksymalnej pensji, uĪyjemy analogicznej funkcji

MAX

.

SELECT MAX(Salary)

FROM Employee

Aby policzyü róĪnicĊ pomiĊdzy minimalną a maksymalną wysokoĞcią pensji, uĪyjemy
tych dwóch funkcji w jednym zapytaniu i znajdziemy róĪnicĊ pomiĊdzy nimi za pomocą
operatora odejmowania:

SELECT MAX(Salary) - MIN(Salary) as RoznicaPensji

FROM Employee

çwiczenie 4.12

Policz Ğrednią, minimalną i maksymalną wysokoĞü pensji pracowników w dziale IT.

Oczekiwany wynik

Rysunek 4.13.

Oczekiwany wynik
üwiczenia 4.12

Rozwiñzanie

PoznaliĞmy juĪ funkcje

MIN

i

MAX

, które zwracają odpowiednio najniĪszą i najwyĪszą

wartoĞü. Istnieje jeszcze jedna funkcja agregująca

AVG

, która zwraca Ğrednią wartoĞü.

Zastosujemy te trzy funkcje w kolumnie

Salary

w wynikach poáączonych tabel

Employee

oraz

Department

:

SELECT AVG(Salary) as AvgSalary, MIN(Salary) as MinSalary, MAX(Salary) as MaxSalary

FROM Employee

INNER JOIN Department ON Employee.DepartmentId = Department.Id

WHERE Name = 'IT'

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

141

çwiczenie 4.13

O ile naleĪaáoby podnieĞü pensjĊ pracownikowi, który zarabia najmniej, Īeby jego
pensja wynosiáa tyle, ile Ğrednia pensja?

Oczekiwany wynik

Rysunek 4.14.
Oczekiwany wynik
üwiczenia 4.13

Rozwiñzanie

PensjĊ pracownika, który zarabia najmniej, znajdziemy za pomocą funkcji

MIN

, a wy-

sokoĞü Ğredniej pensji — za pomocą funkcji

AVG

. Aby dowiedzieü siĊ, o ile naleĪaáoby

podnieĞü wysokoĞü tej minimalnej pensji, Īeby wynosiáa ona tyle, co Ğrednia, naleĪy
znaleĨü róĪnicĊ pomiĊdzy tymi kwotami:

SELECT AVG(Salary) - MIN(Salary)
FROM Employee

çwiczenie 4.14

Policz Ğredni wiek wszystkich pracowników.

Oczekiwany wynik

Rysunek 4.15.
Oczekiwany wynik
üwiczenia 4.14

Rozwiñzanie

Aby wyĞwietliü wiek pracowników, naleĪy obliczyü liczbĊ lat, która minĊáa od dnia
ich urodzin do dnia dzisiejszego:

SELECT DATEDIFF(year, BirthDate, GETDATE())
FROM Employee

NastĊpnie za pomocą funkcji

AVG

znajdujemy Ğrednią z tych wartoĞci:

SELECT AVG(DATEDIFF(year, BirthDate, GETDATE())) as SredniWiek
FROM Employee

Poleć książkę

Kup książkę

142

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

çwiczenie 4.15

Policz Ğrednią liczbĊ miesiĊcy przepracowanych przez tych pracowników, którzy juĪ
w firmie nie pracują.

Oczekiwany wynik

Rysunek 4.16.
Oczekiwany wynik
üwiczenia 4.15

Rozwiñzanie

Aby znaleĨü liczbĊ miesiĊcy, które przepracowali pracownicy niezatrudnieni juĪ w fir-
mie, uĪyjemy funkcji

DATEDIFF

— do obliczenia, ile miesiĊcy minĊáo od daty zatrud-

nienia (

HireDate

) do daty zwolnienia (

RelieveDate

), ale tylko dla tych pracowników,

którzy mają wpisaną datĊ zwolnienia. NastĊpnie za pomocą funkcji

AVG

znajdujemy

Ğrednią z tych wartoĞci:

SELECT AVG(DATEDIFF(month, HireDate, RelieveDate)) as SredniaLiczbaMiesiecy

FROM Employee
WHERE RelieveDate IS NOT NULL

çwiczenie 4.16

ZnajdĨ najpóĨniejszą datĊ urodzenia spoĞród wszystkich pracowników.

Oczekiwany wynik

Rysunek 4.17.
Oczekiwany wynik
üwiczenia 4.16

Rozwiñzanie

Funkcji agregujących moĪna uĪywaü nie tylko na liczbach, ale równieĪ na datach:

SELECT MAX(BirthDate) as MaksymalnaDataUrodzenia
FROM Employee

çwiczenie 4.17

WyĞwietl wszystkie unikalne wysokoĞci páac w firmie i dla kaĪdej z tych wartoĞci
policz, ilu pracowników zarabia taką wáaĞnie kwotĊ.

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

143

Oczekiwany wynik

Rysunek 4.18.
Oczekiwany wynik
üwiczenia 4.17

Rozwiñzanie

Umiemy juĪ stosowaü funkcje agregujące na wszystkich wierszach zapytania, czyli
np. policzyü liczbĊ wierszy w tabeli. Kolejnym krokiem jest moĪliwoĞü stosowania funk-
cji agregujących na grupach rekordów. PokaĪĊ to na prostym przykáadzie.

ZaáóĪmy, Īe mamy tabelĊ

Samochody

, która jest wypeániona danymi jak w tabeli 4.1.

Tabela 4.1. Przykáadowe dane dla samochodów

Marka

Rok_produkcji

Model

Toyota

2013

Yaris

Toyota

2014

Auris

Fiat

2013

Bravo

Fiat

2012

Punto

Toyota

2013

Corolla

ChcielibyĞmy policzyü, ile mamy modeli samochodów dla poszczególnych marek. W tym
celu musimy pogrupowaü je wedáug marki, dodając klauzulĊ

GROUP BY Marka

. Pogru-

powaną tabelĊ moĪemy sobie wyobraziü jako tabelĊ przedstawioną poniĪej tabela 4.2.

Tabela 4.2. Wyniki pogrupowania samochodów wedáug marki

Marka

Rok_produkcji

Model

2013

Yaris

2014

Auris

Toyota

2013

Corolla

2013

Bravo

Fiat

2012

Punto

KaĪda marka jest teraz nagáówkiem grupy. KaĪda grupa posiada wiersze, które pasują
do grupy wedáug kryterium grupowania. Na tych wierszach moĪemy stosowaü teraz
funkcje grupujące (agregujące), które bĊdą wykonywaü dziaáania w ramach grupy.

Poleć książkę

Kup książkę

144

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Tabeli 4.2 nie moĔemy wyĈwietliè. Narysowaäam jñ w celu lepszego zobrazowania

tego, w jaki sposób wyglñda grupowanie.

JeĞli wiĊc wykonamy zapytanie:

SELECT Marka, COUNT(*) as Liczba
FROM Samochody
GROUP BY Marka

wówczas otrzymamy wynik zaprezentowany w tabeli 4.3.

Tabela 4.3. Wynik policzenia liczby modeli samochodów dla kaĪdej marki

Marka

Liczba

Toyota

3

Fiat

2

JeĞli zastosujemy klauzulĊ

GROUP BY

, to w sekcji

SELECT

moĪemy wyĞwietlaü tylko

i wyáącznie:



kolumny, które są kryterium grupowania, tak jak marka samochodu w powyĪszym
przykáadzie,



wyniki funkcji grupujących (agregujących).

Nie moĪemy juĪ odwoáywaü siĊ do pozostaáych kolumn. Przykáadowo, jeĞli napisalibyĞmy:

SELECT Marka, Model
FROM Samochody
GROUP BY Marka

to jaki model samochodu miaáby byü wyĞwietlony? Yaris, Auris czy Corolla? Nie
wiadomo. GrupĊ utworzyliĞmy po to, Īeby mieü tylko jeden wiersz dla danej grupy,
a wiĊc nie moĪemy wyĞwietliü kolumn, które nie zostaáy uĪyte do grupowania. JeĞli na-
piszemy takie zapytanie, w wyniku otrzymamy báąd:

Column 'Samochody.Model' is invalid in the select list because it is not contained
in either an aggregate function or the GROUP BY clause.

Pamiötaj, Ĕe jeĈli uĔyjesz grupowania, w sekcji SELECT moĔesz uĔywaè tylko funkcji
grupujñcych (agregujñcych) oraz kolumn, które wystñpiäy w sekcji GROUP BY.

GrupĊ moĪna równieĪ utworzyü na podstawie wielu kolumn. Na przykáad, jeĞli chcemy
policzyü, ile modeli danej marki zostaáo wyprodukowanych w poszczególnych latach,
moĪemy pogrupowaü dane wedáug marki oraz roku produkcji:

GROUP BY Marka, Rok_produkcji

WyobraĪamy sobie wtedy wyniki pogrupowania tak, jak to przedstawiono w tabeli 4.4.

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

145

Tabela 4.4. Wyniki pogrupowania samochodów wedáug marki i roku produkcji

Marka

Rok_produkcji

Model

Yaris

2013

Corolla

Toyota

2014

Auris

2013

Bravo

Fiat

2012

Punto

MoĪemy teraz np. policzyü liczbĊ wierszy w danej grupie.

SELECT Marka, Rok_produkcji, COUNT(*) as Liczba
FROM Samochody
GROUP BY Marka, Rok_produkcji

Wynik tego zapytania zostaá zaprezentowany w tabeli 4.5.

Tabela 4.5. Wynik zapytania o liczbĊ wierszy w danej grupie

Marka

Rok_produkcji

Liczba

Toyota

2013

2

Toyota

2014

1

Fiat

2013

1

Fiat

2012

1

Nawet jeĈli widzimy, Ĕe w danej grupie znajduje siö tylko jeden wiersz, trzymamy
siö zasady, Ĕe nie wolno nam w sekcji SELECT wyĈwietlaè kolumn, które nie sñ kry-
terium grupowania.

Wróümy wiĊc do üwiczenia. Mamy wyĞwietliü wszystkie unikalne wartoĞci zarobków
i policzyü, ilu pracowników zarabia taką wáaĞnie kwotĊ.

Budujemy wiĊc grupĊ wedáug zarobków:

GROUP BY Salary

NastĊpnie zliczamy liczbĊ wierszy w kaĪdej grupie:

SELECT Salary, COUNT(*) as LiczbaPracownikow
FROM Employee
GROUP BY Salary

Poleć książkę

Kup książkę

146

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

çwiczenie 4.18

WyĞwietl wszystkie unikalne wysokoĞci páac w firmie.

Oczekiwany wynik

Rysunek 4.19.
Oczekiwany wynik
üwiczenia 4.18

Rozwiñzanie

Umiemy juĪ rozwiązaü powyĪsze üwiczenie za pomocą klauzuli

DISTINCT

:

SELECT DISTINCT Salary
FROM Employee

MoĪemy to jednak równieĪ zrobiü, uĪywając grupowania:

SELECT Salary
FROM Employee
GROUP BY Salary

To, Ĕe w klauzuli SELECT moĔesz uĔyè tylko kolumn wystöpujñcych w sekcji GROUP BY,
nie oznacza, Ĕe musisz ich uĔyè. ZaleĔy to tylko i wyäñcznie od Twoich wymagaþ i po-
trzeb. W klauzuli SELECT moĔe wystñpiè dowolny podzbiór kolumn z sekcji GROUP BY,

mogñ teĔ zostaè uĔyte same funkcje grupujñce (agregujñce).

Przykäady poprawnego uĔycia:

SELECT X, COUNT(*), SUM(Z)
FROM Tabela
GROUP BY X, Y

SELECT COUNT(*)
FROM Tabela
GROUP BY X, Y, Z

SELECT X, Z
FROM Tabela
GROUP BY X, Y, Z

Zawsze jeĈli patrzysz na zapytanie grupujñce, upewnij siö, Ĕe w sekcji SELECT nie ma
kolumn, które nie wystöpujñ w sekcji GROUP BY.

PoĈwiöciäam temu zagadnieniu tak wiele miejsca, poniewaĔ jest to jeden z najczöĈciej
popeänianych bäödów dotyczñcych grupowania.

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

147

çwiczenie 4.19

WyĞwietl, ilu pracowników pracuje w poszczególnych dziaáach.

Oczekiwany wynik

Rysunek 4.20.
Oczekiwany wynik
üwiczenia 4.19

Rozwiñzanie

Informacje o pracownikach mamy w tabeli

Employee

, natomiast informacje o dziaáach

— w tabeli

Department

. Musimy wiĊc poáączyü te dwie tabele:

SELECT *
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
GROUP BY Name

NastĊpnie wyniki poáączenia tabel musimy pogrupowaü wedáug nazwy dziaáu i poli-
czyü liczbĊ wierszy w kaĪdej grupie, czyli liczbĊ pracowników przypisanych danemu
dziaáowi:

SELECT Name, COUNT(*) as LiczbaPracownikow
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
GROUP BY Name

çwiczenie 4.20

WyĞwietl, ilu pracowników pracuje w poszczególnych dziaáach. Wyniki rozszerz rów-
nieĪ o te dziaáy, które nie mają przypisanego Īadnego pracownika.

Oczekiwany wynik

Rysunek 4.21.
Oczekiwany wynik
üwiczenia 4.20

Poleć książkę

Kup książkę

148

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Rozwiñzanie

Aby uwzglĊdniü równieĪ dziaáy, które nie mają przypisanego Īadnego pracownika,
uĪyjemy záączenia zewnĊtrznego

LEFT JOIN

. Grupowanie i sposób liczenia pracowników

bĊdą dokáadnie takie same, jak w poprzednim üwiczeniu:

SELECT Name, COUNT(*) as LiczbaPracownikow
FROM Department
LEFT JOIN Employee ON Employee.DepartmentId = Department.Id
GROUP BY Name

JeĞli wykonamy to zapytanie, okazuje siĊ, Īe dla dziaáów, które nie mają przypisane-
go Īadnego pracownika, np.

Controlling

, otrzymaliĞmy wynik

1

(rysunek 4.22).

Rysunek 4.22.
Wyniki z báĊdnie
zwróconą liczbą
pracowników dla
dziaáów, w których
nikt nie pracuje

Dla dziaáu

Controlling

istnieje jeden wiersz zawierający informacje o tym dziale, ale

brakuje informacji o pracownikach, poniewaĪ nie istnieje Īaden z nich przypisany
temu dziaáowi. PamiĊtaj o tym, jak dziaáa funkcja

COUNT

. JeĞli parametrem wywoáania

tej funkcji jest

*

, zlicza ona wszystkie wiersze — bez wzglĊdu na to, czy wiersz ten

posiada wartoĞci z obu tabel, czy teĪ zostaá uzupeániony wartoĞciami

NULL

dla záączenia

zewnĊtrznego

LEFT JOIN

. JeĞli parametrem wywoáania tej funkcji jest nazwa kolumny

— zlicza ona tylko te wiersze, w których wartoĞü tej kolumny jest róĪna od

NULL

. Mo-

Īemy wykorzystaü to dziaáanie w wypadku powyĪszego üwiczenia i jako parametr do
funkcji

COUNT

przekazaü kolumnĊ

Employee.Id

:

SELECT Name, COUNT(Employee.Id) as LiczbaPracownikow
FROM Department
LEFT JOIN Employee ON Employee.DepartmentId = Department.Id
GROUP BY Name

çwiczenie 4.21

Dla kaĪdego dziaáu policz liczbĊ pracowników zarabiających wiĊcej niĪ 1500 zá.

Oczekiwany wynik

Rysunek 4.23.
Oczekiwany wynik
üwiczenia 4.21

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

149

Rozwiñzanie

JeĞli uĪywamy grupowania, wciąĪ moĪemy uĪywaü warunków w stosunku do ko-
lumn, które nie wystĊpują jako kryterium grupowania. W tym przykáadzie bĊdziemy
grupowaü wedáug nazwy dziaáów, a w klauzuli

WHERE

sprawdzimy wysokoĞü zarobków.

Przy przetwarzaniu zapytania najpierw są wykonywane warunki z sekcji

WHERE

, mamy

wiĊc wówczas dostĊpne jeszcze wszystkie kolumny, a dopiero potem jest wykonywane
grupowanie.

Sekcja

GROUP BY

znajduje siĊ w zapytaniu po sekcji

WHERE

.

Zapytanie bĊdzie wiĊc wyglądaáo nastĊpująco:

SELECT Name, COUNT(*) as LiczbaPracownikow
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
WHERE Salary > 1500
GROUP BY Name

çwiczenie 4.22

Dla kaĪdego dziaáu wyĞwietl Ğrednią pensjĊ. Wyniki posortuj wedáug nazwy dziaáu.

Oczekiwany wynik

Rysunek 4.24.
Oczekiwany wynik
üwiczenia 4.22

Rozwiñzanie

Aby policzyü Ğrednią pensjĊ w dziale, poáączymy tabele

Employee

i

Department

. Wyniki

pogrupujemy wedáug nazwy dziaáu i uĪyjemy funkcji

AVG

, której argumentem bĊdzie

kolumna

Salary

, aby policzyü Ğrednią pensjĊ:

SELECT Name, AVG(Salary) as SredniaPensja
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id

Otrzymane wyniki posortujemy za pomocą klauzuli

ORDER BY

. TĊ klazulĊ obowiązują

takie same zasady jak klauzulĊ

SELECT

: moĪemy tu uĪywaü tylko tych kolumn, które

byáy kryterium grupowania, oraz funkcji agregujących. JeĞli bĊdziemy chcieli posortowaü
wyniki wedáug nazwiska pracownika (kolumna

LastName

w tabeli

Employee

), która to

kolumna nie jest kryterium grupowania, otrzymamy znany juĪ báąd:

Column "Employee.LastName" is invalid in the ORDER BY clause because it is not
contained in either an aggregate function or the GROUP BY clause.

Poleć książkę

Kup książkę

150

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Sekcja

ORDER BY

znajduje siĊ na samym koĔcu zapytania, po sekcji

GROUP BY

. Zapytanie

bĊdzie wiĊc wyglądaáo nastĊpująco:

SELECT Name, AVG(Salary) as SredniaPensja
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
GROUP BY Name
ORDER BY Name

çwiczenie 4.23

Dla kaĪdego dziaáu policz, ile pieniĊdzy idzie miesiĊcznie na wypáaty (czyli dla ak-
tywnych pracowników). UwzglĊdnij równieĪ dziaáy, które nie mają przypisanego
Īadnego pracownika.

Oczekiwany wynik

Rysunek 4.25.

Oczekiwany wynik
üwiczenia 4.23

Rozwiñzanie

Poáączymy tabelĊ

Department

z tabelą

Employee

przy uĪyciu záączenia zewnĊtrznego

LEFT JOIN

, tak aby uwzglĊdniü równieĪ dziaáy, które nie mają przypisanego Īadnego

pracownika. NastĊpnie wyniki pogrupujemy wedáug nazwy dziaáu i dla kaĪdej grupy,
czyli dla kaĪdego dziaáu, policzymy sumĊ wynagrodzeĔ pracowników tego dziaáu:

SELECT Name, SUM(Salary) as SumaWynagrodzen

FROM Department

LEFT JOIN Employee ON Employee.DepartmentId = Department.Id

GROUP BY Name

Po wykonaniu zapytania otrzymamy wynik jak na rysunku 4.26.

Rysunek 4.26.

Wynik obliczenia
sumy wynagrodzeĔ
dla kaĪdego dziaáu

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

151

ZauwaĪ, Īe po wykonaniu tego zapytania, dla dziaáów, które nie mają przypisanego
Īadnego pracownika, otrzymasz wartoĞü

NULL

, poniewaĪ wynik dziaáania funkcji

SUM

(jeĞli nie zostaá zwrócony Īaden wiersz z powodu braku pracowników) jest równy

NULL

.

Aby pokazaü wyniki w bardziej czytelnej postaci, zastąpimy wartoĞü

NULL

wartoĞcią

0

,

za pomocą funkcji

ISNULL

:

SELECT Name, SUM(ISNULL(Salary,0)) as SumaWynagrodzen
FROM Department
LEFT JOIN Employee ON Employee.DepartmentId = Department.Id
GROUP BY Name

çwiczenie 4.24

WyĞwietl nazwĊ dziaáu, w którym Ğrednia pensja jest najwyĪsza.

Oczekiwany wynik

Rysunek 4.27.
Oczekiwany wynik
üwiczenia 4.24

Rozwiñzanie

Poáączymy tabele

Department

i tabele

Employee

, uĪywając záączenia wewnĊtrznego

INNER JOIN

(nie musimy wyĞwietlaü danych tych dziaáów, które nie mają przypisanego

Īadnego pracownika, bo skoro nie ma w nich pracowników, to wiersze te na pewno
nie speánią warunków zadania — jeĞli dziaá nie ma pracowników, to ich Ğrednia pensja
wynosi zero).

Wyniki pogrupujemy wedáug nazwy dziaáu i policzymy Ğrednią pensjĊ dla dziaáu za
pomocą funkcji

AVG

:

SELECT Name, AVG(Salary)
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
GROUP BY Name

Aby znaleĨü dziaá, w którym są najwyĪsze zarobki, moĪemy posortowaü wyniki ma-
lejąco wedáug tych Ğrednich zarobków. W sekcji

ORDER BY

moĪemy równieĪ uĪywaü

funkcji grupujących (agregujących):

ORDER BY AVG(Salary) DESC

NastĊpnie uĪyjemy klauzuli

TOP 1

, aby wyĞwietliü tylko jeden wiersz, znajdujący siĊ

na górze — poniewaĪ posortowaliĞmy wyniki malejąco wedáug Ğrednich zarobków, na
górze bĊdzie znajdowaá siĊ wiersz z najwyĪszymi Ğrednimi zarobkami.

W treĞci zadania byáo napisane tylko „wyĞwietl nazwĊ dziaáu” — nie byáo mowy o Ğred-
nich zarobkach, usuwamy wiĊc z klauzuli

SELECT

funkcjĊ

AVG(Salary)

.

Poleć książkę

Kup książkę

152

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Ostatecznie zapytanie bĊdzie wyglądaáo nastĊpująco:

SELECT TOP 1 Name

FROM Department

INNER JOIN Employee ON Employee.DepartmentId = Department.Id

GROUP BY Name

ORDER BY AVG(Salary) DESC

çwiczenie 4.25

Policz, ilu pracowników urodziáo siĊ w poszczególnych latach.

Oczekiwany wynik

Rysunek 4.28.

Oczekiwany wynik
üwiczenia 4.25

Rozwiñzanie

W klauzuli

GROUP BY

moĪemy uĪywaü nie tylko kolumn, ale równieĪ funkcji skalarnych

operujących na kolumnach. MoĪemy wiĊc pogrupowaü pracowników wedáug roku ich
urodzenia, który wyliczymy z daty urodzenia za pomocą funkcji

YEAR

:

GROUP BY YEAR(BirthDate)

Dla kaĪdej z grup wyĞwietlamy rok urodzenia oraz liczbĊ pracowników, którzy urodzili
siĊ w tym roku:

SELECT YEAR(BirthDate) as Rok, COUNT(*) as LiczbaPracownikow

FROM Employee

GROUP BY YEAR(BirthDate)

Pamiötaj, Ĕe jeĈli grupujesz dane wedäug funkcji skalarnej, której argumentem jest

okreĈlona kolumna, w klauzuli SELECT moĔesz wyĈwietliè wartoĈè tej funkcji (tak

jak w powyĔszym wypadku), ale nie moĔesz próbowaè wyĈwietlania tej kolumny, np.

poniĔsze zapytanie zwróci bäñd:

SELECT BirthDate

FROM Employee

GROUP BY YEAR(BirthDate)

Natomiast jeĈli grupujesz dane wedäug kolumny, w klauzuli SELECT moĔesz wy-

Ĉwietliè nie tylko tö kolumnö, ale równieĔ korzystaè z róĔnych funkcji wyliczajñcych

wartoĈè na podstawie tej kolumny, np. poniĔsze zapytanie jest poprawne:

SELECT YEAR(BirthDate)

FROM Employee

GROUP BY BirthDate

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

153

çwiczenie 4.26

WyĞwietl wszystkie pierwsze litery imion aktywnych pracowników i policz, ilu pra-
cowników ma imiĊ zaczynające siĊ na daną literĊ.

Oczekiwany wynik

Rysunek 4.29.

Oczekiwany wynik
üwiczenia 4.26

Rozwiñzanie

Aby znaleĨü pierwszą literĊ imienia pracownika, uĪyjemy funkcji

SUBSTRING

, która

wyĞwietla okreĞlony podciąg dla danego ciągu znaków. Dla przypomnienia napiszĊ,
Īe ta funkcja przyjmuje nastĊpujące parametry:



nazwĊ kolumny, która jest ciągiem znaków,



numer znaku w ciągu, od którego chcemy rozpocząü dany podciąg znaków,



liczbĊ znaków, która bĊdzie dáugoĞcią podciągu.

Pierwszą literĊ imienia znajdziemy wiĊc w nastĊpujący sposób:

SUBSTRING(FirstName,1,1)

Teraz dla tabeli

Employee

, dla aktywnych pracowników, utworzymy grupy na podstawie

pierwszej litery imion i dla kaĪdej grupy policzymy liczbĊ wierszy:

SELECT

SUBSTRING(FirstName,1,1) as PierwszaLitera,

COUNT(*) as Liczbapracownikow

FROM Employee

WHERE Active = 1

GROUP BY SUBSTRING(FirstName,1,1)

çwiczenie 4.27

Dla kaĪdego dziaáu policz, ile pracuje w nim kobiet i ilu mĊĪczyzn.

Oczekiwany wynik

Rysunek 4.30.
Oczekiwany wynik
üwiczenia 4.27

Poleć książkę

Kup książkę

154

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Rozwiñzanie

Poáączymy tabelĊ

Department

oraz tabelĊ

Employee

przy uĪyciu záączenia wewnĊtrz-

nego

INNER JOIN

. NastĊpnie otrzymane wyniki pogrupujemy wedáug nazwy dziaáu oraz

páci pracownika. PoniewaĪ nie kaĪdy z pracowników ma wpisaną w bazie danych páeü,
uĪyjemy warunku, który wyĞwietli tylko tych pracowników, którzy mają podaną páeü.
NastĊpnie uĪyjemy funkcji

COUNT

, aby policzyü liczbĊ wierszy w kaĪdej grupie.

Zapytanie bĊdzie wiĊc wyglądaáo nastĊpująco:

SELECT Name, Gender, COUNT(*) as LiczbaPracowników
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
WHERE Gender IS NOT NULL
GROUP BY Name, Gender

çwiczenie 4.28

Dla kaĪdego dziaáu znajdĨ Ğrednią, najwiĊkszą i najmniejszą kwotĊ pensji.

Oczekiwany wynik

Rysunek 4.31.
Oczekiwany wynik
üwiczenia 4.28

Rozwiñzanie

Do tej pory w üwiczeniach uĪywaliĞmy zazwyczaj jednej funkcji grupującej w danym
zapytaniu. Nic nie stoi na przeszkodzie, aby uĪyü ich wiĊcej w jednym zapytaniu i obli-
czyü wiĊcej wartoĞci dla danej grupy:

SELECT
Name,
AVG(Salary) as 'Ħrednia pensja',
MIN(Salary) as 'Minimalna pensja',
MAX(Salary) as 'Maksymalna pensja'
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
GROUP BY Name

çwiczenie 4.29

Dla kaĪdego kierownika wyĞwietl Ğrednie zarobki w jego dziale oraz áączną sumĊ,
którą przeznacza on miesiĊcznie na wypáaty (oczywiĞcie dla aktywnych pracowników).

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

155

Oczekiwany wynik

Rysunek 4.32.
Oczekiwany wynik
üwiczenia 4.29

Rozwiñzanie

Aby znaleĨü dane kierowników, poáączymy tabelĊ

Department

z tabelą

Employee

na

podstawie warunku záączenia

ON Manager.Id = Department.ManagerId

. Dodatkowo

po raz kolejny doáączymy tabelĊ

Employee

— Īeby znaleĨü wszystkich aktywnych pra-

cowników tego dziaáu. UĪyjemy tu wiĊc innego warunku záączenia

ON Department.Id =

Employee.DepartmentId

. PamiĊtamy o tym, aby nadaü aliasy tabeli

Employee

, poniewaĪ

uĪywamy jej dwa razy w tym samym zapytaniu.

NastĊpnie zbudujemy grupĊ w oparciu o dane kierownika (imiĊ i nazwisko) oraz nazwĊ
dziaáu. Dla kaĪdej z tych grup policzymy Ğrednią, minimalną i maksymalną wysokoĞü
zarobków.

Zapytanie bĊdzie wyglądaáo nastĊpująco:

SELECT
Manager.FirstName,
Manager.LastName,
Department.Name,
AVG(Employee.Salary) as 'ħrednie zarobki',
SUM(Employee.Salary) as 'Suma wypđat'
FROM Employee as Manager
INNER JOIN Department ON Manager.Id = Department.ManagerId
INNER JOIN Employee ON Department.Id = Employee.DepartmentId
WHERE Active = 1
GROUP BY Manager.FirstName, Manager.LastName, Department.Name

çwiczenie 4.30

Oblicz wysokoĞü Ğredniej páacy aktywnych pracowników w dziaáach. Wyniki ogra-
nicz tylko do tych dziaáów, w których Ğrednie zarobki przekraczają 1500 zá. Wyniki
posortuj wedáug nazwy dziaáu.

Oczekiwany wynik

Rysunek 4.33.
Oczekiwany wynik
üwiczenia 4.30

Poleć książkę

Kup książkę

156

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Rozwiñzanie

Aby znaleĨü wysokoĞü Ğredniej páacy aktywnych pracowników w poszczególnych dzia-
áach, napiszemy zapytanie:

SELECT Name, AVG(Salary) as Srednia_pensja
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
WHERE Active = 1
GROUP BY Name

Chcemy jednak ograniczyü te wyniki tylko do tych dziaáów, w których Ğrednia pensja
jest wiĊksza niĪ 1500 zá. Musimy wiĊc porównaü tĊ wartoĞü ze Ğrednią pensją w kaĪ-
dej grupie (w tym wypadku: w kaĪdym dziale).

Aby rozdzieliü warunki nakáadane na wiersze przed pogrupowaniem (sekcja

WHERE

)

od warunków nakáadanych na grupĊ, zostaáa utworzona nowa sekcja —

HAVING

. W tej

sekcji mamy dostĊpne tylko kolumny grup, czyli te kolumny, które zostaáy uĪyte do
grupowania, oraz funkcje agregujące (grupujące). Sekcja

HAVING

wystĊpuje w zapytaniu

po sekcji

GROUP BY

. Ostatnią sekcją zapytania jest sekcja

ORDER BY

.

Zapytanie bĊdzie wiĊc wyglądaáo nastĊpująco:

SELECT Name, AVG(Salary) as Srednia_pensja
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
WHERE Active = 1
GROUP BY Name
HAVING AVG(Salary) > 1500

ORDER BY Name

PoniewaĔ podczas przetwarzania zapytania sekcja HAVING jest wykonywana przed
sekcjñ SELECT, nie moĔemy w sekcji HAVING uĔyè aliasu, który zostaä nadany w sekcji
SELECT. Alias ten w tym momencie nie jest jeszcze znany. Próba wykonania poniĔszego
zapytania zakoþczyäaby siö wiöc bäödem:

SELECT AVG(Salary) as Srednia_pensja
FROM Employee
GROUP BY LastName
HAVING Srednia_pensja > 1500

çwiczenie 4.31

WyĞwietl miasta, w których znajduje siĊ wiĊcej niĪ jeden dziaá.

Oczekiwany wynik

Rysunek 4.34.
Oczekiwany wynik
üwiczenia 4.31

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

157

Rozwiñzanie

Aby otrzymaü informacjĊ, w jakim mieĞcie znajduje siĊ dany dziaá, musimy poáączyü
tabelĊ

Department

z tabelą

City

. Utworzymy grupy wedáug nazwy miasta i tak zdefi-

niujemy dla nich warunek, aby zostaáy wyĞwietlone tylko te grupy, które mają wiĊcej niĪ
jeden wiersz:

SELECT City.Name
FROM City
INNER JOIN Department ON Department.CityId = City.Id
GROUP BY City.Name
HAVING COUNT(*) > 1

çwiczenie 4.32

Policz Ğrednią wieku aktywnych pracowników w poszczególnych dziaáach. WyĞwietl
tylko te dziaáy, w których Ğrednia wieku jest wiĊksza niĪ 27 lat.

Oczekiwany wynik

Rysunek 4.35.
Oczekiwany wynik
üwiczenia 4.32

Rozwiñzanie

Aby policzyü wiek pracownika, uĪyjemy funkcji

DATEDIFF

, która policzy liczbĊ lat od

daty urodzenia pracownika do dnia dzisiejszego. Poáączymy tabelĊ

Employee

z tabelą

Department

i pogrupujemy wyniki wedáug nazwy dziaáu i obliczymy Ğrednią wieku.

W sekcji

HAVING

moĪemy równieĪ uĪywaü róĪnych obliczeĔ, moĪemy wiĊc uĪyü

funkcji

DATEDIFF

(która bĊdzie parametrem funkcji agregującej

AVG

), aby wyĞwietliü

tylko te grupy, gdzie Ğredni wiek pracownika jest wyĪszy niĪ 30 lat.

Zapytanie bĊdzie wyglądaü nastĊpująco:

SELECT Name, AVG(DATEDIFF(year,BirthDate,GETDATE())) as Sredni_wiek
FROM Department
INNER JOIN Employee ON Employee.DepartmentId = Department.Id
WHERE Active = 1
GROUP BY Name
HAVING AVG(DATEDIFF(year,BirthDate,GETDATE())) > 30

Poleć książkę

Kup książkę

158

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Zadania do samodzielnego wykonania

Zadanie 4.1

Policz wszystkie towary z tabeli

Product

.

Oczekiwany wynik

Rysunek 4.36.
Oczekiwany wynik
zadania 4.1

Zadanie 4.2

Policz wszystkie towary, których sugerowana cena (kolumna

Price

) jest niĪsza niĪ 100 zá.

Oczekiwany wynik

Rysunek 4.37.
Oczekiwany wynik
zadania 4.2

Zadanie 4.3

Policz wszystkie zamówienia (tabela

Orders

) záoĪone w 2015 roku (kolumna

OrderDate

).

Oczekiwany wynik

Rysunek 4.38.
Oczekiwany wynik
zadania 4.3

Zadanie 4.4

ZnajdĨ Ğrednią oraz najniĪszą i najwyĪszą sugerowaną cenĊ spoĞród wszystkich towarów.

Oczekiwany wynik

Rysunek 4.39.
Oczekiwany wynik
zadania 4.4

Poleć książkę

Kup książkę

Rozdziaä 4.

i Funkcje agregujñce

159

Zadanie 4.5

WyĞwietl Ğrednią cenĊ produktów z kategorii

Drive

.

Oczekiwany wynik

Rysunek 4.40.
Oczekiwany wynik
zadania 4.5

Zadanie 4.6

WyĞwietl áączną sumĊ wartoĞci wszystkich zamówieĔ záoĪonych w lutym 2015 roku.

Oczekiwany wynik

Rysunek 4.41.
Oczekiwany wynik
zadania 4.6

Zadanie 4.7

Policz sumĊ wartoĞci zamówieĔ wystawionych przez kobiety.

Oczekiwany wynik

Rysunek 4.42.
Oczekiwany wynik
zadania 4.7

Zadanie 4.8

WyĞwietl nazwy wszystkich grup produktów oraz liczbĊ produktów znajdujących siĊ
w danej grupie.

Oczekiwany wynik

Rysunek 4.43.
Oczekiwany wynik
zadania 4.8

Poleć książkę

Kup książkę

160

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

Zadanie 4.9

Dla kaĪdego pracownika wyĞwietl sumĊ wartoĞci záoĪonych przez niego zamówieĔ.
W wynikach nie uwzglĊdniaj pracowników, którzy nie záoĪyli Īadnego zamówienia.

Oczekiwany wynik

Rysunek 4.44.
Oczekiwany wynik
zadania 4.9

Zadanie 4.10

WyĞwietl imiĊ i nazwisko pracownika oraz sumĊ záoĪonych przez niego zamówieĔ
w 2015 roku. Wyniki ogranicz tylko do tych pracowników, dla których ta suma zamówieĔ
wynosi pomiĊdzy 2000 zá i 3000 zá.

Oczekiwany wynik

Rysunek 4.45.
Oczekiwany wynik
zadania 4.10

Zadanie 4.11

Dla kaĪdego towaru wyĞwietl áączną liczbĊ tych z nich, które zostaáy zamówione.

Oczekiwany wynik

Rysunek 4.46.
Oczekiwany wynik
zadania 4.11

Zadanie 4.12

Dla kaĪdego produktu, który byá kiedykolwiek zamówiony, wyĞwietl datĊ, kiedy staáo
siĊ to po raz pierwszy.

Poleć książkę

Kup książkę

Skorowidz

%, 28
*, 113
[], 29
[^], 29
_, 28

A

AND, 13
apostrof, 29
ASC, 35
ascending, 35
AVG, 133, 140

C

CASE, 223
CAST, 224
CONVERT, 224, 225
COUNT, 133
CROSS JOIN, 95, 118

D

DATEDIFF, 65, 78, 139, 157

day, 65
hour, 65
minute, 66
month, 65
second, 66
year, 65

DATENAME, 63

month, 63
weekday, 63

datepart, 65
DATEPART, 62

day, 62
month, 62
year, 62

DAY, 62
DESC, 35
descending, 35
DISTINCT, 39, 102

E

EXCEPT, 174, 185

F

format zapisu daty, 12
FROM, 7
FULL OUTER JOIN, 94, 117
funkcja YEAR, 62
funkcje

agregujące, 133
skalarne, 61
wbudowane, 61

G

GETDATE, 65, 78

H

HAVING, 156

I

INNER JOIN, 92, 98, 102
INTERSECT, 175
IS NOT NULL, 40
ISNULL, 42, 45, 98, 108

J

jĊzykiem deklaratywnym, 7
JOIN, 92

Poleć książkę

Kup książkę

288

SQL. Jak osiñgnñè mistrzostwo w konstruowaniu zapytaþ

K

klauzula

ORDER BY, 34
SELECT, 7

kolumna wyliczalna, 17
komunikat No column name, 17
konwersja typów, 224

L

LEFT JOIN, 117
LEFT OUTER JOIN, 93
LEN, 74, 139
LOWER, 75

M

MAX, 133
MIN, 133, 139
MONTH, 62

N

NULL, 40, 42

O

operacje na zbiorach, 173
operator, 10

AND, 13
BETWEEN, 24, 27
IN, 24, 25, 27
mniejszoĞci <=, 10
nierównoĞci <>, 10, 27
NOT, 27
OR, 13, 24
porównania =, 10
wiĊkszoĞci >=, 10

OR, 13
ORDER BY, 34, 79, 101

P

peáne záączenie zewnĊtrzne, 94, 117
podzapytania, 193
porządek

malejący, 35
rosnący, 35

R

RIGHT OUTER JOIN, 93

S

SELECT, 7, 10, 152
sáowo kluczowe

DISTINCT, 39
LIKE, 29

SQL, 7

skáadnia, 7

SUBSTRING, 73, 111
SUM, 133

T

TOP(N) PERCENT, 39
TOP(N) WITH TIES, 38
TOP(N), 37, 38, 39
TOP, 37, 39, 101
typ danych, 224

daty i czasu, 224

date, 224
datetime, 224
time, 224

numeryczne, 224

decimal, 224
int, 224

znakowe, 224

char, 224
nchar, 224
nvarchar, 224
varchar, 224

U

UNION ALL, 173, 177, 181
UPPER, 75

W

warunek, 13
WHERE, 7, 10
WITH TIES, 38, 39
wzorzec, 28

Y

YEAR, 62, 71

Z

záączenia, 91

krzyĪowe, 95
wewnĊtrzne, 92, 98, 102
zewnĊtrzne, 117

lewostronne, 93
prawostronne, 93

znak

%, 28
*, 113
[], 29
[^], 29
_, 28

Poleć książkę

Kup książkę