BD1 - Labs

Plan zajęć

Funkcje agregujące i kauzule GROUP BY oraz HAVING
Operator algebry relacyjnej - iloczyn kartezjański
Złączenia relacji - operator JOIN

Polecenie SELECT

Polecenie ( instrukcja) SELECT umozliwia pobranie danych z bazy danych. W ramach polecenia wyróżniamy odpowiednie części opisane przez słowa kluczowe - klauzule - realizujące określone działania. Poniżej pełna wersja polecenia.

  SELECT   [DISTINCT] nazwy kolumn, wyrażenia arytmetyczne, funkcje
  FROM     nazwy tabel lub widoków
  WHERE    warunek (wybieranie wierszy)
  GROUP BY nazwy kolumn
  HAVING   warunek (grupowanie wybieranych wierszy)
       [UNION, INTERSECT, EXCEPT] operacje na zbiorach, służą do łączenia wyników wielu zapytań
  ORDER BY nazwy kolumn lub pozycje kolumn  [ ASC | DESC]

Klauzula SELECT wskazuje listę kolumn, które zostaną zwrócone w tabeli wynikowej.
Klauzula FROM jest odpowiedzialna za wskazanie tabel z których bedą pobierane dane.
Klauzula WHERE określa warunki, jakie mają być spełnione, aby rekord (krotka) pojawiła się w tabeli wynikowej.
Klauzula GROUP BY grupuje wiersze tablicy wynikoweej według identycznych wartości w kolumnach o podanych nazwach.
Klauzula HAVING określa warunki dla rekordów uzyskanych w wyniku przetworzenia klazulą GROUP BY.
Klauzula ORDER BY określa sposób uporządkowania tabeli wynikowej.

Do prezentacji możliwości polecenia SELECT wykorzystamy bazę KURS w której do tabeli UCZEST_KURS dodaliśmy dodatkowe atrybuty: oplata - DECIMAL i ocena - DECIMAL. Diagram ERD zmodyfikowanej bazy został przedstawiony na rys.1. Odpowiednie polecenia SQL i dane zawarte są skrypcie kurs_update.sql.

Lab04_img01 — Rys.1 Diagram ERD dla bazy danych KURS v.2

Skrypt kurs_update.sql ( [listing dokumentu] [link do dokumentu] )

   

alter table uczest_kurs add oplata decimal(8,2) ;
alter table uczest_kurs add ocena decimal(5,2) ;

