Lekcja 17. Podzapytania jednowierszowe.

Podzapytania jednowierszowe zwracają do zewnętrznej instrukcji SQL zero lub jeden wiersz. Podzapytanie możemy umieścić w klauzuli WHERE, klauzuli HAVING lub klauzuli FROM instrukcji SELECT.

Podzapytanie w klauzuli WHERE

Podzapytanie można umieścić w klauzuli WHERE innego zapytania. Klauzula WHERE poniższego zapytania zawiera podzapytanie. Należy zauważyć, że zostało ono umieszczone w nawiasach okrągłych. Ten przykład pobiera wartości pól first_name i last_name z wiersza tabeli employee, w którym ­last­_name ma wartość Forest

Podzapytanie w klauzuli WHERE ma postać :

To zapytanie jest uruchamiane jako pierwsze (i tylko jeden raz) i zwraca emp_no dla wiersza, w którym last­­­_name na wartość Forest. Wartość emp­­_no w tym wierszu wynosi 9 i jest ona przysłana do klauzuli WHERE zewnętrznego zapytania, dlatego też zwraca ono ten sam wynik co poniższe:

Użycie innych operatorów jednowierszowych

W klauzuli WHERE podzapytania przestawionego na początku był wykorzystywany operator równości (=). W podzapytaniach jednowierszowych można również używać innych operatorów porównania, takich jak <>, <, >, <= i >= .

W poniższym przykładzie w klauzuli WHERE zewnętrznego zapytania użyto operatora > . W podzapytaniu zastosowano funkcję AVG() do obliczania średniego wynagrodzenia pracowników, która jest przesłana do klauzuli WHERE zewnętrznego zapytania. Całe zapytanie zwraca emp­­_no, first­_name, last_name i salary pracowników, których wynagrodzenie jest większe od średniego wynagrodzenia wszystkich pracowników:

 

Poniższy przykład przestawia uruchomienie samego podzapytania:

 

Wartość 385796,85 zwrócona przez podzapytanie jest wykorzystywana w klauzuli WHERE zewnętrznego zapytania, w związku z czym jest ono tożsame z poniższym:

Podzapytanie w klauzuli HAVING

W klauzuli HAVING zewnętrznego zapytania możemy umieścić podzapytanie. To pozwala na filtrować wyniki grupy wierszy na podstawie wyników zwracanych przez podzapytanie.

W poniższym przykładzie użyto podzapytania w klauzuli HAVING zewnętrznego zapytania. Przykład pobiera kod (job_code) oraz średnie wynagrodzenie pracowników o danym kodzie, jeżeli jest ono większe niż średnie wynagrodzenie wszystkich pracowników:

 

Zauważmy, że podzapytanie oblicza najpierw średnie wynagrodzenie wszystkich pracowników za pomocą funkcji AVG(). Przeanalizujmy przykład, aby zrozumieć działanie tego zapytania. Oto wynik uruchomienia podzapytania: 

 

Wartość 385796,85 jest wykorzystywana w klauzuli HAVING zewnętrznego zapytania do odfiltrowania tylko wierszy grup, w których średnia wynagrodzeń jest większa od 385796,85. Poniższe zapytanie przestawia wersję zewnętrznego zapytania, która pobiera kod oraz średnie wynagrodzenie pracowników pogrupowanych według job_code:

 

Średnie wynagrodzenie większe niż niż 385796,85 występują w grupach z job_code równym Eng i SRep . Zgodnie z oczekiwaniami te same grupy zostały zwrócone przez zapytanie przestawione na początku.

Podzapytanie w klauzuli FROM

Podzapytanie można umieścić w klauzuli FROM zewnętrznego zapytania. Tego typu podzapytania nazywamy widokami wbudowanymi. Poniższy, prosty przykład pobiera pracowników, dla których emp_no jest mniejsze od 10:

 

Podzapytanie zwraca do zapytania zewnętrznego wiersze z tabeli employee, w których emp_no ma wartość mniejszą od 10. Zapytanie zewnętrzne pobiera i wyświetla te wartości emp_no. Z perspektywy klauzuli FROM zewnętrznego zapytania wyniki podzapytania są po prostu kolejnym źródłem danych. 

Kod źródłowy

Lekcja 15. Grupowanie wierszy.

Czasami chcemy pogrupować wiersze tabeli i uzyskać jakieś informacje o nich. Na przykład możemy chcieć uzyskać średnie wynagrodzenie pracowników w poszczególnych krajach.

Grupowanie wierszy za pomocą klauzuli GROUP BY

Klauzula GROUP BY grupuje wiersze w bloki ze wspólną wartością jakiejś kolumny. Na przykład poniższe zapytanie grupuje wiersze tabeli employee w bloki z tą samą wartością job_country:

Należy zauważyć, że w wynikach znajduje się tylko jeden wiersz dla każdego bloku wierszy z tą samą wartością job_country.

Używanie wielu kolumn w grupie

W klauzuli GROUP BY można określić kilka kolumn. Na przykład poniższe zapytanie zawiera w klauzuli GROUP BY  kolumny job­_grade i job_country z tabeli employee:

Używanie funkcji agregujących z grupami wierszy

Do funkcji agregującej można przesłać bloki wierszy. Wykona ona obliczenia na grupie wierszy z każdego bloku i zwróci jedną wartość dla każdego bloku. Na przykład aby uzyskać liczbę wierszy z tą samą wartością job­­­_country w tabeli employee, musimy:

•          pogrupować wiersze w bloki z tą samą wartością job_country­ za pomocą GROUP BY
•         zliczyć wiersze w każdej grupie za pomocą funkcji COUNT(job_country­)

