Kurs Oracle - Podstawy SQL

Podstawy języka SQL

Strona 3

Spis treści

Zawartość tabel wykorzystywanych na kursie___________________________________5

Zawartość tabeli DEPT _______________________________________________________ 6

Zawartość tabeli EMP ________________________________________________________ 6

Zawartość tabeli SALGRADE __________________________________________________ 6

Budowa tabel wykorzystywanych na kursie_______________________________________ 7

Relacyjny model danych____________________________________________________9

Operatory relacyjne _________________________________________________________ 10

Własności relacyjnej bazy danych______________________________________________ 10

Przegląd języka SQL______________________________________________________13

Zasady SQL ________________________________________________________________ 14

Ćwiczenia __________________________________________________________________ 28

Funkcje grupowe ________________________________________________________31

Stosowanie funkcji grupowych ________________________________________________ 32

Klauzula GROUP BY ________________________________________________________ 33

Wybierane wyraŜenia a funkcje grupowe________________________________________ 34

Klauzula HAVING __________________________________________________________ 35

Kolejność występowania klauzul _______________________________________________ 37

Ćwiczenia __________________________________________________________________ 38

Wybieranie danych z wielu tabel ____________________________________________40

Złączenie równościowe _______________________________________________________ 41

Złączenia nierównościowe ____________________________________________________ 42

Ćwiczenia __________________________________________________________________ 44

Inne metody łączenia tabel_________________________________________________48

Złączenia zewnętrzne ________________________________________________________ 49

Połączenie tabeli samej ze sobą ________________________________________________ 49

Operatory zbiorowe _________________________________________________________ 50

Podstawy języka SQL

Strona 4

Ćwiczenia __________________________________________________________________53

Podzapytania ___________________________________________________________ 56

Podzapytania zagnieŜdŜone____________________________________________________57

Podzapytania zwracające jeden wiersz __________________________________________57

Podzapytania zwracające wiele wierszy__________________________________________58

Ćwiczenia __________________________________________________________________63

Język definiowania danych ________________________________________________ 64

Struktury danych ORACLE ___________________________________________________65

Tworzenie tabel _____________________________________________________________65

Warunki integralności ________________________________________________________66

Tworzenie tabeli przez zapytanie _______________________________________________72

Zmiana definicji tabeli________________________________________________________73

Język manipulowania danymi ______________________________________________ 77

Wstawianie wierszy __________________________________________________________78

Modyfikacja wierszy _________________________________________________________79

Usuwanie wierszy ____________________________________________________________79

Transakcje _________________________________________________________________80

Perspektywy ____________________________________________________________ 83

Perspektywy ________________________________________________________________84

Tworzenie perspektyw________________________________________________________84

Usuwanie perspektywy _______________________________________________________85

UŜytkownicy i uprawnienia ________________________________________________ 87

Zarządzanie uŜytkownikami___________________________________________________88

Uprawnienia w bazie Oracle ___________________________________________________89

Role _______________________________________________________________________93

Podstawy języka SQL

Strona 7

Budowa tabel wykorzystywanych na kursie

Podczas  kursu  będziemy  korzystać  z  uprzednio  utworzonych  tabel  opisujących
zatrudnionych  w  pewnej  firmie,  ich  wynagrodzenia  i  miejsca  pracy.  Firma  dzieli  się  na
departamenty. KaŜdy pracownik naleŜy do pewnej grupy zaszeregowania, w zaleŜności od
wysokości pensji, którą otrzymuje.

Tabela DEPT — tabela zawierająca wszystkie departamenty

Kolumna

Opis

DEPTNO

Departament number — unikalny numer departamentu

DNAME

Nazwa departamentu — przechowywana w zapisie duŜymi literami

LOC

Lokalizacja departamentu (miasto w którym znajduje się departament)

Tabela EMP

—

wykaz wszystkich pracowników

Kolumna

Opis

EMPNO

Employee number — unikalny numer pracownika

ENAME

Nazwisko pracownika — przechowywane w zapisie duŜymi literami

JOB

Etat, stanowisko pracy

MGR

Identyfikator szefa (czyli EMPNO we wierszu szefa)

HIREDATE

Data zatrudnienia

SAL

Pensja

COMM

Prowizja naliczona od początku roku, dotyczy pracowników
zatrudnionych na stanowisku SALESMAN

DEPTNO

Numer departamentu w którym zatrudniony jest pracownik. Wartość
w tym polu musi odpowiadać jednemu i tylko jednemu wierszowi
w tabeli DEPT

Podstawy języka SQL

Strona 8

Tabela SALGRADE — tabela „widełek” zaszeregowania

Kolumna

Opis

GRADE

Numer grupy zaszeregowania

LOSAL

LOW SALARY — dolna granica widełek płacowych dla stawki
zaszeregowania GRADE

HISAL

HIGH SALARY — górna granica widełek

Podstawy języka SQL

Strona 9

Relacyjny model danych

Podstawy języka SQL

Strona 10

Relacyjna baza danych jest to zbiór dwuwymiarowych tabel. Z modelem relacyjnym
powiązane są następujące pojęcia:

•

tabela,

•

kolumna,

•

wiersz,

•

pole.

Model relacyjny opiera się na pojęciach zaczerpniętych z algebry. Pojęcia te to:

•

relacja,

•

operator działający na relacjach i dający w wyniku relacje.

Relacje przedstawiane są w postaci tabel, zaś wybieranie danych z tabel to wynik działania
operatorów relacyjnych na tych tabelach.

Operatory relacyjne

WyróŜniamy następujące operatory relacyjne:

SELEKCJA

pobieranie danych z relacji, w wyniku otrzymujemy
wszystkie wiersze, które spełniają zadany warunek

PROJEKCJA

operacja pobrania wszystkich wierszy, ale tylko wskazanych
kolumn z tych wierszy

ILOCZYN KARTEZJAŃSKI wynik połączenia kaŜdy z kaŜdym wierszy z dwóch relacji

ZŁĄCZENIE

połączenie dwóch relacji poprzez pewne kryterium łączace
niektóre wiersze z obu relacji

SUMA ZBIOROWA

wszystkie wiersze z obu relacji

CZĘŚĆ WSPÓLNA

wiersze wspólne dla obu relacji

RÓśNICA ZBIOROWA

wiersze, które występują w jednej, a nie występują w drugiej
relacji

Własności relacyjnej bazy danych

Relacyjna baza danych ma następujące własności:

•

baza jest widziana przez uŜytkownika jako zbiór tabel,

•

nazwy tabel w bazie muszą być unikalne,

•

tabele składają się wierszy i kolumn,

•

językiem słuŜącym do operowania na bazie danych jest język nieproceduralny oparty
na algebrze relacji. Obecnie standardem jest SQL.

Podstawy języka SQL

Strona 13

Przegląd języka SQL

Podstawy języka SQL

Strona 14

SQL jest oparty na wyraŜeniach języka angielskiego. Jest językiem deklaratywnym —
podajemy tylko, co naleŜy wykonać, ale nie specyfikujemy w jaki sposób.

Język SQL słuŜy do następujących celów:

•

specyfikowania zapytań,

•

operowania danymi — DML (Data Modification Language) — wstawiania,
modyfikowania i usuwania danych z bazy danych,

•

definiowania danych — DDL (Data Definition Language) — dodawania do bazy
nowych obiektów,

•

sterowania danymi — DCL (Data Control Language) — określania praw dostępu do
danych.

Zasady SQL

Zapisywanie poleceń SQL

•

Polecenia SQL mogą być rozmieszczone w kilku liniach. Koniec polecenia SQL
zaznacza się średnikiem.

•

Zaleca się umieszczanie klauzul od nowej linii.

•

MoŜna uŜywać tabulacji.

•

Nie wolno dzielić słowa pomiędzy linie.

•

Obojętne, czy uŜywamy małych czy wielkich liter, chyba Ŝe sprawdzamy zawartość
pola.

Podstawowy blok zapytań SQL

Za pomocą polecenia SELECT uzyskujemy informacje z bazy.

Polecenie SELECT

składa się co najmniej z klauzuli

SELECT

i klauzuli

FROM

W klauzuli

SELECT

wymieniamy interesujące nas kolumny.

W klauzuli

FROM

wskazujemy, skąd pobrać dane.

Aby pobrać nazwiska pracowników oraz ich zawód, napiszemy:

SELECT ENAME, JOB
FROM EMP;

ENAME      JOB
---------- ---------
KING       PRESIDENT
BLAKE      MANAGER
CLARK      MANAGER
JONES      MANAGER
MARTIN     SALESMAN
ALLEN      SALESMAN

Podstawy języka SQL

Strona 15

TURNER     SALESMAN
JAMES      CLERK
WARD       SALESMAN
FORD       ANALYST
SMITH      CLERK
SCOTT      ANALYST
ADAMS      CLERK
MILLER     CLERK

Nazwy kolumn oddzielamy przecinkami.

Aby wybrać wszystko z danej tabeli wpisujemy * (gwiazdkę) zamiast listy kolumn.

SELECT *
FROM EMP;

EMPNO ENAME      JOB             MGR HIREDATE        SAL        COMM     DEPTNO
---- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING       PRESIDENT            81/11/17        5000                    10
7698 BLAKE      MANAGER         7839 81/05/01        2850                    30
7782 CLARK      MANAGER         7839 81/06/09        2450                    10
7566 JONES      MANAGER         7839 81/04/02        2975                    20
7654 MARTIN     SALESMAN        7698 81/09/28        1250       1400         30
7499 ALLEN      SALESMAN        7698 81/02/20        1600        300         30
7844 TURNER     SALESMAN        7698 81/09/08        1500          0         30
7900 JAMES      CLERK           7698 81/12/03         950                    30
7521 WARD       SALESMAN        7698 81/02/22        1250        500         30
7902 FORD       ANALYST         7566 81/12/03        3000                    20
7369 SMITH      CLERK           7902 80/12/17         800                    20
7788 SCOTT      ANALYST         7566 82/12/09        3000                    20
7876 ADAMS      CLERK           7788 83/01/12        1100                    20
7934 MILLER     CLERK           7782 82/01/23        1300                    10

W klauzuli

SELECT

mogę teŜ być uŜyte:

•

wyraŜenia arytmetyczne,

•

aliasy (nazwy zastępcze) kolumn,

•

konkatenacja,

•

literały.

WyraŜenia arytmetyczne

W wyraŜeniu arytmetycznym mogą występować nazwy kolumn, stałe wartości liczbowe
oraz operatory arytmetyczne:

dodawanie

odejmowanie

mnoŜenie

dzielenie

Przykład

SELECT ENAME, SAL*12
FROM EMP;

ENAME          SAL*12
---------- ----------
KING            60000
BLAKE           34200

Podstawy języka SQL

Strona 16

CLARK           29400
JONES           35700
MARTIN          15000
ALLEN           19200
TURNER          18000
JAMES           11400
WARD            15000
FORD            36000
SMITH            9600
SCOTT           36000
ADAMS           13200
MILLER          15600

W wyraŜeniach zachowywany jest następujący priorytet działań:

•

mnoŜenie,

•

dzielenie,

•

dodawanie,

•

odejmowanie.

Np.  w  wyraŜeniu  arytmetycznym  250  +12*34  najpierw  zostanie  obliczona  wartość
wyraŜenia  12*24,  a  do  wyniku  zostanie  dodana  wartość  250.  Kolejność  wykonywania
działań moŜna zmienić przy pomocy nawiasów. Na przykład w wyraŜeniu arytmetycznym
(250  +12)*34  najpierw  zostanie  obliczona  wartość  wyraŜenia  250+12,  a  wynik  zostanie
pomnoŜony przez 34.

Aliasy kolumn

Domyślne nagłówki kolumn moŜemy zastąpić innymi nazwami, które będą bardziej
znaczące.

MoŜna uŜywać polskich liter. Alias podaje się bezpośrednio po nazwie kolumny, której
nazwę chcemy zmienić. Spacje w aliasie nie są dopuszczane, moŜna natomiast utworzyć
alias ze spacją biorąc całość w podwójne apostrofy.

Przykład

SELECT ENAME NAZWISKO, SAL*12 WYNAGR, COMM PROWIZJA
FROM EMP;

NAZWISKO   WYNAGR     PROWIZJA
---------- ---------- ----------
KING            60000
BLAKE           34200
CLARK           29400
JONES           35700
MARTIN          15000       1400
ALLEN           19200        300
TURNER          18000          0
JAMES           11400
WARD            15000        500
FORD            36000
SMITH            9600
SCOTT           36000
ADAMS           13200
MILLER          15600

Podstawy języka SQL

Strona 17

Operator konkatenacji

Operator konkatenacji (||) pozwala na łączenie kolumny z kolumną, literałem, wyraŜeniem
arytmetycznym lub wartością stałą. Argumenty są łączone i tworzą jedna kolumnę
wynikową.

Aby połączyć kolumny EMPNO i ENAME i opatrzyć je tytułem PRACOWNIK,
napiszemy:

SELECT EMPNO||ENAME PRACOWNIK
FROM EMP;

PRACOWNIK
--------------------------------------------------
7839KING
7698BLAKE
7782CLARK
7566JONES
7654MARTIN
7499ALLEN
7844TURNER
7900JAMES
7521WARD
7902FORD
7369SMITH
7788SCOTT
7876ADAMS
7934MILLER

Literały

Oprócz kolumn na liście

SELECT

mogą znajdować się literały (ciągi znaków lub liczby).

Wtedy dla kaŜdego wiersza zostanie wypisana ta sama wartość literału:

SELECT ENAME PRACOWNIK, ' PRACUJE W DEPARTAMENCIE', DEPTNO DEPARTAMENT
FROM EMP;

PRACOWNIK    'PRACUJE W DEPARTAMENCIE' DEPARTAMENT
-----------  ------------------------- -----------
KING         'PRACUJE W DEPARTAMENCIE'  10
BLAKE        'PRACUJE W DEPARTAMENCIE'  30
CLARK        'PRACUJE W DEPARTAMENCIE'  10
JONES        'PRACUJE W DEPARTAMENCIE'  20
MARTIN       'PRACUJE W DEPARTAMENCIE'  30
ALLEN        'PRACUJE W DEPARTAMENCIE'  30
TURNER       'PRACUJE W DEPARTAMENCIE'  30
JAMES        'PRACUJE W DEPARTAMENCIE'  30
WARD         'PRACUJE W DEPARTAMENCIE'  30
FORD         'PRACUJE W DEPARTAMENCIE'  20
SMITH        'PRACUJE W DEPARTAMENCIE'  20
SCOTT        'PRACUJE W DEPARTAMENCIE'  20
ADAMS        'PRACUJE W DEPARTAMENCIE'  20
MILLER       'PRACUJE W DEPARTAMENCIE'  10

