2 Baza w SQL – złączenia - powtórzenie
1. Zaprojektuj bazę danych Autorzy. Pamiętaj o kodowaniu polskich znaków.
2. Zaprojektuj tabele: Autorzy i Ksiazki. Pamiętaj o zdefiniowaniu kluczy podstawowych i obcych.
Tabela Autorzy Tabela Ksiazki
3. Uzupełnij tabele danymi.
4. Zrzuty tabel i zapytań umieść w pliku Worda.
5. Pełne złączenie tabel:
Utwórz zapytanie wybierające dane z tabel Autorzy i Ksiazki z następujących kolumn:
tytul, imie, nazwisko.
SELECT Ksiazki.tytul, Autorzy. imie, Autorzy.nazwisko FROM Autorzy, Ksiazki;
Ponieważ nazwy pobieranych kolumn z obu tabel nie powtarzają się, można też zastosować formułę skróconą:
SELECT tytul, imie, nazwisko FROM Autorzy, Ksiazki;
Serwer wykonał tzw. pełne złączenie obu tabel w którym każdy wiersz z tabeli Ksiazki został połączony z każdym wierszem tabeli Autorzy (iloczyn kartezjański obu tabel).
Zdecydowanie bardziej interesująca byłaby lista wszystkich książek wraz z imionami i nazwiskami ich autorów. W związku z tym do zapytania należy dodać warunek wiążący autorów i napisane przez nich książki.
6. Powiązanie łączonych tabel:
Utwórz zapytanie wybierające dane z tabel Autorzy i Ksiazki wiążące książki z ich autorami. W warunku WHERE zapisz zachodzące powiązanie (id z tabeli Autorzy i autorId z tabeli Ksiazki).
Jeżeli w zapytaniu konieczne jest określanie tabel, z których pochodzą pobierane kolumny, czyli zapytanie powinno mieć analogiczną składnię do instrukcji przedstawionej w pkt.4, zamiast nazw pełnych często stosuje się aliasy, nierzadko jedno- lub dwuliterowe. Pozwala to na znaczne skrócenie zapisu, na przykład zamiast pisać Ksiazki.tytul, wystarczy k.tytul.
7. Aliasy zamiast pełnych nazw tabel.
Utwórz zapytanie z pkt.5 w taki sposób, aby przy każdej kolumnie znajdowało się określenie, z której tabeli ona pochodzi. Zastosuj możliwie skróconą wersję zapisu.
SELECT k.tytul, a. imie, a.nazwisko FROM Ksiazki AS k, Autorzy As a WHERE k.autorId = a.id;
Ponieważ słowo AS jest opcjonalne, można też użyć wersji jeszcze bardziej skondensowanej:
SELECT k.tytul, a. imie, a.nazwisko FROM Ksiazki k, Autorzy a WHERE k.autorId = a.id;
Należy przy tym zwrócić uwagę, że aliasy nazw tabel zostały użyte również w warunku klauzuli WHERE.
8. Złączenie typu INNER JOIN. Jest to połączenie danych z dwóch tabel będące odpowiednikiem instrukcji opisanej w punkcie 4. Konstrukcja zapytania SELECT ze złączeniem INNER JOIN będzie miała postać:
SELECT kolumna1, kolumna2, … kolumnaN FROM tabela1 [INNER] JOIN tabela2 [ON warunek]
Jeżeli pobierane są kolumny o takich samych nazwach, to konieczne jest dodatkowe określenie tabel, z których pochodzą; można stosować aliasy nazw tabel (zgodnie z opisem w pkt.7).
Aby zatem uzyskać pełne złączenie tabel Autorzy i Książki, zawierające kolumny tytul, imie, nazwisko można zastosować jedną z instrukcji:
SELECT tytul, imie, nazwisko FROM Ksiazki JOIN Autorzy;
SELECT tytul, imie, nazwisko FROM Ksiazki INNER JOIN Autorzy;
SELECT Ksiazki.tytul, Autorzy. imie, Autorzy.nazwisko FROM Ksiazki INNER JOIN Autorzy;
SELECT k.tytul, a. imie, a.nazwisko FROM Ksiazki k INNER JOIN Autorzy a;
Są to odpowiedniki przypomnianej wcześniej instrukcji:
SELECT tytul, imie, nazwisko FROM Autorzy, Ksiazki;
We wszystkich wymienionych przypadkach można również zastosować dodatkowe wyrażenie określające warunki złączenia występujące po klauzuli ON.
9. Użyj złączenia typu INNER JOIN do wyświetlenia danych z tabel Ksiazki i Autorzy. Zastosuj klauzulę ON z warunkiem łączącym książki i autorów.
10. Złączenie typu LEFT JOIN pozwala na uwzględnienie w wyniku danych, które nie mają swoich odpowiedników w złączanych tabelach. Oznacza to, że jeżeli w pierwszej tabeli znajdują się wiersze, które nie mają swoich odpowiedników w drugiej tabeli, i tak zostaną uwzględnione w złączeniu, a w pustych kolumnach zostaną wpisane wartości NULL. Wróćmy do przykładu z książkami i autorami. Może się zdarzyć, że w tabeli Ksiazki znajdą się dane książki, której nie będzie wpisu w tabeli Autorzy. Będzie to błąd polegający na niespójności danych w bazie, ale jest to całkiem realna sytuacja. Uzupełnijmy więc tabelę Ksiazki. Należy dodać książkę o identyfikatorze 10, tytule „Klin”, identyfikatorze autora 4 i numerze ISBN 8385103201. Tabela Autorzy niech pozostanie bez zmian. Książka o identyfikatorze 10 ma autora o identyfikatorze 4.
Taki identyfikator nie istnieje jednak w tabeli Autorzy.
Zastosuj złączenie LEFT JOIN, aby wyświetlić dane książek i ich autorów, tak aby w wyniku pojawiły się również tytuły, dla których nie ma wpisów w tabeli Autorzy.
Wyszukiwanie niespójnych danych.
Wyświetl dane takich książek, dla których brakuje wpisów w tabeli Autorzy. Aby wykonać to ćwiczenie należy użyć zapytania o konstrukcji podobnej do tej z pkt.9, ale z dodanym warunkiem w klauzuli WHERE (Autorzy.id IS NULL). W zapytaniu wykorzystany został operator IS NULL wskazujący, czy mamy do czynienia z wartością pustą NULL.
11. Złączenie typu RIGHT JOIN działa analogicznie jak LEFT JOIN, z tą różnicą, że w tabeli wynikowej uwzględniane są wiersze z drugiej tabeli, które nie mają swoich odpowiedników w pierwszej tabeli. Załóżmy, że w tabeli Autorzy znajdzie się wpis zawierający w polu id identyfikator, który nie występuje w tabeli Ksiązki. Uzupełnijmy tabelę Autorzy. Należy dodać autora o identyfikatorze 5, imieniu „Terry” i nazwisku „Pratchett”. Tabela Ksiazki niech pozostanie bez zmian. W tabeli Autorzy będzie istniał wpis dotyczący autora, którego książek nie ma w bazie.
Zastosuj złączenie RIGHT JOIN, aby wyświetlić dane książek i ich autorów, tak aby w wyniku pojawili się również autorzy, których książek nie ma jeszcze w tabeli Ksiazki.
Jego wykonanie spowoduje uzyskanie wyniku widocznego poniżej na rysunku.
Wyszukiwanie niespójnych danych.
Wyświetl dane takich Autorów, dla których brakuje wpisów w tabeli Ksiazki. Aby wykonać to ćwiczenie należy użyć zapytania o konstrukcji podobnej do tej z pkt.11, ale z dodanym warunkiem w klauzuli WHERE (tytul IS NULL).
Łatwo zauważyć, że RIGHT JOIN i LEFT JOIN to funkcjonalne odpowiedniki. Wystarczy zamienić kolejność tabel, aby uzyskiwać jedno lub drugie zapytanie.
12. Konwersja typu zapytania.
Użyj złączenia typu LEFT JOIN do uzyskania takich samych rezultatów jak w pkt.12.
13. Pokaż pracę nauczycielowi w celu sprawdzenia wykonanych ćwiczeń.
