SQL. Grupowanie wierszy Group by, Having

Programowanie w języku Java

Tabela: pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
wydzial_id
pensja
prowizja

Grupowanie wierszy

Do tego momentu funkcje agregujące wykonywały swoje operacje traktując dane jako niezróżnicowane i jednolite typy informacji. Na przykład obliczanie średniej prowizji dla wszystkich pracowników dotyczyło czytania wszystkich wierszy w kolumnie prowizja. Jednak możemy przyjąć inny scenariusz,w którym potrzebne jest obliczanie średniej  prowizji dla pracowników każdego wydziału.

Klauzula Group by

Używając klauzuli GROUP BY możemy podzielić wiersze w tabeli ma małe grupy. I wtedy użyć funkcji agregujących do uzyskana wyników dla poszczególnych grup.

Zadanie 6: rozwiązanie
Oblicz średnią prowizję dla pracowników w poszczególnych wydziałach.

SELECT wydzial_id, AVG (pensja)
FROM pracownicy
GROUP BY wydzial_id;

Ponieważ nie ma klauzuli WHERE zwracane są wszystkie wiersze z tabeli pracownicy. Następnie wiersze są grupowane według wydzial_id, więc funkcja AVG jest stosowana do kolumny pensja obliczając średnią pensję dla każdego wydziału.

Uwaga. Kiedy używasz GROUP BY upewnij się iż wszystkie kolumny, które nie mają funkcji agregujących a są wymienione w SELECT, są zawarte w klauzuli GROUP BY.

Czasami możesz potrzebować wyświetlić wyniki dla grup bez grup.

Zadanie 7
Wyświetl całkowitą sumę wynagrodzeń, które są wypłacane poszczególnym stanowisko w poszczególnych wydziałach

SELECT wydzial_id, stanowisko_id, sum(pensja)
FROM pracownicy
GROUP BY wydzial_id, job_id;

Najpierw tabela pracowników podzielona jest ze względu na id_wydziału, a następnie każda z tych grup jest pogrupowana według stanowisk.
Zauważ,  że GROUP BY grupuje wiersze lecz nie gwarantuje uporządkowania wyników. Aby posortować grupowania użyj klauzuli ORDER BY.

SELECT wydzial_id, stanowisko_id, sum(pensja)
FROM pracownicy
GROUP BY wydzial_id, job_id
ORDER BY job_id;

Klauzula Group By:

  • wszystkie kolumny w klauzuli SELECT, które nie są użyte w funkcjach agregujących muszą być użyte w klauzuli GROUP BY
  • używając klauzuli WHERE można wyłączyć wiersze zanim przydzieli się je do grup. Nie można zatem użyć Where by ograniczyć grupy np:SELECT wydzial_id, AVG(pensja)
    FROM pracownicy
    WHERE AVG(pensja)>4000
    GROUP BY wydzial_id;
  • nie można używać aliasu kolumny w klauzuli GROUP BY np:
    SELECT wydział_id as wydz, AVG(pensja)
    FROM pracownicy
    GROUP BY wydz;

Poodbnie jak klauzuli WHERE, można użyć klauzuli having aby ograniczać grypy.

Klauzula HAVING

Zadanie 8

Wyświetl maksymalne pencsje w wydziłach, które są wyższe niż 8000.

Jak to zrobić? Należy znależć maksymalną prowizję dla każdego wydziału poprzez grupowanie według numeru wydziału. W tej systuacji nie możemy jednak zastosowac klauzuli Where (patrz punkty wyżej).

SELECT wydzial_id , MAX(pensja)
FROM pracownicy
GROUP BY wydział_id
HAVING MAX(pensja)>8000;

Jak wykonywane jest to zapytanie? Gdy używasz HAVING Serwer Oracle wykonuje następujące kroki:

  1. Najpierw wiersze są grupowane zgodnie z GROUP BY
  2. Następnie do grup zastowawna jest funkcja agregująca (MAX)
  3. Grupy, które spełniają krytera HAVING są wyświetlane

Zadanie 9
Wyświetl raport obliczający średnią pensję w każdym wydziale i wtedy wyświetlający maksymalną średnią pensję.

SELECT MAX(AVG(pensja))
FROM pracownicy
GROUP BY wydzial_id;

