Sekcja: Złączenie tabel

40. Wyświetlanie danych z tabeli pracownicy.

Zapytanie odczytuje dane z tablicy pracownicy. W kolumnie nr_miejsca wyświetlają się nie nazwy miejsc tylko ich indeksy. Drugie zapytanie wyświetla miejsca gdzie pracują

pracownicy. Aby wyświetlić nazwy miejsc należy zrealizować złączenie obu tabel.

SELECT * from pracownicy ORDER BY nazwisko; -- Wyświetla pracowników

SELECT * from miejsca; -- Wyświetla miejsca

Przykład 40: Wyświetlanie danych z tabeli pracownicy.

40a. Złączenie tabel - brak warunku złączenia.

Zapytanie łączące tabele bez zdefiniowanego warunku złączenia spowoduje powstanie tzw.

iloczynu kartezjańskiego rekordu obu tabel. Jest to oczywiście rozwiązanie nieprawidłowe.

Aby temu zapobiec trzeba zastosować warunek złączenia tabel. Patrz następny przykład SELECT pracownicy.nazwisko, pracownicy.stanowisko, pracownicy.dzial,

miejsca.miasto, miejsca.ulica

FROM pracownicy, miejsca

--

ORDER BY pracownicy.nazwisko;

Przykład 40a: Złączenie tabel - brak warunku złączenia.

40b. Złączenie tabel pracownicy i miejsca.

Zapytanie odczytuje nr_miejsca pracy z tabeli 'pracownicy' a następnie przeszukuje tabele

'miejsca'wcelu znalezienia nazwy miasta i ulicy. Wybieranie danych z wielu tabel nazywa się złączeniem (JOIN). W klauzuli SELECT podajemy kolumny, które chcemy zawrzeć w

zapytaniu. W klauzuli FROM podajemy nazwy złączanych tabel. W klauzuli WHERE

określamy warunki złączenia. Gdy złączamy dwie tabele klauzula WHERE musi zawierać

jeden warunek złączenia. Komentarz w SQL realizujemy za pomocą znaków: --

SELECT pracownicy.nazwisko, pracownicy.stanowisko, pracownicy.dzial,

miejsca.miasto, miejsca.ulica

FROM pracownicy, miejsca -- tabele które chcemy

złączyć

WHERE pracownicy.nr_miejsca = miejsca.nr_miejsca -- warunek złączenia

ORDER BY pracownicy.nazwisko;

Przykład 40b: Złączenie tabel pracownicy i miejsca.

41. Złączenie trzech tabel.

W tym zapytaniu złączamy trzy tabele: 'pracownicy', 'miejsca' i 'wypozyczenia'. Klauzula WHERE musi zawierać przynajmniej dwa warunki złączenia. W naszym przykładzie dwa

pierwsze warunki dotyczą złączenia tabel, trzeci dotyczy warunku wyboru wierszy.

SELECT wypozyczenia.nr_wypozyczenia, pracownicy.nazwisko,

pracownicy.stanowisko, pracownicy.dzial, miejsca.miasto, miejsca.ulica

FROM pracownicy, miejsca, wypozyczenia

WHERE pracownicy.nr_miejsca = miejsca.nr_miejsca AND pracownicy.nr_prac =

wypozyczenia.nr_prac_wyd

AND miejsca.miasto = 'Warszawa'

ORDER BY pracownicy.nazwisko;

Przykład 41: Złączenie trzech tabel.

42. Zastosowanie klauzuli JOIN ON do łączenie tabel.

Inny typ złączenia tabel polega na zastosowaniu konstrukcji JOIN.....ON. Kiedy używamy słowa JOIN w klauzuli FROM warunki złączenia musza być wyspecyfikowane po klauzuli

ON. W klauzuli WHERE można określić dodatkowe warunki selekcji wierszy.

SELECT pracownicy.nazwisko, pracownicy.stanowisko, pracownicy.dzial,

miejsca.miasto, miejsca.ulica

FROM pracownicy

JOIN miejsca ON pracownicy.nr_miejsca = miejsca.nr_miejsca

WHERE pracownicy.stanowisko = 'Sprzedawca'

ORDER BY pracownicy.nazwisko;

Przykład 42: Zastosowanie klauzuli JOIN ON do łączenie tabel.

43. Zastosowanie aliasów w zapytaniu.

Stosowanie aliasów w zapytaniu. Aliasy definiuje się w celu skrócenia nazwy tabel. W

naszym przykładzie alias p wskazuje na tabele 'pracownicy' zaś alias m na tabele 'miejsca'.

Stosowanie aliasów powoduje większa czytelność zapytania.