-- wstawienie danych - tabela uczest_kurs - uczestnicy na kursach
-- kurs 1 - angielski 1 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=1 and id_uczest=1;
update uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=1 and id_uczest=3;
update uczest_kurs set ( oplata, ocena ) = ( 500., 5 )  where  id_kurs=1 and id_uczest=5;
update uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=1 and id_uczest=7;
update uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=1 and id_uczest=8;
update uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=1 and id_uczest=10;
update uczest_kurs set ( oplata, ocena ) = ( 500., 5 )  where  id_kurs=1 and id_uczest=11;
update uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=1 and id_uczest=12;
-- kurs 2 - angielski 1 gr 2
update uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=2 and id_uczest=2;
update uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=2 and id_uczest=16;
update uczest_kurs set ( oplata, ocena ) = ( 500., 5 )  where  id_kurs=2 and id_uczest=17;
update uczest_kurs set ( oplata, ocena ) = ( 500., 3 )  where  id_kurs=2 and id_uczest=18;
update uczest_kurs set ( oplata, ocena ) = ( 500., 4 )  where  id_kurs=2 and id_uczest=20;
-- kurs 3 - angielski 2 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=3 and id_uczest=1;
update uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=3 and id_uczest=2;
update uczest_kurs set ( oplata, ocena ) = ( 700., 5 )  where  id_kurs=3 and id_uczest=3;
update uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=3 and id_uczest=5;
update uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=3 and id_uczest=7;
update uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=3 and id_uczest=17;
update uczest_kurs set ( oplata, ocena ) = ( 700., 5 )  where  id_kurs=3 and id_uczest=18;
update uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=3 and id_uczest=20;
-- kurs 4 - angielski 3 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=4 and id_uczest=1;
update uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=4 and id_uczest=2;
update uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=4 and id_uczest=3;
update uczest_kurs set ( oplata, ocena ) = ( 800., 3 )  where  id_kurs=4 and id_uczest=5;
update uczest_kurs set ( oplata, ocena ) = ( 800., 3 )  where  id_kurs=4 and id_uczest=21;
update uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=4 and id_uczest=22;
update uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=4 and id_uczest=25;
-- kurs 5 - angielski 4 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 850., 4 )  where  id_kurs=5 and id_uczest=1;
update uczest_kurs set ( oplata, ocena ) = ( 850., 4 )  where  id_kurs=5 and id_uczest=2;
update uczest_kurs set ( oplata, ocena ) = ( 850., 4 )  where  id_kurs=5 and id_uczest=3;
update uczest_kurs set ( oplata, ocena ) = ( 850., 3 )  where  id_kurs=5 and id_uczest=5;
update uczest_kurs set ( oplata, ocena ) = ( 850., 3 )  where  id_kurs=5 and id_uczest=21;
update uczest_kurs set ( oplata, ocena ) = ( 850., 5 )  where  id_kurs=5 and id_uczest=22;
-- kurs 6 - niemiecki 1 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=6 and id_uczest=8;
update uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=6 and id_uczest=9;
update uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=6 and id_uczest=13;
update uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=6 and id_uczest=15;
update uczest_kurs set ( oplata, ocena ) = ( 600., 5 )  where  id_kurs=6 and id_uczest=19;
update uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=6 and id_uczest=24;
update uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=6 and id_uczest=27;
-- kurs 7 - niemiecki 1 gr 2
update uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=7 and id_uczest=11;
update uczest_kurs set ( oplata, ocena ) = ( 600., 4 )  where  id_kurs=7 and id_uczest=17;
update uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=18;
update uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=23;
update uczest_kurs set ( oplata, ocena ) = ( 600., 5 )  where  id_kurs=7 and id_uczest=25;
update uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=28;
update uczest_kurs set ( oplata, ocena ) = ( 600., 3 )  where  id_kurs=7 and id_uczest=30;
-- kurs 8 - niemiecki 2 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 650., 4 )  where  id_kurs=8 and id_uczest=8;
update uczest_kurs set ( oplata, ocena ) = ( 650., 4 )  where  id_kurs=8 and id_uczest=9;
update uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=13;
update uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=15;
update uczest_kurs set ( oplata, ocena ) = ( 650., 5 )  where  id_kurs=8 and id_uczest=19;
update uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=24;
update uczest_kurs set ( oplata, ocena ) = ( 650., 3 )  where  id_kurs=8 and id_uczest=27;
-- kurs 9 - niemiecki 3 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=9 and id_uczest=8;
update uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=9 and id_uczest=9;
update uczest_kurs set ( oplata, ocena ) = ( 800., 4 )  where  id_kurs=9 and id_uczest=13;
update uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=9 and id_uczest=24;
update uczest_kurs set ( oplata, ocena ) = ( 800., 5 )  where  id_kurs=9 and id_uczest=27;
-- kurs 10 - hiszpanski 1 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=6;
update uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=16;
update uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=18;
update uczest_kurs set ( oplata, ocena ) = ( 700., 4 )  where  id_kurs=10 and id_uczest=22;
update uczest_kurs set ( oplata, ocena ) = ( 700., 5 )  where  id_kurs=10 and id_uczest=24;
update uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=10 and id_uczest=29;
update uczest_kurs set ( oplata, ocena ) = ( 700., 3 )  where  id_kurs=10 and id_uczest=30;
-- kurs 11 - hiszpanski 2 gr 1
update uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=6;
update uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=16;
update uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=18;
update uczest_kurs set ( oplata, ocena ) = ( 900., 4 )  where  id_kurs=11 and id_uczest=22;
update uczest_kurs set ( oplata, ocena ) = ( 900., 5 )  where  id_kurs=11 and id_uczest=24;
update uczest_kurs set ( oplata, ocena ) = ( 900., 3 )  where  id_kurs=11 and id_uczest=29;
update uczest_kurs set ( oplata, ocena ) = ( 900., 3 )  where  id_kurs=11 and id_uczest=30;

Funkcje agregujące i kauzule GROUP BY oraz HAVING

Funkcje agregujące w przeciwieństwie do funkcji skalarnych ( działające w ramach jednej krotki) umożliwiają przetworzenie określonej grupy krotek i opracowanie wyniku zgodnie z ich funkcjonalnością.

Standardowe funkcje agregujące dostępne w języku SQL.

COUNT() - zwraca liczbę krotek
SUM() - zwraca sumę wartości dla zadanego atrybutu dla wybranej grupy krotek
AVG() - zwraca średnią arytmetyczną dla zadanego atrybutu dla wybranej grupy krotek
MIN() - zwraca wartość minimalną dla zadanego atrybutu dla wybranej grupy krotek
MAX() - zwraca wartość maksymalną dla zadanego atrybutu dla wybranej grupy krotek

Funkcje agregujące mogą działać na wszystkich krotkach relacji lub na wybranych zgodnie z określonym kryterium wykorzystując klauzalę GROUP BY. Zwrócone wyniki możemy dodatkowo przefiltrować wykorzystując słowo kluczowe HAVING.

Przykładowe użycie funkcji agregujących oraz klauzul GROUP BY i HAVING zostanie przedstawione poniżej dla danych zawartych w bazie KURS.