Podstawy języka SQL

Strona 18

Obsługa wartości NULL

Jeśli wartość wiersza w kolumnie jest nieokreślona, to mówimy, Ŝe na ona wartość NULL.
Nie jest to toŜsame z wartością 0.

Wartość wyraŜenia w którym będzie występować wartość NULL równieŜ będzie NULL.
Dlatego teŜ wyraŜenie SAL*12+COMM zwróci NULL tam, gdzie COMM było równe
NULL.

SELECT ENAME, SAL*12+COMM
FROM EMP;

ENAME      SAL*12+COM
---------- ----------
KING
BLAKE
CLARK
JONES
MARTIN          16400
ALLEN           19500
TURNER          18000
JAMES
WARD            15500
FORD
SMITH
SCOTT
ADAMS
MILLER

Aby w wyraŜeniu potraktować wartość NULL jako wartość róŜną od NULL, trzeba uŜyć
funkcji NVL.

SELECT ENAME, SAL*12+NVL(COMM,0) ROCZNE_WYN
FROM EMP;

ENAME      ROCZNE_WYN
---------- ----------
KING            60000
BLAKE           34200
CLARK           29400
JONES           35700
MARTIN          16400
ALLEN           19500
TURNER          18000
JAMES           11400
WARD            15500
FORD            36000
SMITH            9600
SCOTT           36000
ADAMS           13200
MILLER          15600

W powyŜszym przykładzie wartości róŜne od NULL zachowują swoją wartość, zaś
wartości NULL zostaną potraktowane jak 0.

Drugi parametr funkcji NVL określa, na jaką wartość naleŜy zamienić wartość NULL:

NVL(DATA,'85/09/01')
NVL(NUMBER, 10)
NVL(MIASTO,'KRAKÓW')

Podstawy języka SQL

Strona 19

Eliminacja duplikatów

Domyślnie wyświetlane są wyniki zapytania bez eliminowania powtarzających się
wartości, np.:

SELECT DEPTNO
FROM EMP;

DEPTNO
----------
        10
        30
        10
        20
        30
        30
        30
        30
        30
        20
        20
        20
        20
        10

Słowo kluczowe DISTINCT

Eliminację powtarzających się wartości uzyskujemy stosując słowo kluczowe

DISTINCT

w klauzuli

SELECT

SELECT DISTINCT DEPTNO
FROM EMP;

DEPTNO
----------
        10
        20
        30

MoŜna teŜ zaŜądać wyspecyfikowania wzajemnie róŜnych list wartości poprzez podanie
w klauzuli

SELECT

wielu nazw kolumn.

Słowo kluczowe DISTINCT

DISTINCT

odnosi się do wszystkich nazw kolumn

występujących w klauzuli

SELECT

SELECT DISTINCT JOB, DEPTNO
FROM EMP;

JOB       DEPTNO
--------- ----------
ANALYST           20
CLERK             10
CLERK             20
CLERK             30
MANAGER           10

Podstawy języka SQL

Strona 20

MANAGER           20
MANAGER           30
PRESIDENT         10
SALESMAN          30

Klauzula ORDER BY

Aby określić kolejność, w jakiej będą zwracane wyniki, naleŜy uŜyć klauzuli

ORDER BY

(uporządkuj wg). Klauzula

ORDER BY

musi być ostatnią klauzulą polecenia SELECT.

SELECT ENAME, JOB
FROM EMP
ORDER BY ENAME;

ENAME      JOB
---------- ---------
ADAMS      CLERK
ALLEN      SALESMAN
BLAKE      MANAGER
CLARK      MANAGER
FORD       ANALYST
JAMES      CLERK
JONES      MANAGER
KING       PRESIDENT
MARTIN     SALESMAN
MILLER     CLERK
SCOTT      ANALYST
SMITH      CLERK
TURNER     SALESMAN
WARD       SALESMAN

Domyślnie dane są sortowane w porządku rosnącym (ASCENDING

ASCENDING

) — od najmniejszych

do największych liczb, od wcześniejszych do późniejszych dat, zaś ciągi znakowe są
sortowane wg ustawień NLS (określanych przy tworzeniu bazy danych).

Aby odwrócić kolejność sortowania naleŜy uŜyć słowa DESC

DESC

(DESCENDING

DESCENDING

) uŜytego

bezpośrednio po nazwie kolumny wyspecyfikowanej w klauzuli

ORDER BY

SELECT ENAME, JOB, HIREDATE
FROM EMP
ORDER BY HIREDATE DESC;

ENAME      JOB       HIREDATE
---------- --------- ---------
ADAMS      CLERK     83/01/12
SCOTT      ANALYST   82/12/09
MILLER     CLERK     82/01/23
JAMES      CLERK     81/12/03
FORD       ANALYST   81/12/03
KING       PRESIDENT 81/11/17
MARTIN     SALESMAN  81/09/28
TURNER     SALESMAN  81/09/08
CLARK      MANAGER   81/06/09
BLAKE      MANAGER   81/05/01
JONES      MANAGER   81/04/02
WARD       SALESMAN  81/02/22
ALLEN      SALESMAN  81/02/20
SMITH      CLERK     80/12/17

Podstawy języka SQL

Strona 21

MoŜna sortować według kilku kolumn, wtedy po słowie kluczowym ORDER BY

ORDER BY

naleŜy

podać nazwy kolumn, po których chcemy sortować.

SELECT ENAME, JOB, DEPTNO
FROM EMP
ORDER BY  DEPTNO, ENAME;

ENAME      JOB       DEPTNO
---------- --------- ----------
CLARK      MANAGER           10
KING       PRESIDENT         10
MILLER     CLERK             10
ADAMS      CLERK             20
FORD       ANALYST           20
JONES      MANAGER           20
SCOTT      ANALYST           20
SMITH      CLERK             20
ALLEN      SALESMAN          30
BLAKE      MANAGER           30
JAMES      CLERK             30
MARTIN     SALESMAN          30
TURNER     SALESMAN          30
WARD       SALESMAN          30

Nazwy kolumn, po których sortujemy, muszą być wyspecyfikowane
w klauzuli

SELECT

Sortowanie dotyczy tylko wyniku zapytania wyświetlanego na ekranie. Dane w tabelach
nie są sortowane.

Klauzula WHERE

Klauzula

WHERE

odpowiada operacji selekcji.

Klauzula ta specyfikuje kryteria doboru wierszy. Klauzula

WHERE

, o ile jest, musi

występować bezpośrednio po klauzuli

FROM

Operatory w klauzuli

WHERE

mogą być dwojakiego rodzaju:

•

operatory logiczne,

•

operatory SQL.

Operatory logiczne

Dane znakowe i ciągi znaków w klauzuli

WHERE

muszą być ujęte w pojedyncze

apostrofy. Przy porównywaniu znaków Oracle rozróŜnia małe i wielkie litery.

Podstawy języka SQL

Strona 22

Aby wybrać nazwiska, zawód i numer departamentu dla wszystkich zatrudnionych na
stanowisku CLERK, napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE JOB='CLERK';

ENAME      JOB       DEPTNO
---------- --------- ----------
JAMES      CLERK             30
SMITH      CLERK             20
ADAMS      CLERK             20
MILLER     CLERK             10

Aby wybrać wszystkich zatrudnionych po 01.01.1982r., napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE HIREDATE>'82/01/01'

ENAME      JOB       DEPTNO
---------- --------- ----------
SCOTT      ANALYST           20
ADAMS      CLERK             20
MILLER     CLERK             10

MoŜna w klauzuli

WHERE

porównywać dla kaŜdego wiersza wartości dwóch kolumn.

Np. Ŝeby wybrać wszystkich, którzy otrzymują wyŜszą prowizję niŜ pensję, napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE COMM>SAL;

ENAME      JOB       DEPTNO
---------- --------- ----------
MARTIN     SALESMAN          30

Operatory SQL

WyróŜniamy 4 operatory SQL, działające na wszystkich typach danych:

•

BETWEEN... AND,

•

(lista),

•

LIKE,

•

IS NULL

IS NULL.

Operator BETWEEN...AND

Operator BETWEEN...AND

BETWEEN...AND

słuŜy do sprawdzenia, czy wartość znajduje się w podanym

przedziale (wliczając w to krańce przedziału). Górna granica musi następować po dolnej.

Podstawy języka SQL

Strona 23

Aby wybrać wszystkich, którzy zarabiają od 1000 do 1500, wliczając w to tych, którzy
zarabiają 1000 i 1500, napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE SAL BETWEEN 1000 AND 1500;

ENAME      JOB       DEPTNO
---------- --------- ----------
MARTIN     SALESMAN          30
TURNER     SALESMAN          30
WARD       SALESMAN          30
ADAMS      CLERK             20
MILLER     CLERK             10

Operator IN

Operator IN

słuŜy do sprawdzania, czy dana wartość znajduje się na wyspecyfikowanej

liście. Aby wybrać podwładnych osób o identyfikatorach 7839 i 7902, napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE MGR IN (7839,7902);

ENAME      JOB       DEPTNO
---------- --------- ----------
BLAKE      MANAGER           30
CLARK      MANAGER           10
JONES      MANAGER           20
SMITH      CLERK             20

Dane znakowe występujące na liście naleŜy ująć w pojedyncze apostrofy.

Operator LIKE

Operator LIKE

słuŜy do wybierania wartości odpowiadających podanemu wzorcowi.

Wzorzec tworzą dwa specjalne symbole:

•

% (znak procent) — odpowiada dowolnemu ciągowi znaków,

•

_ (znak podkreślenia) — odpowiada dokładnie jednemu dowolnemu znakowi.

Aby wybrać osoby, które jako drugą literę w nazwisku mają literę "A", napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE ENAME LIKE '_A%';

ENAME      JOB       DEPTNO
---------- --------- ----------
MARTIN     SALESMAN          30
JAMES      CLERK             30
WARD       SALESMAN          30

Podstawy języka SQL

Strona 24

Aby wybrać osoby, których nazwisko składa się z pięciu liter, napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE ENAME LIKE '_____';

ENAME      JOB       DEPTNO
---------- --------- ----------
BLAKE      MANAGER           30
CLARK      MANAGER           10
JONES      MANAGER           20
ALLEN      SALESMAN          30
JAMES      CLERK             30
SMITH      CLERK             20
SCOTT      ANALYST           20
ADAMS      CLERK             20

Operator IS NULL

Operator IS NULL

IS NULL

słuŜy do wyszukiwania wartości NULL.

Aby wybrać dane o osobach, które nie posiadają szefa, napiszemy:

SELECT ENAME, JOB, DEPTNO
FROM EMP
WHERE MGR IS NULL;

ENAME      JOB       DEPTNO
---------- --------- ----------
KING       PRESIDENT         10

Nie moŜna napisać w klauzuli

WHERE

warunku MGR=NULL, gdyŜ wtedy Oracle nie

wyszuka Ŝadnych wartości.

Operatory negacji

Operatory negacji słuŜą do zaprzeczania warunkom w klauzuli WHERE.

nie jest równy (VAX, UNIX, PC)

^=, _=

nie jest równy (IBM)

nie jest równy (wszystkie systemy operacyjne)

NOT

kolumna=

nie jest równy

NOT

kolumna>

nie jest większy

NOT

NOT BETWEEN

BETWEEN

nie jest w podanym przedziale

NOT

NOT IN

nie jest w zbiorze

NOT

NOT LIKE

nie jest zgodny z maską

IS NOT

IS NOT NULL

NULL

nie jest NULL

Aby wybrać wszystkie osoby, które nie zarabiają więcej niŜ 2000, napiszemy:

Podstawy języka SQL

Strona 25

SELECT ENAME, SAL
FROM EMP
WHERE NOT SAL>2000;

ENAME      SAL
---------- ----------
MARTIN           1250
ALLEN            1600
TURNER           1500
JAMES             950
WARD             1250
SMITH             800
ADAMS            1100
MILLER           1300

Aby wybrać osoby, które nie otrzymują prowizji, napiszemy:

SELECT ENAME, JOB, COMM
FROM EMP
WHERE COMM IS NOT NULL

ENAME      JOB       COMM
---------- --------- ----------
MARTIN     SALESMAN        1400
ALLEN      SALESMAN         300
TURNER     SALESMAN           0
WARD       SALESMAN         500

Tworzenie złoŜonych kryteriów

Do budowania warunków złoŜonych słuŜą operatory AND

AND

(i) oraz OR

(lub).

Warunek (X AND

AND

Y) jest prawdziwy wtedy i tylko wtedy, gdy X jest prawdziwy i Y jest

prawdziwy.

Warunek (X OR

Y) jest prawdziwy wtedy i tylko wtedy, gdy X jest prawdziwy lub Y jest

prawdziwy.

Operator AND

AND

ma większy priorytet niŜ operator OR

Aby wybrać wszystkich pracowników zatrudnionych na stanowisku 'MANAGER'
i zarabiających więcej niŜ 2000, napiszemy:

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL>2000

AND JOB='MANAGER'

NAME      JOB       SAL
---------- --------- ----------
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
JONES      MANAGER         2975

PoniewaŜ operator AND

AND

ma wyŜszy priorytet niŜ operator OR

, to poniŜszy przykład

znajdzie nam wszystkie osoby zatrudnione na stanowisku 'CLERK' oraz te osoby
zatrudnione na stanowisku 'MANAGER', których zarobki przekraczają 1000.

Podstawy języka SQL

Strona 26

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL>1000

AND JOB='MANAGER' OR JOB='CLERK';

ENAME      JOB       SAL
---------- --------- ----------
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
JONES      MANAGER         2975
JAMES      CLERK            950
SMITH      CLERK            800
ADAMS      CLERK           1100
MILLER     CLERK           1300

PoniŜszy przykład, w którym zastosowano nawiasy zmieniające kolejność wykonywania
działań wybierze tylko osoby zatrudnione na stanowisku 'CLERK' lub na stanowisku
'MANAGER', które przy tym zarabiają więcej niŜ 1000.

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL>1000

AND (JOB='MANAGER' OR JOB='CLERK');

ENAME      JOB       SAL
---------- --------- ----------
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
JONES      MANAGER         2975
ADAMS      CLERK           1100
MILLER     CLERK           1300

Hierarchia operatorów

W kaŜdym wyraŜeniu, o ile nawiasy nie wskazują inaczej, operacje są wykonywane
począwszy od operatorów o najwyŜszym priorytecie. Jeśli dwa operatory o tym samym
priorytecie występują obok siebie, to są one wykonywane od lewej do prawej.