SELECT p.nazwisko, p.stanowisko, p.dzial, m.miasto, m.ulica

FROM pracownicy p, miejsca m

-- p jest aliasem pracownicy,

m jest aliasem miejsca

WHERE p.nr_miejsca = m.nr_miejsca AND p.stanowisko = 'Sprzedawca'

ORDER BY p.nazwisko;

Przykład 43: Zastosowanie aliasów w zapytaniu.

44. Lewostronne złączenie zewnętrzne.

Operacja złączenia kojarzy dane z dwóch tabel, tworząc pary z wierszy, w których kolumny złączenia (para klucz obcy - klucz główny) mają taką samą wartość. Jeśli wiersz z tabeli nie zostanie połączony z żadnym wierszem drugiej tabeli to nie trafia do wartości zwracanej przez zapytanie. Tak się dzieje ze złączeniami zwanymi złączeniami wewnętrznymi. Standard ISO opisuje inne operacje złączenia zwane złączeniami zewnętrznymi. Złączenie zewnętrzne ma taką właściwość, że zachowuje wiersze nie spełniające warunku złączenia. Poniżej

przedstawiono lewostronne złączenie zewnętrzne. Złączenie to zawiera również rekordy z tablicy pracownicy nie powiązane z tabelą miejsca.

SELECT p.nazwisko, p.stanowisko, p.dzial, m.miasto, m.ulica

FROM pracownicy p

LEFT JOIN miejsca m ON p.nr_miejsca = m.nr_miejsca

ORDER BY p.nazwisko;

Przykład 44: Lewostronne złączenie zewnętrzne.

45. Prawostronne złączenie zewnętrzne.

Poniżej przedstawiono prawostronne złączenie zewnętrzne. Złączenie to zawiera również rekordy z tablicy miejsca nie powiązane z z żadnym rekordem z tabeli pracownicy.

SELECT p.nazwisko, p.stanowisko, p.dzial, m.miasto, m.ulica

FROM pracownicy p

RIGHT JOIN miejsca m ON p.nr_miejsca = m.nr_miejsca

ORDER BY p.nazwisko;

Przykład 45: Prawostronne złączenie zewnętrzne.

46. Pełne złączenie zewnętrzne.

Poniżej przedstawiono pełne złączenie zewnętrzne. Złączenie to zawiera rekordy z tablicy pracownicy nie powiązane z z żadnym rekordem z tabeli miejsca oraz rekordy z tabeli

miejsca nie powiązane z żadnym rekordem tabeli pracownicy.

SELECT p.nazwisko, p.stanowisko, p.dzial, m.miasto, m.ulica

FROM pracownicy p

FULL JOIN miejsca m ON p.nr_miejsca = m.nr_miejsca

ORDER BY p.nazwisko;

Przykład 46: Pełne złączenie zewnętrzne

Sekcja: Funkcje skalarne i arytmetyczne

50. Wybieranie wyliczonych wartości.

Wybieranie wyliczonych wartości. W zapytaniu SQL możemy używać następujących

operatorów arytmetycznych w celu obliczenia wartości:

+ dodawanie

- odejmowanie

* mnożenie

/ dzielenie

Operatorów tych możemy użyć do budowy bardziej rozbudowanych wyrażeń

matematycznych, włącznie z użyciem nawiasów w celu zaznaczenia kolejności wykonywania działań. Wynik poniższego zapytania zawiera obliczoną kolumnę, która jest sumą kolumn pensja+dodatek. Dla pracowników, którzy w polu dodatek mają wartość null - dodatkowa kolumna nie zostanie wyliczona. Wartości null nie mogą brać udziału w wyliczeniach.

SELECT p.imie, p.nazwisko, p.pensja, p.dodatek, p.pensja + p.dodatek

FROM pracownicy p

WHERE p.pensja > 1100

ORDER BY p.nazwisko;

Przykład 50: Wyświetlanie danych z tabeli pracownicy.

51. Nadawanie nazwy wyliczonej kolumny.

Kolumnę wynikową możemy nazwać. W poniższym zapytaniu po słowie kluczowym AS

podana jest nazwa kolumny. Nazwę tą należy umieścić w podwójnych cudzysłowach jeśli

zawiera spacje.

SELECT p.imie, p.nazwisko, p.pensja, p.dodatek, p.pensja + p.dodatek AS

Do_wyplaty

FROM pracownicy p

WHERE p.pensja > 1100

ORDER BY p.nazwisko;

Przykład 51: Nadawanie nazwy wyliczonej kolumny.

52. Funkcja COALESCE - wartości liczbowe.

Funkcja COALESCE jest funkcją operującą na wartości null. Jeśli wartość 1-go argumentu