Demonstruje to poniższe zapytanie:

W zestawie wyników widzimy, że w jednym wierszu job­_country ma wartość Canada, trzy wiersze mają wartość ­job­_country równą England itd.

Przejdźmy do przykładu z początku lekcji. Aby uzyskać średnie wynagrodzenie pracowników w poszczególnych krajach musimy:

•          za pomocą klauzuli GROUP BY pogrupować wiersze z tabeli employee w bloki z tą samą wartością job_country
•         za pomocą funkcji AVG(salary) obliczyć średnie wynagrodzenie w każdym bloku wierszy

Demonstruje to poniższe zapytanie:

Z klauzulą GROUP BY możemy używać dowolnych funkcji agregujących. Na przykład poniższe zapytanie pobiera sumę wynagrodzeń pracowników z poszczególnych krajów:

Warto zaznaczyć, że nie musimy umieszczać kolumn wykorzystywanych w klauzuli GROUP BY bezpośrednio w instrukcji SELECT. Ponadto wywołanie funkcji agregującej można również umieścić w klauzuli ORDER BY. 
Oba przypadki pokazuje poniższe zapytanie:

Kod źródłowy

Lekcja 14. Funkcje agregujące.

Funkcje prezentowane do tej pory operują na pojedynczych wierszach i zwracają jeden wiersz wyników dla każdego wiersza wejściowego. W tym podrozdziale poznamy funkcje agregujące, które operują na grupie wierszy i zwracają jeden wiersz wyników. Funkcje agregujące są czasem nazywane grupującymi, ponieważ operują na grupach wierszy.

Funkcja	Opis
AVG(x)	Zwraca średnią wartość x
COUNT(x)	Zwraca liczbę wierszy zawierających x, zwróconych przez zapytanie
LIST(x)	Zwraca ciąg oddzielonych separatorem wartości x
MAX(x)	Zwraca maksymalną wartość x
MIN(x)	Zwraca minimalną wartość x
SUM(x)	Zwraca sumę x

Najważniejsze właściwości funkcji agregujących:

• Funkcje agregujące mogą być używane z dowolnymi, prawidłowymi wyrażeniami. Na przykład funkcje COUNT(), MAX() I MIN() mogą być używane z liczbami napisami i datami.
• Wartość NULL jest ignorowana przez funkcje agregujące, ponieważ wskazuje, że wartość jest nieznana i z tego powodu nie może zostać użyta w funkcji.
• Wraz z funkcją agregującą można użyć słowa kluczowego DISTINCT, aby wykluczyć z obliczeń powtarzające się wpisy.

AVG

Funkcja AVG(x) oblicza średnią wartość x. Poniższe zapytanie zwraca średnią zarobków pracowników. Należy zwrócić uwagę, że do funkcji AVG() jest przesyłana kolumna salary z tabeli employee:

Funkcje agregujące mogą być używane z dowolnymi prawidłowymi wyrażeniami. Na przykład poniższe zapytanie przesyła do funkcji AVG() wyrażenie salary + 10000. Na skutek tego do wartości salary w każdym wierszu jest dodawane 10000, a następnie jest obliczana średnia wyników:

W celu wyłączenia z obliczeń identycznych wartości można użyć słowa kluczowego DISTINCT. Na przykład w poniższym zapytaniu użyto go do wyłączenia identycznych wartości z kolumny salary podczas obliczania średniej za pomocą funkcji AVG():

Należy zauważyć, że w tym przypadku średnia jest nieco wyższa niż wartość zwrócona przez pierwsze zapytanie prezentowane na samym początku. Jest tak dlatego ponieważ wartości 111262.2 , 69482.63 oraz 35000,00 występują w kolumnie salary dwukrotnie. Dublujące się wartości uznawane są za duplikat i wyłączone są z obliczeń wykonywanych przez funkcję AVG().

COUNT

Funkcja COUNT(x) oblicza liczbę wierszy zwróconych przez zapytanie. Poniższe zapytanie zwraca liczbę wierszy w tabeli employee, korzystając z funkcji COUNT():

LIST
Funkcja LIST(x) zwraca ciąg oddzielonych przecinkiem wartości x. Poniższe zapytanie zwraca nazwy państw pochodzące z kolumny job_country z tabeli employee, korzystając z funkcji LIST():

Opcjonalnie można przesłać opcjonalny parametr określający separator:

MAX I MIX

Funkcja MAX(x) i MIN(x) zwracają maksymalną i minimalną wartość x. Poniższe zapytanie zwraca maksymalną i minimalną wartość z kolumny salary tabeli employee, korzystając z funkcji MAX() i MIN():

 

Funkcje MAX() i MIN() mogą być używane ze wszystkimi typami danych, włączenie z napisami i datami. Gdy używamy MAX() z napisami, są one porządkowane alfabetycznie, z „maksymalnym” napisem umieszczonym na dole listy i „minimalnym” napisem umieszonym na górze listy. Poniższy przykład pobiera „minimalny” i „maksymalny” napis z kolumny first_name tabeli employee, korzystając MAX() i MIN():

W przypadku dat „maksymalną” datą jest najpóźniejszy moment, „minimalną” – najwcześniejszy. Poniższe zapytanie pobiera maksymalną i minimalną wartość z kolumny hire_date tabeli employee, korzystając z funkcji MAX() i MIN():

SUM

Funkcja SUM(x) dodaje wszystkie wartości w x i zwraca wynik. Poniższe zapytanie zwraca sumę z kolumny salary tabeli employee, korzystając z funkcji SUM():

Kod źródłowy