1. =, <>, <=, >=, >,<, BETWEEN…AND

BETWEEN…AND

, IN

, LIKE

, IS NULL

IS NULL

2. NOT

NOT

3. AND

AND

4. OR

Aby zapis był bardziej przejrzysty i w celu uniknięcia błędów, zaleca się stosowanie
nawiasów.

Krótki opis poleceń właściwych dla SQL*Plus

Pod SQL*Plus najczęściej uruchamiamy skrypty SQL-owe.

Najczęściej uŜywane polecenia:

Podstawy języka SQL

Strona 27

SPOOL plik;

wszystkie polecenia SQL do czasu wydania rozkazu SPOOL OFF będą
zapisywane do pliku plik

@plik

uruchomienie skryptu plik

ed plik

edycja pliku plik w domyślnym edytorze

SPOOL OFF

kończy wysyłanie danych do pliku

DESC

wyświetla opis struktury tabeli

CONNECT

zmiana podłączenia do bazy

EXIT

wyjście z programu

Podstawy języka SQL

Strona 28

wiczenia

1.Wybierz wszystkie dane z tablicy SALGRADE.

    GRADE     LOSAL     HISAL
--------- --------- ---------
        1       700      1200
        2      1201      1400
        3      1401      2000
        4      2001      3000
        5      3001      9999

2. Wybierz wszystkie dane z tablicy EMP.

    EMPNO ENAME      JOB             MGR HIREDATE       SAL      COMM    DEPTNO
--------- ---------- --------- --------- -------- --------- --------- ---------
     7839 KING       PRESIDENT           81/11/17      5000                  10
     7698 BLAKE      MANAGER        7839 81/05/01      2850                  30
     7782 CLARK      MANAGER        7839 81/06/09      2450                  10
     7566 JONES      MANAGER        7839 81/04/02      2975                  20
     7654 MARTIN     SALESMAN       7698 81/09/28      1250      1400        30
     7499 ALLEN      SALESMAN       7698 81/02/20      1600       300        30
     7844 TURNER     SALESMAN       7698 81/09/08      1500         0        30
     7900 JAMES      CLERK          7698 81/12/03       950                  30
     7521 WARD       SALESMAN       7698 81/02/22      1250       500        30
     7902 FORD       ANALYST        7566 81/12/03      3000                  20
     7369 SMITH      CLERK          7902 80/12/17       800                  20
     7788 SCOTT      ANALYST        7566 82/12/09      3000                  20
     7876 ADAMS      CLERK          7788 83/01/12      1100                  20
     7934 MILLER     CLERK          7782 82/01/23      1300                  10

3. Wybierz wszystkie dane o pracownikach, których zarobki mieszczą się w przedziale
<1000,2000>.

ENAME         DEPTNO       SAL
---------- --------- ---------
MARTIN            30      1250
ALLEN             30      1600
TURNER            30      1500
WARD              30      1250
ADAMS             20      1100
MILLER            10      1300

4. Wybierz numery i nazwy departamentów, sortując według nazw departamentów.

   DEPTNO DNAME
--------- --------------
       10 ACCOUNTING
       40 OPERATIONS
       20 RESEARCH
       30 SALES

Podstawy języka SQL

Strona 29

5. Wybierz wszystkie, wzajemnie róŜne stanowiska pracy.

JOB
---------
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN

6. Wybierz dane o pracownikach zatrudnionych w departamentach 10 i 20 w kolejności
alfabetycznej ich nazwisk.

    EMPNO ENAME      JOB             MGR HIREDATE       SAL      COMM    DEPTNO
--------- ---------- --------- --------- -------- --------- --------- ---------
     7876 ADAMS      CLERK          7788 83/01/12      1100                  20
     7782 CLARK      MANAGER        7839 81/06/09      2450                  10
     7902 FORD       ANALYST        7566 81/12/03      3000                  20
     7566 JONES      MANAGER        7839 81/04/02      2975                  20
     7839 KING       PRESIDENT           81/11/17      5000                  10
     7934 MILLER     CLERK          7782 82/01/23      1300                  10
     7788 SCOTT      ANALYST        7566 82/12/09      3000                  20
     7369 SMITH      CLERK          7902 80/12/17       800                  20

7. Wybierz nazwiska i stanowiska pracy wszystkich pracowników z departamentu 20
zatrudnionych na stanowisku CLERK.

ENAME      JOB
---------- ---------
SMITH      CLERK
ADAMS      CLERK

8. Wybierz następujące informacje o wszystkich pracownikach, którzy posiadają szefa.

ENAME      JOB             SAL
---------- --------- ---------
BLAKE      MANAGER        2850
CLARK      MANAGER        2450
JONES      MANAGER        2975
MARTIN     SALESMAN       1250
ALLEN      SALESMAN       1600
TURNER     SALESMAN       1500
JAMES      CLERK           950
WARD       SALESMAN       1250
FORD       ANALYST        3000
SMITH      CLERK           800
SCOTT      ANALYST        3000
ADAMS      CLERK          1100
MILLER     CLERK          1300

9. Wybierz nazwiska i całkowite, roczne zarobki wszystkich pracowników.

ENAME      RENUMERATION
---------- ------------
KING              60000
BLAKE             34200
CLARK             29400
JONES             35700
MARTIN            16400

Podstawy języka SQL

Strona 30

ALLEN             19500
TURNER            18000
JAMES             11400
WARD              15500
FORD              36000
SMITH              9600
SCOTT             36000
ADAMS             13200
MILLER            15600

10. Wybierz następujące dane o tych pracownikach, którzy zostali zatrudnieni w 1982
roku.

ENAME         DEPTNO HIREDATE
---------- --------- ---------
SCOTT             20 09-DEC-82
MILLER            10 23-JAN-82

11. Wybierz nazwiska, roczną pensję oraz prowizję tych wszystkich pracowników, których
miesięczna pensja przekracza prowizję. Wyniki posortuj według malejących zarobków.
Jeśli dwóch lub więcej pracowników ma taką samą pensję, uporządkuj dane o nich według
nazwisk zgodnie z alfabetem.

ENAME      ANNUAL_SAL      COMM
---------- ---------- ---------
ALLEN           19200       300
TURNER          18000         0
WARD            15000       500

12. Spowoduj wyświetlenie następujących wyników.

Kto, gdzie, kiedy?
-------------------------------------------------------------------------------
KING pracuje na stanowisku PRESIDENT w zespole 10 od 17-NOV-81
BLAKE pracuje na stanowisku MANAGER w zespole 30 od 01-MAY-81
CLARK pracuje na stanowisku MANAGER w zespole 10 od 09-JUN-81
JONES pracuje na stanowisku MANAGER w zespole 20 od 02-APR-81
MARTIN pracuje na stanowisku SALESMAN w zespole 30 od 28-SEP-81
ALLEN pracuje na stanowisku SALESMAN w zespole 30 od 20-FEB-81
TURNER pracuje na stanowisku SALESMAN w zespole 30 od 08-SEP-81
JAMES pracuje na stanowisku CLERK w zespole 30 od 03-DEC-81
WARD pracuje na stanowisku SALESMAN w zespole 30 od 22-FEB-81
FORD pracuje na stanowisku ANALYST w zespole 20 od 03-DEC-81
SMITH pracuje na stanowisku CLERK w zespole 20 od 17-DEC-80
SCOTT pracuje na stanowisku ANALYST w zespole 20 od 09-DEC-82
ADAMS pracuje na stanowisku CLERK w zespole 20 od 12-JAN-83
MILLER pracuje na stanowisku CLERK w zespole 10 od 23-JAN-82

Podstawy języka SQL

Strona 31

Funkcje grupowe

Podstawy języka SQL

Strona 32

Funkcje grupowe słuŜą do działania na grupach wierszy. Wynikiem funkcji grupowej jest
pojedyncza wartość dla całej grupy.

Jeśli nie wyspecyfikujemy inaczej, wszystkie wiersze tabeli są traktowane jako jedna
grupa.

Funkcja

Wynik funkcji

AVG ([D

DISTINCT

ISTINCT

| ALL

ALL

] wyraŜenie)

wartość średnia wyraŜeń, NULL nie jest
uwzględniane

COUNT ([DISTINCT

DISTINCT

| ALL

ALL

] wyraŜenie)

ilość wystąpień wartości wyraŜeń róŜnych od
NULL, gwiazdka (*) uŜyta w miejscu
wyraŜenia powoduje obliczenia ilości
wszystkich wierszy łącznie z duplikatami
i wartościami NULL

MAX ([DISTINCT

DISTINCT

| ALL

ALL

] wyraŜenie)

maksymalna wartość wyraŜenia

MIN ([D

DISTINCT

ISTINCT

| ALL

ALL

] wyraŜenie)

minimalna wartość wyraŜenia

STDDEV ([DISTINCT

DISTINCT

| ALL

ALL

] wyraŜenie)

odchylenie standardowe wyraŜeń, bez
uwzględniania wartości NULL

SUM ([DISTINCT

DISTINCT

| ALL

ALL

] wyraŜenie)

suma wartości wyraŜeń, bez uwzględniania
wartości NULL

VARIANCE ([DISTINCT

DISTINCT

| ALL

ALL

]

wyraŜenie)

wariancja wyraŜeń, bez uwzględniania wartości
NULL

Kwalifikator DISTINCT

DISTINCT

ogranicza działanie funkcji grupowych do róŜnych wartości

argumentów.

Kwalifikator ALL

ALL

jest domyślny — funkcje grupowe nie eliminują duplikatów.

Argumentami funkcji grupowych są liczby, a w przypadku funkcji MAX, MIN i COUNT
takŜe daty, znaki i ciągi znaków.

Wszystkie funkcje grupowe, za wyjątkiem COUNT(*) ignorują wartości NULL.

Stosowanie funkcji grupowych

Aby obliczyć średni zarobek w firmie, napiszemy:

SELECT AVG(SAL)
FROM EMP;

AVG(SAL)
----------
2073,21429

Zwróćmy uwagę, Ŝe została zwrócona jedna wartość dla wszystkich wierszy. Cała firma
jest tu traktowana jako jedna grupa.

Podstawy języka SQL

Strona 33

MoŜna nałoŜyć na to polecenie warunek w klauzuli

WHERE

, np. aby znaleźć minimalny

zarobek na stanowisku 'MANAGER', napiszemy:

SELECT MIN(SAL)
FROM EMP
WHERE JOB='MANAGER';

MIN(SAL)
----------
2450

Aby znaleźć, ilu pracowników pracuje w departamencie 10, napiszemy:

SELECT COUNT(*)
FROM EMP
WHERE DEPTNO=10;

COUNT(*)
----------
3

Klauzula GROUP BY

Do podzielenia wierszy tablicy na grupy uŜywamy klauzuli

GROUP BY

. Pojedynczą

grupę stanowią wszystkie wiersze, dla których wartości podane w klauzuli

GROUP BY

są

identyczne.

Aby obliczyć średnie zarobki w kaŜdym departamencie, napiszemy:

SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

DEPTNO     AVG(SAL)
---------- ----------
        10 2916,66667
        20       2175
        30 1566,66667

Przed grupowaniem moŜemy wyeliminować pewne wiersze za pomocą klauzuli

WHERE

SELECT JOB, MAX(SAL)
FROM EMP
WHERE JOB<>'CLERK'
GROUP BY JOB;

JOB       MAX(SAL)
--------- ----------
ANALYST         3000
MANAGER         2975
PRESIDENT       5000
SALESMAN        1600

W klauzuli

GROUP BY

moŜna podać kilka wyraŜeń, wtedy wiersze będą grupowane

w mniejszych grupach.

Podstawy języka SQL

Strona 34

Aby obliczyć minimalny zarobek w kaŜdym departamencie w podziałem na stanowiska,
napiszemy:

SELECT DEPTNO, JOB, MIN(SAL)
FROM EMP
GROUP BY DEPTNO, JOB;