Liczba wszystkich krotek w relacji Uczestnik - funkcja agregująca COUNT().

SELECT COUNT(*) FROM uczestnik ;

Suma wszyskich wpłat - relacja uczest_kurs - funkcja agregująca SUM()

SELECT SUM(oplata) FROM uczest_kurs ;

Wartość średnia wpłat - relacja uczest_kurs - funkcja agregująca AVG()

SELECT AVG(oplata) FROM uczest_kurs ;

Wartość maksymalnej wpłaty - relacja uczest_kurs - funkcja agregująca MAX()

SELECT MAX(oplata) FROM uczest_kurs ;

Wartość minimalnej wpłaty - relacja uczest_kurs - funkcja agregująca MIN()

SELECT MIN(oplata) FROM uczest_kurs ;

Liczba wpłat według kwoty - relacja uczest_kurs - funkcja agregująca COUNT() i klauzula GROUP BY

SELECT oplata, COUNT(*) FROM uczest_kurs GROUP BY oplata ;

Liczba osób na poszczególnych kursach - relacja uczest_kurs - funkcja agregująca COUNT() i klauzula GROUP BY

SELECT id_kurs, COUNT(*) FROM uczest_kurs GROUP BY id_kurs ORDER BY 1 ;

Lista z liczbą kursów dla każdego uczestnika ( nazwisko, liczba ) - relacje uczestnik i uczest_kurs - funkcja agregująca COUNT() i klauzula GROUP BY.

SELECT nazwisko, count(*) FROM uczest_kurs, uczestnik WHERE id_uczest = id_uczestnik 
GROUP BY nazwisko ORDER BY 1;

Lista osób które uczestniczyły w co najmniej 4 kursach ( nazwisko, liczba ) - relacje uczestnik i uczest_kurs - funkcja agregująca COUNT() i klauzula GROUP BY posrotowane od największej liczby kursów i nazwiska zgodnie z alfabetem.

SELECT nazwisko, count(*) FROM uczest_kurs, uczestnik WHERE id_uczest = id_uczestnik 
GROUP BY nazwisko HAVING count(*) > 3 ORDER BY 2 DESC, 1;

Złączenia relacji - operator JOIN

Złączenie relacji daje w wyniku relację składającą się ze wszystkich możliwych krotek, które są kombinacjami dwu krotek, po jednej z każdej ze wskazanych relacji, takich że mają tę samą wartość wspólnego atrybutu. Do realizacji złączeń wykorzytywany jest operator JOIN. W ramach złączenia istnieją nastepujące rozwiązania: złaczenie naturalne (INNER JOIN) zgodne z równozłączeniem, złączenie typu (CROSS JOIN) zwane złączeniem krzyżowym (iloczyn relacji) oraz złączenie zewnętrzne (lewostronne, prawostronne i pełne) niedostępne z poziomu łączenia tabel z wykorzystaniem kluzuli WHERE.

Złączenie naturalne INNER JOIN (rys.2). Poniżej dwa przykłady złączenia INNER JOIN pomiędzy relacjami Kurs i Kurs_opis oraz relacjami Uczestnik, uczest_kurs, Kurs i Kurs_opis.

Lab04_INNER_JOIN — Rys.2 Złączenie [ INNER ] JOIN umożliwia połączenie rekordów z dwóch tabel ( relacji ),jeżeli w obu tabelach występują rekordy z tą samą wartością atrybutu (atrybutów) złączenia.

SELECT { atrybuty } 
FROM relacja1 R1 [INNER] JOIN relacja2 R2 ON R1.id = R2.id;

SELECT Kurs.id_kurs, Kurs.id_grupa, Kurs_opis.opis
FROM Kurs INNER JOIN Kurs_opis ON Kurs.id_nazwa = Kurs_opis.id_nazwa ;

SELECT Uczestnik.nazwisko, Uczestnik.imie, Kurs_opis.opis, Kurs.id_kurs, Kurs.id_grupa
FROM Kurs JOIN uczest_kurs 
            ON Kurs.id_kurs = uczest_kurs.id_kurs 
          JOIN Uczestnik 
            ON Uczestnik.id_uczestnik = uczest_kurs.id_uczest 
          JOIN Kurs_opis
            ON Kurs.id_nazwa = Kurs_opis.id_nazwa ;

Lewostronne złączenie zewnętrzne LEFT OUTER JOIN (rys.3). Przykładowe złączenie: imię i nazwiska wszystkich wykładowców oraz kursy prowadzone przez nich. W ramach złączenia wykorzystamy cztery relacje: Wykladowca, wykl_kurs, Kurs i Kurs_opis.

SELECT { atrybuty } 
FROM relacja1 R1 LEFT [OUTER] JOIN relacja2 R2 ON R1.id = R2.id;