Zauważ, że klauzula GROUP BY jest obowiązkowa podczas zagnieżdżania funkcji grupujących.

SQL. Funkcje agregujące Avg, Count, Max, Min, Sum, Nvl

Tabela: pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
wydzial_id
pensja
prowizja

Zadanie 1
Wykonaj raport dotyczący minimalnej, maksymalnej i średniej pensji oraz sumie miesięcznych zarobków hadlowców z działu sprzedaży.

 

Funkcje agregujące

Wykonanie tego raportu wymaga zastosowania operatorów matematycznych na kolumnie pensja i wybranych rekordach tabeli pracownicy.

SQL udostępnia funkcje agregujące, które operują na zbiorze wierszy i zwracają jednen wynik dla grupy.

SELECT AVG(pensja), MAX(pensja), MIN(pensja), SUM(pensja)
FROM pracownicy
WHERE stanowisko_id Like ‚%HAND%’;

AVG
COUNT – zlicza ilość wartości nie NULL
MAX – zwraca maksymalną wartość ignorując wartości NULL
MIN – zwraca minimalną wartość ignorując wartości NULL
SUM – zwraca sumę wartości ignorując wartości NULL

Zadanie 2
Wyświetl pracownika, który pracuje w firmie najdłużej i najkrócej.

SELECT MIN(data_zatrudnienia), MAX(data_zatrudnienia)
FROM pracownicy;

Zadanie 3
Wyświetl liczbę pracowników, którzy otrzymują prowizję w wydziale nr 8

SELECT COUNT (prowizja_proc)
FROM pracownicy
WHERE wydzial_id=8;

Zadanie 4
Utwórz raport, który wyświetli liczbę wydziałów, które zatrudniają pracowników (czyli nie chcesz zliczyć oddziałów, które nie mają pracowników).

SELECT COUNT (DISTINCT wydzial_id)
FROM pracownicy;

Jak pamiętasz funkcje agregujące ignorują wartości NULL.

Zadanie 5
Oblicz średnią prowizję otrzymywaną przez pracowników

SELECT AVG(prowizja_proc)
FROM employess;

Powyższe zapytanie zwróci średnią dla pracowników, którzy otrzymują prowizję, a nie dla wszystkich pracowników. Pamiętaj, że funkcje agregujące ignorują wartości NULL. Jeśli chcesz otrzymać poprawną średnią użyj funkcji Nvl.

Aby zmusić funkcje agregujące do włączenia wartości NULL należy używać funkcji NVL.

Powyższe zapytanie powinno wyglądać tak:
SELECT AVG (NVL(prowizja_proc,0))
FROM pracownicy;

W tym zapytania dla każdego pracownika, który nie otrzymuje prowizji, podstawiana jest wartość 0. Średnia jest wtedy obliczana jako całkowita prowizja wypłacana wszystkim pracownikom podzielona przez ilość wszystkich pracowników w firmie. Średnia jest obliczana na podstawie wszystkich wierszy w tabeli, niezależnie od tego czy wartości null są przechowywane w kolumnie prowizja_proc.

Zadanie 6
Oblicz średnią prowizję dla pracowników w poszczególnych wydziałach.

Aby napisać to zapytanie potrzebujemy czegoś więcej niż tylko funkcji obliczającej średnią, czyli AVG. Musimy, w jakiś sposób wyodrębnić średnią dla pracowników poszczególnych wydziałów, a nie wszystkich pracowników firmy. W tym przypadku potrzebujemy funkcji grupujących wiersze. O funkcjach tych przeczytasz we wpisie Podstawy SQL. Grupowanie wierszy

 

 

 

 

PL/SQL. Rodzaje bloków

WSTĘP DO PL/SQL

Rodzaje bloków PL/SQL:

1. bloki anomimowe (nienazwane)

  • declared inline

2. bloki nazwane (podprogramy)

Blok anonimowy

Uruchamiamy program Oracle SQL Developer. Wpisujemy poniższy kod:

BEGINDBMS_OUTPUT.PUT_LINE(‚Hello World!’);END;
By wykonać kod wciskamy przycisk Run Script (zielona strzałka z kartką) lub wciskamy klawisz F5. Zobaczymy informację:


PL/SQL procedure successfully completed.

