Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
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.
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
___________________________________________________________________________________________________
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
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
Cała operacja wydobywania danych trwa w tym przypadku znacznie krócej. Jakość tych danych jest
przy tym lepsza.
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.
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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,
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
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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;
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.
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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;
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Ł
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
FROM DB2ADMIN.PRACOWNICY ...
ORDER BY STANOWISKO ASC, NAZWISKO ASC;
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
dla szukanych wierszy.
SELECT IMIĘ, NAZWISKO, STANOWISKO, DZIAL
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ść
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
TRUE.
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
FROM DB2ADMIN.PRACOWNICY
WHERE STANOWISKO = 'KIEROWNIK'
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.
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
Wartości mogą być typu numerycznego, znakowego, typu daty lub czasu.
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;
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
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
:
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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".
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.
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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%';
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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,
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
DB2ADMIN.MIEJSCA.MIASTO,
DB2ADMIN.MIEJSCA.ULICA
FROM DB2ADMIN.PRACOWNICY,DB2ADMIN.MIEJSCA
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
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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,
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:
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
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.
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
2
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
przykład tego samego polecenia dla systemu InterBase:
SELECT P.IMIE, P.NAZWISKO, P.PENSJA, P.DODATEK,
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.
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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.
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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;
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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:
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
Wyrażenie CASE
Wyrażenie
CASE
pozwala na wybranie pewnej wartości w zależności od wartości w innej
kolumnie.
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.
3
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
ORDER BY P.STANOWISKO;
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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;
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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ę muszą 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
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
WHERE NAZWISKO LIKE '%SKI';
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
.
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
4
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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;
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
.
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
tabeli służy wyrażenie SQL
ALTER
TABLE
.
Kolejne polecenie
ALTER
TABLE
doda dwie kolumny: FIRMA oraz NIP do tabeli KLIENCI_TEST.
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
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
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)
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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;
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
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
NR_PRACOWN1KA. Ustalenie klucza głównego (
PRIMARY
KEY
) podczas tworzenia tabeli:
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.
5
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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ą
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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,
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
W takim przypadku należy ponownie zainstalować możliwie najnowszego Service Packa w wersji
językowej odpowiadającej naszemu systemowi.
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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).
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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.
6
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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ń
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:
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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:
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
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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.
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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.
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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ść
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
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.
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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ę.
7
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
aktualnie podłączeni to WYPAUT.
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.
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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ć.
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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.
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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 ( . . .
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
VALUES ('00000004', 'PAWEL', 'FIODOROWICZ' , 'DD 76545321', 'KRAWIECTWO',
'ARMII KRAJOWEJ', '22A', 'WARSZAWA', '01-200', '555-233-256', '44-342-116');
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');
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
INSERT INTO DB2ADMIN.KLIENCI
VALUES ('00000014', 'ANETA', 'PAPROCKA' , NULL, NULL, 'TUWIMA', '2',
'WROCLAW', '37-400', NULL, '442-671-899');
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,
8
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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));
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);
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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);
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,
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
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');
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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');
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
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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);
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
VALUES ('00000005', '00000006', '000007', '0010', '0010', '000003', '000003',
'1998-10-29', '1998-11-02', 200, 100);
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);
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000015', '00000014', '000004', '0001', '0001', '000002', '000002',
'1999-02-04', '1999-02-04', 200, 100);
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);
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
INSERT INTO DB2ADMIN.WYPOZYCZENIA
VALUES ('00000025', '00000010', '000004', '0009', NULL, '000002', NULL,
'2000-02-09', NULL, 200, 100);
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.
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
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....................................................................................................................8
Wartość pusta NULL..............................................................................................................................................8
Autoryzacja dostępu do tabeli................................................................................................................................9
Widoki...................................................................................................................................................................... 9
Podsumowanie....................................................................................................................................................... 10
ROZDZIAŁ 3. ZAPYTANIA SQL............................................................................... 11
Struktura polecenia SELECT..............................................................................................................................11
Wybieranie wszystkich kolumn........................................................................................................................... 11
Wybieranie określonych kolumn......................................................................................................................... 12
Wybieranie i jednoczesnym porządkowaniem................................................................................................... 12
Wybieranie niepowtarzających się wierszy........................................................................................................ 14
9
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
Wybieranie określonych wierszy......................................................................................................................... 14
Operatory logiczne używane w klauzuli WHERE............................................................................................. 15
Operatory AND oraz OR......................................................................................................................................15
Predykat IN............................................................................................................................................................18
Predykat BETWEEN............................................................................................................................................19
Wybieranie wartości NULL................................................................................................................................. 20
Wyszukiwanie częściowe - predykat LIKE.........................................................................................................20
Podsumowanie....................................................................................................................................................... 23
ROZDZIAŁ 4 . WYBIERANIE DANYCH Z WIELU TABEL....................................... 24
Składnie złączenia - predykat JOIN....................................................................................................................25
Stosowanie aliasów w zapytaniu.......................................................................................................................... 27
Podsumowanie....................................................................................................................................................... 27
ROZDZIAŁ 5. FUNKCJE SKALARNE I ARYTMETYCZNE..................................... 29
Wybieranie wyliczonych wartości....................................................................................................................... 29
Nazywanie wyliczone. Kolumny.......................................................................................................................... 30
Funkcja COALESCE ...........................................................................................................................................31
Dziesiętna reprezentacja wartości....................................................................................................................... 32
Zaokrąglanie wyników..........................................................................................................................................32
Porównania daty ...............................................................................................................................................33
Funkcje daty.......................................................................................................................................................... 34
Wybieranie podłańcucha...................................................................................................................................... 36
Łączenie łańcuchów.............................................................................................................................................. 38
Wyrażenie CASE ...........................................................................................................................................39
Podsumowanie....................................................................................................................................................... 39
ROZDZIAŁ 6.FUNKCJE KOLUMNOWE I GRUPUJĄCE......................................... 40
Funkcje kolumnowe ........................................................................................................................................ 40
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
Klauzula GROUP BY .......................................................................................................................................... 41
Klauzula HAUING................................................................................................................................................43
Podsumowanie....................................................................................................................................................... 44
ROZDZIAŁ 7. KLAUZULA UNION............................................................................ 45
Łączenie wielu wyników zapytania .....................................................................................................................45
Klauzula union all................................................................................................................................................. 47
Podsumowanie....................................................................................................................................................... 48
ROZDZIAŁ 8. PODZAPYTANIA............................................................................... 49
Używanie podzapytań........................................................................................................................................... 49
Podzapytania z użyciem słowa kluczowego IN...................................................................................................49
Podzapytania z użyciem słowa kluczowego ALL............................................................................................... 50
Podzapytania z użyciem słowa kluczowego ANY lub SOHE............................................................................ 51
Podzapytania w klauzuli HAVING..................................................................................................................... 51
Podsumowanie....................................................................................................................................................... 52
ROZDZIAŁ 9. UTRZYMYWANIE DANYCH.............................................................. 53
Tworzenie tabel..................................................................................................................................................... 53
Tworzenie widoków.............................................................................................................................................. 54
Dodawanie i usuwanie rekordów.........................................................................................................................55
Zmienianie danych w tabeli..................................................................................................................................56
Usuwanie tabel.......................................................................................................................................................56
Podsumowanie....................................................................................................................................................... 57
ROZDZIAŁ 10. OGRANICZENIA I INTEGRALNOŚĆ REFERENCYJNA................ 58
Ograniczenia..........................................................................................................................................................58
Integralność danych - klucz główny.................................................................................................................... 58
Integralność refereicyjna - klucz obcy................................................................................................................ 59
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
Podsumowanie....................................................................................................................................................... 61
ROZDZIAŁ 11.INSTALACJA DB2............................................................................ 62
Instalacja dla systemu Windows i Windows NT................................................................................................ 62
Podsumowanie....................................................................................................................................................... 66
ROZDZIAŁ 12. NARZĘDZIA DB2............................................................................. 67
Control Center.......................................................................................................................................................67
Command Center.................................................................................................................................................. 68
Przygotowywanie zapytań SQL i ich wykonywanie.......................................................................................... 68
Wykonywanie skryptów SQL.............................................................................................................................. 69
Wyświetlanie wyników wykonania zapytania.................................................................................................... 70
Command linę Processor......................................................................................................................................71
Tworzenie bazy......................................................................................................................................................73
Ustawienia narzędzi DB2......................................................................................................................................73
Information Center............................................................................................................................................... 74
Podsumowanie....................................................................................................................................................... 75
ROZDZIAŁ 13. INTERBASE..................................................................................... 76
Instalacja InterBase 6.01...................................................................................................................................... 76
Narzędzie IBConsole.............................................................................................................................................76
Tworzenie bazy danych w InterBase...................................................................................................................77
Narzędzie InterBase Manager............................................................................................................................. 78
Narzędzie Interactiue SQ1................................................................................................................................... 78
Wykonywanie skryptów....................................................................................................................................... 80
Podsumowanie....................................................................................................................................................... 81
ROZDZIAŁ 14. STRUKTURA PRZYKŁADOWEJ BAZY DANYCH......................... 82
Opis tabel............................................................................................................................................................... 82
1
Arkadiusz Jakubowski – Podstawy SQL – ćwiczenia praktyczne
__________________________________________________________
_________________________________________
Tabela KLIENCI...................................................................................................................................................82
Tabela SAMOCHODY......................................................................................................................................... 82
Tabela PRACOWNICY........................................................................................................................................83
Tabela MIEJSCA.................................................................................................................................................. 83
Tabela WYPOŻYCZENIA.................................................................................................................................. 84
Relacje pomiędzy tabelami...................................................................................................................................86
Skrypty tworzące strukturę bazy WYPAUT......................................................................................................86
ROZDZIAŁ 15. SKRYPTY......................................................................................... 87
Skrypt tworzący tabelę KLIENCI i wypełniający ją danymi........................................................................... 87
Skrypt tworzący tabelę SAMOCHODY i wypełniający ją danymi..................................................................89
Skrypt tworzący tabelę PRACOWNICY i wypełniający ją danymi................................................................ 91
Skrypt tworzący tabelę MIEJSCA i wypełniający ją danymi.......................................................................... 93
Skrypt tworzący tabelę WYPOŻYCZENIA i wypełnia jacy ją danymi..........................................................95
Podsumowanie....................................................................................................................................................... 98
1