<> null funkcja zwraca tę wartość, w przeciwnym razie funkcja zwraca 2-gi argument. Jest to oczywiście czynność doraźna stosowana wtedy gdy nie możemy poprawić kodu bazy.

Atrybuty liczbowe, zwłaszcza takie na których chcemy wykonywać operacje arytmetyczne nie powinny przyjmować wartości null. Zamiast tego należy wymuszać wstawianie wartości zero

SELECT p.imie, p.nazwisko, p.pensja, COALESCE (p.dodatek,0) AS Dodatek,

p.pensja + COALESCE (p.dodatek,0) AS "Do wypłaty"

FROM pracownicy p

WHERE p.pensja > 1100

ORDER BY p.nazwisko;

Przykład 52: Funkcja COALESCE - wartości liczbowe.

53. Funkcja COALESCE - wartości znakowe.

Funkcję możemy używać nie tylko do podstawienia za symbol NULL wartości liczbowych.

W przykładzie funkcja COALESCE została użyta w celu zastąpienia wszystkich wystąpień wartości null na ciąg ,,nie posiada’’. Wyświetleni będą wszyscy klienci. Dla tych którzy nie posiadają karty kredytowej w polu nr_karty będzie wpisany cią „nie posiada”.

SELECT k.imie, k.nazwisko, COALESCE (k.karta,'nie posiada') AS nr_karty

FROM klienci k;

Przykład 53: Funkcja COALESCE - wartości znakowe.

54. Wybieranie podłańcucha znaków.

W zapytaniach SQL możemy używać funkcji operujących na łańcuchach znaków.

Przykładem takiej funkcji jest funkcja SUBSTRING(). Jeżeli zachodzi potrzeba wyboru

części stringu możemy zastosować funkcję SUBSTRING (s FROM n FOR m). Wybiera ona z łańcucha s m znaków, począwszy od pozycji n. W naszym przykładzie 4-ry znaki od 3-go miejsca z kolumny nazwisko.

SELECT SUBSTRING (k.nazwisko FROM 3 FOR 4) AS fragment, k.nazwisko

FROM klienci k;

Przykład 54: Wybieranie podłańcucha znaków.

55. Łączenie łańcuchów znaków.

Często w zapytaniu istnieje potrzeba połączenia kilku atrybutów w jeden. Stosujemy do tego operator konkatenacji '||'. Powoduje on, że z kilku atrybutów otrzymujemy jeden.

SELECT k.imie || ' ' || k.nazwisko AS Klient,

'ul. ' || k.ulica || ' ' || k.dom AS Ulica,

k.kod || ' ' || k.miasto AS Miasto

FROM klienci k

ORDER BY k.nazwisko

Przykład 55: Łączenie łańcuchów znaków.

56. Wyrażenie CASE.

Wyrażenie CASE pozwala na wybranie pewnej wartości w zależności od wartości w innej

kolumnie. W przykładzie sprawdzamy czy klient pochodzi z Warszawy, jeśli tak to

wpisywana jest wartość „Klient oddziału macierzystego”, w przeciwnym razie jest to Klient z przedstawicielstwa”.

SELECT k.imie, k.nazwisko, k.miasto,

CASE k.miasto

WHEN 'Warszawa' THEN 'Klient oddziału macierzystego'

ELSE 'Klient z przedstawicielstwa'

END

FROM klienci k

ORDER BY k.nazwisko

Przykład 55: Wyrażenie CASE.

Sekcja: Modyfikacja danych w bazie danych.

90. Modyfikacja danych w języku SQL.

Język SQL umożliwia manipulowanie danymi zarówno przy wyszukiwaniu, wstawianiu,

poprawianiu jak i kasowaniu. Polecenia modyfikacji bazy nie są tak złożone jak polecenie SELECT.

Możemy wyodrębnić w języku SQL trzy polecenia do modyfikacji danych:

• INSERT - powoduje dodanie wiersza danych do tabeli,

• UPDATE - powoduje modyfikację istniejących danych w wierszach tabeli,

• DELETE - powoduje usunięcie wierszy danych z tabeli.

91. Dodawanie do tabeli nowego wiersza - INSERT.

W języku SQL istnieją dwie postacie polecenia INSERT. Pierwsza z nich wprowadza jeden wiersz do tabeli. Jeśli do tabeli wprowadzamy wszystkie atrybuty to możemy pominąć ich listę za nazwą tabeli.

INSERT INTO pracownicy (nr_prac, imie, nazwisko, data_zatr, dzial,

stanowisko, pensja, dodatek, nr_miejsca, tel)

VALUES (1, 'Jan', 'Kowalski', '1997-02-01', 'Obslugi Klienta',

'Sprzedawca', 1100, 123, 1, '997-231-123');

