Zajęcia 2 - Podstawy SQL 2

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.