DEPTNO     JOB       MIN(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450
        10 PRESIDENT       5000
        20 ANALYST         3000
        20 CLERK            800
        20 MANAGER         2975
        30 CLERK            950
        30 MANAGER         2850
        30 SALESMAN        1250

Wybierane wyraŜenia a funkcje grupowe

PoniŜsze polecenie spowoduje obliczenie średnich zarobków w departamentach. Nie
będzie jednak zbyt uŜyteczne, bo nie będzie widoczne, która średnia odpowiada któremu
departamentowi.

SELECT AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

AVG(SAL)
----------
2916,66667
2175
1566,66667

Aby poprawić to polecenie, umieśćmy na liście wyboru klauzuli

SELECT

takŜe numer

departamentu:

SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

DEPTNO     AVG(SAL)
---------- ----------
        10 2916,66667
        20       2175
        30 1566,66667

Na liście wyboru klauzuli

SELECT

mogą występować tylko i wyłącznie

nazwy kolumn, które są przedmiotem działania klauzuli

GROUP BY

, chyba,

e występują one jako argument funkcji grupującej.

Podstawy języka SQL

Strona 35

Klauzula HAVING

Do wybierania interesujących nas grup słuŜy klauzula

HAVING

. W klauzuli

HAVING

umieszczamy warunek wyraŜony za pomocą funkcji grupowej.

Aby wybrać średnie zarobki dla grup zawodowych, gdzie maksymalne zarobki są wyŜsze
niŜ 2000, napiszemy:

SELECT JOB, AVG(SAL)
FROM EMP
GROUP BY JOB
HAVING MAX(SAL)>2000;

JOB       AVG(SAL)
--------- ----------
ANALYST         3000
MANAGER   2758,33333
PRESIDENT       5000

Klauzula

HAVING

moŜe poprzedzać klauzulę

GROUP BY

lub odwrotnie.

Czasami to samo kryterium moŜna wyrazić zarówno za pomocą klauzuli

HAVING

jak

i klauzuli

WHERE

SELECT JOB, AVG(SAL)
FROM EMP
HAVING JOB<>'CLERK'
GROUP BY JOB;

JOB       AVG(SAL)
--------- ----------
ANALYST         3000
MANAGER   2758,33333
PRESIDENT       5000
SALESMAN        1400

lub

SELECT JOB, AVG(SAL)
FROM EMP
WHERE JOB<>'CLERK'
GROUP BY JOB;

JOB       AVG(SAL)
--------- ----------
ANALYST         3000
MANAGER   2758,33333
PRESIDENT       5000
SALESMAN        1400

W takiej sytuacji bardziej efektywne jest umieszczenie warunku w klauzuli

WHERE

Podstawy języka SQL

Strona 36

Kolejność występowania klauzul

Wymagany porządek klauzul jest następujący:

SELECT

lista wyraŜeń

FROM

tabela

WHERE

warunek selekcji wierszy

HAVING

warunek selekcji grup

ORDER

wyraŜenia

6. ;

Podstawy języka SQL

Strona 37

wiczenia

1. Znajdź minimalną pensje w firmie.

  MINIMUM
---------
      800

2. Znajdź minimalną, maksymalną i średnią pensję w firmie.

MIN(SAL)  MAX(SAL)  AVG(SAL)
--------- --------- ---------
      800      5000 2073,2143

3. Oblicz minimalną, maksymalną pensje dla kaŜdego stanowiska pracy.

JOB       MINIMALNA_PENSJA MAKSYMALNA_PENSJA
--------- ---------------- -----------------
ANALYST               3000              3000
CLERK                  800              1300
MANAGER               2450              2975
PRESIDENT             5000              5000
SALESMAN              1250              1600

4. Oblicz, ilu pracowników jest kierownikami (MANAGER).

MANAGERS
---------
        3

5. Znajdź średnie miesięczne pensje oraz średnie roczne zarobki dla kaŜdego stanowiska.
Uwzględnij prowizje.

JOB           AVSAL    AVCOMP
--------- --------- ---------
ANALYST        3000     36000
CLERK        1037,5     12450
MANAGER   2758,3333     33100
PRESIDENT      5000     60000
SALESMAN       1400     17350

6. Znajdź róŜnice między najwyŜszą i najniŜszą pensją.

DIFFERENCE
----------
      4200

7. Znajdź departamenty zatrudniające powyŜej trzech pracowników.

   DEPTNO  COUNT(*)
--------- ---------
       20         5
       30         6

Podstawy języka SQL

Strona 38

8. Sprawdź, czy wszystkie numery pracowników są rzeczywiście róŜne (uŜywając
grupowania).

9. Podaj najniŜsze pensje wypłacane podwładnym swoich kierowników. Wyeliminuj grupy
o minimalnych zarobkach niŜszych niŜ 1000. Uporządkuj według pensji.

      MGR  MIN(SAL)
--------- ---------
     7788      1100
     7782      1300
     7839      2450
     7566      3000
               5000

Podstawy języka SQL

Strona 39

Wybieranie danych z wielu tabel

Podstawy języka SQL

Strona 40

Złączenie równościowe

Chcemy uzyskać następującą informację: dla kaŜdego pracownika znaleźć jego nazwisko,
zawód (tabela EMP) i nazwę departamentu, w którym pracuje (tabela DEPT).

W obu tych tabelach występuje kolumna DEPTNO, określająca numer departamentu
i zawierająca takie same wartości, mówiące o związku pomiędzy tymi tabelami.

Związek oparty o relację równości nazywamy związkiem równościowym (equi-join).

Warunek równości zapisuje się jak zwykły warunek, z tym, Ŝe wartości do porównania są
pobierane z róŜnych tabel.

Aby powiązać ze sobą logicznie tabele EMP i DEPT, napiszemy:

SELECT ENAME, JOB, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;

ENAME      JOB       DNAME
---------- --------- --------------
KING       PRESIDENT ACCOUNTING
BLAKE      MANAGER   SALES
CLARK      MANAGER   ACCOUNTING
JONES      MANAGER   RESEARCH
MARTIN     SALESMAN  SALES
ALLEN      SALESMAN  SALES
TURNER     SALESMAN  SALES
JAMES      CLERK     SALES
WARD       SALESMAN  SALES
FORD       ANALYST   RESEARCH
SMITH      CLERK     RESEARCH
SCOTT      ANALYST   RESEARCH
ADAMS      CLERK     RESEARCH
MILLER     CLERK     ACCOUNTING

Razem z informacjami pobranymi z tablicy EMP otrzymujemy nazwy departamentów
pobrane z tablicy DEPT. Wiersze tabeli EMP są łączone z takimi wierszami tabeli DEPT,
dla których wartość EMP.DEPTNO pokrywa się z DEPT.DEPTNO.

Poprzedzenie nazwy kolumny nazwą tabeli jest wymagane tylko wtedy, gdy w tabelach
wymienionych w klauzuli

FROM

występuje kolumna o tej samej nazwie (DEPTNO).

Podanie samej nazwy kolumny byłoby niejednoznaczne. ChociaŜ wartość tego pola dla
obu tabeli jest taka sama, to ze względów składniowych wymagane jest wskazanie tabeli
z której będzie pobrana wartość.

W poniŜszym przykładzie w klauzuli

SELECT

jest wymagane odwołanie do tablicy

DEPT:

SELECT DEPT.DEPTNO, ENAME, JOB, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
ORDER BY DEPT.DEPTNO;

DEPTNO     ENAME      JOB       DNAME
---------- ---------- --------- --------------
        10 KING       PRESIDENT ACCOUNTING
        10 CLARK      MANAGER   ACCOUNTING

Podstawy języka SQL

Strona 41

        10 MILLER     CLERK     ACCOUNTING
        20 JONES      MANAGER   RESEARCH
        20 SCOTT      ANALYST   RESEARCH
        20 ADAMS      CLERK     RESEARCH
        20 SMITH      CLERK     RESEARCH
        20 FORD       ANALYST   RESEARCH
        30 BLAKE      MANAGER   SALES
        30 MARTIN     SALESMAN  SALES
        30 ALLEN      SALESMAN  SALES
        30 TURNER     SALESMAN  SALES
        30 JAMES      CLERK     SALES
        30 WARD       SALESMAN  SALES

Aliasy tabel

Aliasy, czyli nazwy zastępcze, ułatwiają pisanie zapytań. Aliasy definiuje się w klauzuli

FROM

. Obowiązują one jedynie w zapytaniu, w którym są zdefiniowane. NaleŜy uŜywać

aliasów takŜe w klauzuli

SELECT

, mimo iŜ tekstowo występuje ona wcześniej niŜ

klauzula

FROM

Przykład uŜycia aliasów:

SELECT D.DEPTNO, ENAME, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO
ORDER BY D.DEPTNO;

Jeśli alias zostanie zdefiniowany dla tabeli, to oryginalnej nazwy tabeli nie wolno juŜ
uŜywać w tym zapytaniu.

Złączenia nierównościowe

Złączenia nierównościowe (non-equi-join) nie są oparte o relację równości. Związek
pomiędzy wierszami dwóch tabel określa się poprzez zastosowanie innego operatora niŜ
równość.

Na przykład związek pomiędzy tabelami EMP i SALGRADE jest oparty na następujących
zasadach: określenie stawki zaszeregowania pracownika polega na wskazaniu do jakiego
przedziału (LOSAL, HISAL) naleŜą jego zarobki.

Do utworzenia tego warunku zastosujemy operator BETWEEN…AND

BETWEEN…AND

SELECT ENAME, SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE SAL BETWEEN LOSAL AND HISAL;

ENAME      SAL        GRADE
---------- ---------- ----------
JAMES             950          1
SMITH             800          1
ADAMS            1100          1
MARTIN           1250          2
WARD             1250          2
MILLER           1300          2
ALLEN            1600          3

Podstawy języka SQL

Strona 42

TURNER           1500          3
BLAKE            2850          4
CLARK            2450          4
JONES            2975          4
FORD             3000          4
SCOTT            3000          4
KING             5000          5

Reguły łączenia tabel

Ogólna zasada łączenia tabel:

Minimalna liczba warunków ł

cych = liczba tabel —1

Podstawy języka SQL

Strona 43

wiczenia

1. Wybierz nazwiska oraz nazwy departamentów wszystkich pracowników, w kolejności
alfabetycznej nazw departamentów.

ENAME      DNAME
---------- --------------
KING       ACCOUNTING
CLARK      ACCOUNTING
MILLER     ACCOUNTING
JONES      RESEARCH
SCOTT      RESEARCH
ADAMS      RESEARCH
SMITH      RESEARCH
FORD       RESEARCH
BLAKE      SALES
MARTIN     SALES
ALLEN      SALES
TURNER     SALES
JAMES      SALES
WARD       SALES

2. Wybierz nazwiska wszystkich pracowników wraz z numerami i nazwami
departamentów, w których są zatrudnieni.

ENAME         DEPTNO DNAME
---------- --------- --------------
KING              10 ACCOUNTING
BLAKE             30 SALES
CLARK             10 ACCOUNTING
JONES             20 RESEARCH
MARTIN            30 SALES
ALLEN             30 SALES
TURNER            30 SALES
JAMES             30 SALES
WARD              30 SALES
FORD              20 RESEARCH
SMITH             20 RESEARCH
SCOTT             20 RESEARCH
ADAMS             20 RESEARCH
MILLER            10 ACCOUNTING

3. Dla pracowników o miesięcznej pensji 1500 podaj ich nazwiska, miejsca usytuowania
ich departamentów oraz nazwy tych departamentów.

ENAME      LOCATION      DNAME
---------- ------------- --------------
KING       NEW YORK      ACCOUNTING
BLAKE      CHICAGO       SALES
CLARK      NEW YORK      ACCOUNTING
JONES      DALLAS        RESEARCH
ALLEN      CHICAGO       SALES
FORD       DALLAS        RESEARCH
SCOTT      DALLAS        RESEARCH

Podstawy języka SQL

Strona 44

4. Utwórz następującą listę pracowników z zaszeregowaniem ich do klas zarobkowych.

ENAME      JOB             SAL     GRADE
---------- --------- --------- ---------
JAMES      CLERK           950         1
SMITH      CLERK           800         1
ADAMS      CLERK          1100         1
MARTIN     SALESMAN       1250         2
WARD       SALESMAN       1250         2
MILLER     CLERK          1300         2
ALLEN      SALESMAN       1600         3
TURNER     SALESMAN       1500         3
BLAKE      MANAGER        2850         4
CLARK      MANAGER        2450         4
JONES      MANAGER        2975         4
FORD       ANALYST        3000         4
SCOTT      ANALYST        3000         4
KING       PRESIDENT      5000         5

5. Wybierz informację o pracownikach, których zarobki odpowiadają klasie 3.

ENAME      JOB             SAL     GRADE
---------- --------- --------- ---------
ALLEN      SALESMAN       1600         3
TURNER     SALESMAN       1500         3

6. Wybierz pracowników zatrudnionych w Dallas.

ENAME            SAL LOCATION
---------- --------- -------------
JONES           2975 DALLAS
FORD            3000 DALLAS
SMITH            800 DALLAS
SCOTT           3000 DALLAS
ADAMS           1100 DALLAS

7.Podaj następujące dane o wszystkich pracownikach, z wyjątkiem tych, którzy pracują na
stanowisku „CLERK”. Uporządkuj je według malejących zarobków.

ENAME      JOB             SAL     GRADE DNAME
---------- --------- --------- --------- --------------
JAMES      CLERK           950         1 SALES
SMITH      CLERK           800         1 RESEARCH
ADAMS      CLERK          1100         1 RESEARCH
MARTIN     SALESMAN       1250         2 SALES
WARD       SALESMAN       1250         2 SALES
MILLER     CLERK          1300         2 ACCOUNTING
ALLEN      SALESMAN       1600         3 SALES
TURNER     SALESMAN       1500         3 SALES
BLAKE      MANAGER        2850         4 SALES
CLARK      MANAGER        2450         4 ACCOUNTING
JONES      MANAGER        2975         4 RESEARCH
FORD       ANALYST        3000         4 RESEARCH
SCOTT      ANALYST        3000         4 RESEARCH
KING       PRESIDENT      5000         5 ACCOUNTING

Podstawy języka SQL

Strona 47

Inne metody łączenia tabel

Podstawy języka SQL

Strona 48

Złączenia zewnętrzne

Podczas łączenia tabel wiersz w tabeli nie mający swojego odpowiednika w drugiej tabeli
nie zostanie wybrany. Np. w przykładzie łączenia tabeli EMP i DEPT poprzez kolumnę
DEPNO nie został wybrany wiersz tabeli DEPT, gdzie DEPTNO=40 (departament
OPERATIONS), poniewaŜ nikt nie pracuje w tym departamencie.

Spróbujmy  poprawić  to  złączenie  tak,  aby  został  równieŜ  wybrany  departament  40.
Uczynimy to za pomocą złączenia zewnętrznego (outer-join), gdzie wiersz w tabeli DEPT,
który  nie  ma  swojego  odpowiednika  w  tabeli  EMP  zostanie  połączony  z  wierszem  tabeli
EMP  zawierającym  same  wartości  NULL  (mimo,  Ŝe  wiersz  taki  w  rzeczywistości  nie
istnieje).

Złączenie zewnętrzne oznaczamy znakiem (+) po tej stronie równości, która dotyczy tabeli
z niepełną informacją.

SELECT ENAME, D.DEPTNO, DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO;

ENAME      DEPTNO     DNAME
---------- ---------- --------------
KING               10 ACCOUNTING
CLARK              10 ACCOUNTING
MILLER             10 ACCOUNTING
JONES              20 RESEARCH
SCOTT              20 RESEARCH
ADAMS              20 RESEARCH
SMITH              20 RESEARCH
FORD               20 RESEARCH
BLAKE              30 SALES
MARTIN             30 SALES
ALLEN              30 SALES
TURNER             30 SALES
JAMES              30 SALES
WARD               30 SALES
                   40 OPERATIONS

Operator złączenia zewnętrznego moŜe występować tylko po jednej stronie równości.

Połączenie tabeli samej ze sobą

Dzięki aliasom moŜemy połączyć tabelę samą ze sobą. Rozpatrzmy następujący przykład
— chcemy wybrać pracowników, którzy zarabiają mniej od swoich kierowników:

SELECT E.ENAME NAZW_PR,
       E.SAL PENS_PR,
       M.ENAME NAZW_SZ,
       M.SAL PENS_SZ
FROM   EMP E, EMP M
WHERE  E.MGR=M.EMPNO
       AND E.SAL<M.SAL;

Podstawy języka SQL

Strona 49

NAZW_PR      PENS_PR    NAZW_SZ    PENS_SZ
---------- ---------- ---------- ----------
BLAKE            2850 KING             5000
CLARK            2450 KING             5000
JONES            2975 KING             5000
MARTIN           1250 BLAKE            2850
ALLEN            1600 BLAKE            2850
TURNER           1500 BLAKE            2850
JAMES             950 BLAKE            2850
WARD             1250 BLAKE            2850
SMITH             800 FORD             3000
ADAMS            1100 SCOTT            3000
MILLER           1300 CLARK            2450

Jak widać w klauzuli

FROM,

odwołaliśmy się dwukrotnie do tej samej tabeli za pomocą

dwóch aliasów E i M.

Operatory zbiorowe

Operatory zbiorowe uŜywane są do działań na wynikach dwóch lub więcej zapytań
SELECT. WaŜne jest, aby wszystkie zapytania, na których będziemy uŜywać operatorów
zbiorowych zwracały te samy typy wierszy, np.: (liczba, ciąg znaków, ciąg znaków, data).

Operator UNION

Operator zbiorowy UNION

UNION

jest uŜywany do tworzenia sumy dwóch zbiorów wierszy

(wyników zapytania SELECT). Aby otrzymać wszystkie, wzajemnie róŜne wiersze
zwracane w wyniku dwóch poleceń SELECT, napiszemy:

SELECT JOB
FROM EMP
WHERE DEPTNO=30
UNION
SELECT JOB
FROM EMP
WHERE DEPTNO=10

JOB
---------
CLERK
MANAGER
PRESIDENT
SALESMAN

Operacja UNION

UNION

(podobnie jak inne operatory zbiorowe) powoduje automatyczną

eliminację duplikatów. Aby otrzymać w wyniku wszystkie wiersze z obu tabel naleŜy uŜyć
operatora UNION ALL

UNION ALL

Podstawy języka SQL

Strona 50

Operator INTERSECT

Operator zbiorowy INTERSECT

INTERSECT

(przecięcie zbiorów) powoduje wybranie wierszy

wspólnych dla wyników obu zapytań SELECT.

SELECT JOB
FROM EMP
WHERE DEPTNO=30
INTERSECT
SELECT JOB
FROM EMP
WHERE DEPTNO=20

JOB
---------
CLERK
MANAGER

Operator MINUS

Operator zbiorowy MINUS

MINUS

(róŜnica zbiorów) powoduje wybranie wierszy zwracanych

przez pierwszy rozkaz, nie zwracanych przez rozkaz drugi.

SELECT JOB
FROM EMP
WHERE DEPTNO=30
MINUS
SELECT JOB
FROM EMP
WHERE DEPTNO=20

JOB
---------
SALESMAN

Zapytanie moŜe się składać z więcej niŜ dwóch zapytań SELECT połączonych
operatorami zbiorowymi. W takim przepadku najwyŜszy priorytet ma operator
INT

INT

INTERSECT

ERSECT

, potem operatory UNION

UNION

i MINUS

MINUS

(równe priorytety). W przypadkach

wątpliwych najlepiej stosować nawiasy.

Klauzula ORDER BY w zapytaniach z operatorami zbiorowymi

W zapytaniach z operatorami zbiorowymi moŜna uŜyć klauzuli

ORDER BY

. Odnosi się

ona zawsze do wyniku zapytania i moŜe być uŜyta tylko raz. Umieszcza się ją zawsze na
końcu zapytania. PoniewaŜ na listach wyboru poleceń SELECT nazwy mogą być róŜne,
w klauzuli

ORDER BY

specyfikuje się nie nazwę kolumny, tylko jej pozycję.

Podstawy języka SQL

Strona 51

SELECT EMPNO, ENAME, SAL
FROM EMP
UNION
SELECT ID, NAME, SALARY
FROM EMP_HISTORY
ORDER BY 2;

Wyniki powyŜszego zapytania zostaną posortowane wg kolumny drugiej, czyli wg
nazwisk pracowników.

Podstawy języka SQL

Strona 52

wiczenia

1. Znajdź departament w którym nikt nie jest zatrudniony.

   DEPTNO DNAME
--------- --------------
       40 OPERATIONS

2. Obok numeru i nazwiska pracownika podaj numer i nazwisko jego kierownika.

    EMPNO ENAME          MGRNO MGR_NAME
--------- ---------- --------- ----------
     7698 BLAKE           7839 KING
     7782 CLARK           7839 KING
     7566 JONES           7839 KING
     7654 MARTIN          7698 BLAKE
     7499 ALLEN           7698 BLAKE
     7844 TURNER          7698 BLAKE
     7900 JAMES           7698 BLAKE
     7521 WARD            7698 BLAKE
     7902 FORD            7566 JONES
     7369 SMITH           7902 FORD
     7788 SCOTT           7566 JONES
     7876 ADAMS           7788 SCOTT
     7934 MILLER          7782 CLARK

3. Zmodyfikuj rozwiązanie poprzedniego zadania w ten sposób, aby wyświetlić takŜe
informacje o pracowniku KING, który nie posiada szefa.

    EMPNO ENAME          MGRNO MGR_NAME
--------- ---------- --------- ----------
     7839 KING
     7698 BLAKE           7839 KING
     7782 CLARK           7839 KING
     7566 JONES           7839 KING
     7654 MARTIN          7698 BLAKE
     7499 ALLEN           7698 BLAKE
     7844 TURNER          7698 BLAKE
     7900 JAMES           7698 BLAKE
     7521 WARD            7698 BLAKE
     7902 FORD            7566 JONES
     7369 SMITH           7902 FORD
     7788 SCOTT           7566 JONES
     7876 ADAMS           7788 SCOTT
     7934 MILLER          7782 CLARK

4. Znajdź takie stanowisko pracy (JOB), które było obsadzone zarówno w pierwszej
połowie 1982 roku jak i w pierwszej połowie 1983 roku.

JOB
---------
CLERK

Podstawy języka SQL

Strona 55

Podzapytania

Podstawy języka SQL

Strona 56

Podzapytania zagnieŜdŜone

Podzapytanie to polecenie SELECT zagnieŜdŜone w innym poleceniu SELECT.

SELECT

kolumna_1, kolumna_2, …

FROM

tabela

WHERE

kolumna = (

SELECT

kolumna

FROM

tabela

WHERE

warunek);

Podzapytanie to wewnętrzny SELECT wykonywany w pierwszej kolejności, po to, aby
jego wynik został uŜyty do wykonania zapytania zewnętrznego.

Podzapytania zwracające jeden wiersz

Podzapytania zwracające pojedynczą wartość moŜna traktować jak zwykłe wyraŜenie.

Aby znaleźć pracowników zarabiających powyŜej średniej, moŜemy postąpić następująco:

1. znaleźć średnią pensję:

SELECT AVG(SAL)
FROM EMP;

AVG(SAL)
----------
2073,21429

2. znaleźć pracowników, których pensja jest wyŜsza niŜ obliczona w poprzednim
zapytaniu pensja średnia.

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL>(wynik zapytania z punktu 1)

PowyŜsze dwa rozkazy moŜna połączyć w jeden:

SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL>(SELECT AVG(SAL)
           FROM EMP);

ENAME      JOB       SAL
---------- --------- ----------
KING       PRESIDENT       5000
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
JONES      MANAGER         2975

Podstawy języka SQL

Strona 57

FORD ANALYST 3000
SCOTT ANALYST 3000

Aby znaleźć wszystkich zatrudnionych na tym samym stanowisku co SMITH, napiszemy:

SELECT ENAME, JOB
FROM EMP
WHERE JOB=(SELECT JOB
           FROM EMP
           WHERE ENAME='SMITH');

ENAME      JOB
---------- ---------
JAMES      CLERK
SMITH      CLERK
ADAMS      CLERK
MILLER     CLERK

Podzapytania zwracające wiele wierszy

Jeśli w firmie pracowałoby więcej osób o nazwisku SMITH, to poprzednie podzapytanie
nie miałoby sensu. Co więcej zapytanie wewnętrzne zamiast pojedynczej wartości
zwróciłoby kolumnę wartości, co prowadziłoby do błędu w zewnętrznym zapytaniu.

Przekształćmy to zapytanie tak, aby wyszukiwało wszystkie osoby zatrudnione na
stanowiskach, na których pracuje jakikolwiek SMITH.

SELECT ENAME, JOB
FROM EMP
WHERE JOB IN (SELECT JOB
FROM EMP
WHERE ENAME='SMITH');

Spróbujmy teraz znaleźć pracowników, których pensja jest na liście najwyŜszych
zarobków w departamentach.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
              FROM EMP
              GROUP BY DEPTNO);