Aby wyświetlić efekt działania programu w oknie konsoli trzeba ustawić zmienną SERVEROUTPUT. W związku z tym przed BEGIN dodajemy

SET SERVEROUTPUT ON

Nasz program powinien wyglądać teraz tak:
SET SERVEROUTPUT ON
BEGINDBMS_OUTPUT.PUT_LINE(‚Hello World!’);
END;



W dolnym oknie Script Output powyżej informacji PL/SQL procedure successfully completed powinniśmy zobaczyć nasz napis Hello World!

SQL. Pobieranie danych z kilku tabel: natural join, join, using, using on



Tabele:
pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
kierownik_id

stanowiska
stanowisko_id
nazwa_stanowiska
kierownik_id
lokalizacja

Zadanie 1
Przygotuj raport o wszystkich pracownikach pracujących w firmie i  ich stanowiskach.

Informacje o pracownikach i ich stanowiskach są umieszczone są w rożnych tabelach. Należy napisać zapytanie SQL pobierające dane z dwóch różnych tabel używając połączeń JOINS. To zapytanie będzie pobierało dane stanowisko_id z tabeli pracownicy a nazwa_stanowiska z tabeli Stanowiska używając stanowisko_id jak kolumnę wspólną.

Wynikiem zapytania będzie pojedynczy raport zawierający następujące kolumny: pracownik_id, stanowisko_id, nazwa_stanowiska

Istnieje kilka typów połączeń JOINS, których możemy używać:

  1. połączenia naturalne z klauzulą NATURAL JOIN
  2. Samopołączenia
  3. Non equijon
  4. outer joins:
    left outer joins
    right outer joins
    pelne outer joins
  5. cross joins

NATURAL JOIN

SELECT pracownik_id, imie, stanowisko_id, nazwa_stanowiska

FROM pracownicy NATURAL JOIN stanowiska;

Zauważmy iż tabela stanowiska jest połaczona z pracownicy poprzez stanowisko_id, które jest jedyną kolumną o tej samej nazwie w obu tabelach. Jeśli są inne wspólne pola połaczenie użyje ich wszystkich.
Możemy dodać ograniczenie używając klauzuli WHERE.

Zadanie 2
Raport nazw wydziału, w których zatrudnieni są pracownicy.

 

USING

Tabele:
pracownicy
pracownik_id
imie
nazwisko
stanowisko_id
kierownik_id

wydzialy
wydzial_id
wydzial_nazwa
kierownik_id
lokalizacja

Chcemy stalenić wydziały pracowników. Aby to wykonac należy porównać wartości w kolumnie stanowisko_id w tabeli stanowiska ze stanowisko_id w tabeli pracownicy. Jeśli wspólnymi kolumnami są dwie kolumny stanowisko_id oraz kierownik_id, naturalne połączenie użyje obuj tych kolumn do pokazania wyniku. Ale my chcemy połączyć te tabele za pomocą tylko kolumny stanowisko_id. Aby to wykonać musimy użyć klauzuli USING by podać tylko jedną kolumnę, która ma zostac użyta do połączenia.
Relacja pomiędzy tabelą pracownicy i stanowiska to wiele-do-jednego. Wartość w stanowisko_id kolulmnie w obu tabelach musi być taka sama. Częśto taki typ połączeń obejmuje klucz głóny i obcy.


SELECT pracownik_id, nazwisko, nazwa_wydzialu, wydzial_id
FROM  pracownicy JOIN wydzialy USING (wydzial_id)

W tym zapytaniu kolumna wydzial_id w tabelach pracownicy i wydziały są połączone, a zatem nazwa_wydzialu gdzi epracownik pracuje zostala wyświetlona.
Kolumny pracownik_id i nazwisko należą do tabeli pracownicy, podczas gdy nazwa_wydzialu należy do tabeli wydziału – i wydzial_id należy do obu tych tabel. Dlatego używanie przedrostka z nazwą tabel  rozwiązuje niejednoznaczność i zwiększa szybkość wykonywania zapytania – gdyż mówi serverowi Oracle gdzie znajdzie kolumnę. Jednak określanie nazw kolumn używając nazw tabel może być czasochołonee, szczególnie gdy nazwy tabel są długie np


zbyt_dluga_nazwa_tabeli.nazwa_kolumny