SELECT w.nazwisko, w.imie, ko.opis
FROM wykladowca w LEFT JOIN wykl_kurs wk ON w.id_wykladowca = wk.id_wykl
                  LEFT JOIN kurs k USING (id_kurs)
                  LEFT JOIN kurs_opis ko USING (id_nazwa)
ORDER BY w.nazwisko ;

Prawostronne złączenie zewnętrzne RIGHT OUTER JOIN (rys.4). Przykładowe złączenie: imię i nazwiska wykładowców, którzy mają kursy oraz wszystkie kursy dostępne w ramach bazy. W ramach złączenia wykorzystamy cztery relacje: Wykladowca, wykl_kurs, Kurs i Kurs_opis.

Lab04_RIGHT_JOIN — Rys.4 Złączenie RIGHT [ OUTER ] JOIN umożliwia połączenie rekordów z dwóch tabel ( relacji ). Początkowo działa jak INNER JOIN a następnie do tabeli wynikowej dodaje wszystkie rekordy tabeli prawej które nie mają odpowiednich rekordów w lewej tabeli. Wartości atrybutów z lewej tabeli otrzymują wartość NULL.

SELECT { atrybuty } 
FROM relacja1 R1 RIGHT [OUTER] JOIN relacja2 R2 ON R1.id = R2.id;

SELECT w.nazwisko, w.imie, ko.opis
FROM wykladowca w RIGHT JOIN wykl_kurs wk ON w.id_wykladowca = wk.id_wykl
                  RIGHT JOIN kurs k USING (id_kurs)
                  RIGHT JOIN kurs_opis ko USING (id_nazwa)
ORDER BY ko.opis ;

Pełne złączenie zewnętrzne FULL OUTER JOIN (rys.5). Przykładowe złączenie: imię i nazwiska wszystkich wykładowców oraz wszystkie kursy dostępne w ramach bazy. W ramach złączenia wykorzystamy cztery relacje: Wykladowca, wykl_kurs, Kurs i Kurs_opis.

Lab04_FULL_JOIN — Rys.5 Złączenie FULL [ OUTER ] JOIN umożliwia połączenie rekordów z dwóch tabel ( relacji ). Początkowo działa jak INNER JOIN a następnie do tabeli wynikowej dodaje wszystkie rekordy tabeli prawej i lewej, które nie mają odpowiednich rekordów w odpowiednich tabelach. Wartości atrybutów w dołaczanych rekordach otrzymują wartość NULL.

SELECT { atrybuty } 
FROM relacja1 R1 FULL [OUTER] JOIN relacja2 R2 ON R1.id = R2.id;

SELECT w.nazwisko, w.imie, ko.opis
FROM wykladowca w FULL JOIN wykl_kurs wk ON w.id_wykladowca = wk.id_wykl
                  FULL JOIN kurs k USING (id_kurs)
                  FULL JOIN kurs_opis ko USING (id_nazwa)
ORDER BY w.nazwisko, ko.opis ;

Złączenia zewnętrzne i filtrowanie po wartościach NULL. Na rys. 6, 7 i 8 przedstawiono wykorzystanie złączeń zewnętrznych i filtorowania na wartościach NULL.

Lab04_LEFT_NULL_JOIN — Rys.6 Złączenie LEFT [ OUTER ] JOIN i filtrowanie po wartości NULL dla atrybutu złączenia dla tabeli prawej.

SELECT nazwisko, imie FROM wykladowca w LEFT JOIN wykl_kurs wk ON w.id_wykladowca = wk.id_wykl 
WHERE wk.id_wykl IS NULL ORDER BY 1;

Lab04_RIGHT_NULL_JOIN — Rys.7 Złączenie RIGHT [ OUTER ] JOIN i filtrowanie po wartości NULL dla atrybutu złączenia dla tabeli lewej.

SELECT opis FROM kurs k RIGHT JOIN kurs_opis ko ON k.id_nazwa = ko.id_nazwa WHERE k.id_nazwa IS NULL ;

Lab04_FULL_JOIN_NULL — Rys.8 Złączenie FULL [ OUTER ] JOIN i filtrowanie po wartości NULL dla atrybutu złączenia dla obu tabel.

SELECT w.nazwisko, w.imie, ko.opis FROM
wykladowca w FULL JOIN wykl_kurs wk ON w.id_wykladowca = wk.id_wykl
             FULL JOIN kurs k USING(id_kurs) 
             FULL JOIN kurs_opis ko ON k.id_nazwa = ko.id_nazwa
WHERE wk.id_kurs IS NULL OR wk.id_wykl IS NULL;

Lab04 - Funkcje agregujące, złączenia [ ver. BD1.2025.10.20.003 ]

Plan zajęć

Polecenie SELECT

Funkcje agregujące i kauzule GROUP BY oraz HAVING

Złączenia relacji - operator JOIN

Linki do dodatkowych materiałów (dostęp na dzień 20.10.2025)