ENAME      SAL        DEPTNO
---------- ---------- ----------
BLAKE            2850         30
FORD             3000         20
SCOTT            3000         20
KING             5000         10

RozwaŜmy  teraz  sytuację,  Ŝe  w  firmie  pracuje  osoba,  której  zarobki  pokrywają  się
z największym  zarobkiem  w  danym  departamencie,  ona  jednak  pracuje  w  innym
departamencie.  PowyŜsze  zapytanie  wypisze  taką  osobę,  jako  Ŝe  nie  został  nałoŜony
warunek, aby osoba pracowała w departamencie, z którego pochodzi najwyŜsza pensja.

Podstawy języka SQL

Strona 58

Warunek, w którym porównujemy wiele wartości

Spróbujmy przerobić powyŜsze zapytanie:

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE (SAL,DEPTNO) IN (SELECT MAX(SAL), DEPTNO
                       FROM EMP
                       GROUP BY DEPTNO);

ENAME      SAL        DEPTNO
---------- ---------- ----------
BLAKE            2850         30
FORD             3000         20
SCOTT            3000         20
KING             5000         10

PowyŜsze zapytanie wybierze nam osoby, które zarabiają najwięcej w swoich działach —
został nałoŜony takŜe warunek, aby osoba wybierana pracowała w dziale, do którego
naleŜy najwyŜsza pensja.

Kolumny na liście wyboru wewnętrznego (w klauzuli

WHERE

lub

HAVING

) muszą

występować w kolejności i typach zgodnych z kolejnością i typami występującymi
w klauzuli

SELECT

zewnętrznego polecenia.

Operatory ALL i ANY

Operatory ALL

ALL

i ANY

ANY

moŜna stosować w podzapytaniach zwracających więcej niŜ jeden

wiersz. Podaje się je w klauzulach

WHERE

HAVING

razem z operatorami

porównywania.

Operator ANY

ANY

— wiersz zostanie wybrany, jeśli wyraŜenie jest zgodne co najmniej z jedną

wartością wybraną w podzapytaniu.

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL > ANY (SELECT DISTINCT SAL
                 FROM EMP
                 WHERE DEPTNO=10);

ENAME      SAL        DEPTNO
---------- ---------- ----------
KING             5000         10
BLAKE            2850         30
CLARK            2450         10
JONES            2975         20
ALLEN            1600         30
TURNER           1500         30
FORD             3000         20
SCOTT            3000         20

NajniŜszy zarobek w departamencie 10 wynosi 1300. Zapytanie wybierze wszystkich,
którzy zarabiają więcej niŜ 1300.

Podstawy języka SQL

Strona 59

Aby nie były wybierane wielokrotnie te same zarobki (3000 jest większe i od 1300 i od
2450 — zostałoby wybrane dwukrotnie), często stosuje się razem z operatorami ALL

ALL

i ANY

ANY

słowo kluczowe DISTINCT

DISTINCT

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO=10;

ENAME      SAL        DEPTNO
---------- ---------- ----------
KING             5000         10
CLARK            2450         10
MILLER           1300         10

Operator ALL

ALL

— warunek musi być spełniony przez wszystkie wartości wybrane

w podzapytaniu.

Wybierzmy wszystkich pracowników, którzy zarabiają więcej niŜ ktokolwiek
w departamencie 30:

SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE SAL > ALL (SELECT DISTINCT SAL
                 FROM EMP
                 WHERE DEPTNO=30)
ORDER BY SAL;

ENAME      SAL        DEPTNO
---------- ---------- ----------
JONES            2975         20
FORD             3000         20
SCOTT            3000         20
KING             5000         10

NajwyŜszy zarobek w departamencie 30 wynosi 2850. Zapytanie wybierze wszystkich,
którzy zarabiają więcej niŜ 2850.

Klauzula HAVING z zagnieŜdŜonymi zapytaniami

Podzapytania mogą występować równieŜ w klauzuli

HAVING

(przypominamy —

klauzula

HAVING

odnosi się do grup wierszy). Nie istnieje limit na liczbę poziomów

zagnieŜdŜania podzapytań.

Aby wybrać zawody, w których średnia płaca jest wyŜsza niŜ średnia płaca w zawodzie
'MANAGER', napiszemy:

SELECT JOB, AVG(SAL)
FROM EMP
HAVING AVG(SAL) > (SELECT AVG(SAL)
                   FROM EMP
                   WHERE JOB='MANAGER')
GROUP BY JOB;

JOB       AVG(SAL)
--------- ----------
ANALYST         3000
PRESIDENT       5000

Podstawy języka SQL

Strona 60

Aby wybrać stanowisko, na którym są najniŜsze średnie zarobki, napiszemy:

SELECT JOB, AVG(SAL)
FROM EMP
HAVING AVG(SAL) = (SELECT MIN(AVG(SAL))
                   FROM EMP
                   GROUP BY JOB)
GROUP BY JOB;

JOB       AVG(SAL)
--------- ----------
CLERK         1037,5

Sortowanie danych w podzapytaniu

W podzapytaniu nie moŜe występować klauzula

ORDER BY

. Klauzula

ORDER BY

moŜe wystąpić tylko raz dla całego zapytania i wtedy musi pojawić się jako ostatnia.

Zapytania skorelowane

Blok skorelowany jest szczególnym przypadkiem zagnieŜdŜonego zapytania. Zwykłe
podzapytanie jest wykonywane raz, na samym początku, a do jego wyników odwołuje się
zapytanie zewnętrzne. W przypadku zapytania skorelowanego podzapytanie jest
wykonywane dla kaŜdego wiersza z zapytania zewnętrznego.

Aby znaleźć osoby, które zarabiają mniej niŜ wynosi średnia w ich zawodach, napiszemy:

SELECT ENAME, SAL, DEPTNO, AVG(SAL)
FROM EMP E
WHERE SAL < (SELECT AVG(SAL)
             FROM EMP
             WHERE JOB=E.JOB)
ORDER BY JOB;

ENAME      SAL        DEPTNO
---------- ---------- ----------
JAMES             950         30
SMITH             800         20
CLARK            2450         10
MARTIN           1250         30
WARD             1250         30

Operator EXIST

W przypadku zapytań skorelowanych czasami interesuje nas tylko, czy wiersz spełniający
podane warunki istnieje. Wtedy stosujemy operator EXIST

EXIST

Podstawy języka SQL

Strona 61

Aby za pomocą operatora EXIST

EXIST

znaleźć pracowników, którzy mają podwładnych,

napiszemy:

SELECT ENAME, SAL, DEPTNO
FROM EMP E
WHERE EXISTS (SELECT EMPNO
              FROM EMP
              WHERE EMP.MGR=E.EMPNO)
ORDER BY DEPTNO;