Zamiast tego można używać aliasów tabel. tak jak alias kolumny daje kolumnie nowa nazwę, tak alias tabeli daje tabeli inną nazwę np

t.nazwa_klumny

Aliasy tabel pomagają zmiejszyć kod SQL.

Pamiętaj że nie możesz określić kolumny, która jest użwana w klauzuli USING. Na przykład to zapytanie spowoduje wyświetlenie błędu:

SELECT l.miasto.d.nazwa_wydzialu
FROM lokalizacje l JOIN wydziały d
USING (lokalizacja_id)
WHERE d.lokalizacja_id =1400;

Nie można użyć aliasu  kolumny lokalizacja_id  w klauzui WHERE ponieważ kolumna jest użyta w klauzuli USING. Zapytanie powinno wyglądać tak:

SELECT l.miasto.d.nazwa_wydzialu, d.kierownik_id
FROM lokalizacje l JOIN wydziały d
USING (lokalizacja_id)
WHERE lokalizacja_id =1400;


Zauważ, że kierownik_id jest obecne w obu tabelach zarówno pracownicy jak i wydzialy. Jeśli kierownik_id nie będzie miał prefixu tabeli to server wyświetli błąd.



USING ON

Można zastąpić klauzulę USING na klauzulę ON  by uszczegółowić warunek połączenia. Zaletą używania klauzuli ON jest możliwość uszczegółowienia bezwzględnego warunku lub sprecyzowania kolumn do połączenia.


Na przykład, aby złączyć taelę pracownicy i wydziały poprzez kolumnę wydzial_id można napisać takie zapytanie SQL:

SELECT p.pracownicy, p.nazwisko,p.wydzial_id. w. wydzial_id, w.lokazliacja_id
FROM pracownicy p JOIN wydzialy d
ON (p.wydzial_id=w.wydzial_id);

W zapytaniu jeśli wydzial_id w tabeli pracownicy jestrówny z wydział_id w tabeli wydziały, wiersz jest zwracany


ON (p.wydzial_id=w.wydzial_id);


Użycie aliasu tabeli jest konieczne aby określić pasujące kolumny.  


Zadanie 3
Wyświetl raport o wszystkich pracownikach, ich wydziałach i miastach w których się mieszczą.


W tym celu musimy połączyć trzy tabele: pracownicy, wydzialy, lokalizacje


SELECT pracownik_id, city, nazwa_wydzialu
FROM pracownicy p
JOIN wydzialy w
ON w.wydzial_id = p.wydzial_id
JOIN lokalizacje l
ON w.lokalizacja_id = l.lokalizacja_id;


Pierwsze połączenie, ktore jest wykonywane to : pracownicy JOIN wydzialy:


FROM pracownicy p
JOIN wydzialy w
ON w.wydzial_id = p.wydzial_id


Pierwszy warunek połaczenia może odnosić się do kolumn w tabelach pracownicy i wydzialu al enie może odnosić się do kolumn z tabeli lokalizacje.

Drugie połączenie może odnosić się do kolumn z wszsytkich trzech tabel:

JOIN lokalizacje l
ON w.lokalizacja_id = l.lokalizacja_id;

Uwaga. Przykładowy kod można również napisać za pomocą klauzuli USING.

Zadanie 4
Przygotuj raport o numerach lokazlizacji wszystkich pracowników których kirownik ma numer 12.

SELECT p.pracownik_id, p.nazwisko, p.wydzial_id, w.wydzial_id, w.lokazacja_id
FROM pracownicy p JOIN wydzialy w
ON (p.wydzial_id = w.wydzial_id)
AND p.kierownik_id=12;

Do połączenia dodane zostały dodatkowe warunki. Zapytanie pokazuje wykonane połączenie na tabeli pracownicy i wydziały i dodatkowo wyświetla

FROM pracownicy p JOIN wydzialy tylko pracowników których kierowniki ma numer 12

Jako alternatywę możesz też użyć WHERE aby zastosować dodatkowe warunki. Wynik zapytania będzie taki sam

SELECT p.pracownik_id, p.nazwisko, p.wydzial_id, w.wydzial_id, w.lokazacja_id
FROM pracownicy p JOIN wydzialy w
ON (p.wydzial_id = w.wydzial_id)
WHERE p.kierownik_id=12;