Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
1
Rozdzia
ł 1. Jak korzystać z ćwiczeń
Ćwiczenia te są kierowane do osób, które chcą się nauczyć języka SQL i mam nadzieję,
że w znacznym stopniu okażą się one pomocne. Do nauki języka SQL potrzebny jest jego interpreter,
czyli baza danych. Do studiowania języka SQL zostały wykorzystane:
baza danych InterBase firmy Inprise (Borland) oraz baza danych DB2 firmy IBM.
Dlaczego InterBase? Dlaczego IBM DB2? Po pierwsze są to systemy relacyjnej bazy danych,
w których została zaimplementowana pełna składnia języka SQL. Po drugie zarówno InterBase firmy
Inprise (Borland) i DB2 firmy IBM w wersji Personal Edition są dostępne za darmo. Po trzecie są to w
pełni profesjonalne i popularne systemy, które dodatkowo są łatwe w obsłudze i administrowaniu.
Oba systemy bazy, jak już wspomniałem są dostępne w Internecie za darmo. Na pewno większość
z czytelników wybierze InterBase'a z powodów objętości wersji instalacyjnej. Prawdą jest, że DB2
firmy IBM posiada więcej możliwości, ale jeśli chodzi o zakres interpretowanego języka SQL oba
systemy są sobie równe. Dla osób nie posiadających łącza do Internetu o większej niż przeciętna
przepustowości, InterBase będzie lepszym wyborem.
Dlaczego nie MS Access
Nie chcę umniejszać możliwościom MS Access. W pewnych zastosowaniach, jest to w pełni
funkcjonalny i wystarczający system bazy danych. MS Access został szczelnie obudowany
pomocniczymi narzędziami, które ułatwiają zarządzanie danymi oraz ułatwiają zarządzanie samym
systemem bazy MS Access. Pomimo ze istnieje możliwość „rozmowy" z MS Access za pomocą
języka
SQL,
jest
ona
niewygodna
do
tego
stopnia,
że staje się nieprzyjemna
i zniechęcająca.
Studiowanie
ćwiczeń z InterBase
Aby móc pracować z InterBase, musimy go najpierw zainstalować. Instalacja InterBase została
opisana w rozdziale 13. Tam również znajdziemy informacje o miejscu skąd można skopiować wersję
instalacyjną. Serwer bazy danych InterBase dostępny jest również z większością produktów, narzędzi
programistycznych firmy Inprise (Borland), min: Delphi, JBuilder, C++ Builder. W rozdziale 13.
znajduje się również opis narzędzi InterBase, które umożliwiają na pracę z bazą danych i które
oczywiście umożliwią wykonywanie ćwiczeń. Przed rozpoczęciem studiowania ćwiczeń, ale już po
zainstalowaniu InterBase należy:
1. Stworzyć bazę WYPAUT. Informacje o tym, jak to zrobić znajdziemy w sekcji „Tworzenie bazy
danych w Interbase" w rozdziale 13.
2. Po stworzeniu bazy WYPAUT musimy uruchomić skrypty tworzące i wypełniające danymi tabele
w bazie WYPAUT. Skrypty te można skopiować z serwera ftp wydawnictwa Helion
(ftp://ftp.helion.com.pl/przyklady/cwsql.zip - znajdziesz skrypty dla DB2 i InterBase'a). Skrypty te
znajdują się również na końcu tych ćwiczeń. Zostały one jednak opublikowane dla DB2.
Informacje o tym, jak je dostosować dla InterBase'a znajdziesz w rozdziale 14. w sekcji „Skrypty
tworzące strukturę bazy WYPAUT".
3. Po zainstalowaniu InterBase'a oraz stworzeniu bazy WYPAUT i jej struktury jesteśmy gotowi do
pracy z ćwiczeniami, które polegają na wykonywaniu poleceń SQL.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
2
Studiowanie
ćwiczeń z DB2
Poniżej zamieszone informacje pozwolą nam na usystematyzowanie czynności, jakie musimy
wykonać, aby móc swobodnie korzystać z ćwiczeń, pracując z systemem DB2. Należy przejść przez
następujące kroki:
1. Musimy zainstalować DB2. Zostało to opisane w rozdziale 10. W tym rozdziale znajdziemy
również informacje o tym, skąd zdobyć kopię instalacji DB2 w wersji 7.1 Personal Edition.
2. Następnie musimy uruchomić menedżera DB2. Do tego celu służy polecenie db2start, które należy
wydać w wierszu poleceń systemu operacyjnego lub z poziomu menu Start | Uruchom. W wersji
DB2 dla Windows 95 lub 98 menedżer DB2 startuje automatycznie. W Windows NT również
może okazać się to niekonieczne, gdy podczas instalacji DB2 wyraźnie zaznaczymy, że menedżer
DB2 ma startować automatycznie podczas startu systemu operacyjnego. Jeżeli posiadamy system
Windows NT, możemy użyć narzędzia Usługi z Panelu sterowania. Tam również możemy
uruchomić menedżera DB2.
3. Po upewnieniu się, że menedżer DB2 został uruchomiony, tworzymy bazę danych WYPAUT
w aplikacji Command Linę Processor. Opis procesu tworzenia bazy WYPAUT znajduje się
w rozdziale 12. Narzędzia DB2 w sekcji „Tworzenie bazy" opisującej narzędzie Command Linę
Processor.
4. Teraz musimy dokonać pewnych ustawień w narzędziu Tools Settings. Musimy określić znak
średnika jako znak oddzielający poszczególne wyrażenia SQL wprowadzane m.in. w aplikacji
Command Center oraz w skryptach tworzących strukturę bazy danych. Więcej informacji na temat
tych ustawień znajdziesz w rozdziale 12. „Narzędzia DB2" w sekcji „Ustawienia narzędzi DB2".
5. Po stworzeniu bazy WYPAUT i ustaleniu znaku średnika jako znaku oddzielającego poszczególne
zapytania w skrypcie, musimy uruchomić skrypty tworzące i wypełniające danymi tabele w bazie
WYPAUT. Skrypty te można skopiować z serwera ftp://ftp.helion.com.pl/przyklady/cwsql.zip.
Skrypty te znajdują się również na końcu tych ćwiczeń. Jeżeli nie mamy dostępu do Intemetu,
należy skrypty przepisać do plików, a następnie je uruchomić. Wykonywanie skryptów zostało
opisane w rozdziale 12. „Narzędzia DB2" w sekcji opisującej narzędzie Command Center.
6. Po wykonaniu powyższych zadań jesteśmy gotowi do pracy z ćwiczeniami, które polegają na
wykonywaniu poleceń SQL.
Zapraszam do
ćwiczeń
Uff. Mam nadzieję, że wszyscy przebrną przez powyższe i będą się czuć swobodnie podczas
studiowania tych ćwiczeń. Równie dobrze można zacząć od poznania systemu, jak i narzędzi
wchodzących w skład systemu DB2 lub InterBase. Po zapoznaniu się z nimi i nabraniu pewnej
wprawy w posługiwaniu się nimi, możemy przejść do realizacji zadań wypunktowanych powyżej.
Życzę miłej lektury Autor
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
3
Rozdzia
ł 2. Koncepcja bazy danych
W tym rozdziale przedstawiona zostanie koncepcja bazy danych, jej podstawowe elementy składowe
jak tabele i widoki. Omówione zostaną również typy danych, definiowanie tabel oraz rola
i zastosowanie języka SQL w relacyjnych bazach danych.
Tradycyjne bazy danych
W czasie kiedy komputery nie były wykorzystywane tak powszechnie jak dzisiaj,
informacje były gromadzone na papierze.
Pracownik firmy musiał ręcznie organizować dane. Zapisywał je, wyszukiwał, aktualizował itd.
Dzisiaj, oprócz operacji, które musi wykonać ręcznie, pozostałe wykonuje za pomocą funkcji, które
dostarcza relacyjny system bazy danych.
Rys.2.1.
Relacyjny system bazy danych
Relacyjny system bazy danych przechowuje wszystkie dane w tabelach. Każda tabela zawiera dane na
konkretny temat, np dane o klientach, dane o pracownikach, towarach itp. System bazy danych
zarządza tymi danymi, pozwala m.in. na szybsze ich wyszukanie i zorganizowanie
Za każdym razem gdy potrzebujemy informacji z bazy danych, musimy „zapytać" system bazy
danych w języku, który on rozumie. Tym językiem jest SQL - Structured Qu-ery Language
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
4
Rys.2.2.
Wydobywanie informacji z tradycyjnej bazy danych oraz z systemu relacyjnej
bazy danych
Do dzisiaj istnieją tzw. tradycyjne bazy danych. Są to bazy informacji, których nośnikiem jest po
prostu papier. Wiele instytucji w tym niestety policja oraz szpitale korzysta do dziś z takich baz.
Scenariusz wydobywania danych w takich instytucjach jest następujący:
§
l osoba potrzebująca danych np. przełożony prosi drugą osobę o pewne dane;
§ pracownik po otrzymaniu polecenia szuka informacji wśród dokumentów, które zostały złożone
albo w archiwum lub w po prostu w szafie;
§ pracownik po zebraniu pewnej ilości segregatorów z danego okresu przegląda je, a następnie
przygotowuje zbiorczy dokument zawierający żądane informacje;
§ l po wydobyciu danych i zorganizowaniu ich do odpowiedniej formy wysyła je do przełożonego.
Czas wykonania tych operacji jest różny i zależy od wielkości organizacji, jej struktury,
ilości potrzebnych danych oraz od pracowitości osób je zbierających.
Rys.2.3
W systemie relacyjnej bazy danych wszystkie powyższe operacje sprowadzają się do sformułowania
tego samego pytania o dane ale w formie zrozumiałej dla komputera, a ściślej mówiąc, w formie
zrozumiałej dla systemu bazy danych.
Cała operacja wydobywania danych trwa w tym przypadku znacznie krócej. Jakość tych danych jest
przy tym lepsza.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
5
Mamy więcej pewności, że dane są prawdziwe, że ktoś się nie pomylił lub pominął pewną część
danych przy zestawianiu żądanych informacji.
Rys.2.4.
SQL - Strukturalny j
ęzyk zapytań
Język SQL jest wykorzystywany w większości relacyjnych systemów baz danych. SQL jest
zaimplementowany m.in. w takich systemach baz danych, jak: DB2, Oracie, Inter-Base, MySQL,
dBase, Paradox. Składnia języka SQL dzieli się na trzy typy:
§ język definiowania struktur danych - DDL (Data Definition Language);
§ R język do wybierania i manipulowania danymi - DML (Data Manipulation Language);
§ l język do zapewniania bezpieczeństwa dostępu do danych - DCL (Data Control Language).
Rys.2.5.
Składnia języka SQL wchodząca w skład języka DDL jest używana przez administratorów systemu
relacyjnej bazy danych w celu utrzymania struktury bazy danych, obiektów bazy danych takich jak
m.in. tabele.
Język DCL jest używany przez administratorów do zapewnienia bezpieczeństwa dostępu do danych,
m.in. do nadawania uprawnień do danych.
Język DML jest używany przez wszystkich użytkowników, którzy mają dostęp do bazy danych. Za
pomocą tego typu składni języka SQL użytkownicy mogą otrzymywać, zmieniać dane, dodawać nowe
itp.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
6
Tabela
Tabela składa się z wierszy i kolumn. Wiersze w tabeli są przechowywane w dowolnym porządku.
Dla każdego wiersza każda z kolumn posiada jedno pole z wartością. Wszystkie wartości w kolumnie
są tego samego typu.
Rys.2.6.
W różnych systemach relacyjnej bazy danych jak np. DB2, Oracie, InterBase czy dBase lub Paradox,
każda tabela jest przechowywana w osobnym zbiorze na dysku twardym lub kilka tabel w jednym
zbiorze. Sposób przechowywania danych z tabeli na dysku twardym jest tematem drugorzędnym.
Ćwiczenia te maja przede wszystkim na celu nauczenie języka SQL. Teraz wystarczy tylko wiedzieć,
że sposób przechowywania tabel zależy od implementacji systemu relacyjnej bazy danych.
Konstrukcja nazwy tabeli
Nazwa tabeli składa się z dwóch części. Pierwsza część to kwalifikator, a druga z kolei to nazwa
tabeli. Kwalifikator i nazwa tabeli oddzielone są kropka. Każda tabela musi mieć unikatową nazwę
w granicach kwalifikatora.
Rys.2.7.
Taka konstrukcja nazwy tabeli nie stosuje się we wszystkich relacyjnych bazach da nych. Między
innymi w opisywanym tutaj systemie InterBase. W InterBase odwołanii do tabeli następuje wprost,
np.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
7
SELECT * PROM KLIENCI . . .
w systemie DB2
SELECT * FROM DB2ADMIN.KLIENCI
Typy danych
W różnych systemach relacyjnej bazy danych inaczej nazywają się typy danych. Jednak ich zakres i
typ jest często identyczny. Każdy system relacyjnej bazy danych posiada w swojej dokumentacji
sekcję, która opisuje typy danych używanych w tym systemie. Poniżej znajdują się przykładowe typy
danych wraz z ich opisem.
Tabela 2.1. numeryczne typy danych
Typ danych
Opis
SMALLINT
liczby całkowite z przedziału –32768 do +32767 (czasami ten zakres jest
mniejszy)
INTEGER
liczby całkowite z przedziału –2147483648 do +2147483647 (lub
mniejszy)
DECIMAL (m,n)
liczby rzeczywiste, gdzie m oznacza całkowitą liczbę cyfr, a n oznacza
liczbę cyfr po przecinku
Tabela 2.2. znakowae typy danych
Typ danych
Opis
CHAR (n)
typ znakowy o stałej długości (max. 255 znaków)
VARCHAR (n)
typ znakowy o zmiennej długości
Tabela 2.3.typy danych daty i czasu
Typ danych
Opis
DATE
typ daty (występują różne standardy zapisywania daty)
TIME
typ czasu (występują różne standardy zapisywania czasu)
Tworzenie tabeli - CREATE TABLE
Tworzenie tabeli polega na definiowaniu jej kolumn. Dla każdej kolumny należy określić nazwę
kolumny, typ danych i długość (w zależności od typu) oraz to, czy jest dozwolone pozostawienie
wartości pustej w kolumnie.
CREATE TABLE UZYTKOWNIK.PRACOWNICY (
ID_PRACOW
CHAR(6)NOT NULL,
IMIE
VARCHAR(18)NOT NULL,
NAZWISKO
VARCHAR(24) NOT NULL,
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
8
STANOWISKO
VARCHAR(12)NOT NULL,
DZIAL
VARCHAR(12) NOT NULL,
DATA_URODZ
DATE,
TELEFON_DOM
CHAR(12));
Warto
ść pusta NULL
Wartość
NULL
jest to wartość nieokreślona, która może zostać użyta w każdym polu tabeli niezależnie
od typu kolumny. Wartość
NULL
jest różna od zera lub spacji.
W tabeli na rysunku 2.8 osobom, które nie posiadają firmy, w kolumnach: FIRMA oraz NIP
przypisano wartość
MULL
.
R
YS
.2.8.
Przy konstruowaniu tabeli poleceniem
CREATE TABLE
w poprzedniej sekcji określiliśmy dla pewnych
kolumn parametr
NOT NULL
.
Oznacza to, że przy wstawianiu nowych wierszy musimy określić
wartości dla tych kolumn, nie mogą one być wartością
NULL
.
Definicja kolumny w poleceniu
CREATE
TABLE
pozostawiona bez klauzuli
NOT NULL
określa, że dozwolone jest wstawienie do tej kolumny
wartości
NULL
.
Istnieje jeszcze opcja o następującej składni:
NOT NULL WITH DEFAULT ( (wartość)]
gdzie parametr wartość określa domyślną wartość dla kolumny. Wartość domyślna zostanie nadana
dla kolumny automatycznie, gdy nie określimy jej wprost przy wstawianiu nowego wiersza do tabeli.
Autoryzacja dost
ępu do tabeli
Możemy udostępnić nasze dane innym użytkownikom, a ściślej mówiąc możemy udostępnić tabele
innemu użytkownikowi. W tym celu stosuje się polecenie języka SOŁ
GRANT
.
Poniższy przykład
nadaje uprawnienia użytkownikowi o nazwie UŻYTKOWNIK do tabeli PRACOWNICY. Od tej pory
UŻYTKOWNIK może wybierać (wykonywać zapytania
SELECT
)
dane z naszej tabeli.
GRANT SELECT ON PRACOWNICY TO U
ŻYTKOWNIK;
Prawa do tabeli można odebrać poleceniem
REVOKE
.
Oto przykład:
REVOKE SELECT ON PRACOWNICY FROM U
ŻYTKOWNIK;
Powyższy przykład użycia polecenia
GRANT
umożliwia tylko wybieranie danych z tabeli. Poniższy
przykład umożliwia wybieranie, wstawianie i aktualizowanie danych w tabeli.
GRANT SELECT, INSEKT, UPDATE ON PRACOWNICY TO UZYTKOWNIK;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
9
Widoki
Za pomocą widoków możemy ograniczyć zakres danych dostępnych dla użytkownika. Widok
może ograniczać dane z jednej tabeli lub może to być kompozycja danych z kilku tabel. Dane
w widoku mogą być ograniczone do kilku kolumn lub do pewnego zakresu wierszy.
Widoki stosuje się w różnych celach:
§ w celu zabezpieczenia danych przed niepowołanym dostępem;
§ uproszczenia korzystania z danych dla końcowego użytkownika.
Przykładem zwiększenia bezpieczeństwa może być widok, który nie obejmuje kolumny z danymi o
zarobkach. Wiadomo, że nie wszyscy użytkownicy powinni mieć dostęp do takich danych.
Rys.2.9.
Podsumowanie
1. Relacyjna baza danych jest kolekcją tabel.
2. Użytkownicy nie musza się martwić o to, jak dane są przechowywane w bazie danych oraz jak są
wydobywane.
3. SQL jest językiem do komunikowania się z baza danych.
4. Język SQL jest używany do:
§ wydobywania danych (
SELECT
);
§ manipulowania danymi (
IHSERT
,
UPDATE
,
DELETE
);
§ definiowania, redefiniowania i usuwania obiektów wchodzących w skład
§ struktury bazy danych (
CREATE
,
ALTER
,
DROP
);
§ definiowania uprawnień do danych (
GRANT
,
REVOKE
).
5. Użytkownicy mogą mieć dostęp do danych poprzez widoki.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
10
Rozdzia
ł 3. Zapytania SQL.
Polecenie
SELECT
jest używane do pobierania danych z bazy danych (z tabel lub widoków). W tym
rozdziale zapoznamy się ze składnią polecenia
SELECT
.
Rozdział ten ma na celu nauczenie formułowania zapytań SQL do wyświetlana wsstkich wierszy z
tabeli, wybierania określonych kolumn, używania warunków, używania stów kluczowych
BETWEEN, IN, LIKE Oraz DISTINCT.
Struktura polecenia SELECT
Tabela 3.1
SELECT
opisuje 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)
ORDER BY
nazwy kolumn lub pozycje kolumn
Każde polecenie
SELECT
musi posiadać klauzule
SELECT
oraz
FROM
,
pozostałe klauzule są opcjonalne.
Inne klauzule wchodzące w skład polecenia
SELECT
zostaną szczegółowo omówione później.
Wybieranie wszystkich kolumn
Poniższe polecenie
SELECT
wyświetla wszystkie kolumny i wiersze z tabeli PRACOWNICY.
SELECT
*
FROM DB
2
ADMIN
.
PRACOWNICY
;
Rys. 3.1.
Wybieranie wszystkich kolumn i wierszy ma sens tylko w przypadku małych tabel, W praktyce
buduje się zapytania, które znacznie ograniczają wynik zapytania.
Wybieranie okre
ślonych kolumn
Polecenie
SELECT
,
którego użyjemy za chwilę, wyświetla kolumny IMIĘ, NAZWISKO i DZIAŁ z
tabeli PRACOWNICY.
SELECT IMIE, NAZWISKO, DZIAL FROM DB2ADMIN.PRACOWNICY;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
11
Rys.3.2
Wybieranie i jednoczesnym porz
ądkowaniem
Następujące polecenie
SELECT
wyświetla kolumny IMIĘ, NAZWISKO i DZIAŁ z tabeli
PRACOWNICY i jednocześnie porządkuje dane według nazwiska.
SELECT IMIE, NAZWISKO, DZIAL FROM DB2ADMIN.PRACOWNICY
ORDER BY NAZWISKO ASC;
Rys.3.3.
Wynik wykonania zapytania jest uporządkowany według kolumny wskazanej w klauzuli ORDER BY.
Słowo kluczowe
ASC
mówi o tym, że sortowanie zostanie dokonane w porządku rosnącym.
Sortowanie rosnące jest domyślne więc słowo kluczowe
ASC
nie musi być wyspecyfikowane.
Porządek malejący uzyskuje się przez zastosowanie słowa
DESC
.
W zależności od implementacji bazy danych kolumna występująca w klauzuli ORDER
BY
musi być
częścią wyniku wykonania zapytania.
Możliwe jest wskazanie większej liczby kolumn w klauzuli
ORDER BY
.
Przykładowo może istnieć
potrzeba wybrania danych w tabeli z jednoczesnym sortowaniem według stanowiska, na którym dana
osoba pracuje, a następnie według nazwiska.
SELECT IMI
Ę, NAZWISKO, STANOWISKO, DZIAŁ
FROM DB2ADMIN.PRACOWNICY ...
ORDER BY STANOWISKO ASC, NAZWISKO ASC;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
12
Rys.3.4.
Istnieje inny sposób na wskazanie kolumn w klauzuli
ORDER BY
.
Zamiast nazywać kolumny, możemy
je wskazać poprzez ich pozycje na liście
SELECT
.
SELECT IMIE, NAZWISKO, STANOWISKO, DZIAL FROM DB2ADMIN.PRACOWNICY
ORDER BY 3 ASC, 2 ASC;
Inne przykłady:
ORDER BY 3 ASC, NAZWISKO ASC
ORDER BY 3 ASC, 2 ASC, DZIAL ASC;
Dozwolona jest tylko jedna klauzula
ORDER BY
w zapytaniu
SELECT
.
Klauzulę
ORDER BY
określa się
jako ostatnią w całym zapytaniu
SELECT
.
Wybieranie niepowtarzaj
ących się wierszy
Słowo kluczowe
DISTINCT
zapewnia, że wynik zwrócony z zapytania zawierać będzie tylko
niepowtarzające się wiersze. Wszystkie powtarzające się wartości nie zostaną wyświetlone.
SELECT DISTINCT STANOWISKO FROM DB2ADMIN.PRACOWNICY;
Rys.3.5.
Słowo kluczowe
DISTINCT
musi występować zaraz po słowie kluczowym
SELECT
.
SELECT DISTINCT STANOWISKO, DZIA
Ł FROM DB2ADMIN. PRACOWNICY;
Takie zapytanie wyświetli wszystkie stanowiska obejmowane w danych działach. Jeżeli w danym
dziale pojawią się dwa takie same stanowiska, tylko jedno zostanie wyświetlone.
Słowo
DISTINCT
eliminuje wiersze, które posiadają duplikaty we wszystkich kolumnach
wyspecyfikowanych w wyrażeniu
SELECT
.
Tylko jedno słowo
DISTINCT
może zostać użyte w całym
zapytaniu
SELECT
.
Wybieranie okre
ślonych wierszy
Do wybrania określonych wierszy z tabeli używa się klauzuli
WHERE
,
która służy do określenia
kryterium wyboru wierszy. W klauzuli WHERE specyflkujemy warunek, który musi być spełniony
dla szukanych wierszy.
SELECT IMI
Ę, NAZWISKO, STANOWISKO, DZIAL
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
13
PROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO = 'SPRZEDAWCA
’
;
Rys.3.6.
W przypadku kolumn typu znakowego, daty lub czasu, wartości dla których sprawdzany jest warunek
muszą być otoczone apostrofem. Przy porównywaniu kolumn typu znakowego należy pamiętać, że
rozróżniane są wielkie i małe litery. Dla kolumn typu numerycznego jak np. INTEGER, SMALLINT,
wartości do porównania nie są otaczane apostrofem.
SELECT NR_KLIENTA, NR_SAMOCHODU, NR_PRACOW_WYP, CENA_JEDN
FROM DB2ADMIN.WYPOZYCZENIA
WHERE CENA_JEDN >= 100;
Operatory logiczne u
żywane w klauzuli WHERE
SELECT NR_KLIENTA, NR_SAMOCHODU, NR_PRACOW WYP, CENA_JEDN
FROM DB2ADMIN. WYPOZYCZENIA
WHERE CENA_JEDN = 100 - równa
CENA_JEDN <> 100 - nie równa
CENA_JEDN > 100 - wi
ększa niż
CENA_JEDN >= 100 - wi
ększa lub równa
CENA_JEDN < 100 - mniejsza ni
ż
CENA_JEDN <= 100 - mniejsza lub równa
Operatory AND oraz OR
Kiedy w warunku używamy operatora
AND
,
aby wiersz został zawarty w wyniku, oba warunki
połączone operatorem
AND
muszą zostać spełnione, tzn. muszą zwrócić wartość prawdy (TRUE).
Warunek z operatorem
OR
zwróci wartość TRUE, gdy przynajmniej jedna ze stron zwróci wartość
TRUE.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
14
Rys.3.7.
SELECT IMIE, NAZWISKO, STANOWISKO, DZIAL
FROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO = 'SPRZEDAWCA'
AND DZIAL = 'OBS
ŁUGA KLIENTA’;
Takie zapytanie SQL zwróci w wyniku wszystkich pracowników pracujących na stanowisku
sprzedawca w dziale obsługi klienta.
SELECT IMIE, NAZWISKO, STANOWISKO, DZIAL
FROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO = 'SPRZEDAWCA'
OR DZIAL = 'TECHNICZNY';
Rys.3.8.
Następne zapytanie zwróci wszystkich pracowników pracujących na stanowisku sprzedawca oraz
wszystkich pracowników pracujących w dziale technicznym niezależnie od tego, czy pracują na
stanowisku sprzedawca.
Operatorów
AND
i
OR
możemy używać razem do budowy bardziej złożonych warunków. Następujące
zapytanie zwróci wszystkich pracowników pracujących na stanowisku kierownika w dziale obsługi
klienta oraz wszystkich pracowników z działu technicznego.
Wiersze zostaną uporządkowane wg działu a następnie wg nazwiska.
SELECT IMIE, NAZWISKO, STANOWISKO, DZIAL
FROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO = 'KIEROWNIK'
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
15
AND DZIAL = 'OBS
ŁUGA KLIENTA'
OR DZIAL = 'TECHNICZNY'
ORDER BY DZIAL, NAZWISKO;
Rys.3.9.
W poprzednim przykładzie widoczna jest wyższość operatora
AND
nad operatorem
OR
.
Następne
zapytanie posiada w klauzuli
WHERE
warunki otoczone nawiasami. Nawiasy pozwalają określić
kolejność sprawdzania warunków.
SELECT IMIE, NAZWISKO, STANOWISKO, DZIAL
FROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO = 'KIEROWNIK
’
AND (DZIAL = 'OBS
ŁUGA KLIENTA' OR DZIAŁ = 'TECHNICZNY')
ORDER BY DZIAL, NAZWISKO;
Zapytanie wyświetli osoby pracujące tylko na stanowisku kierownika w dziale obsługi klienta lub w
dziale technicznym.
Rys.3.10.
Predykat IN
Predykat
IN
pozwala porównać wartość do wartości ze zbioru. Wartości typu znakowego, daty i czasu
muszą być otoczone apostrofem.
SELECT IMIE, NAZWISKO, STANOWISKO, DZIAL
FROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO IN ('SPRZEDAWCA
’
, 'KIEROWNIK');
Rys.3.11.
Wartości mogą być typu numerycznego, znakowego, typu daty lub czasu.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
16
SELECT MARKA, TYP, ROK_PROD, POJ_SILNIKA
FROM DB2ADMIN. SAMOCHODY
WHERE POJ_SILNIKA IN (1400, 1600);
Rys.3.12.
Predykat BETWEEN
Predykat
BETWEEN
pozwala sprawdzić, czy dana wartość zawiera się między dwoma wskazanymi
wartościami.
SELECT MARKA, TYP, ROK_PROD, KOLOR, POJ_SILNIKA
FROM DB2ADMIN.SAMOCHODY
WHERE POJ_SILNIKA BETWEEN 1100 AND 1800;
Rys.3.13.
Zapytanie zwróciło dane o samochodach, których pojemność silnika zawiera się miedzy 1100 a 1800
cm sześciennych.
Klauzula:
WHERE POJ_SILNIKA BETWEEN 1100 AND 1800;
jest równa następującemu zapisowi:
WHERE POJ_SILNIKA >= 1100 AND POJ_SILNIKA <= 1800;
Wybieranie warto
ści NULL
Wybieranie wierszy z tabeli, w których jedno z pól zawiera wartość pustą
NULL
,
polega na użyciu
predykatu
NULL
.
W przykładzie użycia predykatu
NULL
wybieramy wszystkich klientów, którzy nie posiadają karty
kredytowej. Zwrócone zostaną wiersze z danymi o klientach, którzy w polu NR_ KARTY_KREDYT
nie posiadaj ą żadnego wpisu.
SELECT IMI
Ę, NAZWISKO, ULICA, MIASTO
PROM DB2ADMIN.KLIENCI
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
17
WHERE NR_KARTY_KREDYT IS NULL;
Możliwe jest wybranie wszystkich klientów posiadających kartę kredytową. Wtedy w klauzuli
WHERE
dla sprawdzenia wartości w polu NR_KARTY_KREDYT używamy również predykatu
NULL
,
ale z
zaprzeczeniem.
SELECT IMIE, NAZWISKO, NR_KARTY_KREDYT, MIASTO
FROM DB2ADMIN.KLIENCI
WHERE NR_KARTY_KREDYT IS NOT NULL;
Rys.3.14.
Wyszukiwanie cz
ęściowe - predykat LIKE
Często istnieje konieczność wyszukania np. nazwisk klientów, które zaczynają się od konkretnej
litery.
SELECT IMIE, NAZWISKO, ULICA, MIASTO
FROM DB2ADMIN.KLIENCI
WHERE NAZWISKO LIKE 'K%';
Rys.3.15.
Inne przykłady użycia predykatu
LIKE
:
SELECT IMIE, NAZWISKO, ULICA, MIASTO
FROM DB2ADMIN.KLIENCI
WHERE NAZWISKO LIKE '%SKI';
Rys.3.16.
Zapytanie zwróci wiersze z danymi o klientach, których nazwiska kończą się na „ski".
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
18
W następnym przykładzie wyszukamy klientów, którzy w swoim nazwisku posiadają litery „K" oraz
„A" w wymienionym porządku.
SELECT IMIE, NAZWISKO, ULICA, MIASTO
FROM DB2ADMIN.KLIENCI
WHERE NAZWISKO LIKE '%K%A%';
Rys.3.17.
W zapytaniach z predykatem
LIKE
można stosować zaprzeczenie NOT oraz operatory
AND
i OR. Oto
przykłady:
SELECT IMIE, NAZWISKO, ULICA, MIASTO
FROM DB2ADMIN.KLIENCI ,
WHERE NAZWISKO NOT LIKE 'K%';
Rys.3.18.
Następujące zapytanie wyszuka wszystkich klientów, których nazwiska nie zaczynają się na literę „K"
oraz „D".
SELECT IMIE, NAZWISKO, ULICA, MIASTO
FROM DB2ADMIN.KLIENCI
WHERE NAZWISKO NOT LIKE 'K%'
AND NAZWISKO NOT LIKE 'D%' ;
Możliwe jest również wyszukanie np. klientów, których nazwiska zawierają drugą literę „O". Znak
„_" zastępuje dowolny pojedynczy znak.
SELECT IMIE, NAZWISKO, ULICA, MIASTO
FROM DB2ADMIN.KLIENCI
WHERE NAZWISKO LIKE '_0%';
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
19
Rys.3.19.
Oto drugi przykład, w którym pomijamy dwie pierwsze litery nazwiska:
SELECT IMIE, NAZWISKO, ULICA, MIASTO
FROM DB2ADMIN.KLIENCI
WHERE NAZWISKO LIKE '_C%';
Podsumowanie
1. Do wybierania danych z tabeli służy polecenie
SELECT
.
2. Można wybierać wszystkie i określone kolumny tabeli.
3. Można wybierać wszystkie i określone wiersze.
4. Można wybierać dane i jednocześnie je uporządkować.
5. W zapytaniu
SELECT
można użyć słów kluczowych:
§
DISTINCT
- w celu wyszukania nie powtarzających się wierszy;
§
LIKE
- w celu określenia wartości dla warunku;
§
IN - w celu wskazania zbioru wartości dla warunku;
§
BETWEEN
- w celu wskazania zakresu wartości dla warunku.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
20
Rozdzia
ł 4 . Wybieranie danych z wielu tabel.
W tym rozdziale zajmiemy się wyszukiwaniem danych z wielu tabel. Do tej pory wszystkie zapytania
wyszukujące koncentrowały się na jednej tabeli. Poza tym nauczymy się używać skrótów w
odwoływaniu się do tabel w zapytaniach SQL. Poznamy również predykat
JOIN
.
R
YS
.4.1.
W naszej przykładowej bazie danych WYPAUT, dla każdego numeru miejsca (miejsca pracy
pracownika) w tabeli PRACOWNICY istnieje jeden wiersz w tabeli MIEJSCA.
Rys. 4.2.
DB2 odczytuje numer miejsca pracy pracownika z tabeli PRACOWNICY, a następnie przeszukuje
tabelę MIEJSCA w celu znalezienia odpowiadającego temu numerowi wiersza, który opisuje
dokładnie miejsce pracy tzn. adres, telefon itd. W języku baz danych, jakim jest SQL, pytanie
przedstawione na poprzednim rysunku może wyglądać tak:
SELECT DB2ADMIN.PRACOWNICY.NAZWISKO,
DB2ADMIN.PRACOWNICY.STANOWISKO,
DB2ADMIN.PRACOWNICY.DZIAL,
DB2ADMIN.MIEJSCA.MIASTO,
DB2ADMIN.MIEJSCA.ULICA
FROM DB2ADMIN.PRACOWNICY,DB2ADMIN.MIEJSCA
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
21
WHERE
DB2ADMIN.PRACOWNICY.NR_MIEJSCA=DB2ADMIN.MIEJSCA.NR_MIEJSCA
ORDER BY DB2ADMIN.PRACOWNICY.NAZWISKO;
Rys.4.3.
Wybieranie danych z wielu tabel nazywa się powszechnie złączeniem (ang. join). W celu złączenia
dwóch lub większej ilości tabel:
§ w klauzuli
SELECT
musimy wyspecyfikować kolumny, które chcemy zawrzeć w zapytaniu;
§ w klauzuli
FROM
określamy nazwy złączanych tabel;
§ w klauzuli
WHERE
określamy warunki złączenia.
Sk
ładnie złączenia - predykat JOIN
Istnieją dwa typy składni zapytania złączającego. Pierwszy typ został zaprezentowany już w
poprzedniej sekcji. Oto zapytanie, które zostało zbudowane przy użyciu tej składni:
SELECT DB2ADMIN.PRACOWNICY.NAZWISKO,
DB2ADMIN.PRACOWNICY.STANOWISKO,
DB2ADMIN.PRACOWNICY.DZIAL,
DB2ADMIN.MIEJSCA.MIASTO,
DB2ADMIN.MI
ĘJSCA.ULICA
FROM DB2ADMIN.PRACOWNICY,
DB2ADMIN.MIEJSCA
WHERE DB2ADMIN.PRACOWNICY.NR_MIEJSCA = DB2ADMIN.MIEJSCA.NR_MIEJSCA
ORDER BY DB2ADMIN.PRACOWNICY.NAZWISKO;
Przy złączaniu dwóch tabel, do poprawnego wyświetlenia wyniku klauzula
WHERE
musi zawierać
jeden warunek. Gdy złączamy trzy tabele, klauzula WHERE musi zawierać przynajmniej dwa
warunki. Dwa pierwsze warunki w tym przykładzie dotyczą złączenia tabel, trzeci dotyczy warunku
wyboru wierszy. Oto przykład:
SELECT DB2ADMIN. WYPO
ŻYCZENIA.NR_WYPOZYCZENIA,
DB2ADMIN.PRACOWNICY.NAZWISKO,
DB2ADMIN.PRACOWNICY.STANOWISKO,
DB2ADMIN.PRACOWNICY.DZIAL,
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
22
DB2ADMIN.MIEJSCA.MIASTO,
DB2ADMIN.MIEJSCA.ULICA
FROM DB2ADMIN.PRACOWNICY,
DB2ADMIN.MIEJSCA,
DB2ADMIN.WYPOZYCZENIA
WHERE DB2ADMIN.PRACOWNICY.NR_MIEJSCA = DB2ADMIN.MIEJSCA.NR_MIEJSCA
AND DB2ADMIN.PRACOWNICY.NR_PRACOWNIKA = DB2ADMIN.WYPOZYCZENIA.NR_PRACOW_WYP
AND DB2ADMIN.MIEJSCA.MIASTO = 'WARSZAWA'
ORDER BY DB2ADMIN.PRACOWNICY.NAZWISKO;
Rys.4.4.
Inny typ złączenia polega na zastosowaniu konstrukcji
JOIN
...
ON
.
SELECT DB2ADMIN.PRACOWNICY.NAZWISKO,
DB2ADMIN.PRACOWNICY.STANOWISKO, DB2ADMIN.PRACOWNICY.DZIAL,
DB2ADMIN.MIEJSCA.MIASTO, DB2ADMIN.MIEJSCA.ULICA
FROM DB2ADMIN.PRACOWNICY JOIN
DB2ADMIN.MIEJSCA ON
DB2ADMIN.PRACOWNICY.NR_MIEJSCA = DB2ADMIN.MIEJSCA.NR_MIEJSCA
WHERE DB2ADMIN.PRACOWNICY.STANOWISKO = 'SPRZEDAWCA'
ORDER BY DB2ADMIN.PRACOWNICY.NAZWISKO;
Kiedy używamy słowa
JOIN
w klauzuli
FROM
,
warunki złączenia muszą być wyspecyfikowane po
klauzuli
ON
.
W klauzuli
WHERE
można określić dodatkowe warunki. Oto wynik wykonania
powyższego zapytania:
Rys.4.5.
Stosowanie aliasów w zapytaniu
Aliasy definiuje się w celu skrócenia nazwy tabeli. Jak wiemy na nazwę tabeli składa się kwalifikator i
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
23
nazwa tabeli. Kwalifikator mówi o tym, kto jest właścicielem tabeli. W naszym przykładzie użycia
aliasów, alias P wskazuje na tabelę DB2ADMIN.PRACOWNICY natomiast alias M opisuje tabelę
DB2ADMIN.MIEJSCA.
SELECT P.NAZWISKO, P.STANOWISKO, P.DZIA
Ł , M.MIASTO, M.ULICA
FROM DB2ADMIN.PRACOWNICY P,
DB2ADMIN.MIEJSCA M
WHERE
P.NR_MIEJSCA = M.NR_MIEJSCA AND P.STANOWISKO = 'SPRZEDAWCA'
ORDER BY P.NAZWISKO;
Wynik wykonania tego zapytania jest taki sam jak w ten w poprzedniej sekcji. Począwszy od tej sekcji
w przykładach będziemy stosować aliasy dla nazw tabel.
Podsumowanie
1. Dane mogą być wydobywane z jednej lub wielu tabel.
2. W zapytaniu wybierającym dane z przynajmniej dwóch tabel można użyć predykatu JOIN.
3. Jeżeli w zapytaniu, które wybiera dane z przynajmniej dwóch tabel, nie zostanie
wyspecyfikowany warunek po słowie kluczowym
WHERE
lub
ON
,
to zwrócony wynik będzie
przedstawiał iloczyn kartezjański.
4. W zapytaniach można użyć aliasów zamiast nazw tabel.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
24
Rozdzia
ł 5. Funkcje skalarne i arytmetyczne.
W tym rozdziale zajmiemy się używaniem funkcji skalarnych i arytmetycznych. Będziemy używać
funkcji arytmetycznych do przeliczania wartości w kolumnach. Poznamy podstawowe funkcje
skalarne m.in. funkcje operujące na datach i czasie. Pod koniec tego rozdziału poznamy sposób na
wybieranie wartości przy użyciu wyrażenia
CASE
.
Wybieranie wyliczonych warto
ści
W zapytaniu SQL możemy użyć 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ń.
SELECT P.IMI
Ę, P.NAZWISKO, P.PENSJA, P.DODATEK, P.PENSJA + P.DODATEK
FROM DB2ADMIN.PRACOWNICY'P WHERE P.PENSJA > 1100
ORDER BY P,NAZWISKO;
Rys.5.1.
Wynik zapytania zawiera obliczoną kolumnę, która jest sumą kolumn; PENSJA I DODATEK.
Kolumna z wynikiem została domyślnie nazwana „5", ponieważ jest ona piąta z kolei. Nazwa taka
została nadana w DB2 zainstalowanym pod kontrolą systemu Windows NT. W innych systemach
operacyjnych, DB2 może wy liczoną kolumnę nazywać inaczej.
Dla dwóch pracowników, którzy zajmują stanowisko kierowników nie zostały obliczone wartości. Nie
posiadają oni żadnego dodatku. Ściślej mówiąc, w polu DODATEK wartość dodatku dla tych osób
wynosi
MOLL
.
Wartości NULL nie mogą brać udziału w obliczeniach. W dalszej części tego rozdziału
dowiemy się, jak obejść taki przypadek.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
25
Nazywanie wyliczone. Kolumny
Kolumnę wynikową możemy nazwać. Poniżej znajduje się identyczne zapytanie jak w poprzedniej
sekcji. Po słowie kluczowym
AS
podana jest nazwa dla nowej wyliczonej kolumny.
SELECT P.IMIE, P.NAZWISKO, P.PENSJA, P.DODATEK,
P.PENSJA + P.DODATEK AS DO_WYPLATY
FROM DB2ADMIN.PRACOWNICY P
WHERE P. PENSJA > 1100
ORDER BX P.NAZWISKO;
Rys.5.2.
W tym przykładzie tak samo jak w przykładzie z poprzedniej sekcji do obliczeń nie mogła być wzięta
pod uwagę wartość
NULL
.
Stąd puste pola widoczne na powyższym rysunku. Zostanie to rozwiązane
w następnej sekcji.
Nazwa tabeli wyliczonej może być otoczona cudzysłowem co pozwala na użycie nazwy składającej
się z kilku słów. Ilustruje to poniższy przykład.
SELECT P.IMIE, P.NAZWISKO, P.PENSJA, P.DODATEK,
P.PENSJA + P.DODATEK AS "DO WYPLATY"
FROM DB2ADMIN.PRACOWNICY P
WHERE P.PENSJA > 1100 ORDER BY P.NAZWISKO;
Nowa nazwa kolumny wyliczonej nie może być użyta w klauzuli
WHERE
.
W systemie DB2 może być
natomiast użyta w
ORDER BY
.
SELECT P.IMIE, P.NAZWISKO, P.PENSJA, P.DODATEK
P.PENSJA + P.DODATEK AS DO_WYPLATY
FROM D32ADMIN. PRACOWNICY P
WHERE P.PENSJA > 1100
ORDER BY DO_WYPLATY;
Jak widać w powyższym przykładzie, nazwa kolumny DO_WYPLATY w klauzuli
ORDER BY
nie
może być poprzedzona aliasem jak pozostałe kolumny.
W systemie InterBase, nowa nazwa kolumny wyliczonej nie może być użyta w klauzuli
ORDER BY
.
Zamiast nazwy możemy wskazać numer kolumny, względem której będziemy porządkować dane. Oto
przykład tego samego polecenia dla systemu InterBase:
SELECT P.IMIE, P.NAZWISKO, P.PENSJA, P.DODATEK,
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
26
P.PENSJA + P.DODATEK AS DO_WYPLATY
FROM PRACOWNICY P WHERE P.PENSJA > 1100
Funkcja COALESCE
Funkcja
COALESCE
została zaimplementowana tylko w systemie DB2. Funkcja
COALESCE
jest funkcją
operującą na wartości
NULL
.
Zwraca pierwszy argument który nie jest wartością
NULL
.
Funkcja ta jest
równoważna funkcji
VALUE
.
Funkcja
YALUE
jest synonimem funkcji
COALESCE
.
,
Poniższy przykład użycia funkcji
COALESCE
rozwiązuje nasz problem z poprzedniej sekcji. Działanie
funkcji
COALESCE
najpierw w kolumnie DODATEK zamienia wszystkie wystąpienia wartości
NULL
na wartość zera, a następnie robi to samo przy obliczaniu wartości do wypłaty.
SELECT P.IMIE, P.NAZWISKO, P.PENSJA,
COALESCE ( P.DODATEK, O ) AS DODATEK,
P.PENSJA + COALESCE (P.DODATEK, 0) AS DO_WYP
ŁATY
FROM DB2ADMIN.PRACOWNICY P
WHERE P,PENSJA > 1100 ORDER BY P.NAZWISKO;
Rys.5.3.
W kolejnym 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 zostali wszyscy klienci.
Dla tych, którzy nie posiadają karty kredytowej, w polu NRJCARTY został wpisany ciąg „nie
posiada".
SELECT K.IMIE, K.NAZWISKO,
COALESCE<K.NR_KARTY_KREDYT, 'Nie posiada') AS NR__KARTY
FROM DB2ADMIN.KLIENCI K;
Rys.5.4.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
27
Dziesi
ętna reprezentacja wartości
Funkcja
DECIMAL
została zaimplementowana tylko w systemie DB2. Funkia DBCIMAL zwraca
dziesiętną reprezentację wartości numerycznej.
Pierwszy parametr zawiera wartość do reprezentacji, drugi parametr określa ilość cyfr przed
przecinkiem, trzeci parametr określa liczbę miejsc po przecinku.
SELECT P.IMIE, P.NAZWISKO, P.PENSJA,
DECIMAL ( (P.PENSJA * 11.3)/100, 8, 2} AS KWOTA_PODWYZKI
FROM DB2ADMIN.PRACOWNICY P ORDER BY P.NAZWISKO;
Przykład oblicza kwotę 11.3% podwyżki.
Rys.5.5.
Zaokr
ąglanie wyników
Funkcja
ROUND
została zaimplementowana tylko w systemie DB2. Służy ona do zaokrąglania
wyników, Funkcja ta w pierwszym argumencie musi zawierać wartość do zaokrąglenia, w drugim
natomiast podaje się liczbę miejsc po przecinku, do jakiej ma zostać zaokrąglona
wartość. Poniższy przykład zaokrągla wartości do liczb całkowitych. Wartości dziesiętne poniżej 0,50
zostały zaokrąglone do zera, natomiast powyżej 0,50 do jedności.
SELECT P.IMIE, P.NAZWISKO, P.PENSJA,
ROUND ( (P.PENSJA * 11.31/100, 0) AS KWOTA_PODWYZKI
FROM DB2ADMIN.PRACOWNICY P
ORDER BY P.NAZWISKO;
Rys.5.6.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
28
Porównania daty
Kolumny typu daty lub czasu mogą być porównywane z innymi wartościami reprezentującymi datę
lub czas. Wartości przedstawiające datę lub czas muszą być otoczone pojedynczym cudzysłowem. W
poniższym przykładzie zostaną wyświetlone dane
pracowników zatrudnionych w lub po dacie 1998-01-01.
SELECT P.IMIE, P.NAZWISKO, P.DZIAL,
P.STANOWISKO, P.DATA_ZATR
FROM DB2ADMIN. PRACOWNICY P
WHERE P.DATA_ZATR >= '1998-01-01'
ORDER BY P.NAZWISKO;
Rys.5.7.
Kolejne zapytanie wybiera pracowników zatrudnionych co najmniej 2 lata. Porównywana wartość
020000 przedstawia 02 rok, 00 miesięcy i 00 dni. Funkcja
CURRENT DATĘ
zwraca bieżącą datę.
Funkcja ta nie jest dostępna w systemie InterBase.
SELECT P.IMIE, P.NAZWISKO, P.DZIAL,
P.STANOWISKO, P.DATA__ZATR
FROM DB2ADMIN.PRACOWNICY P
WHERE CURRENT DATE - P.DATA_ZATR >= 020000
ORDER BY P.NAZWISKO;
Rys.5.8.
Oprócz funkcji
CORRENT DATĘ
,
która zwraca bieżącą datę, mamy do wykorzystania funkcję
zwracającą bieżący czas
CURRENT TIME
oraz funkcję
CURRENT TIMESTAMP
zwracającą dokładny
bieżący czas. Obie pozostałe funkcje również nie są dostępne w systemie InterBase.
Funkcje daty
Funkcja
YEAR
pozwala odczytać rok z pełnego formatu daty. Funkcja
YEAR
oraz wszystkie pozostałe
w tej sekcji nie zostały niestety zaimplementowane w InterBase. Kolejny przykład jest identyczny do
tego z poprzedniej sekcji z tym wyjątkiem, że dodatkowa kolumna przedstawia ilość przepracowanych
lat przez pracownika, który pracuje dłużej niż dwa lata.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
29
SELECT P.IMIE, P.NAZWISKO, P.DZIAL, P.STANOWISKO, P.DATA_ZATR,
YEAR (CURRENT DAT
Ę - P.DATA_ZATR) AS ILOSC_LAT
FROM DB2ADMIN.PRACOWNICY P
WHERE CURRENT DATE - P.DATA_ZATR >= 020000
ORDER BY P.NAZWISKO;
Rys.5.9.
Poza funkcją
YEAR
mamy do dyspozycji funkcje
MDNTH
oraz
DAY
,
które odpowiednio wydobywają z
daty miesiąc i dzień. Oto przykład:
SELECT P. IMI
Ę, P. NAZWISKO, P.DATA_ZATR
YEAR(P.DATA_ZATR) AS ROK,
MONTH(P.DATA_ZATR) AS MIESIAC,
DAY(P.DATA_ZATR) AS DZIEN
FROM DB2ADMIN.PRACOWNICY P;
Rys.5.10.
W naszej przykładowej bazie danych znajduje się tabela WYPOŻYCZENIA, która m.in. przechowuje
dane o dacie wypożyczenia samochodu i o dacie jego oddania. Następny przykład będzie obliczał
ilość dni, przez które samochód był wypożyczony.
SELECT K.NAZWISKO, W.NR_WYPOZYCZENIA,
W.DATA_WYP, W.DATA_ODD,
DAYS(W.DATA_ODD)- DAYS(W.DATA_WYP) + l AS ILOSC_DNI
FROM DB2ADMIN.KLIENCI K, DB2ADMIN.WYPO
ŻYCZENIA W
WHERE K.NR_KLIENTA = W.NR_KLIEHTA AND W.DATA_ODD IS NOT NULL;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
30
Rys.5.11.
Ciąg DAYS(W.DATA_ODD) - DAYS (W. DAT_WYP) + l AS ILOSC_DNI występujący w
zapytaniu odejmuje od daty oddania datę wypożyczenia samochodu i dodaje jeden. Dodanie jednego
dnia ma na celu zaznaczenie sytuacji, gdy klient oddał samochód w dniu wypożyczenia. W takim
przypadku różnica tych dat równa jest zero. W pozostałych przypadkach również dodawana musi być
liczba jeden, aby zawrzeć w wyniku pierwszy dzień wypożyczenia. Funkcja
DAYS
odczytuje z daty
ilość dni od daty l stycznia 0001 roku plus jeden.
Następny przykład użycia funkcji
DAYS
polega na odjęciu od istniejących dat dwóch dni. Możemy
również posłużyć się funkcją
YEARS
oraz
MONTHS
,
które odpowiednio oznaczają lata i miesiące.
SELECT K. NAZWISKO, W. NR_WYPOZYCZENIA,
W.DATA_WYP, W.DATA_ODD
W.DATA_WYP - 2 DAYS, W. DATA_ODD - 2 DAYS
FROM DB2ADMIN.KLIENCI K,
DB2ADMIN.WYPO
ŻYCZENIA W
WHERE K.NR_KLIENTA = W. NR_KLIENTA
AND W.DATA_ODD IS NOT NULL
AND K.MIASTO = 'WARSZAWA';
Rys.5.12.
Wybieranie pod
łańcucha
W razie potrzeby wybrania tyko pewnej części łańcucha musimy zastosować funkcję
SUBSTR
.
Na
poniższym rysunku funkcja
SUBSTR
wybiera ciąg o długości sześciu znaków począwszy od trzeciego
znaku.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
31
Rys.5.13.
SELECT SUBSTR(K.NAZWISKO, 3, 4), K.NAZWISKO
FROM DB2ADMIN.KLIENCI K;
Rys.5.14.
W InterBase funkcję
SUBSTR
należy „uaktywnić". Polega to na zadeklarowaniu funkcji, która zostanie
pobrana z zewnętrznej biblioteki dołączanej dynamicznie DLL. Aby funkcja ,.'."
SUBSTR
była aktywna
w InterBase, wykonaj poniższe polecenie w Interactive SQL.
DECLARE EXTERNAL FUNCTION SUBSTR
CSTRING(80), SMALLINT, SMALLINT
RETURNS CSTRING(SO) FREE_IT
ENTRY_POINT 'IB_UDF_Substr' MODULE_NAME 'ib_udf.dll';
Po wykonaniu powyższego polecenia, możemy przejść do opcji IBConsole, aby zobaczyć tę funkcję,
klikając w panelu po lewej stronie w ikonę External Function.
Inaczej niż w DB2, w InterBase funkcja
SUBSTR
wybiera ciąg począwszy od pozycji podanej w
drugim argumencie a skończywszy na trzecim argumencie. Zatem polecenie:
SELECT SUBSTR(K.NAZWISKO, 3, 4), K.NAZWISKO
FROM KLIENCI K;
zwróci następujące wyniki:
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
32
Rys.5.15.
Łączenie łańcuchów
Funkcja
CONCAT
pozwala łączyć ciągi znaków w jeden łańcuch wynikowy. Funkcja ta jest dostępna
tylko w DB2. Poniższy przykład zapytania wyświetli listę klientów wraz z adresem zamieszkania.
Taka lista może posłużyć jako źródło do korespondencji seryjnej.
SELECT K.IMIE CONCAT ' ' CONCAT K.NAZWISKO AS KLIENT,
'ul. ' CONCAT K.DLICA CONCAT ' ' CONCAT K. NUMER AS ULICA,
K. KOD CONCAT ' ' CONCAT K. MIASTO AS MIASTO
FROM DB2ADMIN.KLIENCI K
ORDER BY K.NAZWISKO;
Zamiast funkcji
CONCAT
można użyć znaków | |:
SELECT K.IMI
Ę | | ' ' | | K.NAZWISKO AS KLIENT, ...
Rys.5.16.
Wyra
żenie CASE
Wyrażenie
CASE
pozwala na wybranie pewnej wartości w zależności od wartości w innej
kolumnie.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
33
Wyrażenie
CASE
dostępne jest tylko w systemie DB2. W przykładzie poniżej sprawdzamy, czy klient
pochodzi z Warszawy; jeżeli tak, to w kolumnie 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 DB2ADMIN.KLIENCI K ORDER BY K.NAZWISKO;
Rys.5.17.
Podsumowanie
1. Funkcje arytmetyczne mogą być używane w klauzuli
SELECT
orazw HERE.
2. Kolumny wyliczone mogą być nazwane przez zastosowanie klauzuli
AS
.
3. Funkcje skalarne mogą być używane do zmiany reprezentacji danych - funkcje: DECIMAL,
SUBSTR, CONCAT.
4. Funkcje skalarne mogą być użyte do wydobycia lat, miesięcy oraz dni z różnych formatów daty.
5. Wyrażenie CASE pozwala na wybór wartości dla kolumny w zależności od zdefiniowanego
warunku.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
34
Rozdzia
ł 6.Funkcje kolumnowe i grupujące
W tym rozdziale poznamy funkcje operujące na kolumnach, które mogą być użyte w celu wydobycia
wyników z jednego lub większej ilości wierszy Poznamy również zasady grupowania wierszy
Funkcje kolumnowe
Do funkcji kolumnowych zalicza się ftmkcje
SUM
,
AVG
,
MIN
,
MAX
oraz
COUNT
Funkcje te są
używane w klauzulach
SELECT
lub
HAVING
SUM
- funkcja służąca do obliczenia sumy wartości w określonych kolumnach,
AVG
- oblicza średnią wartości \v kolumnie,
MIN
- znajduje minimalną wartość,
MAX
- znajduje maksymalną wartość,
COUNT
- śluzy do zliczania wystąpień pewnej wartości w wierszach
Poniższy przykład wyświetli całkowitą sumę wszystkich pensji pracowników, średnią pensję,
minimalną i maksymalna pensję oraz ilość pracowników
SELECT SUM(P.PENSJA) AS PENSJA,
AVG(P.PENSJA) AS SREDNIA,
MIN (P.PENSJA) AS PENSJA_MIN,
MAX(P.PENSJA) AS PENSJA_MAX,
COUNT(*) AS ILOSC FROM DB2ADMIN PRACOWNICY P,
Rys.6.1.
W poprzednim przykładzie funkcja
COUNT
została użyta do zliczenia wszystkich wierszy w tabeli
(
COUNT
(*)),
może być ona użyta również do zliczenia wierszy zawierających powtarzającą się wartość
w kolumnie. W tym przykładzie zliczamy liczbę działów i stanowisk w firmie.
SELECT COUNT(DISTINCT P.DZIAL) AS ILOSC_DZIALOW,
COUNT(DISTINCT P.STANOWISKO) AS ILOSC_STANOWISK
FROM DB2ADMIN.PRACOWNICY P;
Rys.6.2.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
35
Stosowanie funkcji kolumnowych można przeprowadzić również na pewnym podzbiorze wierszy,
SELECT SUM(P.PENSJA) AS PENSJA,
AVG(P.PENSJA) AS SREDNIA,
MIN(P.PENSJA) AS PENSJA_MIN,
MAX(P.PENSJA) AS PENSJA_MAX,
COUNT(*) AS ILOSC FROM DB2ADMIN.PRACOWNICY P
WHERE P.DZIAL = 'OBSLUGA KLIENTA
’
;
Rys.6.3.
Klauzula GROUP BY
Klauzula
GROUP BY
grupuje wiersze o tej samej wartości wyszczególnionych kolumn. Funkcje
agregujące SQL (
AYG
,
MAX
,
MIN
,
SUM
oraz
COUNT
)
w klauzuli
SELECT
operują na każdej grupie
osobno.
Rys.6.4.
Następujący przykład zapytania pogrupuje wiersze według stanowiska.
SELECT P.STANOWISKO, SUM (P.PENSJA) AS PENSJA,
AYG(P.PENSJA) AS SREDNIA,
MIN(P.PENSJA) AS PENSJA_MIN,
MAX(P.PENSJA) AS PENSJA_MAX,
COUNT(*) AS ILOSC
FROM DB2ADMIN. PRACOWNICY P
GROUP BY P.STANOWISKO
ORDER BY P.STANOWISKO;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
36
Rys.6.5.
Klauzula HAUING
Klauzula
HAYING
używana jest w połączeniu z klauzulą
GROUP BY
w celu ograniczenia wyświetlanych
grup. Warunek szukania musi zawierać funkcję agregującą. Po zgrupowaniu wierszy przez klauzulę
GROUP BY
,
klauzula
HAYING
wyświetla tylko te wiersze spośród zgrupowanych, które spełniają
warunki wyszczególnione w klauzuli
HAYING
.
Rys.6.6.
Klauzula
HAYING
może być użyta tylko wówczas, gdy w zapytaniu znajduje się klauzula
GROUP
BY.
Następny przykład zapytania wyświetla wszystkich pracowników, którzy wypożyczyli samochody na
łączną jednostkową wartość powyżej 400 zł.
SELECT P.NAZWISKO, SUM (W.CENA_JEDN)
FROM DB2ADMIN.PRACOWNICY P,
DB2ADMIN.WYPOZYCZENIA W
WHERE P.NR_PRACOWNIKA = W.NR_PRACOW_WYP
GROUP BY P.NAZWISKO;
HAVING SUM(W.CENA_JEDN) > 400
ORDER BY P.NAZWISKO;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
37
Rys.6.7.
Podsumowanie
1. Funkcje kolumnowe mogą być użyte tylko w klauzulach
SELECT
i
HAVING
.
2. Klauzula
SELECT
może zawierać tylko funkcje kolumnowe oraz kolumny wskazane w klauzuli
ORDER BY
.
3. Klauzula
HAVING
może zawierać dowolne funkcje kolumnowe operujące na dowolnych
kolumnach tabeli. Te kolumny nie musza być wyspecyfikowane w klauzuli SELECT.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
38
Rozdzia
ł 7. Klauzula UNION.
W tym rozdziale zapoznamy się z klauzulą
UNION
,
która pozwala na łączenie dwóch lub więcej
wyników wykonania zapytania
SELECT
.
Poznamy składnię wyrażenia
UNION
,
zasady dla listy w
klauzuli
SELECT
oraz różnice między klauzulą
UNION
i
UNION ALL
.
Łączenie wielu wyników zapytania
Klauzula
UNION
łączy dwa lub więcej polecenia
SELECT
w jedną tabelę wynikową. Klauzula
SELECT
musi zwracać tę samą liczbę kolumn. Kolumny pokrywające się mu-szą mieć tę samą szerokość i typ
danych. Nazwy tych kolumn mogą być różne.
Rys.7.1.
Klauzula UNION łączy dwa zestawy wyników w jeden i jednocześnie usuwa duplikaty. Poniższy
rysunek ilustruje zastosowanie klauzuli
UNION
.
Jak widać, powtarzające się wiersze na szarym tle
zostały umieszczone tylko raz w końcowym wyniku zapytania z klauzulą
UNION
.
W kolejnym przykładzie są zwracane dane o imieniu i nazwisku wszystkich klientów i pracowników,
których nazwiska kończą się na „ski". Tylko jedna osoba o imieniu i nazwisku Jan Kowalski
występuje jednocześnie w tabeli klientów i pracowników.
SELECT IMIE, NAZWISKO
FROM DB2ADMIN.KLIENCI
WHERE NAZWISKO LIKE '%SKI'
UNION
SELECT IMIE, NAZWISKO
FROM DB2ADMIN. PRACOWNICY
WHERE NAZWISKO LIKE '%SKI';
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
39
Rys.7.2.
Za każdym razem zapytania łączące wyniki z klauzulą
UNION
wyświetlają wyniki posortowane
rosnąco. Jeżeli chcemy zawrzeć klauzulę ORDER BY, która posortuje nam wynik malejąco, musi ona
być umieszczona na końcu zapytania.
SELECT IMIE, NAZWISKO
FROM DB2ADMIN.KLIENCI
WHERE NA2WISKO LIKE '%SKI'
UNION
SELECT IMIE, NAZWISKO
FROM DB2ADMIN.PRACOWNICY
WHERE NAZWISKO LIKE '%SKI'
ORDER BY NAZWISKO DESC;
Rys.7.3.
W systemie InterBase powyższe zapytanie należy zmodyfikować poprzez zastąpienie ostatniej
klauzuli
ORDER BY
następującą:
ORDER BY 2 DESC;
InterBase nie pozwala w zapytaniach łączących wyniki na specyfikowanie nazwy kolumny w klauzuli
ORDER BY
.
Klauzula
UNION ALL
Różnica pomiędzy klauzulą
UNION
a
UNION
ALL polega na tym, że wynik łączenia zapytań klauzulą
UNION ALL
zawiera powtarzające się wiersze.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
40
Rys.7.4.
Klauzula
UNION ALL
dziafa szybciej niż
UNION
.
Tak więc, gdy łączymy kilka wyników zapytania, i
gdy jesteśmy pewni, że łączone wyniki nie zawierają duplikatów, możemy używać klauzuli
UNION
ALL
.
Podsumowanie
1. Wyniki zapytania
SELECT
z tą samą liczbą kolumn będących tego samego typu danych mogą być
łączone poprzez użycie klauzuli
UNION
.
2. Klauzula
UNION
sortuje dane wynikowe i usuwa duplikaty.
3. Klauzula
UNION ALL
działa szybciej niż
UNION
.
4. Użyj klauzuli
UNION ALL
gdy jesteś pewien, że łączone wyniki nie zawierają duplikatów.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
41
Rozdzia
ł 8. Podzapytania.
Rozdział ten opisuje używanie podzapytań. Znajdują się tutaj informacje, jak konstruować
podzapytania, jak używać podzapytań w klauzuli
WHERE
oraz w klauzuli
HAYING
oraz jak budować
podzapytania ze słowami kluczowymi
IN
,
ALL
,
ANY
lub
SOME
.
U
żywanie podzapytań
Przypuśćmy, że musimy znaleźć pracowników, którzy otrzymują wynagrodzenie na kwotę większą
niż wynosi średnia. Musimy najpierw sprawdzić, jaka jest średnia dla każdego pracownika.
SELECT AVG(P.PENSJA)
FROM DB2ADMIN.PRACOWNICY P;
Wynik wynosi: 1530,00
Teraz szukamy pracowników, którzy zarabiają poniżej tej średniej:
SELECT P.IMIE, P.NAZWISKO, P.DZIAL, P.STANOWISKO
FROM DB2ADMIN.PRACOWNICY P WHERE P.PENSJA > 1530;
Rys.8.1.
Wykonaliśmy zadanie. Znaleźliśmy pracowników, którzy zarabiają powyżej średniej. Ale
dokonaliśmy tego w dwóch krokach za pomocą dwóch zapytań.
Teraz otrzymamy ten sam wynik, ale przy użyciu podzapytania.
SELECT P,IMIE, P.NAZWISKO, P.DZIAL, P.STANOWISKO
FROM DB2ADMIN.PRACOWNICY P
WHERE P.PENSJA > (SELECT AVG(P.PENSJA)
FROM DB2ADMIN.PRACOWNICY P);
Podzapytania z u
życiem słowa kluczowego IN
Słowo kluczowe
IN
pozwala na zidentyfikowanie wszystkich elementów w zbiorze A które nie
występują w zbiorze B.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
42
Rys.8.2.
Zapytanie wyświetla listę samochodów, których do tej pory nie wypożyczył żaden klient. Zapytanie
wybiera te samochody, które nie znajdują się w tabeli WYPOŻYCZENIA, czyli te, które nie były do
tej pory przedmiotem wypożyczenia.
SELECT S.NR_SAMOCHODO, S.MARKA, S,TYP
FROM D32ADMIN.SAMOCHODY S
WHERE S.NR_SAMOCHODU
NOT IN
(SELECT W.NR_SAMOCHODU
FROM DB2ADMIN.WYPOZYCZENIA W);
Rys.8.3.
Podzapytania z u
życiem słowa kluczowego ALL
Przykładowe podzapytanie ze słowem
ANY
będzie wykonane w dwóch krokach. Jako pierwsze jest
wykonywane podzapytanie, które znajduje średnią pensję w każdym dziale. W drugim kroku, każda
pensja pracownika porównywana jest z listą średnich pensji. Wyświetleni zostaną pracownicy, których
pensja jest wyższa od wszystkich średnich pensji obliczonych w podzapytaniu.
Rys.8.4.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
43
Podzapytania z u
życiem słowa kluczowego ANY lub SOHE
Zapytanie z rysunku 8.5 jest wykonywane w dwóch krokach. Jako pierwsze jest wykonywane
podzapytanie, które znajduje średnią pensję w każdym dziale. W drugim kroku, każda pensja
pracownika porównywana jest z listą średnich pensji. Ostatecznie wyświetleni zostaną wszyscy
pracownicy, których pensja jest wyższa od najmniejszej średniej pensji obliczonej w podzapytaniu.
Rys.8.5.
Podzapytania w klauzuli HAVING
Musimy znaleźć działy, w których średnia pensja pracowników jest wyższa od średniej pensji w
firmie. Do średnich pensji nie będą brani pod uwagę kierownicy działów.
Gdybyśmy musieli wykonać to zadanie „ręcznie", to musielibyśmy przejść przez trzy kroki. W
pierwszym kroku musielibyśmy znaleźć średnią pensję w firmie, nie biorąc pod uwagę kierowników.
SELECT AVG(P.PENSJA)
FROM DB2ADMIN.PRACOWNICY P
WHERE P.STANOWISKO <> 'KIEROWNIK
’
;
Rys.8.6.
W drugim kroku obliczylibyśmy średnie pensje pracowników w poszczególnych działach, nie biorąc
przy tym pod uwagę kierowników.
SELECT P.DZIAL, AVG(P.PENSJA) AS SREDNIA_PENSJA
FROM DB2ADMIN.PRACOWNICY P
WHERE P.STANOWISKO <> 'KIEROWNIK'
GROUP BY P.DZIAL
ORDER BY SREDNIA_PENSJA;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
44
Rys.8.7.
Jeżeli używasz InterBase, zamień ostatni wiersz powyższego polecenia na:
ORDER BY 2;
W trzecim kroku musielibyśmy porównać wartości średnich pensji poszczególnych działów ze średnią
pensją w firmie.
Ostatecznie wykonujemy to zadanie za pomocą pojedynczego zapytania z podzapytaniem w klauzuli
HAVING
.
SELECT P.DZIAL, AVG(P.PENSJA) AS SREDNIA_PENSJA
FROM DB2ADMIN.PRACOWNICY P
WHERE P.STANOWISKO <> 'KIEROWNIK
’
GROUP BY P.DZIAL
HAVING AVG(P.PENSJA) > (SELECT AVG(P.PENSJA)
FROM DB2ADMIN.PRACOWNICY P
WHERE P.STANOWISKO <> 'KIEROWNIK') ORDER BY SREDNIA_PENSJA;
Rys.8.8.
Podsumowanie
1. Podzapytania muszą być otoczone nawiasami.
2. Podzapytania nie mogą zawierać klauzuli
UNION
,
UNION
ALL lub
ORDER BY
.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
45
Rozdzia
ł 9. Utrzymywanie danych.
W tym rozdziale nauczymy się tworzyć tabele i widoki. Poznamy składnię języka SQL niezbędną do
ich tworzenia. Nauczymy się również wstawiać wiersze do tabeli, zmieniać dane w tabeli, usuwać
wiersze oraz usuwać tabele.
Tworzenie tabel
Na rysunku 9.1 znajdziesz wypełnioną danymi tabelę KLIENCIJTEST, na której będziemy ćwiczyć
zagadnienia poruszane w tym rozdziale.
Rys.9.1.
Następujące wyrażenie
CREATE TABLE
tworzy tabelę KLIENCI_TEST.
CREATE TABLE DB2ADMIN.KLIENCI_TEST (
NR_KLIENTA
CHAR(8) NOT NULL,
IMIE
VARCHAR(20) NOT NULL,
NAZWISKO
VARCHAR(20) NOT NULL,
NR_KARTY_KREDYT
CHAR(20) ,
ULICA
VARCHAR(24) NOT NULL,
NUMER
CHAR(8) NOT NULL,
MIASTO
VARCHAR(24) NOT NUIi,
KOD
CHAR(6) NOT NULL,
NRJTELEFONU
CHAR (16),
PRIMARY KEY (NR_KLIENTA) ) ;
Definiując tabelę musimy określić jej nazwę np. KLIENCI_TEST. Następnie określić kolumny dla tej
tabeli. Każda kolumna musi posiadać: unikatową nazwę w obrębie tabeli oraz typ danych, jakie będą
przechowywane w kolumnie. Dodatkowo przy definiowaniu kolumn określić można, czy dozwolone
jest pozostawienie jej pustej; jeżeli nie, dodajemy klauzulę
NOT NULL
do definicji kolumny. Np.
kolumna NR_KARTY_ KREDYT nie jest wymagana - podczas wstawiania nowego wiersza - pole w
tej kolumnie możemy pozostawić puste. Może dziś (prawie) każdy posiada kartę płatniczą, ale nie
każdy posiada kartę kredytową. Dodatkowo nie każdy klient ma życzenie płacić kartą kredytową.
Słowo kluczowe
PRIMARY KEY
określa klucz główny dla tabeli. Klucz główny oraz klucz obcy
zostanie opisany w następnym rozdziale.
Tabelę możemy przebudować, dodając nową kolumnę lub ją usuwając, możemy zmienić typ danych
kolumny, jak również zmienić inne cechy tabeli oraz kolumn w niej zawartych. Do zmiany struktury
tabeli służy wyrażenie SQL
ALTER TABLE
.
Kolejne polecenie
ALTER TABLE
doda dwie kolumny: FIRMA oraz NIP do tabeli KLIENCI_TEST.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
46
ALTER TABLE DB2ADMIN.KLIENCI_TEST
ADD FIRMA VARCHAR(40)
ADD NIP CHAR(12) ;
W InterBase kolejne wiersze ze słowem
ADD
w powyższym poleceniu należy oddzielić
przecinkiem. Aby zapobiec błędom, musimy wykonać polecenie
ALTER TABLE
.
Następne przykłady
będą operować również na tych kolumnach.
Tworzenie widoków
Dane zawarte w widoku nie sąjej fizycznymi danymi a danymi należącymi do tabeli lub kilku tabel z
których widok czerpie dane. Widoki przede wszystkim są tworzone w celu ograniczenia dostępu do
danych w tabelach bazy danych. Do tworzenia widoków służy polecenie
CREATE VIEW
.
Poniższy przykład tworzy widok zawierający dane klientów, którzy posiadają firmę.
CREATE VIEW DB2ADMIN.KLIENCI_FIRMY AS
SELECT K.IMIE, K.NAZWISKO, K.FIRMA, K.NIP, K.MIASTO
FROM DB2ADMIN.KLIENCI K
WHERE K.FIRMA IS NOT NULL;
Teraz możemy wybierać dane z widoku tak, jak do tej pory wybieraliśmy dane z tabeli.
SELECT *
FROM DB2ADMIN.KLIENCI_FIRMY;
Rys.9.2.
Następny przykład tworzy widok, który ogranicza dane pracowników do wszystkich danych oprócz
informacji na temat dodatku i pensji.
CREATE VIEW DB2ADMIN.V__PRACOWNICY AS
SELECT P.NR_PRACOWNIKA, P.IMIE, P.NAZWISKO,
P.DATA_ZATR, P.DZIAL, P.STANOWISKO,
P.NR_MIEJSCA, P.NRJTELEFONU
FROM DB2ADMIN.PRACOWNICY P;
Dodawanie i usuwanie rekordów
Aby dodać jeden lub więcej rekordów do istniejącej tabeli, należy posłużyć się wyrażeniem SQL
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
47
INSERT
.
Aby dodać rekord do tabeli KLIENCI_TEST zdefiniowanej w sekcji „Tworzenie tabel",
napisz i wykonaj poniższe wyrażenie SQL. Upewnij się, że tabela KLIENCIJTEST posiada kolumny
FIRMA oraz NIP, które dodaliśmy do struktury tabeli poleceniem
ALTER TABLE
.
INSERT INTO DB2ADMIN.KLIENCI_TEST
VALUES ( '00000031', 'MARIUSZ', 'DOLATA', NULL, 'KOCHANOWSKIEGO
’
, '3',
'WROC
ŁAW', '37-300', '167-763-234', 'KWIATY', '2224-444-224');
Dodaj jeszcze kilka rekordów:
INSERT INTO DB2ADMIN.KLIENCI_TEST
YALUES ('00000032', 'TOMASZ', 'DOMAGA
ŁA
’
, 'HX 145345678', 'RÓ
ŻANA', '4/9',
'WARSZAWA
’
, '01-900', '46-744-431', NULL, NULL);
INSERT INTO DB2ADMIN.KLIENCI_TEST
VALUES ('00000033', 'PAWE
Ł', 'MALCZYKOWSKI' , 'HF 14565661', 'SŁONECZNA',
'9', 'WARSZAWA
1
, '01-900', '16-742-114', NULL, NULL);
INSERT INTO DB2ADMIN.KLIENCI_TEST
VALUES ('00000034', 'PIOTR', 'MUSZY
ŃSKI' , 'DD 72325221',
'SZYBOWCOWA', '22A
’
, 'WARSZAWA', '01-200', '44-342-116',
'WULKANIZACJA', '4356-098-876');
INSERT INTO DB2ADMIN.KLIENCI_TEST
VALUES ('00000035', 'ANNA', 'MIKOLAJCZYK
’
,NULL , 'JA
ŁOWCOWA', '24',
'WROC
ŁAW', '37-200', '144-188-415', 'FRYZJERSTWO', '2343-112-345');
Powyższe wyrażenia dodały nowe wiersze do tabeli KLIENCI_TEST. Każde z tych wyrażeń wypełnia
wartościami wszystkie kolumny tabeli. Aby wstawić dane tylko do wybranych kolumn, należy je
określić, a następnie podać wartości:
INSEKT INTO DB2ADMIN. KLIENCI_TEST {NR_KLIENTA, IMIE, NAZWISKO, ULICA, NUMER,
MIASTO, KOD)
VALUES
('00000036
’
,
'MAGDALENA',
'BRZOZA'
,
'ALEJE
LIPOWE',
'4/3',
_
'
ŚWIDNICA, '58-100');
Powyższe polecenie
INSEKT
dodało nowy wiersz do tabeli KLIENCI_TEST. Wypełnione zostały
wszystkie kolumny oprócz kolumny NR_KARTY_KREDYT i kolumny TELEFON. Wartości dla
tych kolumn nie są wymagane więc wstawienie nowego wiersza przebiegło bez błędu.
Istnieje możliwość dodania wielu wierszy za jednym razem. Wstawienie kilku rekordów w jednym
poleceniu polega na użyciu klauzuli
SELECT
.
Oto przykład:
INSERT INTO DB2ADMIN.KLIENCI_TEST (NR_KLIENTA, IMIE, NAZWISKO, ULICA, NUMER,
MIASTO, KOD)
SELECT NR_KLIENTA, IMIE, NAZWISKO, ULICA, NUMER, MIASTO, KOD
FROM DB2ADMIN.KLIENCI
WHERE FIRMA IS NULL;
Aby usunąć rekordy z tabeli, użyj polecenia
DELETE
FROM np.
DELETE FROM DB2ADMIN.KLIENCI_TEST WHERE FIRMA IS NOT NULL;
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
48
Polecenie
DELETE FROM
bez klauzuli
WHERE
usuwa wszystkie rekordy z tabeli, np.
DELETE FROM DB2ADMIN.KLIENCI_TEST;
Zmienianie danych w tabeli
Polecenie
UPDATE
zmienia wartości we wskazanych kolumnach tabeli dla jednego lub większej ilości
wierszy. Poniższe polecenie
UPDATE
zwiększa kwotę dodatku pracownika zatrudnionego na
stanowisku sprzedawcy o 50 zł.
UPDATE DB2ADMIN.PRACOWNICY
SET DODATEK = DODATEK + 50
WHERE STANOWISKO = 'SPRZEDAWCA';
Teraz możemy sprawdzić, czy wartości dodatku dla sprzedawców zostały zmienione:
SELECT *
FROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO = 'SPRZEDAWCA
’
;
Jeżeli zmieniamy wartości więcej niż jednej kolumny, muszą one być oddzielone przecinkiem.
Poniższe polecenie zwiększa dodatek dla kierowników o 30 zł oraz zwiększa pensje o 10%.
UPDATE DB2ADMIN.PRACOWNICY
SET DODATEK = DODATEK + 30,
PENSJA = PENSJA + (PENSJA *10) /100
WHERE STANOWISKO = 'KIEROWNIK';
Usuwanie tabel
Tabela KLIENCI_TEST nie będzie nam już więcej potrzebna. Aby usunąć tabelę, musimy użyć
polecenia
DROP TABLE
:
DROP TABLE KLIENCI TEST;
Polecenie usuwające tabelę usuwa jednocześnie wszystkie dane zawarte w tabeli oraz usuwa
wszystkie widoki które czerpią dane z usuwanej tabeli.
Podsumowanie
1. Usunięcie tabeli powoduje usunięcie danych i widoków związanych z usuwaną tabelą.
2. Możemy określić wiersze, które mają zostać usunięte lub zmienione poprzez zamieszczenie
odpowiedniego warunku w klauzuli
WHERE
.
3. Opuszczenie klauzuli
WHERE
w pleceniach
UPDATE
lub
DELETE
powoduje, że wszystkie wiersze
zostaną zmienione lub usunięte.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
49
Rozdzia
ł 10. Ograniczenia i integralność referencyjna
W tym rozdziale dowiemy się istotnych informacji o ograniczeniach, integralności danych tabeli oraz
o integralności referencyjnej. Wszystkie te zagadnienia składają się na bezpieczeństwo i jakość
danych gromadzonych w bazie danych.
Ograniczenia
Możesz zdefiniować ograniczenie sprawdzające poprawność wpisywanych danych do tabeli poprzez
określenie warunku sprawdzającego
CHECK
.
Poniższy przykład ilustruje wyrażenie zmieniające strukturę tabeli PRACOWNICY poprzez dodanie
ograniczenia zapobiegającego wpisaniu kwoty dodatku większej od kwoty pensji.
ALTER TABLE DB2ADMIN.PRACOWNICY ADD CHECK (PENSJA > DODATEK);
Jeżeli wpiszesz teraz wyrażenie dodające wiersz do tabeli pracownicy, który będzie zawierał
w kolumnie DODATEK wartość większą niż w kolumnie PENSJA np.
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES ('0011', 'JOLANTA', 'NOWAKOWSKA
1
, '1999-05-01', 'OBSLUGA
KLIENTA
’
, 'SPRZEDAWCA', 1100, 1200, '000001' , '433-451-154' ) ;
Baza DB2 wygeneruje komunikat o błędzie, który mówi o naruszeniu ograniczenia sprawdzającego
CHECK
:
DB21034E The command was processed as an SQL statement
because it was not a valid Comnand Lin
ę Processor command.
During SQL processing it returned:
SQL0545N The re
ąuested operation is not allowed because a rów
does not satisfy the check constraint
"DB2ADMIN.PRACOWNICY.SQLQ10121215529810". SQLSTATE=23513
Integralno
ść danych - klucz główny
Każda tabela bazy danych powinna zawierać klucz główny. Klucz główny tabeli to kolumna lub grupa
kolumn, która w sposób jednoznaczny identyfikuje wiersz w tabeli. Na przykład, dla tabeli
zawierającej dane o pracownikach kluczem głównym może być, kolumna o nazwie
NR_PRACOWNIKA, która jednoznacznie określa danego pracownika. Kluczem głównym może być
numer telefonu w tabeli przechowującej dane abonentów operatora telefonicznego. Jak już
wspomniałem, klucz główny może składać się z wielu kolumn. Przykładem takiego klucza głównego
może być kolumna NUMER oraz ROK w tabeli przechowującej dane o wystawionych fakturach,
gdzie kolumna NUMER określa numer faktury a kolumna ROK określa rok wystawienia. Wartości z
tych kolumn wzięte razem są różne w każdym wierszu.
Jak już wspomniałem, dla tabeli PRACOWNICY kluczem głównym może być kolumna
NR_PRACOWN1KA. Ustalenie klucza głównego (
PRIMARY KEY
)
podczas tworzenia tabeli:
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
50
CREATE TABLE DB2ADMIN. PRACOWNICY (
NR_PRACOWNIKA
CHAR(4)NOT NULL,
IMIE
VARCHAR(20) NOT NULL,
NAZWISKO
VARCHAR(20) NOT NULL,
DATA_ZATR
DATE NOT NULL,
DZIAL
VARCHAR(20) NOT NULL,
STANOWISKO
VARCHAR(20) NOT NULL,
PENSJA
DECIMAL(8,2),
DODATEK
DECIMAL(8,2)
NR_MIEJSCA
CHAR(6) NOT NULL,
NRJTELEFONU
CHAR(16)
PRIMARY KEY (NR_PRACOWNIKA));
zapobiegnie wstawieniu dwóch identycznych wierszy. W przypadku gdy dodamy drugi wiersz z
danymi pracownika o numerze już istniejącym w tabeli, DB2 wyświetli błąd z informacją o
naruszeniu integralności danych.
DB21034E The command was processed as an SQL statement because it was not a
valid
Command
Lin
ę Processor command. During SQL processing it
returned:SQL0803N One or mor
ę values in the INSERT statement, UPDATE
statement, or foreign key update caused by a DELETE statement ar
ę not valici
because they would produce duplicate rows for a table with a primary key,
uni
ąue constraint,or uniąue index. SQLSTATE=23505
Integralno
ść refereicyjna - klucz obcy
Klucz obcy to jedna lub więcej kolumn tabeli odwołujących się do kolumny lub kolumn klucza
głównego w innej tabeli. Klucze obce są wykorzystywane do utrzymywania integralności
referencyjnej w bazie danych. Tworząc klucz obcy, definiujemy związek między tabelą klucza
głównego i tabelą klucza obcego. Związek taki powstaje podczas złączania kolumn takich samych
typów danych z każdej tabeli. Złączanie tabel przez odpowiednie kolumny chroni dane z tabeli klucza
obcego przed „osieroceniem", jakie mogłoby nastąpić w wyniku usunięcia odpowiadających im
danych z tabeli klucza głównego. Definiowanie kluczy obcych jest po prostu sposobem łączenia
danych przechowywanych w różnych tabelach bazy danych.
Na przykład, w tabeli PRACOWNICY widocznej na poniższym zdjęciu kluczem obcym jest kolumna
NR_MIEJSCA. Ta kolumna czerpie wartości z tabeli MIEJSCA z kolumny NR_MIEJSCA (klucz
główny w tabeli MIEJSCA). Gdy odczytamy numer miejsca z tabeli PRACOWNICY, możemy się
odwołać do tabeli MIEJSCA i odczytać z niej pełny adres miejsca pracy pracownika. Rysunek 10.1
ilustruje związek tabeli klucza obcego z tabelą klucza głównego.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
51
Rys.10.1.
Rysunek 10.1 można odczytać następująco: tabela klucza obcego PRACOWNICY jest złączona z
tabelą klucza głównego MIEJSCA poprzez kolumny NR_M1EJSCA. Związek klucza obcego chroni
wiersze z tabeli PRACOWNICY przed osieroceniem na wypadek usunięcia jakiegokolwiek wiersza z
tabeli MIEJSCA.
Aby zapewnić taką ochronę, musimy zdefiniować klucze obce we wszystkich tabelach, które
odwołują się do innych tabel. Taki związek występuje m.in. w naszych przykładowych tabelach
PRACOWNICY oraz MIEJSCA.
ALTER TABLE DB2ADMIN.PRACOWNICY
ADD POREIGN KEY (NR_MIEJSCA)
REFERENCES MIEJSCA (NR MIEJSCA) ON DELETE RESTRICT;
Polecenie to ustanawia klucz obcy w tabeli PRACOWNICY w kolumnie NR_ MIEJSCA. Czytając
dalej to polecenie dowiadujemy się że kolumna ta odwołuje się do kolumny NR_MIEJSCA w tabeli
MIEJSCA. Słowa kluczowe
ON DELETE RESTRICT
mówią, że niemożliwe jest usunięcie wiersza z
tabeli MIEJSCA, gdy istnieje wiersz do niego się odwołujący w tabeli PRACOWNICY. Dla systemu
InterBase zamiast słowa
RESTRICT
jest honorowane słowo
NO ACTION
.
W tabeli 10.1 znajdują się opisy wszystkich możliwych akcji, jakie zostaną zainicjowane w chwili
usuwania wiersza w tabeli zależnej:
Tabela 10.1.
Akcja
Opis
RESTRIC
Ograniczone usuwanie, które mówi, że dopóki istnieją w tabeli PRACOWNICY wiersze
odwołujące do usuwanego adresu
lub dla InterBase
NO ACTION
nie można go usunąć. Aby usunąć dane o adresie z tabeli MIEJSCA, najpierw należy
usunąć wszystkich pracowników pracujących w miejscu o którym informacje chcemy
usunąć
CASCADE
kaskadowe usuwanie, mówi, że gdy usuwamy wiersze z tabeli MIEJSCA, to są
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
52
jednocześnie usuwane wszystkie wiersze z danymi o pracownikach, którzy pracują w
usuwanym miejscu
SET NULL
Wstaw wartość NULL, mówi, że jeśli usuwamy dane o miejscach, to w tabeli
PRACOWNICY w kolumnie NR_MIEJSCA zostanie wstawiona wartość NULL
Podsumowanie
1. Możesz zdefiniować ograniczenie sprawdzające poprawność wpisywanych danych do tabeli
poprzez określenie warunku sprawdzającego
CHECK
.
2. Integralność danych w tabeli zachowuje się dzięki kluczom głównym.
3. Klucze obce służą do utrzymywania integralności referencyjnej.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
53
Rozdzia
ł 11.Instalacja DB2.
Rozdział ten opisuje instalację DB2 w systemie Windows 98 oraz Windows NT. Zawiera również
istotne informacje o źródle wersji instalacyjnej DB2.
Jeżeli nie posiadamy wersji instalacyjnej DB2, możemy ją skopiować ze strony inter-netowej IBM
http://www-4.ibm.com/software/data/db2/udb/downloads.html. Informacje na tej stronie poprowadzą
przez proces kopiowania DB2. Aby skopiować DB2 Personal Edition wersję 7.1, musimy się najpierw
zarejestrować. Po załogowaniu się jako zarejestrowany użytkownik, będziemy musieli jeszcze
wypełnić ankietę. Wersja DB2 Personal Edition jest darmowa (z licencją na jednego użytkownika).
Możemy jej używać do celów edukacyjnych. Nie możemy czerpać korzyści majątkowych z pracy z
systemem DB2 Personal Edition.
Musimy skopiować następujące pliki:
-
winpecmn.zip o wielkości 142 572 kB
-
winpeen.zip o wielkości 48 467 kB
Niestety do instalacji są potrzebne oba.
:
Do rozpakowania powyższych zbiorów musimy się zaopatrzyć w program WinZIP, który można
znaleźć w Internecie na stronie http://www.winzip.com.
Następnym krokiem będzie założenie katalogu np. na dysku D:\DB2INST, do którego rozpakujemy
zbiory instalacyjne skopiowane z Internetu. Do tego samego katalogu należy rozpakować pliki
winpecmn.zip oraz winpeen.zip.
Instalacja dla systemu Windows i Windows NT
Po pomyślnym rozpakowaniu zbiorów musimy uruchomić plik setup.exe znajdujący się w katalogu
D:\DB2INST (lub w innym, do którego rozpakowane zostały skomprymowane pliki). Pojawi się ekran
powitalny z pewnymi opcjami. Wybieramy opcję Install.
Rys.11.1.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
54
Jeżeli w systemie zainstalowana jest wcześniejsza wersja DB2. program instalacyjny nas o tym
powiadomi i zaproponuje usunięcie jej z systemu przed kontynuacją instalacji.
Rys.11.2.
Na rysunku 11.2 pokazany jest wybór składników instalacji. Pełna instalacja polega na wybraniu
wszystkich trzech składników. Dla potrzeb ćwiczeń wystarczy pierwsza pozycja DB2 Personal
Edition.
Po naciśnięciu klawisza Next pojawi się okno Selecl Installation Type. Na tym etapie można wybrać
instalację Typical. Jeżeli wybierzesz typ instalacji Gustom, to pojawi się okno Select Components, w
którym możesz wybrać poszczególne składniki oprogramowania. Jeżeli wybierzesz typ instalacji
Typical, to w następnym oknie Choose Destination Location możemy zmienić docelowy katalog, w
którym zainstalowany zostanie system DB2.
Naciskamy przycisk Next. Jeżeli DB2 jest instalowane dla Windows NT, zobaczymy okno dialogowe,
w którym musimy wprowadzić nazwę użytkownika i hasło dla narzędzia Control Center Server.
Proponuję użyć domyślnej nazwy użytkownika db2admin. Hasło proszę ustawić również na
db2admin. Wszystko w celu sprawnego wykonywania skryptów przedstawionych w niniejszych
ćwiczeniach. Inna nazwa użytkownika i hasła wymagałaby naniesienia zmian w skryptach.
Zaznaczenie opcji Use the same values for the remaining DB2 Username and Password settings u dołu
okna spowoduje nadanie tej samej nazwy użytkownika i hasła dla pozostałych elementów DB2
Administration Server, oraz dla domyślnej instancji DB2.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
55
Rys.11.3.
Ostatnie okno Start Copying Files informuje o wybranych parametrach instalacji. Naciskamy przycisk
Next. Na tym etapie instalator DB2 kopiuje potrzebne zbiory na dysk twardy.
W trakcie instalacji może się okazać, że posiadamy starą wersję sterowników ODBC. Zostanie to
zasygnalizowane odpowiednim komunikatem.
Po zakończeniu pracy instalatora musimy ponownie uruchomić komputer. Pomyślne zakończenie
instalacji zostanie zasygnalizowane oknem First Steps, które się pojawi po ponownym uruchomieniu
komputera.
Na koniec możemy sprawdzić w panelu sterowania w usługach, czy system DB2 został poprawnie
uruchomiony.
Rys.11.4.
W Windows NT nie uruchomienie się usług DB2 może być spowodowane starą wersją Service Packa.
W takim przypadku należy ponownie zainstalować możliwie najnowszego Service Packa w wersji
językowej odpowiadającej naszemu systemowi.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
56
Podsumowanie
1. Instalacja DB2 w systemie Windows 95 i 98 nie wymaga żadnych dodatkowych operacji.
2. Można użyć narzędzia Usługi z Panelu sterowania w Windows NT w celu podglądnięcia czy
usługa DB2 została poprawnie uruchomiona.
3. W Windows NT problem z uruchomieniem usługi DB2 może być spowodowany starą wersją
Service Pack.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
57
Rozdzia
ł 12. Narzędzia DB2.
W tym rozdziale poznamy takie narz
ędzia jak: Control Center, Command Center, Command
Lin
ę Procesor oraz Information Center.
Control Center jest aplikacją pozwalającą na zarządzanie obiektami bazy danych. Narzędzie
Command Center będziemy wykorzystywać do przygotowywania zapytań SQL i ich wykonywania.
To narzędzie pozwala również na wykonywanie skryptów SQL jak również na przeglądanie wyników
wykonania zapytania. Narzędzie Command Linę Procesor (CLP) służy do wykonywania poleceń
systemowych DB2. Information Center jest systemem pomocy z bardzo wygodnym interfejsem.
Control Center
Control Center jest aplikacją, która pozwala na przeglądanie, dodawanie, usuwanie i zmienianie
obiektów baz danych zdefiniowanych w DB2. Tymi obiektami sąm.in. tabele i widoki. Dzięki temu
narzędziu możemy zbudować całą bazę danych, nie używając języka SQL. W liście tabel, oprócz tabel
bazy danych WYPAUT, znajdują się również tabele systemowe, z których można wydobyć
informacje na temat struktury bazy danych. Tabela SY-SIBM.SYSTABLES zawiera wszystkie tabele
zdefiniowane w bazie danych. Tabela SY-SIBM.SYSCOLUMNS zawiera wszystkie informacje o
kolumnach zdefiniowanych we wszystkich tabelach bazy danych. Tabela SYSIBM.SYSYIEWS
zawiera informacje o widokach zdefiniowanych w bazie danych. Panel po lewej stronie okna zawiera
informacje o systemie. Ikona Systems wskazuje na nazwę komputera (WROR-JAKUBOAR).
Ikona Instances zawiera instancje DB2 zainstalowane w systemie. Można definiować wiele instancji
np. w celu oddzielenia bazy testowej od produkcyjnej. Następna ikona -Databses zawiera bazy danych
(w tym przypadku jedna - WYPAUT). Baza WYPAUT z kolei skupia wszystkie obiekty typu tabele,
widoki, indeksy, itd.
Rys.12.1.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
58
Command Center
Aplikacja Command Center będzie najczęściej wykorzystywanym narzędziem przy studiowaniu
niniejszych ćwiczeń. Na poniższym zdjęciu aplikacji Command Center widzimy zakładki lnteractive,
Script, Ouery Results oraz Access Plan, których przeznaczenie zostanie opisane w następnych
sekcjach.
Rys.12.2.
Przygotowywanie zapyta
ń SQL i ich wykonywanie
Podczas budowy zapytań przy bieżącej pracy z bazą danych będziemy korzystać z zakładki
lnteractive. Aby wykonać zapytanie, musimy się upewnić, że jesteśmy podłączeni do bazy danych, na
której chcemy pracować. Są dwa sposoby na podłączenie się do bazy danych. Jeden z nich to wpisanie
polecenia SQL:
CONNECT TO WYPAUT USER db2admin USING db2admin;
w oknie Command na zakładce lnteractive. Po naciśnięciu kombinacji klawiszy Ctrl+Enter powyższe
polecenie zostanie wykonane i zostaniemy podłączeni do bazy danych. Zostanie to zakomunikowane
w oknie poniżej okna Command następującym komunikatem:
-------------------- Command Entered --------------------
CONNECT TO WYPAUT USER db2admin USING ******** •
---------------------------------------------
Database Connection Information
Database server = DB2/NT 7.1.0
SQL authorization ID = DB2ADMIN
Local database alias = WYPAUT
Oprócz tego komunikatu, w polu Database connection zobaczymy wpis informujący o aktualnym
połączeniu. Można to zobaczyć na zdjęciu (JAKUB - DB2 - WYPAUT).
Drugi sposób podłączenia się do bazy danych polega na wybraniu z okna Select Database (rysunek
12.3) konkretnej bazy danych. Okno Select Database wywołuje się przez naciśnięcie myszką klawisza
z trzema kropkami znajdującego się po prawej stronie pola Database connection.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
59
Rys.12.3.
Teraz gdy jesteśmy podłączeni do bazy WYPAUT, możemy wydawać inne polecenia lub zapytania
SQL w oknie Command.
Wykonywanie skryptów SQL
Przed tym, jak zaczniemy pracować z ćwiczeniami musimy, po stworzeniu bazy danych, utworzyć
tabele i wypełnić je danymi. Możemy to wykonać poprzez okno Com-mand na zakładce lnteractive
lub poprzez wykonanie skryptów uprzednio stworzonych. Wpisywanie wszystkich poleceń
tworzących tabele oraz poleceń wstawiających dane jest zbyt czasochłonne. Polecam wykonanie
skryptów, które zostały zamieszczone na serwerze ftp wydawnictwa. Aby wykonać skrypt, musimy go
otworzyć i uruchomić. Wybieramy w tym celu menu Script j Import... Pojawi się okno widoczne na
rysunku 12.4.
W tym oknie musimy najpierw wybrać komputer, na którym znajdują się skrypty poprzez rozwinięcie
listy System name i wybranie konkretnego systemu. Następnie przechodzimy do katalogu ze
skryptami i pojedynczo je otwieramy.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
60
Rys.12.4.
Zawartość skryptu zostanie wyświetlona w oknie Script na zakładce Script. Zobacz poniższe zdjęcie
okna Command Center.
Rys.12.5.
Aby wykonać skrypt naciskamy kombinację klawiszy Ctrl+Enter lub wybieramy myszką przycisk
Execute znajdujący się pod menu głównym z lewej strony okna. Zobaczymy serię komunikatów u
dołu okna mówiącym o tym, że wykonanie poszczególnych poleceń SQL w skrypcie zostało
zakończone pomyślnie, np.
DB20000I The SQL coinmand completed successfully-
Wy
świetlanie wyników wykonania zapytania
Wyniki wykonania zapytań SQL, które zostały wprowadzone na zakładce lnteractive są wyświetlane
na zakładce Ouery Results aplikacji Command Center. Wyniki zapytań uruchomionych z poziomu
zakładki Script są z kolei wyświetlane u dołu w tym samym oknie.
Command lin
ę Processor
Command Linę Processor pozwala na wykonywanie poleceń systemowych DB2. Do poleceń
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
61
systemowych zalicza się również polecenie
CREATE DATABASE
tworzące bazę danych. Inne polecenia
służą do wyświetlania parametrów systemu DB2 i ustawianiu tychże parametrów. Na rysunku 12.6
znajduje się zdjęcie okna Command Linę Processor z wydanym poleceniem
LI ST ACTIVE DATABASES
.
Rys.12.6.
Poniżej znajdują się opisy niektórych poleceń systemowych DB2. Pełną ich listę wraz z opisem
możesz znaleźć w dołączonym systemie pomocy. Wystarczy wpisać znak zapytania i potwierdzić
klawiszem Enter. Możesz również uzyskać krótką podpowiedz na temat konkretnego polecenia,
poprzedzając ją znakiem zapytania w Command Linę Processor, np.
db2 ==> ? CONNECT TO
CONNECT TO database-alias - .- ..
[IN {SHARE MOD
Ę l EXCLUSIVE MODĘ [ON SINGLE NODE]}]
[USER username [{USING password
[NEW new-password CONFIRM confirm-password] |
CHANGE PASSWORD}]]
CONNECT TO
<nazwa bazy danych> -łączy aplikację do bazy danych
Przykład:
CONNECT TO WYPAUT USER db2admin USING db2admin
przyłącza aplikację do bazy danych WYPAUT. Parametry
USER
oraz USING pozwalają określić
użytkownika bazy danych oraz hasło.
CREATE DATABASE
<nazwa bazy danych> - tworzy bazę danych.
Przykład:
CREATE DATABASE WYPAUT
stworzy bazę danych z domyślnymi wartościami parametrów bazy.
DB2START/DB2STOP
- startuje (lub zatrzymuje) menedżera bazy danych.
DROP DATABASE
<nazwa bazy danych> - usuwa bazę danych z systemu.
Przykład:
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
62
DROP DATABASE WYPAUT
GET CONNECTION STATE
- wyświetla informacje o stanie połączenia z bazą danych.
Jeżeli jesteśmy podłączeni do bazy WYPAUT poleceniem CONNECT
TO WYPAUT, to wydanie polecenia GET
CONNECTION STATE
spowoduje wyświetlenie podobnego
komunikatu:
Database Connection State
Connection state = Connectable and Connected
Connection mod
ę = SHARE
Local database = WYPAUT
alias
Database name = WYPAUT
GET
INSTANCE - wyświetla informacje o instancji bazy danych zainstalowanej w systemie.
LIST ACTIYE
DATABASES - wyświetla informacje o aktywnych bazach danych,
przyłączonych do nich aplikacjach i o ścieżce dostępu do zbiorów w których przechowywane
są dane z bazy danych.
LIST APPLICATIONS
- wyświetla informacje o aktywnych aplikacjach podłączonych do bazy
danych. Wykonanie tej komendy spowoduje wyświetlenie podobnego komunikatu:
LIST DATABASE
DIRECTORY - wyświetla informacje o systemowym katalogu DB2, w
którym przechowywane są wszystkie informacje o bazach danych. Na wydruku widać, że
jedyną bazą, jaka została utworzona do tej pory jest baza WYPAUT. Katalog D:\DB2 to
miejsce, gdzie przechowywany jest systemowy katalog.
System Database Directory
Number of entries in the directory = l
Database l entry:
Database alias = WYPAUT
Database name = WYPAUT
Database drive = D:\DB2
Database release level =9.00
Comment =
Directory entry type = indirect
Catalog node number = O
QUIT
- powoduje zamknięcie sesji z Command Line Processor.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
63
Tworzenie bazy
Polecenie systemowe
CREATE DATABASE
tworzy nową bazę danych. Dla potrzeb naszych ćwiczeń
musimy taką bazę stworzyć. Ponieważ polecenie
CREATE DATABASE
należy do poleceń systemowych
DB2, musimy je wprowadzić i wykonać w aplikacji Command Linę Processor.
Na rysunku 12.7 znajduje się okno aplikacji CLP z wykonanym poleceniem
CREATE DATABASE
WYPAUT. Po pomyślnym wykonaniu tego polecenia ujrzymy komunikat: The CREATE
DATABASE command completed successfulty. Gdy już stworzymy bazę WYPAUT, możemy
wykonywać dalsze czynności, np. wykonać skrypty SQL tworzące tabele i wypełniające je danymi.
Rys.12.7.
Ustawienia narz
ędzi DB2
Do poprawnej pracy w aplikacji Command Center, a w szczególności do wykonywania skryptów
musimy zmienić pewne domyślne parametry.
Z poziomu aplikacji Control Center lub z np. aplikacji Command Center wybieramy menu Tools |
Tools Settings. Pojawi się okno widoczne na rysunku 12.8.
Rys.12.8.
Na zakładce General musimy zaznaczyć opcję Use statement termination character, która stanowi o
tym, że znak średnika będzie znakiem oddzielającym poszczególne wyrażenia SQL wprowadzane
m.in. w aplikacji Command Center.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
64
Information Center
Information Center jest aplikacją wspomagającą użytkownika w wyszukiwaniu pomocnych
informacji. Jest to pewnego rodzaju system pomocy. Jego budowa i organizacja pozwala na szybkie
wyszukanie potrzebnych informacji.
Rys.12.9.
Znajdziemy tutaj pełny opis składni języka SQL, jak również opis poleceń systemowych DB2.
Znajdują się tam również odpowiednie łącza do stron WWW firmy IBM, gdzie można znaleźć
dodatkowe informacje. Polecam używanie tej aplikacji za każdym razem, gdy istnieje potrzeba
sprawdzenia składni danego polecenia lub np. odczytania informacji o błędzie.
Podsumowanie
1. Do wykonywania systemowych poleceń DB2 służy aplikacja Command Linę Processor.
2. Wykonywanie pojedynczych poleceń SQL oraz skryptów dokonuje się w aplikacji Command
Center.
3. Aplikacja Information Center pozwala na szybkie wyszukiwanie pomocnych informacji.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
65
Rozdzia
ł 13. InterBase.
Serwer SQL InterBase firmy Inprise znajduje się na stronie internetowej firmy BSC:
http://www.borland.com.pl. Jest to oczywiście wersja darmowa. Do pracy z ćwiczeniami potrzebny
nam jest jeden plik: ib_server_6_0_1.zip, który jest wersją Server i Client InterBase dla Windows.
Plik zajmuje 5,36 MB. Przy modemie pozwalającym na prędkość przesyłu 33,6 kb/s, transmisja
powinna trwać 30 minut. Więc koszt skopiowania z Internetu InterBase jest niewielki. Jak się można
później zorientować, możliwości, jakie oferuje InterBase są naprawdę duże.
Instalacja InterBase 6.01
Po rozpakowaniu pliku ib_server_6_0_1.zip, utworzony zostanie katalog: ib_server_6_0_1. W tym
katalogu znajduje się jeszcze katalog - server, w którym znajduje się program instalacyjny setup.exe.
Po uruchomieniu go pojawi się ekran powitalny, z którego przejdziemy do następnego panelu
klawiszem Next. Panel, który teraz widzimy Important installation Information zawiera bardzo ważne
informacje o instalacji oraz informacje o tym, jak utworzyć bazę danych. Jeżeli instalujemy InterBase
w środowisku Windows NT musimy się upewnić, że system został zaktualizowany przez Service Pack
5, odpowiedni w wersji językowej do posiadanego systemu.
Przejdźmy do następnego panelu instalacyjnego przyciskiem Next. Pojawi się teraz tekst umowy
licencyjnej, który należy zaakceptować, aby móc kontynuować instalację. Naciskamy przycisk Yes.
Pojawi się okno, które pozwala wybrać komponenty InterBase'a. Okno to widoczne jest na poniższym
zdjęciu. Proszę wybrać wszystkie komponenty i ewentualnie zmienić katalog docelowy, w którym
zainstalowany zostanie system InterBase. Po skończeniu naciskamy przycisk Install.
Rys.13.1.
Następuje kopiowanie plików na dysk twardy, a po ich skopiowaniu pojawi się jeszcze okno z
informacją, że proces instalacji został zakończony. Naciskamy przycisk Finish.
Narz
ędzie IBConsole
IBConsole jest odpowiednikiem narzędzia DB2 Control Center. Tutaj również mamy możliwość
podglądania obiektów bazy danych, jakimi są m.in. tabele i widoki. Przede wszystkim IBConsole jest
narzędziem, w którym możemy stworzyć bazę danych.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
66
Z poziomu IBConsole możemy również wywołać narzędzie lnteractive SQL, które pozwala na
wykonywanie poleceń SQL. Okno aplikacji IBConsole znajduje się poniżej.
Rys.13.2.
Tworzenie bazy danych w InterBase
Jeżeli nie jesteśmy w aplikacji IBConsole, musimy j ą uruchomić z Menu Start | Programy | Interbase |
IBConsole. Z menu Server wybieramy pozycję Login. W oknie, które się pojawi wpisujemy
użytkownika SYSDBA i hasło masterkey. Po załogowaniu się do menedżera bazy InterBase przejdź
do menu Database do pozycji Create Database, Okno, które się pojawi jest widoczne na rysunku 13.3.
Rys.13.3.
Aby stworzyć bazę danych, wypełnij to okno, jak możesz zauważyć na rysunku 13.3. W końcu
naciśnij przycisk OK. Baza została utworzona. Teraz możemy przejść do wykonywania skryptów,
które utworzą tabele w bazie danych i wypełnią je danymi. Opis wykonywania skryptów znajduje się
w następnej sekcji.
Narz
ędzie InterBase Manager
InterBase Manager można wywołać z menu Start | Programy l InterBase InterBase Server Manager.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
67
Pozwala on m.in. na ustalenie czy serwer InterBase ma być wywoływany automatycznie przy starcie
systemu operacyjnego.
Rys.13.4.
Narz
ędzie Interactiue SQ1
Narzędzie lnteractive SQL pozwala na wprowadzanie poleceń SQL i ich wykonywania na bazie
danych. Wywołuje sieje z poziomu aplikacji IBConsole z menu Tools | lnteractive SQL Wykonywanie
wprowadzonych poleceń SQL dokonuje się przez naciśnięcie kombinacji klawiszy Ctrl+E (Execute).
Rys.13.5.
Czasami gdy będziemy wychodzić z Interactive SQL, będziemy pytani, czy zatwierdzić transakcję.
Transakcjąjest każda operacja na danych w bazie danych. Transakcja musi się wykonać w całości lub
zostać wycofana.
Oto okno dialogowe z pytaniem, czy zatwierdzić transakcję.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
68
Rys.13.6.
Wszystkie polecenia SQL zawarte w tych ćwiczeniach wymagają, aby ich działanie było zatwierdzane
(przycisk Yes w okienku na rysunku 13.6).
Aplikacja Interactive SQL umożliwia również wykonywanie skryptów SQL. Wykonywanie skryptów
SQL zostało opisane w kolejnej sekcji.
Wykonywanie skryptów
Aby wykonać skrypty tworzące tabele i wypełniające je danymi, musimy przejść do menu Query w
lnteractive SQL i wybrać pozycję Load Script. Okno, które się pojawi pozwala na wybranie pliku
skryptu.
Na rysunku 13.7 znajdziesz okno aplikacji Interactive SQL z załadowanym skryptem, który tworzy i
wypełnia danymi tabelę KLIENCI.
Rys.13.7.
Po naciśnięciu kombinacji klawiszy Ctrl+E skrypt zostanie wykonany i utworzona zostanie tabela
KLIENCI. Pozostałe skrypty również muszą zostać wykonane do utworzenia całej struktury bazy
danych. Skrypt zostanie wykonany wtedy, gdy jesteśmy podłączeni do bazy WYPAUT. Na zdjęciu
powyżej w pasku stanu na samym dole jest wyświetlona informacja, że baza, do której jesteśmy
aktualnie podłączeni to WYPAUT.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
69
Podsumowanie
1. InterBase jest równie dobrym systemem bazy danych do studiowania niniejszych ćwiczeń.
2. Wykonywanie pojedynczych poleceń SQL oraz skryptów dokonuje się w aplikacji Interactive
SQL.
3. Aplikacja IBConsole pozwala na szybkie wyszukiwanie pomocnych informacji.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
70
Rozdzia
ł 14. Struktura przykładowej bazy danych.
Przykładowa baza wypożyczalni samochodów WYPAUT składa się z pięciu tabel. Przechowuje ona
dane o klientach, pracownikach, samochodach, miejscach, z których samochody można wypożyczyć
oraz dane o wypożyczeniach.
Każde wypożyczenie jest odnotowywane w tabeli WYPOŻYCZENIA. ^ ...... <«
Każdy: klient, samochód, miejsce wypożyczenia i oddania, pracownik wypożyczający i przyjmujący
posiada numer, po którym jest identyfikowany w tabeli WYPOŻYCZENIA. Pojedynczy rekord z
tabeli WYPOŻYCZENIA opisuje jedno wypożyczenie samochodu. Tak więc, gdy odczytujemy ten
rekord, możemy odnaleźć dane o kliencie, który wypożyczył dany samochód, dane o pracowniku
obsługującym klienta oraz o miejscu wypożyczenia i oddania samochodu.
Opis tabel
Szczegółowy opis tabel wchodzących w skład przykładowej bazy danych wypożyczalni
samochodów.
Tabela KLIENCI
Tabela KLIENCI przechowuje dane na temat klientów wypożyczających samochody. Między innymi
na podstawie tych danych może zostać wystawiona faktura.
Tabela 14.1.
Tabela SAMOCHODY
Tabela SAMOCHODY zawiera informacje o dostępnych samochodach, które klient może
wypożyczyć.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
71
Tabela 14.2.
Tabela PRACOWNICY
Tabela PRACOWNICY zawiera dane wszystkich pracowników firmy wypożyczającej samochody.
Tabela 14.3.
Tabela MIEJSCA
W tabeli MIEJSCA znajdują się informacje o miejscach, z których klient wypożyczył samochód, oraz
informacje o miejscach oddania.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
72
Tabela 14.4.
Tabela WYPO
ŻYCZENIA
Tabela WYPOŻYCZENIA jest najbardziej rozbudowana tabela. Znajdują się tutaj wszelkie
informacje o wypożyczonych samochodach, miejscu wypożyczenia i oddania, klientach, dacie itd.
Tabela 14.5.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
73
Relacje pomi
ędzy tabelami
Poniżej znajduje się diagram związków encji dla naszej przykładowej bazy danych wypożyczalni
samochodów. Diagram przedstawia relacje pomiędzy tabelami.
Z diagramu możemy odczytać wszystkie występujące relacje miedzy tabelami. Jeden KLIENT może
dokonać wielu WYPOŻYCZEŃ niekoniecznie w tym samym czasie. Jeden SAMOCHÓD może być
WYPOŻYCZANY wielokrotnie. Jeden PRACOWNIK może obsłużyć wiele WYPOŻYCZEŃ.
Samochód może zostać WYPOŻYCZONY/ODDANY wielokrotnie w różnych MIEJSCACH.
Rys.14.1.
Skrypty tworz
ące strukturę bazy WYPAUT
W następnych sekcjach znajdują się listingi skryptów tworzących tabele bazy WYPAUT. Skrypty te
jednocześnie wypełniają tabele przykładowymi danymi. Poniższe skrypty zostały przygotowane do
wykonania w systemie DB2. Aby wykonać je w InterBase musimy:
§ usunąć wiersz, który łączy się z bazą danych
CONNECT
TO...;
§ usunąć wiersz, który usuwa tabelę
DROP TABLE
,
ponieważ InterBase przerywa przetwarzanie
skryptu, gdy wystąpi błąd. Taki Wad wystąpi, gdy po raz pierwszy uruchomimy skrypt. Polega on
na usuwaniu tabeli, która jeszcze nie istnieje;
§ usunąć kwalifikatory DB2ADMIN przed nazwą tabeli w poleceniach
CREATE TABLE
oraz w
poleceniach
INSERT
.
Fragment polecenia SQL tworzącego tabelę oraz polecenia wstawiającego
wiersz w InterBase powinien wyglądać tak:
CREATE TABLE KLIENCI ( ...
...INSERT INTO KLIENCI VALUES ( . . .
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
74
Rozdzia
ł 15. Skrypty
Skrypt tworz
ący tabelę KLIENCI i wypełniający ją danymi
CONNECT
TO
WYPAUT
USER
DB
2
ADMIN
USING
DB
2
ADMIN
;
DROP
TABLE
DB2ADMIN.KLIENCI;
CREATE
TABLE
DB2ADMIN.KLIENCI
(
NR_KLIENTA
CHAR(8)
NOT
NULL,
IMIE
VARCHAR(20)
NOT
NULL,
NAZWISKO
VARCHAR(20)
NOT
NULL,
NR_KARTY_KREDYT
CHAR(20)
,
FIRMA
VARCHAR(40)
,
ULICA
VARCHAR(24)
NOT
NULL,
NUMER
CHAR(8)
NOT
NULL,
MIASTO
VARCHAR(24)
NOT
NULL,
KOD
CHAR(6)
NOT
NULL,
NIP
CHAR(12)
,
NR_TELEFONU
CHAR(16),
PRIMARY
KEY
(NR_KLIENTA));
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000001',
'JAN',
'KOWALSKI',
NULL,
NULL,
'KOCHANOWSKIEGO',
'3',
'WROCLAW',
'37-300',
NULL,
'167-763-234');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000002',
'TOMASZ',
'ADAMCZAK'
,
'HH
12345678',
'KOWALSKI
S.C.',
'KWIATOWA',
'4/9',
'WARSZAWA',
'01-900',
'543-123-456',
'46-744-431');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000003',
'PIOTR',
'MALCZYK'
,
'HF
12445661',
'ADA
S.C.',
'ROZANA',
'9',
'WARSZAWA',
'01-900',
'443-133-251',
'16-742-114');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000004',
'PAWEL',
'FIODOROWICZ'
,
'DD
76545321',
'KRAWIECTWO',
'ARMII
KRAJOWEJ',
'22A',
'WARSZAWA',
'01-200',
'555-233-256',
'44-342-116');
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
75
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000005',
'ANIELA',
'DALGIEWICZ'
,NULL
,
'MODNA
PANI',
'BOHATEROW
GETTA',
'24',
'WROCLAW',
'37-200',
'456-134-153',
'144-188-415');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000006',
'JOANNA',
'KWIATKOWSKA',
NULL,
NULL,
'TUWIMA',
'2/5',
'SWIDNICA',
'58-100',
NULL,
'963-733-231');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000007',
'BOZENA',
'MALINOWSKA',
NULL,
NULL,
'LELEWELA',
'34/1',
'SWIDNICA',
'58-100',
NULL,
'965-553-778');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000008',
'TOMASZ',
'NOWAK',
NULL,
NULL,
'ZEROMSKIEGO',
'5A/8',
'SWIDNICA',
'58-100',
NULL,
'911-135-536');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000009',
'KRZYSZTOF',
'DOMAGALA',
NULL,
NULL,
'LESNA',
'5',
'SWIDNICA',
'58-100',
NULL,
'922-233-232');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000010',
'ARKADIUSZ',
'DOCZEKALSKI',
NULL,
NULL,
'LESNA',
'2',
'SWIDNICA',
'58-100',
NULL,
'922-233-267');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000011',
'ANNA',
'KOWALSKA'
,'KJ
98765412'
,
'MODNIARSTWO',
'POWSTANCOW
SLASKICH',
'4',
'WROCLAW',
'37-200',
'422-132-354',
'444-283-
901');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000012',
'KRZYSZTOF',
'DOBROWOLSKI'
,
NULL,
'KAMIENIARSTWO',
'STRZEGOMSKA',
'124',
'WROCLAW',
'37-400',
'433-133-332',
'443-285-202');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000013',
'MARCIN',
'KRZYKALA'
,
NULL,
NULL,
'KONOPNICKIEJ',
'1/4',
'WROCLAW',
'37-400',
NULL,
'442-211-109');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000014',
'ANETA',
'PAPROCKA'
,
NULL,
NULL,
'TUWIMA',
'2',
'WROCLAW',
'37-400',
NULL,
'442-671-899');
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
76
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000015',
'SEBASTIAN',
'KOWNACKI'
,
NULL,
NULL,
'GLOWACKIEGO',
'2/9',
'WROCLAW',
'37-400',
NULL,
'423-681-129');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000016',
'MICHAL',
'MICHALSKI'
,
NULL,
NULL,
'KWIATOWA',
'9/3',
'WROCLAW',
'37-500',
NULL,
'499-621-921');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000017',
'MICHAL',
'SZYKOWNY'
,
'WW
12398765',
NULL,
'LESNA',
'3',
'WARSZAWA',
'00-100',
NULL,
'191-221-622');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000018',
'MARCIN',
'MARCINKOWSKI'
,
'WQ
14368781',
NULL,
'OKREZNA',
'33',
'WARSZAWA',
'00-200',
NULL,
'122-127-647');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000019',
'RAFAL',
'RAFALSKI'
,
'WS
12358672',
'NAPRAWA
SAMOCHODOW',
'PRZEMYSLOWA',
'1',
'WARSZAWA',
'00-200',
'999-765-120',
'822-324-742');
INSERT
INTO
DB2ADMIN.KLIENCI
VALUES
('00000020',
'ROBERT',
'NOWAK'
,
'AS
61333699',
'TAPICERSTWO',
'MOSTOWA',
'9B',
'WARSZAWA',
'00-100',
'987-765-333',
'811-311-147');
Skrypt tworz
ący tabelę SAMOCHODY i wypełniający ją danymi
CONNECT TO WYPAUT USER db2admin USING db2admin;
DROP TABLE DB2ADMIN.SAMOCHODY;
CREATE TABLE DB2ADMIN.SAMOCHODY (
NR_SAMOCHODU CHAR(6) NOT NULL,
MARKA VARCHAR(20) NOT NULL,
TYP VARCHAR(16) NOT NULL,
ROK_PROD DATE NOT NULL,
KOLOR VARCHAR(16) NOT NULL,
POJ_SILNIKA SMALLINT NOT NULL,
PRZEBIEG INTEGER NOT NULL,
PRIMARY KEY (NR_SAMOCHODU));
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
77
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000001', 'MERCEDES', '190D', '1999-01-01', 'BIALY', 1800, 23000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000002', 'MERCEDES', '230D', '1999-01-01', 'NIEBIESKI', 2000, 35000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000003', 'FIAT', 'SEICENTO', '2000-01-01', 'CZERWONY', 1100, 13000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000004', 'FIAT', 'SEICENTO', '1999-01-01', 'BIALY', 900, 10000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000005', 'FIAT', 'TIPO', '1998-01-01', 'BORDOWY', 1400, 43000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000006', 'POLONEZ', 'CARO', '1997-01-01', 'ZIELONY', 1600, 55000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000007', 'OPEL', 'CORSA', '2000-01-01', 'ZIELONY', 1100, 11000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000008', 'OPEL', 'VECTRA', '1999-01-01', 'SZARY', 1800, 36000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000009', 'MERCEDES', '190D', '1996-01-01', 'BRAZOWY', 1800, 69000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000010', 'FORD', 'ESCORT', '2000-01-01', 'NIEBIESKI', 1600, 8000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000011', 'FORD', 'ESCORT', '1999-01-01', 'BIALY', 1600, 23000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000012', 'FORD', 'KA', '1998-01-01', 'BORDOWY', 1100, 54000);
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
78
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000013', 'FIAT', 'SEICENTO', '1999-01-01', 'ZLOTY', 1100, 25000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000014', 'FIAT', 'SEICENTO', '2000-01-01', 'BIALY', 1100, 18000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000015', 'SEAT', 'IBIZA', '1998-01-01', 'ZOLTY', 1800, 63000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000016', 'FORD', 'SIERRA', '1995-01-01', 'CZERWONY', 1600, 87000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000017', 'OPEL', 'CORSA', '2000-01-01', 'ZIELONY', 1400, 9000);
INSERT INTO DB2ADMIN.SAMOCHODY
VALUES ('000018', 'FORD', 'KA', '1999-01-01', 'ZOLTY', 1400, 20000
Skrypt tworz
ący tabelę PRACOWNICY i wypełniający ją danymi
CONNECT TO WYPAUT USER db2admin USING db2admin;
DROP TABLE DB2ADMIN.PRACOWNICY;
CREATE TABLE DB2ADMIN.PRACOWNICY (
NR_PRACOWNIKA CHAR(4) NOT NULL,
IMIE VARCHAR(20) NOT NULL,
NAZWISKO VARCHAR(20) NOT NULL,
DATA_ZATR DATE NOT NULL,
DZIAL VARCHAR(20) NOT NULL,
STANOWISKO VARCHAR(20) NOT NULL,
PENSJA DECIMAL(8,2) ,
DODATEK DECIMAL(8,2) ,
NR_MIEJSCA CHAR(6) NOT NULL,
NR_TELEFONU CHAR(16),
PRIMARY KEY (NR_PRACOWNIKA));
INSERT INTO DB2ADMIN.PRACOWNICY
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
79
VALUES
('0001',
'JAN',
'KOWALSKI',
'1997-02-01',
'OBSLUGA
KLIENTA',
'SPRZEDAWCA', 1100, 123, '000001', '987-231-123');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES
('0002',
'ANNA',
'KAMINSKA',
'1997-01-01',
'OBSLUGA
KLIENTA',
'SPRZEDAWCA', 1200, 115, '000002', '987-231-124');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES ('0003', 'KRZYSZTOF', 'ADAMSKI', '1997-05-01', 'OBSLUGA KLIENTA',
'KIEROWNIK', 2000, NULL, '000001', '987-231-125');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES
('0004',
'PIOTR',
'MICHALSKI',
'1998-06-01',
'TECHNICZNY',
'MECHANIK', 1700, 76, '000001', '987-231-131');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES ('0005', 'BOZENA', 'DOMANSKA', '1997-02-01', 'OBSLUGA KLIENTA',
'SPRZEDAWCA', 1300, 134, '000003', '987-231-126');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES
('0006',
'WOJCIECH',
'BURZALSKI',
'1998-12-01',
'TECHNICZNY',
'MECHANIK', 1800, 80, '000003', '987-231-132');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES ('0007', 'MARZENA', 'KOWNACKA', '1997-05-01', 'KSIEGOWOSC', 'KASJER',
1400, 105, '000001', '987-231-141');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES
('0008',
'DAMIAN',
'MACHALICA',
'1997-05-01',
'TECHNICZNY',
'KIEROWNIK', 2200, NULL, '000001', '987-231-133');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES ('0009', 'ALICJA', 'MAKOWIECKA', '1999-07-01', 'OBSLUGA KLIENTA',
'SPRZEDAWCA', 1400, 120, '000004', '933-241-525');
INSERT INTO DB2ADMIN.PRACOWNICY
VALUES ('0010', 'WOJCIECH', 'BAGIELSKI', '1998-04-01', 'OBSLUGA KLIENTA',
'SPRZEDAWCA', 1200, 100, '000001', '457-531-143');
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
80
Skrypt tworz
ący tabelę MIEJSCA i wypełniający ją danymi
CONNECT TO WYPAUT USER db2admin USING db2admin;
DROP TABLE DB2ADMIN.MIEJSCA;
CREATE TABLE DB2ADMIN.MIEJSCA (
NR_MIEJSCA CHAR(6) NOT NULL,
ULICA VARCHAR(24) NOT NULL,
NUMER CHAR(8) NOT NULL,
MIASTO VARCHAR(24) NOT NULL,
KOD CHAR(6) NOT NULL,
TELEFON CHAR(16) ,
UWAGI VARCHAR(40),
PRIMARY KEY (NR_MIEJSCA));
INSERT INTO DB2ADMIN.MIEJSCA
VALUES ('000001', 'LEWARTOWSKIEGO', '12', 'WARSZAWA', '10-100', '228-277-
097', NULL);
INSERT INTO DB2ADMIN.MIEJSCA
VALUES ('000002', 'ALEJE LIPOWE', '3', 'WROCLAW', '32-134', '388-299-086',
NULL);
INSERT INTO DB2ADMIN.MIEJSCA
VALUES ('000003', 'KOCHANOWSKIEGO', '8', 'KRAKOW', '91-200', '222-312-498',
NULL);
INSERT INTO DB2ADMIN.MIEJSCA
VALUES ('000004', 'LOTNICZA', '9', 'POZNAN', '22-200', '778-512-044', NULL);
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
81
Skrypt tworz
ący tabelę WYPOŻYCZENIA i wypełnia jacy ją danymi
CONNECT TO WYPAUT USER db2admin USING db2admin;
DROP TABLE DB2ADMIN.WYPOZYCZENIA;
CREATE TABLE DB2ADMIN.WYPOZYCZENIA (
NR_WYPOZYCZENIA CHAR(8) NOT NULL,
NR_KLIENTA CHAR(8) NOT NULL,
NR_SAMOCHODU CHAR(6) NOT NULL,
NR_PRACOW_WYP CHAR(4) NOT NULL,
NR_PRACOW_ODD CHAR(4) ,
NR_MIEJSCA_WYP CHAR(6) NOT NULL,
NR_MIEJSCA_ODD CHAR(6) ,
DATA_WYP DATE NOT NULL ,
DATA_ODD DATE ,
KAUCJA DECIMAL(8,2) ,
CENA_JEDN DECIMAL(8,2) NOT NULL,
PRIMARY KEY (NR_WYPOZYCZENIA));
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000001', '00000001', '000003', '0002', '0002', '000001', '000001',
'1998-09-18', '1998-09-23', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000002', '00000003', '000004', '0001', '0001', '000001', '000001',
'1998-09-26', '1998-09-27', NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000003', '00000002', '000004', '0009', '0009', '000002', '000002',
'1998-10-04', '1998-10-04', NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000004', '00000004', '000003', '0010', '0010', '000003', '000003',
'1998-10-19', '1998-10-25', NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000005', '00000006', '000007', '0010', '0010', '000003', '000003',
'1998-10-29', '1998-11-02', 200, 100);
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
82
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000006', '00000005', '000008', '0010', '0002', '000001', '000003',
'1998-11-07', '1998-11-09', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000007', '00000008', '000011', '0009', '0002', '000001', '000001',
'1998-11-20', '1998-11-25', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000008', '00000006', '000011', '0001', '0005', '000004', '000004',
'1998-11-28', '1998-12-02', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000009', '00000007', '000017', '0002', '0002', '000001', '000002',
'1998-12-01', '1998-12-03', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000010', '00000009', '000017', '0002', '0010', '000001', '000002',
'1998-12-15', '1998-12-17', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000011', '00000010', '000001', '0005', '0005', '000003', '000003',
'1998-12-20', '1998-12-23', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000012', '00000012', '000002', '0005', '0005', '000004', '000004',
'1999-01-04', '1999-01-14', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000013', '00000011', '000005', '0001', '0005', '000003', '000001',
'1999-01-24', '1999-01-29', NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000014', '00000013', '000005', '0001', '0001', '000004', '000001',
'1999-02-01', '1999-02-05', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000015', '00000014', '000004', '0001', '0001', '000002', '000002',
'1999-02-04', '1999-02-04', 200, 100);
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
83
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000016', '00000015', '000018', '0009', '0009', '000002', '000002',
'1999-03-20', '1999-03-23', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000017', '00000016', '000013', '0010', '0010', '000004', '000001',
'1999-03-20', '1999-03-22', 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000018', '00000020', '000014', '0001', '0001', '000001', '000001',
'1999-04-01', '1999-04-05', NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000019', '00000019', '000015', '0005', '0005', '000004', '000004',
'1999-05-04', '1999-05-09', NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000020', '00000017', '000017', '0002', '0002', '000003', '000001',
'1999-08-14', '1999-08-17', NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000021', '00000018', '000009', '0002', NULL, '000001', NULL,
'1999-12-04', NULL, NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000022', '00000017', '000001', '0001', NULL, '000002', NULL,
'1999-12-22', NULL, NULL, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000023', '00000009', '000003', '0010', NULL, '000002', NULL,
'2000-01-08', NULL, 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000024', '00000014', '000004', '0005', NULL, '000001', NULL,
'2000-01-24', NULL, 200, 100);
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000025', '00000010', '000004', '0009', NULL, '000002', NULL,
'2000-02-09', NULL, 200, 100);
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
84
Podsumowanie
1. Przykładowa baza WYPAUT składa się z pięciu tabel: KLIENCI, PRACOWNICY, MIEJSCA,
WYPOZYCZENIA i SAMOCHODY.
2. Wszystkie te tabele są ze sobą powiązane relacjami.
3. Skrypty zamieszczone w tym rozdziale są dostępne również na serwerze:
ftp://ftp.helion.com.pl/przyklady/cwsql.zip.
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
85
Spis treści:
ROZDZIA
Ł 1. JAK KORZYSTAĆ Z ĆWICZEŃ.......................................................... 1
Dlaczego nie MS Access ........................................................................................................................................ 1
Studiowanie ćwiczeń z InterBase ......................................................................................................................... 1
Studiowanie ćwiczeń z DB2 .................................................................................................................................. 2
Zapraszam do ćwiczeń .......................................................................................................................................... 2
ROZDZIA
Ł 2. KONCEPCJA BAZY DANYCH ........................................................... 3
Tradycyjne bazy danych....................................................................................................................................... 3
Relacyjny system bazy danych............................................................................................................................. 3
Wydobywanie informacji z tradycyjnej bazy danych oraz z systemu relacyjnej bazy danych...................... 4
SQL - Strukturalny język zapytań....................................................................................................................... 5
Tabela ..................................................................................................................................................................... 6
Konstrukcja nazwy tabeli..................................................................................................................................... 6
Typy danych .......................................................................................................................................................... 7
Tworzenie tabeli - CREATE TABLE .................................................................................................................. 7
Wartość pusta NULL ............................................................................................................................................ 8
Autoryzacja dostępu do tabeli.............................................................................................................................. 8
Widoki .................................................................................................................................................................... 9
Podsumowanie ....................................................................................................................................................... 9
ROZDZIA
Ł 3. ZAPYTANIA SQL. ............................................................................. 10
Struktura polecenia SELECT ............................................................................................................................ 10
Wybieranie wszystkich kolumn ......................................................................................................................... 10
Wybieranie określonych kolumn ....................................................................................................................... 10
Wybieranie i jednoczesnym porządkowaniem ................................................................................................. 11
Wybieranie niepowtarzających się wierszy....................................................................................................... 12
Wybieranie określonych wierszy ....................................................................................................................... 12
Operatory logiczne używane w klauzuli WHERE............................................................................................ 13
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
86
Operatory AND oraz OR.................................................................................................................................... 13
Predykat IN ......................................................................................................................................................... 15
Predykat BETWEEN.......................................................................................................................................... 16
Wybieranie wartości NULL ............................................................................................................................... 16
Wyszukiwanie częściowe - predykat LIKE ....................................................................................................... 17
Podsumowanie ..................................................................................................................................................... 19
ROZDZIA
Ł 4 . WYBIERANIE DANYCH Z WIELU TABEL. ..................................... 20
Składnie złączenia - predykat JOIN .................................................................................................................. 21
Stosowanie aliasów w zapytaniu ........................................................................................................................ 22
Podsumowanie ..................................................................................................................................................... 23
ROZDZIA
Ł 5. FUNKCJE SKALARNE I ARYTMETYCZNE..................................... 24
Wybieranie wyliczonych wartości...................................................................................................................... 24
Nazywanie wyliczone. Kolumny......................................................................................................................... 25
Funkcja COALESCE.......................................................................................................................................... 26
Dziesiętna reprezentacja wartości ..................................................................................................................... 27
Zaokrąglanie wyników........................................................................................................................................ 27
Porównania daty.................................................................................................................................................. 28
Funkcje daty ........................................................................................................................................................ 28
Wybieranie podłańcucha .................................................................................................................................... 30
Łączenie łańcuchów ............................................................................................................................................ 32
Wyrażenie CASE................................................................................................................................................. 32
Podsumowanie ..................................................................................................................................................... 33
ROZDZIA
Ł 6.FUNKCJE KOLUMNOWE I GRUPUJĄCE ........................................ 34
Funkcje kolumnowe ............................................................................................................................................ 34
Klauzula GROUP BY ......................................................................................................................................... 35
Klauzula HAUING.............................................................................................................................................. 36
Podsumowanie ..................................................................................................................................................... 37
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
87
ROZDZIA
Ł 7. KLAUZULA UNION........................................................................... 38
Łączenie wielu wyników zapytania.................................................................................................................... 38
Klauzula
UNION ALL
............................................................................................................................................ 39
Podsumowanie ..................................................................................................................................................... 40
ROZDZIA
Ł 8. PODZAPYTANIA............................................................................... 41
Używanie podzapytań ......................................................................................................................................... 41
Podzapytania z użyciem słowa kluczowego IN ................................................................................................. 41
Podzapytania z użyciem słowa kluczowego ALL ............................................................................................. 42
Podzapytania z użyciem słowa kluczowego ANY lub SOHE........................................................................... 43
Podzapytania w klauzuli HAVING.................................................................................................................... 43
Podsumowanie ..................................................................................................................................................... 44
ROZDZIA
Ł 9. UTRZYMYWANIE DANYCH. ............................................................ 45
Tworzenie tabel ................................................................................................................................................... 45
Tworzenie widoków ............................................................................................................................................ 46
Dodawanie i usuwanie rekordów ....................................................................................................................... 46
Zmienianie danych w tabeli................................................................................................................................ 48
Usuwanie tabel..................................................................................................................................................... 48
Podsumowanie ..................................................................................................................................................... 48
ROZDZIA
Ł 10. OGRANICZENIA I INTEGRALNOŚĆ REFERENCYJNA ............... 49
Ograniczenia........................................................................................................................................................ 49
Integralność danych - klucz główny................................................................................................................... 49
Integralność refereicyjna - klucz obcy............................................................................................................... 50
Podsumowanie ..................................................................................................................................................... 52
ROZDZIA
Ł 11.INSTALACJA DB2. .......................................................................... 53
Instalacja dla systemu Windows i Windows NT .............................................................................................. 53
Podsumowanie ..................................................................................................................................................... 56
ROZDZIA
Ł 12. NARZĘDZIA DB2. ........................................................................... 57
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
88
Control Center..................................................................................................................................................... 57
Command Center ................................................................................................................................................ 58
Przygotowywanie zapytań SQL i ich wykonywanie......................................................................................... 58
Wykonywanie skryptów SQL ............................................................................................................................ 59
Wyświetlanie wyników wykonania zapytania................................................................................................... 60
Command linę Processor .................................................................................................................................... 60
Tworzenie bazy.................................................................................................................................................... 63
Ustawienia narzędzi DB2.................................................................................................................................... 63
Information Center ............................................................................................................................................. 64
Podsumowanie ..................................................................................................................................................... 64
ROZDZIA
Ł 13. INTERBASE. ................................................................................... 65
Instalacja InterBase 6.01 .................................................................................................................................... 65
Narzędzie IBConsole ........................................................................................................................................... 65
Tworzenie bazy danych w InterBase ................................................................................................................. 66
Narzędzie InterBase Manager............................................................................................................................ 66
Narzędzie Interactiue SQ1.................................................................................................................................. 67
Wykonywanie skryptów ..................................................................................................................................... 68
Podsumowanie ..................................................................................................................................................... 69
ROZDZIA
Ł 14. STRUKTURA PRZYKŁADOWEJ BAZY DANYCH......................... 70
Opis tabel ............................................................................................................................................................. 70
Tabela KLIENCI................................................................................................................................................. 70
Tabela SAMOCHODY ....................................................................................................................................... 70
Tabela PRACOWNICY...................................................................................................................................... 71
Tabela MIEJSCA ................................................................................................................................................ 71
Tabela WYPOŻYCZENIA................................................................................................................................. 72
Relacje pomiędzy tabelami ................................................................................................................................. 73
Skrypty tworzące strukturę bazy WYPAUT .................................................................................................... 73
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
89
ROZDZIA
Ł 15. SKRYPTY........................................................................................ 74
Skrypt tworzący tabelę KLIENCI i wypełniający ją danymi.......................................................................... 74
Skrypt tworzący tabelę SAMOCHODY i wypełniający ją danymi ................................................................ 76
Skrypt tworzący tabelę PRACOWNICY i wypełniający ją danymi............................................................... 78
Skrypt tworzący tabelę MIEJSCA i wypełniający ją danymi ......................................................................... 80
Skrypt tworzący tabelę WYPOŻYCZENIA i wypełnia jacy ją danymi......................................................... 81
Podsumowanie ..................................................................................................................................................... 84