ENAME      SAL        DEPTNO
---------- ---------- ----------
KING             5000         10
CLARK            2450         10
JONES            2975         20
SCOTT            3000         20
FORD             3000         20
BLAKE            2850         30

Aby znaleźć departament, w którym nikt nie pracuje:

SELECT DNAME, DEPTNO
FROM DEPT D
WHERE NOT EXISTS (SELECT 1
                  FROM EMP E
                  WHERE E.DEPTNO=D.DEPTNO)
ORDER BY DEPTNO;

DNAME          DEPTNO
-------------- ----------
OPERATIONS             40

ZauwaŜmy, Ŝe zapytanie wewnętrzne nie musi zwracać Ŝadnej wartości z tabeli, istotne
jest, czy w ogóle zostanie zwrócony wiersz, a nie jego zawartość.

Podstawy języka SQL

Strona 62

wiczenia

1. Znajdź pracowników zarabiających maksymalna pensję na ich stanowiskach pracy.
Uporządkuj ich według malejących zarobków.

JOB       ENAME            SAL
--------- ---------- ---------
PRESIDENT KING            5000
ANALYST   FORD            3000
ANALYST   SCOTT           3000
MANAGER   JONES           2975
SALESMAN  ALLEN           1600
CLERK     MILLER          1300

2. Znajdź pracowników zarabiających minimalną pensję na ich stanowiskach pracy.
Uporządkuj ich według wzrastających pensji.

JOB       ENAME            SAL
--------- ---------- ---------
CLERK     SMITH            800
SALESMAN  MARTIN          1250
SALESMAN  WARD            1250
MANAGER   CLARK           2450
ANALYST   FORD            3000
ANALYST   SCOTT           3000
PRESIDENT KING            5000

3 WskaŜ dla kaŜdego departamentu ostatnio zatrudnionych pracowników. Uporządkuj
według dat zatrudnienia.

   DEPTNO ENAME      HIREDATE
--------- ---------- ---------
       30 JAMES      03-DEC-81
       10 MILLER     23-JAN-82
       20 ADAMS      12-JAN-83

4. Podaj następujące dane o pracownikach, których zarobki przekraczają średnią ich
departamentów. Uporządkuj według numerów departamentów.

ENAME         SALARY    DEPTNO
---------- --------- ---------
KING            5000        10
JONES           2975        20
FORD            3000        20
SCOTT           3000        20
BLAKE           2850        30
ALLEN           1600        30

5. Stosując podzapytanie, znajdź departamenty, w których nikt nie pracuje.

   DEPTNO DNAME
--------- --------------
       40 OPERATIONS

Podstawy języka SQL

Strona 63

Język definiowania danych

Podstawy języka SQL

Strona 64

Struktury danych ORACLE

•

Nowe tabele mogą być tworzone takŜe podczas pracy uŜytkowników z bazą danych.

•

Pamięć dla tabel jest przydzielana w miarę potrzeb. Pamięć dla bazy danych jako
całości przydzielana jest z góry, ale moŜliwe jest rozszerzanie jej rozmiarów przy
zastosowaniu odpowiednich opcji.

•

Struktury danych mogą być modyfikowane w trakcie pracy systemu (chyba, Ŝe
akurat na nich dokonywane są zmiany przez uŜytkowników).

•

UŜytkownicy mogą zakładać własne, prywatne struktury.

Tworzenie tabel

Nazwa tabeli musi być zgodna regułami nazewnictwa obiektów bazy danych Oracle:

•

nazwa obiektu musi zaczynać się od litery;

•

nazwa moŜe zawierać litery, cyfry oraz znak podkreślenia. MoŜliwe, ale nie
zalecanie jest uŜywanie znaków $ i #;

•

małe i duŜe litery są równowaŜne;

•

długość nazwy nie moŜe przekraczać 30 znaków;

•

w obrębie bazy danych nie mogą istnieć dwa obiekty o tej samej nazwie — nazwa
musi być unikalna;

•

nazwa nie moŜe być nazwą zastrzeŜoną dla języka;

•

jeśli nazwa nie spełnia tych zaleceń to musi być otoczona podwójnymi
cudzysłowami. Wtedy rozróŜniane są małe i wielkie litery.

Definiując tabelę musimy podać listę kolumn opisywaną przez nazwę kolumny, jej typ
i czasami długość przechowywanej wartości.

Typy kolumn

KaŜda kolumna musi mieć określony typ danych:

CHAR (n)

ciąg dowolnych znaków stałej długości, parametr n wskazuje
maksymalną długość ciągu

VARCHAR2 (n)

VARCHAR (n)

ciąg znaków zmiennej długości, parametr n wskazuje maksymalną
długość ciągu

NUMBER (p,s)

liczba o precyzji p i skali s, precyzja (liczba cyfr znaczących) moŜe
przyjmować wartości od 1 do 38, skala (liczba cyfr po przecinku)
wartości od -84 do 127

DATE

daty w przedziale między 1 stycznia 4712 roku a 31 grudnia 4712
naszej ery, w dacie przechowywany jest tez składnik czasu

Podstawy języka SQL

Strona 65

LONG

ciąg znaków zmiennej długości o maksymalnym zakresie 2 GB,
w tabeli moŜe być tylko jedna kolumna typu LONG

RAW (n)

ciąg bajtów o długości do n bajtów, maksymalne n=2000

LONG RAW

ciąg bajtów o maksymalnej długości do 2 GB, w tabeli moŜe być tylko
jedna kolumna typu LONG RAW

ROWID

do przechowywania adresów fizycznych wierszy

Polecenie tworzenia tabel

Do budowania tabel słuŜy polecenie CREATE TABLE:

CREATE TABLE

nazwa_tablicy

(nazwa_kolumny typ (rozmiar),

nazwa_kolumny typ (rozmiar),

…);

Przykład

Tworzenie tabeli DEPT:

CREATE TABLE DEPT
(DEPTNO     NUMBER(2),
DNAME      VARCHAR2(12),
LOC        VARCHAR2(12));

Warunki integralności

Podczas definiowania tabeli mamy moŜliwość określić, jakie warunki powinny spełniać
dane w wierszach wprowadzanych do tablicy. Warunki takie nazywa się warunkami
integralności (constraints).

MoŜemy zaŜądać, aby wypełnienie wartości w danej kolumnie było obowiązkowe, aby
wartości pochodziły z określonego zakresu, aby były unikalne itd.

Opcje NULL i NOT NULL

Podczas definiowania kolumn tabeli moŜemy zaŜądać, aby wiersze tej tabeli w polach tej
kolumny nie dopuszczały wartości nieokreślonych.

CREATE TABLE

nazwa_tablicy