Przykład 91: Dodawanie do tabeli nowego wiersza - INSERT.

92. Dodawanie wierszy INSERT SELECT.

Druga postać polecenia INSERT pozwala skopiować do tabeli wiersze pobrane z jednej bądź

kilku tabel. Początek polecenia ma taki sam format jak zwykłe polecenie INSERT INTO zaś zapytanie SELECT musi zwracać dane zgodne z formatem polecenia INSERT into.

CREATE TABLE klienciwarszawa

(

nr_klienta integer NOT NULL,

imie character varying(20) NOT NULL,

nazwisko character varying(20) NOT NULL,

karta character(20),

firma character varying(40),

ulica character varying(20) NOT NULL,

dom character(8) NOT NULL,

miasto character varying(20) NOT NULL,

kod character(6) NOT NULL,

nip character(12),

tel character(16),

CONSTRAINT x11 PRIMARY KEY (nr_klienta)

);

INSERT INTO klienciwarszawa SELECT * FROM klienci WHERE miasto='Warszawa'; Przykład 92: Dodawanie wierszy INSERT SELECT.

93. Modyfikowanie danych UPDATE.

Polecenie UPDATE umożliwia zmianę wartości wskazanych atrybutów w jednym lub więcej

rekordach tabeli. Poniższe zapytanie zmienia zwiększa atrybut pensja o 5% zaś atrybut dodatek o 10% dla wszystkich pracowników o stanowisku sprzedawca.

UPDATE pracownicy

SET pensja=pensja*1.05,dodatek=dodatek*1.10

WHERE stanowisko='Sprzedawca';

Przykład 93: Modyfikowanie danych UPDATE.

94. Usuwanie danych z bazy DELETE.

Polecenie DELETE służy do usuwania z tabeli jednego lub więcej rekordów. Poniższe

zapytanie usunie z tabeli pracownicy rekord dotyczący pracownika o numerze 11. Jeśli jest podany warunek to usunięte zostaną tylko te wiersze które go spełniają. Jeśli nie podamy warunku to zostaną usunięte wszystkie wiersze. Sama tabela oczywiście nie zostanie usunięta.

DELETE FROM pracownicy

WHERE nr_prac=11;

Przykład 94: Usuwanie danych z bazy DELETE.

Sekcja: Zadania

100. Diagram ERD bazy danych

Diagram ERD bazy danych pokazujący tabele studenci, wpis i wykładowcy. Diagram

pokazuje strukturę tabel oraz związki pomiędzy tabelami.

101. Zadanie 1.

Powyższy diagram pokazuje związki występujące w bazie pomiędzy tabelami: studenci, wpis i wykładowcy. Należy zaprojektować bazę realizującą powyższy diagram. Należy zwrócić uwagę na właściwy dobór dziedziny każdego argumentu oraz zastosować klucze obce do

łączenia tabel. Instrukcje CREATE TABLE i pozostałe należy umieścić w jednym pliku.

Ograniczenia (PK, FK i inne) należy zrealizować za pomocą CONSTRAINT. Numery kluczy

głównych należy zrealizować za pomocą typu serial lub SEQUENCE.

102. Zadanie 2.

Napisać kwerendy które wprowadzą do tabel dane niezbędne do wykonania następnych

ćwiczeń. Wartości kluczy obcych należy wprowadzić za pomocą wewnętrznych kwerend

SELECT a nie przy pomocy wartości liczbowych.

103. Zadanie 3.

Napisać kwerendę która wyszuka dane studentów (nazwisko, imię, indeks, ocena) którzy dostali ocenę 5.0 z przedmiotu SQL.

104. Zadanie 4.

Napisać kwerendę wyszukującą studentów których numery indeksów należą do przedziału

obustronnie zamkniętego od 10000 do 11000 i są urodzeni w 1988 r.

105. Zadanie 5.

Napisać kwerendę wyszukującą wykładowców którzy nie postawili dziś stopni.

106. Zadanie 6.

Napisać kwerendę której wynikiem będzie odpowiedz:

Imię studenta Nazwisko studenta Przedmiot, Ocena, Imię wykładowcy Nazwisko

wykładowcy.

107. Zadanie 7.

Dla każdego studenta policzyć średnią ze wszystkich ocen z dokładnością 2 miejsc po

przecinku.

108. Zadanie 8.

Policzyć liczbę studentów w każdej grupie.

109. Zadanie 9.

Policzyć ile jest ocen z każdego przedmiotu.

110. Zadanie 10.

Poprawić dla studenta o indeksie=10000 ocenę z przedmiotu SQL z oceny 2 na ocenę 4.