Wykonując zadania przedstawione w tej sekcji, nabierzesz wprawy w posługiwaniu się kwerendamikwerendakwerendami w konkretnych sytuacjach problemowych. Zastosujemy w nich zapytania wyszukujące złożone, czyli służące do ekstrahowania danych z więcej niż jednej tabeli. Aby zrealizować takie zadanie, trzeba uwzględnić powiązania między atrybutami.
Pliki potrzebne do wykonania ćwiczeń
W e‑materiale Bazy danych – ćwiczenia, etap IPzdnHclWpBazy danych – ćwiczenia, etap I dowiedzieliśmy się, jak zainstalować pakiet XAMPP. Znaleźć tam można również pliki baz danych potrzebne do wykonania ćwiczeń w całej serii oraz opis struktury tabel, czyli przeznaczenie oraz typ wartości każdej kolumny we wszystkich wykorzystywanych bazach danych.
Ważne!
Wykorzystywane bazy danych pochodzą z zadań maturalnych.
Zadania oraz bazy zostały opracowane przez Centralną Komisję Egzaminacyjną i pojawiły się na egzaminach maturalnych z informatyki (poziom rozszerzony, cz. II):
w maju 2013 r. (baza vod),
w maju 2014 r. (baza rekrutacja),
w czerwcu 2020 r. (baza jezyki).
Wszystkie arkusze egzaminacyjne można znaleźć na stronie internetowej CKE.
Polecenia do zrealizowania
Ta część e‑materiału zawiera zestaw poleceń do wykonania, wyniki działania kwerend, przykładowe rozwiązania zadań oraz wnioski z każdego wykonanego ćwiczenia.
Polecenie 1
Baza danych: vod
Wybierz z bazy dla wypożyczeń dokonanych w dniu 2011‑12‑31 kolejno: nazwisko, imię i PESEL klienta oraz tytuł wybranego przez niego filmu. Wyniki uporządkuj alfabetycznie według nazwisk klientów.
Poprawne wyniki kwerendy – zwróconych rekordów: 6
Nazwisko
Imie
Pesel
Tytul
Koronski
Waldemar
56070476895
Nasza niania jest agentem
Mikos
Karolina
79040112365
Harry Potter i Czara Ognia
Sobota
Izabela
89121774628
Alicja w Krainie Czarow
Winiarski
Borys
79101889775
Podziemny krag
Wojciechowski
Bartlomiej
89122589754
Dom zly
Wolny
Natalia
66032684427
Niepokonani
Przykładowa poprawna kwerenda:
Linia 1. SELECT Nazwisko przecinek Imie przecinek wypozyczenia kropka Pesel przecinek Tytul FROM.
Linia 2. wypozyczenia przecinek klienci przecinek filmy WHERE Data podkreślnik wyp znak równości cudzysłów 2011 minus 12 minus 31 cudzysłów.
Linia 3. AND klienci kropka Pesel znak równości wypozyczenia kropka Pesel AND.
Linia 4. filmy kropka ID podkreślnik filmu znak równości wypozyczenia kropka ID podkreślnik filmu ORDER BY Nazwisko.
SELECT Nazwisko, Imie, wypozyczenia.Pesel, Tytul FROM
wypozyczenia, klienci, filmy WHERE Data_wyp = "2011-12-31"
AND klienci.Pesel = wypozyczenia.Pesel AND
filmy.ID_filmu = wypozyczenia.ID_filmu ORDER BY Nazwisko
Wnioski płynące z tego zapytania:
Na liście wyjętych w zapytaniu kolumn zapisano: wypozyczenia.Pesel zamiast po prostu Pesel, ponieważ kolumna o takiej nazwie występuje w dwóch tabelach: w tabeli wypozyczenia oraz w tabeli klienci. Unikamy w ten sposób błędu dwuznaczności. Oczywiście równie dobrze można zapisać: klienci.Pesel – wartość dla danego rekordu będzie zawsze taka sama jak w tabeli wypozyczenia, uniknąć należy jedynie powtórzenia nazwy atrybutu, a nie powtórzenia jego wartości.
Kwerenda musi korzystać z aż trzech tabel, ponieważ tytuł filmu jest dostępny tylko w tabeli filmy, datę wypożyczenia odnajdziemy wyłącznie w tabeli wypozyczenia, zaś imię i nazwisko osoby wypożyczającej odnajdziemy tylko w tabeli klienci.
Między trzema tabelami istnieją łącznie dwie relacje: Pesel w tabeli klienci (klucz podstawowy) jest powiązany z atrybutem o tej samej nazwie w tabeli wypozyczenia (klucz obcy), jak również ID_filmu w tabeli filmy (klucz podstawowy) pozostaje w relacji do atrybutu o tej samej nazwie w tabeli wypozyczenia. Oba powiązania są relacjami typu jeden do wielu.
Brak uwzględnienia relacji zawsze prowadzić będzie do uzyskania wyników nadmiarowych.
Uporządkowanie alfabetyczne rekordów zapewniła klauzula ORDER BY – jest to kolejność rosnąca A‑Z, gdyż w przypadku braku wskazania metody sortowania domyślną opcją pozostaje klauzula ASC, czyli porządek rosnący.
Polecenie 2
Baza danych: vod
Znajdź identyfikatory i tytuły filmów, które nigdy nie zostały wypożyczone przez żadnego klienta.
Poprawne wyniki kwerendy – zwróconych rekordów: 9
ID_filmu
Tytul
AF2008
Jumper
AX2000
Straszny film
AX2007
Katyn
AY2000
Oszukac przeznaczenie
AY2007
Mgla
CE2002
Pianista
CF2004
Eurotrip
CG2002
The Ring
DE1999
Mumia
Przykładowa poprawna kwerenda:
Linia 1. SELECT ID podkreślnik filmu przecinek Tytul FROM filmy.
Linia 2. LEFT JOIN wypozyczenia USING otwórz nawias okrągły ID podkreślnik filmu zamknij nawias okrągły.
Linia 3. WHERE wypozyczenia kropka ID podkreślnik filmu IS NULL.
SELECT ID_filmu, Tytul FROM filmy
LEFT JOIN wypozyczenia USING(ID_filmu)
WHERE wypozyczenia.ID_filmu IS NULL
Wnioski płynące z tego zapytania:
Dzięki złączeniu klauzulą LEFT JOIN unikamy dwuznaczności nazwy kolumny ID_filmu oraz konieczności uwzględnienia w zapytaniu relacji jeden do wielu pomiędzy kolumną ID_filmu w tabeli filmy (klucz podstawowy) oraz atrybutem o tej samej nazwie w tabeli wypozyczenia.
Do znalezienia pustych wartości atrybutu wypozyczenia.ID_filmu w porównaniu do znajdującej się w tabeli filmy wartości tej kolumny wykorzystano klauzulę IS NULL – dzięki temu wiadomo, które z filmów nigdy nie zostały wypożyczone.
Zwróćmy uwagę, że po klauzuli WHERE musimy jednoznacznie wpisać: wypozyczenia.ID_filmu IS NULL zamiast: ID_filmu IS NULL – szukamy przecież nie tyle nieistniejących w rejestrze filmów, co istniejących, lecz nigdy nie wypożyczonych.
Polecenie 3
Baza danych: vod
Podaj imiona i nazwiska wszystkich klientów, którzy w roku 2011 wypożyczyli film o identyfikatorze AA1994. Dodatkowo wypisz dokładną datę zamówienia tego filmu.
Poprawne wyniki kwerendy – zwróconych rekordów: 2
Imie
Nazwisko
Data_wyp
Borys
Winiarski
2011‑07‑01
Agnieszka
Kaminska
2011‑10‑12
Przykładowa poprawna kwerenda:
Linia 1. SELECT Imie przecinek Nazwisko przecinek Data podkreślnik wyp FROM klienci przecinek wypozyczenia.
Linia 2. WHERE ID podkreślnik filmu znak równości cudzysłów AA1994 cudzysłów AND Data podkreślnik wyp zamknij nawias ostrokątny znak równości cudzysłów 2011 minus 01 minus 01 cudzysłów.
Linia 3. AND Data podkreślnik wyp otwórz nawias ostrokątny znak równości cudzysłów 2011 minus 12 minus 31 cudzysłów AND.
Linia 4. klienci kropka Pesel znak równości wypozyczenia kropka Pesel.
SELECT Imie, Nazwisko, Data_wyp FROM klienci, wypozyczenia
WHERE ID_filmu = "AA1994" AND Data_wyp >= "2011-01-01"
AND Data_wyp <= "2011-12-31" AND
klienci.Pesel = wypozyczenia.Pesel
Wnioski płynące z tego zapytania:
Ponieważ znamy identyfikator filmu, możemy go użyć w kwerendzie zamiast tytułu. Dzięki temu wystarczy skorzystać z dwóch tabel: klienci oraz wypozyczenia zamiast ze wszystkich trzech.
Daty można porównywać w taki sam sposób jak liczby, czyli z użyciem operatorów: <, <=, >, >=. Najlepiej myśleć o dacie w kategorii liczby sekund, które upłynęły od północy 1970‑01‑01 czasu UTC (tzw. epoka UNIX). A zatem wydarzenie późniejsze oznacza większą liczbę sekund, które zdążyły upłynąć od tego czasu – dlatego właśnie zwykłe porównanie arytmetyczne działa.
Pomimo tego, że wartości dat możemy porównywać tak samo jak liczby, nie zapominajmy o zapisywaniu wartości dat w cudzysłowie lub wewnątrz apostrofów (tak samo jak to robimy w przypadku łańcuchów znaków).
Między użytymi dwoma tabelami istnieje jedna relacja: Pesel w tabeli klienci (klucz podstawowy) jest powiązany z atrybutem o tej samej nazwie w tabeli wypozyczenia (klucz obcy). Jest to relacja typu jeden do wielu.
Alternatywnie, wypożyczenia dokonane w roku 2011 możemy odnaleźć także z użyciem klauzuli LIKE:
Linia 1. SELECT Imie przecinek Nazwisko przecinek Data podkreślnik wyp FROM klienci przecinek wypozyczenia.
Linia 2. WHERE ID podkreślnik filmu znak równości cudzysłów AA1994 cudzysłów.
Linia 3. AND Data podkreślnik wyp LIKE cudzysłów 2011 procent cudzysłów AND.
Linia 4. klienci kropka Pesel znak równości wypozyczenia kropka Pesel.
SELECT Imie, Nazwisko, Data_wyp FROM klienci, wypozyczenia
WHERE ID_filmu = "AA1994"
AND Data_wyp LIKE "2011%" AND
klienci.Pesel = wypozyczenia.Pesel
Polecenie 4
Baza danych: rekrutacja
Wypisz na ekranie: nazwiska, imiona, wiek oraz numery PESEL wszystkich dzieci, których rodzice jako pierwszą preferencję wybrali Przedszkole nr 42 Dolina Smyków (identyfikator w bazie: 31), których nazwisko rozpoczyna się na literę G. Wyniki uporządkuj alfabetycznie według nazwisk zapisanych dzieci.
Poprawne wyniki kwerendy – zwróconych rekordów: 5
Nazwisko
Imie
Wiek
Pesel
Gawor
Jan
6
p06232700213
Glowacki
Krzysztof
3
p09220600171
Gorlik
Jakub
3
p09272811295
Gruca
Szymon
3
p09243005234
Grzegorzewska
Sandra
3
p09231108044
Przykładowa poprawna kwerenda:
Linia 1. SELECT Nazwisko przecinek Imie przecinek Wiek przecinek dzieci kropka Pesel FROM.
Linia 2. dzieci przecinek preferencje WHERE Numer podkreślnik preferencji znak równości 1.
Linia 3. AND Id podkreślnik przedszkola znak równości 31 AND Nazwisko LIKE cudzysłów G procent cudzysłów.
Linia 4. AND dzieci kropka Pesel znak równości preferencje kropka Pesel ORDER BY Nazwisko.
SELECT Nazwisko, Imie, Wiek, dzieci.Pesel FROM
dzieci, preferencje WHERE Numer_preferencji = 1
AND Id_przedszkola = 31 AND Nazwisko LIKE "G%"
AND dzieci.Pesel = preferencje.Pesel ORDER BY Nazwisko
Wnioski płynące z tego zapytania:
Na liście wyjętych w zapytaniu kolumn zapisano: dzieci.Pesel (zamiast po prostu Pesel), aby uniknąć błędu dwuznaczności. Oczywiście równie dobrze można zapisać: preferencje.Pesel, ponieważ wartość w rekordzie będzie identyczna.
Ponieważ znamy identyfikator przedszkola w bazie (wartość: 31), możemy nie korzystać w zapytaniu z tabeli przedszkola – wystarczy użyć dwóch tabel: dzieci oraz preferencje.
Między wspomnianymi dwoma tabelami istnieje jedna relacja: Pesel w tabeli dzieci (klucz podstawowy) jest powiązany z atrybutem o tej samej nazwie w tabeli preferencje (klucz obcy). Jest to relacja typu jeden do wielu.
Klauzula LIKE posłużyła do znalezienia nazwisk rozpoczynających się na literę „G”. Ponieważ musi to być pierwsza litera nazwiska, operator % zapisano wyłącznie po prawej stronie litery. Operator ten – przypomnijmy – oznacza: „brak znaku, jakikolwiek pojedynczy znak lub wiele znaków”, w przeciwieństwie do operatora _ (podkreślenia), reprezentującego zawsze dokładnie jeden znak.
Polecenie 5
Baza danych: rekrutacja
Podaj łączną liczbę pięcio- lub sześcioletnich chłopców zapisanych na drugą preferencję do przedszkoli o wartościach identyfikatorów: 23, 76.
Poprawne wyniki kwerendy – zwróconych rekordów: 1
IluChlopcow
6
Przykładowa poprawna kwerenda:
Linia 1. SELECT COUNT otwórz nawias okrągły Id zamknij nawias okrągły AS IluChlopcow FROM dzieci przecinek preferencje.
Linia 2. WHERE Plec znak równości cudzysłów chlopiec cudzysłów AND otwórz nawias okrągły Wiek znak równości 5 OR Wiek znak równości 6 zamknij nawias okrągły AND.
Linia 3. Numer podkreślnik preferencji znak równości 2 AND otwórz nawias okrągły Id podkreślnik przedszkola znak równości 23 OR.
Linia 4. Id podkreślnik przedszkola znak równości 76 zamknij nawias okrągły AND dzieci kropka Pesel znak równości preferencje kropka Pesel.
SELECT COUNT(Id) AS IluChlopcow FROM dzieci, preferencje
WHERE Plec = "chlopiec" AND (Wiek = 5 OR Wiek = 6) AND
Numer_preferencji = 2 AND (Id_przedszkola = 23 OR
Id_przedszkola = 76) AND dzieci.Pesel = preferencje.Pesel
Wnioski płynące z tego zapytania:
Funkcja agregująca COUNT() pozwala zliczać rekordy (wiersze) spełniające zadane kryteria.
Warto zwrócić uwagę, że argumentem funkcji COUNT() może być dowolny atrybut (kolumna) rekordu; można też ewentualnie użyć zapisu: COUNT(*) – w naszym przypadku wybrano identyfikator preferencji.
W zapytaniu użyto dodatkowo aliasu definiowanego klauzulą AS, który nadał nowo utworzonej, wirtualnej kolumnie nazwę IluChlopcow.
Ponieważ znamy identyfikatory obu przedszkoli (wartości: 23 oraz 76), możemy nie korzystać w zapytaniu z tabeli przedszkola. Do zrealizowania polecenia wystarczyło użycie dwóch tabel: dzieci oraz preferencje.
Warunki połączone spójnikiem OR zamknięto w nawiasach okrągłych, gdyż spójnik OR jest w hierarchii operatorów logicznych mniej ważny niż AND.
Między tabelami istnieje jedna relacja: Pesel w tabeli dzieci (klucz podstawowy) jest powiązany z atrybutem o tej samej nazwie w tabeli preferencje (klucz obcy). Jest to relacja typu jeden do wielu.
Polecenie 6
Baza danych: jezyki
Wypisz (bez powtórzeń) nazwy wszystkich rodzin języków, których używa się we Włoszech lub we Francji.
Poprawne wyniki kwerendy – zwróconych rekordów: 4
Rodzina
indoeuropejska
afroazjatycka
turecka
sino‑tybetanska
Przykładowa poprawna kwerenda:
Linia 1. SELECT DISTINCT Rodzina FROM jezyki przecinek uzytkownicy.
Linia 2. WHERE otwórz nawias okrągły Panstwo znak równości cudzysłów Wlochy cudzysłów OR Panstwo znak równości cudzysłów Francja cudzysłów zamknij nawias okrągły.
Linia 3. AND jezyki kropka Jezyk znak równości uzytkownicy kropka Jezyk.
SELECT DISTINCT Rodzina FROM jezyki, uzytkownicy
WHERE (Panstwo = "Wlochy" OR Panstwo = "Francja")
AND jezyki.Jezyk = uzytkownicy.Jezyk
Wnioski płynące z tego zapytania:
Używamy bardzo przydatnej klauzuli DISTINCT, która wybiera tylko niepowtarzające się rekordy – w codziennej praktyce taka operacja przydaje się bardzo często. Przykładowo, gdy trzeba wypełnić listę wyboru w interfejsie formularza możliwymi wartościami danego atrybutu, wszystkie możliwe opcje listy wybieramy wówczas właśnie bez powtórzeń.
Klauzulę DISTINCT umieszczamy bezpośrednio po słowie SELECT, ponieważ odpowiada ona na pytanie: „jak wybieramy dane?”. Odpowiedź brzmi: „wybierz dystynktywnie”, czyli właśnie SELECT DISTINCT.
Warunki połączone spójnikami OR zamknięto w nawiasach okrągłych, gdyż spójnik OR jest w hierarchii operatorów logicznych mniej ważny niż AND.
Między tabelami istnieje jedna relacja: Jezyk w tabeli jezyki (klucz podstawowy) jest powiązany z atrybutem o tej samej nazwie w tabeli uzytkownicy (klucz obcy). Jest to relacja typu jeden do wielu.
Słownik
kwerenda
kwerenda
(ang. query – zapytanie) - zapisane z użyciem języka SQL polecenie skierowane do systemu bazodanowego, którego celem może być: pobranie, dodanie, modyfikacja lub usunięcie danych albo sposobu ich przechowywania w systemie
pole
pole
(inaczej: kolumna tabeli) - jednostkowa cecha opisująca element (obiekt) tabeli, np. pole „Nazwisko” może być jedną z kolumn tabeli „Uczeń”
rekord
rekord
(inaczej: wiersz tabeli lub krotka) - pojedynczy kompletny zestaw danych dotyczący konkretnego elementu (obiektu) w tabeli
SZBD
SZBD
System Zarządzania Bazą Danych - narzędzie/aplikacja do tworzenia bazy danych (np. MySQL, PostgreSQL, Firebird, Oracle, Microsoft Access)
tabela
tabela
podstawowa struktura logiczna relacyjnej bazy danych definiowana jako zestaw pól (kolumn) opisujących właściwości obiektów, które ma przechowywać