(nazwa_kolumny typ (rozmiar) [

NULL

NOT

NULL

nazwa_kolumny typ (rozmiar) [

NULL

NOT

NULL

…);

Podstawy języka SQL

Strona 66

Opcja NULL

NULL

(domyślna) oznacza, Ŝe pola tej kolumny mogą przyjmować wartość NULL.

Opcja NOT

NOT

NULL

oznacza, Ŝe pola tej kolumny muszą mieć określoną wartość, nie mogą

przyjmować wartości NULL.

Przykład

CREATE TABLE DEPT (
DEPTNO NUMBER NOT NULL,
DNAME VARCHAR2(12));

Polecenie DESCRIBE

Aby zobaczyć, jakie tabele ma kolumny i jakie są na nie nałoŜone warunki, napiszemy:

DESC

[

DESCRIBE

] nazwa_tabeli;

Przykład

DESC DEPT;

Nazwa kolumny Warto

ść

Typ

------------------------------     --------          ----
DEPTNO                              NOT NULL      NUMBER(2)
DNAME                                              CHAR(14)
LOC                                                CHAR(13)

Klauzula CONSTRAINT

Do definiowania innych niŜ NOT

NOT

NULL

warunków integralności słuŜy klauzula

CONSTRAINT

. Warunki mogą być wpisanie bezpośrednio przy definicji kolumny lub na

końcu po zdefiniowaniu wszystkich kolumn.

Warunek umieszczony przy definicji kolumny:

CREATE

TABLE

nazwa_tablicy

(...

nazwa_kolumny typ (rozmiar)

CONSTRAINT

nazwa_warunku typ_warunku [warunek],

…);

Podstawy języka SQL

Strona 67

Warunek umieszczony po definicjach wszystkich kolumn:

CREATE

TABLE

nazwa_tablicy

(...

nazwa_kolumny typ (rozmiar),

…

CONSTRAINT

nazwa_warunku typ_warunku warunek,

CONSTR

CONSTRAINT

AINT

nazwa_warunku typ_warunku warunek,

…);

•

nazwa

warunku —

jest identyfikatorem warunku integralności, nie jest wymagane

jego podanie, ale wtedy system nada warunkowi własny, zazwyczaj nieczytelny
identyfikator. Identyfikator jest potrzebny przy komendach włączających
i wyłączających warunki integralności.

•

typ_warunku —

jeden z następujących: CHECK

CHECK

, PRIMARY

PRIMARY

KEY

, UNIQUE

UNIQUE

FOREIGN

KEY

•

warunek

— dodatkowe informacje w zaleŜności od typu warunku, w przypadku

umieszczenia klauzuli

CONSTRAINT

po definicjach kolumn warunek musi być

zawsze określony.

Warunek CHECK

Określa warunek, jaki musi spełniać wartość w kolumnie kaŜdego wstawianego wiersza,
warunek nie moŜe się odwoływać się do innych tabel.

CONSTRAINT

CHECK

(warunek logiczny);

Warunek logiczny musi być prosty, nie wolno stosować podzapytań. Nie wolno teŜ
uŜywać funkcji, których wartość zaleŜy od okoliczności wywołania, np. SYSDATE czy
USER.

Przykład

CREATE TABLE emp
(...
ENAME VARCHAR2(10) CONSTRAINT upper_name_ch CHECK (ENAME=UPPER(ENAME)),
...);

Warunek PRIMARY KEY

Definiuje klucz główny tabeli. Jeśli kluczem głównym jest jedna kolumna, wygodniej
warunek zapisać po definicji tej kolumny. W przypadku klucza głównego opartego na
kilku kolumnach wygodniej zdefiniować go po definicji wszystkich kolumn.

Podstawy języka SQL

Strona 68

Definicja warunku przy definicji kolumny:

kolumna typ rozmiar

CONSTRAINT

nazwa_warunku

PRIMARY

KEY

KEY;

Definicja warunku po definicji wszystkich kolumn:

CONSTRAINT

nazwa_warunku

PRIMARY

KEY

(kolumna_1, kolumna_2, …);

W tabeli moŜe być tylko jeden klucz główny. Wszystkie kolumny wchodzące w skład
klucza głównego są obowiązkowe — nie musimy dodatkowo nakładać warunku NOT

NOT

NULL

. Dla kaŜdego wiersza zestaw wartości dla klucza głównego musi być unikalny —

inaczej Oracle zgłosi błąd.

Przykład

CREATE TABLE emp
( empno NUMBER(4) CONSTRAINT emp_pk PRIMARY KEY,
...);

lub

CREATE TABLE emp
( empno NUMBER(4),
...,
CONSTRAINT emp_pk PRIMARY KEY (empno),
...);

Warunek UNIQUE

Definiuje klucz unikalny tabeli. Klucz unikalny jest podobny do klucza głównego, z tym,
Ŝ

e nie wymusza automatycznie warunku NOT

NOT

NOT NULL

NULL

na swoich kolumnach.

Jeśli kluczem unikalnym jest jedna kolumna, wygodniej warunek zapisać po definicji tej
kolumny. W przypadku klucza opartego na kilku kolumnach wygodniej zdefiniować go po
definicji wszystkich kolumn.

Definicja warunku przy definicji kolumny:

kolumna typ rozmiar

CONSTRAINT

nazwa_warunku

UNIQUE

Definicja warunku po definicji wszystkich kolumn:

CONSTRAINT

nazwa_warunku

UNIQUE

(kolumna_1, kolumna_2,...)

W tabeli moŜe być wiele kluczy unikalnych. Dla kaŜdego wiersza zestaw wartości dla
klucza unikalnego musi być unikalny — inaczej Oracle zgłosi błąd. Warunek NOT

NOT

NOT NULL

NULL

moŜna do kolumn klucza unikalnego dopisać osobno.

Podstawy języka SQL

Strona 69

Przykład

CREATE TABLE dept
( dname VARCHAR2(12) CONSTRAINT dept_uk UNIQUE
...);

Warunek FOREIGN KEY

Definiuje klucz obcy, reprezentujący związek z inną tabelą. Sprawia Ŝe, wartości kolumn
z tym kluczem mogą przyjmować tylko wartości z klucza głównego lub unikalnego innej,
wskazanej tabeli.

Jeśli kluczem obcym jest jedna kolumna, wygodniej warunek zapisać po definicji tej
kolumny. W przypadku klucza opartego na kilku kolumnach wygodniej zdefiniować go po
definicji wszystkich kolumn.

Definicja warunku przy definicji kolumny:

kolumna typ rozmiar

CONSTRAINT

nazwa_warunku

REFERENCES

nazwa_tabeli lista_kolumn;

Definicja warunku po definicji wszystkich kolumn:

CONSTRAINT

nazwa_warunku

FOREIGN

KEY

(lista kolumn tabeli definiowanej)

REFERENCES

nazwa_tabeli lista_kolumn;

Aby klucz obcy mógł być zdefiniowany musi być zdefiniowana wcześniej tabela, do której
klucz ten się odwołuje, a na zestawie kolumn wskazanym przez klucz obcy musi być
zdefiniowany klucz główny lub unikalny. Jeśli odwołujemy się do klucza głównego obcej
tabeli, to nie musimy specyfikować listy kolumn tego klucza.

Własności klucza obcego

•

W tabeli z kluczem obcym nie moŜna wstawić wiersza o wartościach klucza obcego
nie mających odpowiedników w tabeli obcej.

•

W tabeli z kluczem obcym nie moŜna zmodyfikować wiersza na wartości klucza
obcego nie mających odpowiedników w tabeli obcej.

•

Z  tabeli  obcej  nie  moŜna  usunąć  wiersza  do  którego  odwołują  się  wartości  klucza
obcego  innej  tabeli.  MoŜna  zaŜądać  usuwania  wraz  z  wierszem  wszystkich  wierszy
w  tabeli  z  kluczem  obcym,  do  których  ten  wiersz  się  odwołuje.  W  tym  celu  przy
klauzuli

CONSTRAINT

definiującej klucz obcy naleŜy umieścić dyrektywę ON

DELETE

DELETE CASCADE

CASCADE

Podstawy języka SQL

Strona 70

Przykład

W poniŜszej tabeli emp są zdefiniowane dwa klucze obce:

•

deptno — z kolumną deptno tabeli dept,

•

mgr — z kolumną empno tabeli emp i Ŝądaniem usunięcia wszystkich pracowników
w przypadku usunięcia szefa.

CREATE TABLE EMP (
EMPNO     NUMBER(4) CONSTRAINT EMP_PRIMARY_KEY PRIMARY KEY,
ENAME     VARCHAR(10),
JOB       VARCHAR(9),
MGR       NUMBER(4) CONSTRAINT EMP_SELF_KEY REFERENCES EMP (EMPNO),
HIREDATE  DATE,
SAL       NUMBER(7,2),
COMM      NUMBER(7,2),
DEPTNO    NUMBER(2) NOT NULL
           CONSTRAINT EMP_FOREIGN_KEY FOREIGN KEY REFERENCES DEPT
           ON DELETE CASCADE);

Klauzula DEFAULT

Klauzula

DEFAULT

słuŜy do wskazania, jaka wartość ma być wstawiona do kolumny,

jeśli nie została określona konkretna wartość.

...

kolumna typ (rozmiar)

DEFAULT

wyra

enie

...

WyraŜenie musi być proste, nie wolno stosować podzapytań, dozwolone są funkcje
SYSDATE i USER.

Tworzenie tabeli przez zapytanie

Wynik zapytania moŜna zapamiętać jako nowa tabelę.

CREATE

TABLE

nazwa_tabeli

[(nazwa_kolumny [

NULL

NOT

NOT NULL

NULL

], …)]

AS SELECT

SELECT

zapytanie;

Lista nazw nowej tabeli moŜe być pominięta, jeśli są poprawnie wskazane nazwy kolumn
w poleceniu SELECT.

Jeśli jest podana lista kolumn nowej tabeli, ilość pozycji musi się pokrywać z ilością
pozycji występującą na liście wyboru polecenia SELECT.

Podstawy języka SQL

Strona 71

Przykład

Aby utworzyć tabelę EMP_CLERK, napiszemy:

CREATE TABLE EMP_CLERK
AS
SELECT EMPNO, ENAME, JOB, SAL
FROM EMP
WHERE JOB='CLERK';

Instrukcja przetworzona.

Opis utworzonej tabeli uzyskamy poleceniem DESC

DESC

DESC EMP_CLERK

Nazwa kolumny Warto

ść

Typ

------------------------------ -------- ----
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   VARCHAR2(10)
JOB                                     VARCHAR2(9)
SAL                                     NUMBER(7,2)

Zmiana definicji tabeli

Do zmiany definicji tabeli słuŜy polecenie ALTER TABLE. UmoŜliwia ono dodawanie
nowych kolumn, zmianę maksymalnego rozmiaru wartości oraz zarządzanie warunkami
integralności.

Dodawanie kolumn

Do dodania nowej kolumny słuŜy klauzula

ADD

ALTER

ALTER TABLE

TABLE

nazwa

ADD

nazwa_kolumny typ (rozmiar),

...);

Przykład

ALTER TABLE emp
ADD adress VARCHAR2(40);

Podstawy języka SQL

Strona 72

Zarządzanie warunkami integralności

Warunki integralności dodajemy do tabeli tak jak kolumny, z tym, Ŝe uŜywamy składni
takiej, jak przy definiowaniu warunków po definicjach kolumn.

ALTER

ALTER TABLE

TABLE

nazwa

ADD

(

CONSTRAINT

nazwa_warunku typ_warunku warunek,

...);

Sprawdzanie warunków integralności moŜna włączać i wyłączać:

Włączanie:

ALTER

ALTER TABLE

TABLE

nazwa

ENABLE

ENABLE CONSTRAINT

CONSTRAINT

nazwa_warunku;

Wyłączanie:

ALTER

ALTER TABLE

TABLE

nazwa

DISABLE

DISABLE CONSTRAINT

CONSTRAINT

nazwa_warunku;

Podczas włączania sprawdzania warunków integralności Oracle sprawdza, czy wszystkie
wiersze tabeli spełniają zadane warunki. Jeśli choć jedno sprawdzenie zakończy się
błędem, to warunek nie zostanie włączony.

Warunek integralności moŜna usunąć przy pomocy klauzuli

DROP

ALTER

ALTER TABLE

TABLE

nazwa

DROP

DROP CONSTRAINT

CONSTRAINT

nazwa_warunku;

Modyfikacja definicji kolumny

Do modyfikowania definicji kolumny słuŜy klauzula

MODIFY

ALTER

ALTER TABLE

TABLE

nazwa

MODIFY

nazwa_kolumny typ (rozmiar) [

NULL

NOT

NOT NULL

NULL

...);

Nie moŜna zmienić kolumny w której występują wartości NULL na NOT

NOT

NOT NULL

NULL

Do niepustej tabeli nie moŜna dodać kolumny o własności NOT

NOT

NOT NULL

NULL

Nie moŜna zmniejszyć rozmiaru kolumny ani zmienić jej rozmiaru, jeśli kolumna nie jest
pusta.

Podstawy języka SQL

Strona 73

Usuwanie tabel

Do usuwania tabel słuŜy polecenie DROP TABLE.

DROP

DROP TABLE

TABLE

nazwa;

Usunięcie tabeli powoduje:

•

utratę wszystkich danych w niej zawartych i wszystkich indeksów związanych
z tabelą,

•

wszystkie perspektywy i synonimy oparte na tabeli tracą sens,

•

polecenie jest automatycznie zatwierdzane (nieodwracalne),

•

jeśli istnieją tablice, których klucze obce są powiązane z usuwaną tabela to usuwanie
się nie powiedzie, chyba Ŝe dodamy na końcu klauzulę

CASCADE

CONSTRAINTS

•

tabelę usunąć moŜe właściciel tabeli lub administrator bazy.

Zmiana nazwy tabeli

Nazwę tabeli zmieniamy poleceniem RENAME TABLE:

RENAME T

RENAME TABLE

ABLE

stara_nazwa

nowa_nazwa;

Podstawy języka SQL

Strona 74

Podstawy języka SQL

Strona 75

Język manipulowania danymi

Podstawy języka SQL

Strona 76

Wstawianie wierszy

Polecenie INSERT

Polecenie INSERT słuŜy do wstawiania nowych wierszy do tabeli:

INSERT

INSERT INTO

INTO

nazwa_tabeli [(lista_kolumn)]

VALUES

(lista_warto

ci);

Przy  wstawianiu  do  wszystkich  kolumn  tabeli  nie  musimy  podawać  listy  kolumn,  ale
wtedy  musimy  wartości  wprowadzać  w  takiej  kolejności,  jaka  była  wyspecyfikowana
podczas  polecenia  CREATE  TABLE.  Z  tego  względu,  aby  uniezaleŜnić  się  od
późniejszych  modyfikacji  tabeli  bezpieczniej  jest  podawać  listę  kolumn  w  klauzuli

INSERT INTO

Przykład

INSERT INTO dept (deptno, dname, loc)
VALUES (50, 'SERVICE', 'NEW YORK');

W kaŜdym poleceniu INSERT moŜna wstawić tylko jeden wiersz.

W poleceniu INSERT dopuszczalne są funkcje SQL dotyczące pojedynczych wierszy, nie
wolno natomiast uŜywać podzapytań.

Wstawianie wierszy wybranych w podzapytaniu

MoŜemy wstawić do tabeli wiersze, które są wynikiem zapytania SELECT.

INSERT

INSERT INTO

INTO

nazwa_tabeli [lista_kolumn]

SELECT

lista_wyra

FROM

...

W ten sposób moŜemy za pomocą pojedynczego polecenia INSERT wstawić wiele
wierszy.

Przykład

Aby skopiować do tabeli MANAGER wszystkich pracowników pracujących na
stanowisku MANAGER napiszemy:

Podstawy języka SQL

Strona 77

INSERT INTO manager
SELECT (empno, ename, sal, job, hiredate)
FROM emp
WHERE job='MANAGER';

Modyfikacja wierszy

Polecenie UPDATE

Do zmiany zawartości wierszy słuŜy polecenie UPDATE.

UPDATE

nazwa_tabeli [alias]

SET

kolumna = { wyra

enie | podzapytanie }

[ , kolumna= { wyra

enie | podzapytanie } ...]

[

WHERE

warunek ];

Przykład

UPDATE

dept

SET

dname='MARKETING'

loc='DENVER'

WHERE

deptno=20;

Jeśli nie wpiszemy klauzuli

WHERE

to zostaną zmodyfikowane wszystkie

wiersze tabeli.

W poleceniu UPDATE moŜna uŜywać podzapytań, zarówno zagnieŜdŜonych jak
i skorelowanych.

Usuwanie wierszy

Polecenie DELETE

Do usuwania wierszy z tabeli słuŜy polecenie DELETE.

DELETE

[

FROM

] tabela

[

WHERE

warunek ];

Warunek w klauzuli

WHERE

określa, które wiersze będą usunięte.

Przykład

Podstawy języka SQL

Strona 78

DELETE FROM emp
WHERE job='MANAGER';

Zostaną usunięci prasownicy pracujący na stanowisku MANAGER.

Transakcje

Co to jest transakcja?

Transakcja  to  operacja  zmiany  stanu  bazy,  składającą  się  z  wielu  operacji  aktualizacji
wierszy  w  tabeli.  W  przypadku  przerwania  operacji  zmiany  bazy  w  trakcie  trwania
transakcji,  baza  powinna  wrócić  do  stanu  sprzed  transakcji.  Dopiero  po  zakończeniu
transakcji zmiany dokonane w czasie transakcji będą widoczne dla innych uŜytkowników.

Transakcja rozpoczyna się automatycznie podczas wykonywania pierwszej operacji
DML — zmiany stanu bazy i trwa do:

•

jawnego zatwierdzenia transakcji — polecenie COMMIT. Zmiany stają się
nieodwracalne i widoczne dla innych uŜytkowników;

•

jawnego polecenia wycofania transakcji — polecenie ROLLBACK. Baza danych
wraca wtedy do stanu sprzed transakcji;

•

wykonania polecenia DDL (CREATE, ALTER, DROP). KaŜde takie polecenie jest
transakcją i zatwierdza niezatwierdzoną transakcję wcześniejszą;

•

zakończenia sesji aplikacji — w zaleŜności od aplikacji transakcja jest albo
zatwierdzana albo wycofywana;

•

przerwania sesji — awaria sieci, brak zasilania — transakcje są wycofywane.

Transakcja powinna tworzyć spójną całość, naleŜy unikać zbyt długich transakcji —
powinno się je zatwierdzać po wykonaniu części stanowiącej logiczną całość. Dzięki temu
oszczędzamy zasoby systemowe i umoŜliwiamy innym uŜytkownikom korzystanie
z wprowadzonych zmian.

Polecenie COMMIT

Polecenie to słuŜy do jawnego zatwierdzania transakcji.

COMMIT

[

WORK

];

Polecenie to powoduje:

•

zakończenie transakcji,

•

zatwierdzenie zmian — stają się nieodwracalne,

•

zmiany stają się widoczne dla innych uŜytkowników,

•

usunięcie wszystkich blokad i wszystkich punktów zachowania.

Podstawy języka SQL

Strona 79

Wykonanie jakiegokolwiek polecenia DDL spowoduje taki sam efekt, jak wykonanie
polecenia COMMIT.

Polecenie ROLLBACK

SłuŜy do jawnego wycofywania transakcji.

ROLLBACK

[

WORK

];

Polecenie ROLBACK powoduje:

•

zakończenie transakcji,

•

wycofanie wszystkich zmian dokonanych w sesji od początku transakcji,

•

usunięcie wszystkich blokad i wszystkich punktów zachowania.

Punkty zachowania

Punkty zachowania stosuje się, aby podzielić transakcje na mniejsze części.

SAVEPOINT

nazwa_punktu_zachowania;

Wprowadzenie punktu zachowania pozwala wycofać transakcję do określonego punktu
zachowania, nie tracąc przy tym zmian wprowadzonych wcześniej.

Do wycofania zmian do ostatniego punktu zachowania słuŜy następujące polecenie:

ROLLBACK

[

WORK

]

[

SAVEPOINT

] nazwa_punktu_zachowania;

Polecenie to:

•

wycofuje część transakcji do podanego punktu zachowania,

•

zachowuje ten punkt zachowania, ale powoduje utartę wszystkich późniejszych,

•

zwalnia blokady załoŜone przez polecenia wydane po tym punkcie zachowania.

Niejawne wycofanie

Niejawne całkowite wycofanie transakcji ma miejsce w przypadku np. przerwania sesji
uŜytkownika, awarii komputera, braku zasilania itp.

Niejawne częściowe wycofanie transakcji ma miejsce w przypadku błędu wykonania
polecenia. Wycofywane są wtedy zmiany dokonane przez ten błędny rozkaz.

Podstawy języka SQL

Strona 80

Automatyczny COMMIT

W SQL*Plus moŜemy wymusić automatyczne zatwierdzanie transakcji po kaŜdym
poleceniu INSERT, UPDATE i DELETE.

SET

AUTO

[

COMMIT

]

Dyrektywę odwołujemy poleceniem:

SET

AUTO

[

COMMIT

]

OFF

Podstawy języka SQL

Strona 81

Perspektywy

Podstawy języka SQL

Strona 82

Perspektywy

Perspektywa (view) posiada następujące cechy:

•

jest definiowana w oparciu o tabelę bazową lub inną perspektywę,.

•

jest przechowywana w postaci definiującego ją rozkazu SELECT,

•

nie przechowuje danych — operuje danymi zgromadzonymi w tabelach bazowych.

Perspektyw uŜywamy, aby:

•

ograniczyć dostęp do wszystkich danych z tabeli,

•

ułatwić uŜytkownikom pobieranie rezultatów skomplikowanych zapytań,

•

aby zwolnić uŜytkowników od wnikania w struktury danych,

•

aby udostępnić uŜytkownikom dane bazowe sformatowane i przedstawione
w czytelny sposób.

Tworzenie perspektyw

Polecenie CREATE VIEW

Perspektywę tworzy się za pomocą polecenia CREATE VIEW:

CREATE

[

OR REPLACE

REPLACE

]

VIEW

nazwa_perspektywy

[(lista_kolumn)]

SELECT

…

Lista

kolumn

to lista nazw kolumn perspektywy, muszą one odpowiadać pozycjom

wyboru z listy SELECT.

Jeśli perspektywa o danej nazwie juŜ istnieje, to aby utworzyć na jej miejsce nową musimy
usunąć starą lub uŜyć opcji OR

OR REPLACE

REPLACE

Przykład

Aby utworzyć perspektywę zawierająca niektóre dane o pracownikach zatrudnionych na
stanowisku 'MANAGER', napiszemy:

CREATE VIEW Emp_Manager
AS
SELECT empno, ename, sal, deptno
FROM emp
WHERE job='MANAGER';

Podstawy języka SQL

Strona 83

Perspektywy uŜywa się jak zwykłej tabeli:

SELECT *
FROM Emp_Manager
ORDER BY ename;

W definicji perspektywy nie moŜe występować klauzula

ORDER BY

UŜycie perspektyw

Perspektywy mogą słuŜyć takŜe do modyfikacji danych w tabeli bazowej.

Jeśli  poprzez  perspektywę  zmodyfikujemy  dane  tabeli  bazowej,  moŜe  się  okazać,  Ŝe  po
modyfikacji  będą  one  niedostępne  poprzez  tą  perspektywę.  Aby  uniknąć  tego  moŜemy
nałoŜyć  opcję  CHECK

CHECK

CHECK OPTION

OPTION

, tak aby nie były dozwolone takie modyfikacje wierszy,

które spowodują ich wyrzucenie poza perspektywę.

Przykład

CREATE VIEW Emp_Manager
AS
SELECT empno, ename, sal, job, deptno
FROM emp
WHERE job='MANAGER'
WITH CHECK OPTION;

System nie pozwoli teraz zmienić w wierszu pola

job

MANAGER na inną wartość.

Usuwanie perspektywy

Perspektywę usuwa się poleceniem DROP VIEW:

DROP

DROP VIEW

VIEW

nazwa_perspektywy;

Perspektywa moŜe być usunięta tylko przez jej właściciela lub administratora.

Podstawy języka SQL

Strona 84

Podstawy języka SQL

Strona 85

UŜytkownicy i uprawnienia

Podstawy języka SQL

Strona 86

KaŜdy,  kto  rozpoczyna  pracę  z  Oracle  musi  być  zidentyfikowany  poprzez  podanie
identyfikatora  i  hasła,  aby  móc  wykonać  operacje,  do  których  jako  ten  uŜytkownik  jest
uprawniony.

Zarządzanie uŜytkownikami

Za zarządzanie uŜytkownikami, ich uprawnieniami i zasobami odpowiedzialny jest
administrator. MoŜe on:

•

tworzyć i usuwać uŜytkowników,

•

zmieniać hasła uŜytkowników,

•

wymusić identyfikacje uŜytkowników przez system operacyjny,

•

ograniczyć ilościowo zasoby, które moŜe zajmować uŜytkownik,

•

przydzielić domyślne miejsce w bazie, gdzie będą przechowywane obiekty
uŜytkownika,

•

zdefiniować uprawnienia uŜytkownika.

Dokładnie te zagadnienia są omawiane na kursach administracji.

Tworzenie uŜytkownika

UŜytkownika tworzy administrator poleceniem CREATE USER:

CREATE USER

ytkownik

IDENTIFIED BY

hasło;

Tak utworzony uŜytkownik istnieje juŜ w bazie, ale nie ma jeszcze Ŝadnych uprawnień,
a w szczególności nie moŜe podłączyć się do bazy.

Usuwanie uŜytkownika

Administrator usuwa uŜytkownika poleceniem DROP USER:

DROP

DROP USER

USER

ytkownik;

Jeśli uŜytkownik jest właścicielem jakiś obiektów, to usunięcie się nie powiedzie, chyba Ŝe
na końcu dodamy słowo kluczowe CASCADE

CASCADE

. Wtedy razem z uŜytkownikiem usuwane są

utworzone przez niego obiekty.

Podstawy języka SQL

Strona 87

Zmiana hasła

KaŜdy uŜytkownik moŜe zmienić swoje hasło. SłuŜy do tego polecenie ALTER USER.

ALTER

ALTER USER

USER

ytkownik

IDENTIFIED BY

hasło;

Przykład

Jeśli uŜytkownik Adams chce zmienić hasło na 'tygrys', to pisze:

ALTER USER Adams IDENTIFIED BY tygrys;

Uprawnienia w bazie Oracle

W bazie danych Oracle istnieją dwa typy uprawnień:

•

uprawnienia systemowe — prawo do wykonania określonej akcji lub wykonywania
pewnych akcji na określonym typie obiektów,

•

uprawnienia obiektowe — prawo do wykonywania określonej akcji na konkretnym
obiekcie.

Uprawnienia systemowe

Istnieje ponad 80 róŜnych uprawnień systemowych. W celu ułatwienia pracy
administratorom uprawnienia są grupowane w tzw. role. Predefiniowane role to:

CONNECT

moŜliwość podłączenia do Oracle

RESOURCE

moŜliwość tworzenia tabel, sekwencji, indeksów i innych obiektów

DBA

moŜliwość zakładania innych uŜytkowników, moŜliwość dostępu do
obiektów zastrzeŜonych prze innych uŜytkowników

Nadawanie uprawnień systemowych

Do nadawania uprawnień słuŜy polecenie GRANT:

GRANT

uprawnienie [, uprawnienie...]

ytkownik;

Polecenie GRANT nadaje nowe uprawnienia uŜytkownikowi. Uprawnienia nadawane
kolejnymi poleceniami GRANT się kumulują.

Podstawy języka SQL

Strona 88

Odbieranie uprawnień systemowych

Do odbierania uprawnień słuŜy polecenie REVOKE:

REVOKE

uprawnienie [, uprawnienie...]

FROM

ytkownik;

Polecenie

REVOKE

odbiera

wyspecyfikowane

uprawnienie

uŜytkownikowi,

pozostawiając inne bez zmian.

Dalsze przekazywanie uprawnień

Domyślnie uŜytkownik nie moŜe przekazywać nadanych mu uprawnień innemu
uŜytkownikowi. Aby mógł to zrobić, podczas nadawania mu uprawnienia administrator
musi je nadać z opcją W

WITH ADMIN OPTION

ITH ADMIN OPTION

GRANT

uprawnienie [, uprawnienie...]

ytkownik

WITH ADMIN OPTION

;

Uprawnienia obiektowe

Właścicielem obiektu jest uŜytkownik, który go tworzy. Jeśli uŜytkownik nie udostępnia
praw do swojego obiektu, to jedynie on i administrator władają tym obiektem.

Uprawnienia obiektowe definiują prawa uŜytkownika do obiektu innego uŜytkownika.

Nadawanie uprawnień obiektowych

Uprawnienia obiektowe nadajemy poleceniem:

GRANT

ANT

uprawnienie [, uprawnienie...]

obiekt

ytkownik [, u

ytkownik];

PoniŜsza tabela przedstawia najczęściej nadawane uprawnienia obiektowe.

Prawo

Obiekt

SELECT

wybieranie danych z tabeli lub perspektywy

INSERT

wstawianie wierszy do tabeli lub perspektywy

UPDATE

modyfikacje wierszy lub nieokreślonych kolumn tabeli lub perspektywy

DELETE

usuwanie wierszy z tabeli lub perspektywy

ALTER

zmiana definicji kolumn tabeli

Podstawy języka SQL

Strona 89

INDEX

indeksowanie tabeli

REFERENCES odwołanie do tabeli w obcych kluczach

ALL

wszystkie prawa

EXECUTE

prawo wykonywania procedur, funkcji i pakietów

Przykład

Aby nadać uŜytkownikowi Scott prawa wyboru do swojej tabeli emp, uŜytkownik Adams
napisze:

GRANT SELECT
ON emp
TO Scott;

Dalsze przekazywanie uprawnień

Domyślnie uŜytkownik nie moŜe przekazywać nadanych mu uprawnień obiektowych
innemu uŜytkownikowi. Aby mógł to zrobić, uprawnienie musi być nadane z opcją WITH

WITH

GRANT OPTION

GRANT

uprawnienie [, uprawnienie...]

obiekt

ytkownik

WITH GRANT

WITH GRANT OPTION

OPTION

;

Przykład

Aby nadać uŜytkownikowi Scott prawa wyboru do swojej tabeli emp z prawem
przekazywania tego uprawnienia dalej, uŜytkownik Adams napisze:

GRANT SELECT
ON emp
TO Scott
WITH GRANT OPTION;

Uprzywilejowanie typu PUBLIC

Aby przekazać prawo do obiektu wszystkim uŜytkownikom bazy, uŜywamy opcji
PUBLIC

PUBLIC

GRANT SELECT
ON emp
TO PUBLIC;

Odbieranie uprawnień obiektowych

Do odbierania uprawnień obiektowych słuŜy polecenie REVOKE:

Podstawy języka SQL

Strona 90

REVOKE

uprawnienie [, uprawnienie...]

obiekt

FROM

ytkownik;

Akcje na obiektach innego uŜytkownika

Do obiektu innego uŜytkownika odwołujemy się następująco:

nazwa_u

ytkownika.nazwa_obiektu

Przykład

Aby wybrać wszystko z tabeli emp uŜytkownika Adams, napiszemy:

SELECT *
FROM Adams.emp;

Aby ułatwić odwołania do obiektu, moŜna stworzyć dla nich synonimy.

Synonimy

Dla obiektów moŜna tworzyć nazwy zastępcze — synonimy:

CREATE SYNONYM

nazwa synonimu

FOR

[wła

ciciel.]nazwa_obiektu;

Synonimy usuwa się poleceniem DROP SYNONYM:

DROP

[

PUBLIC

]

SYNONYM

nazwa_synonimu;

Role

Rola to zestaw uprawnień, jaki moŜna przyznawać uŜytkownikom bazy. Rola moŜe
zawierać zarówno uprawnienia systemowe jak i obiektowe. Dzięki rolom ułatwione jest
administrowanie bazą. Jeśli przykładowo dla kierowników działów zdefiniowana jest rola

KIEROWNICY

, to chcąc wszystkim kierownikom nadać jakieś uprawnienie wystarczy

dodać je do roli

KIEROWNICY.

Tworzenie roli

Rolę tworzy się poleceniem CREATE ROLE:

Podstawy języka SQL

Strona 91

CREATE ROLE

rola

[

IDENTIFIED

hasło];

Klauzula

IDENTIFIED BY

słuŜy do określania hasła, przy pomocy którego uŜytkownik

identyfikuje się z rolą.

Definiowanie uprawnień roli

Uprawnieniami dla roli zarządzamy poleceniami GRANT i REVOKE, tak jak robiliśmy to
dla uŜytkownika. KaŜda zmiana uprawnień roli natychmiast skutkuje zmianą uprawnień
osób, które mają tą rolę nadaną.

Usuwanie roli

Do usunięcia roli słuŜy polecenie DROP ROLE:

DROP

DROP ROLE

ROLE

rola;

Podstawy języka SQL

Strona 92

Podstawy języka SQL

Strona 93

Indeks

ADD, 71, 72

ALL, 32, 49, 58, 59, 89

ALTER, 71, 72, 78, 87, 89

ALTER TABLE, 71, 72

ALTER USER, 87

AND, 23, 25, 26, 41, 48

AS, 70, 71, 82, 83

ASCENDING, 20

AUTO, 80

AVG, 32, 33, 34, 35, 37, 56, 59, 60

BETWEEN... AND, 22

CASCADE, 73, 86

CASCADE CONTRAINTS, 73

CHAR, 64, 66

CHECK, 67, 83

COMMIT, 78, 79, 80

CONSTRAINT, 66, 67, 68, 69, 70, 72

COUNT, 32, 33, 37

CREATE, 78

CREATE ROLE, 91

CREATE SYNONYM, 90

CREATE TABLE, 65, 66, 67, 68, 69, 70, 71, 76

CREATE USER, 86

DATE, 64, 70

DEFAULT, 70

DELETE, 69, 70, 77, 78, 80, 88

DESC, 20, 27, 66, 71

DESCENDING, 20

DESCRIBE, 66

DISABLE, 72

DISTINCT, 19, 32, 58, 59

DROP, 72, 73, 78, 83, 86, 90, 91

DROP ROLE, 91

DROP SYNONYM, 90

DROP TABLE, 73

DROP USER, 86

ENABLE, 72

EXECUTE, 89

EXIST, 60, 61

FOR, 90

FOREIGN KEY, 67, 69, 70

FROM, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24,

25, 26, 32, 33, 34, 35, 36, 40, 41, 48, 49, 50,
51, 56, 57, 58, 59, 60, 61, 71, 76, 77, 78, 82,
83, 88, 90

GRANT, 87, 88, 89, 91

HAVING, 35, 36, 58, 59, 60

IDENTIFIED BY, 86, 87, 91

IN, 22, 23, 24, 26, 57, 58

INDEX, 89

INSERT, 76, 77, 80, 88

INTERSECT, 50

INTO, 76, 77

Podstawy języka SQL

Strona 94

IS NULL, 22, 24, 26

LIKE, 22, 23, 24, 26

LONG, 65

MAX, 32, 33, 35, 37, 57, 58

MIN, 32, 33, 34, 37, 38, 60

MINUS, 50

MODIFY, 72

NOT, 24, 25, 26, 61, 65, 66, 68, 70, 71, 72

NULL, 18, 24, 25, 32, 48, 65, 66, 68, 70, 71, 72

NUMBER, 18, 64, 65, 66, 68, 70, 71

ON, 69, 70, 80, 88, 89, 90

ON DELETE CASCADE, 69, 70

OR, 25, 26, 82

ORDER BY, 20, 21, 36, 40, 41, 50, 51, 59, 60,

61, 83

PRIMARY KEY, 67, 68, 70

PUBLIC, 89, 90

RAW, 65

REFERENCES, 69, 70, 89

RENAME TABLE, 73

REPLACE, 82

REVOKE, 88, 90, 91

ROLLBACK, 78, 79

ROWID, 65

SAVEPOINT, 79

SELECT, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23,

24, 25, 26, 32, 33, 34, 35, 36, 40, 41, 48, 49,
50, 51, 56, 57, 58, 59, 60, 61, 70, 71, 76, 77,
82, 83, 88, 89, 90

SET, 77, 80

STDDEV, 32

SUM, 32

TO, 73, 79, 87, 88, 89

UNION, 49, 50, 51

UNIQUE, 67, 68, 69

UPDATE, 77, 80, 88

VALUES, 76

VARCHAR, 64, 70

VARCHAR2, 64, 65, 66, 67, 69, 71

VARIANCE, 32

VIEW, 82, 83

WHERE, 21, 22, 23, 24, 25, 26, 33, 35, 36, 40,

41, 48, 49, 50, 56, 57, 58, 59, 60, 61, 71, 77,
78, 82, 83

WITH ADMIN OPTION, 88

WITH GRANT OPTION, 89

WORK, 78, 79