Materiały szkoleniowe
Podstawy języka SQL
Podstawy języka SQL
Strona 2
Spis treści
Zawartość tabel wykorzystywanych na kursie ..................................................................... 4
Zawartość tabeli DEPT ................................................................................................................5
Zawartość tabeli EMP ..................................................................................................................5
Zawartość tabeli SALGRADE .....................................................................................................5
Budowa tabel wykorzystywanych na kursie ...............................................................................6
Relacyjny model danych ....................................................................................................... 8
Operatory relacyjne ......................................................................................................................9
Własności relacyjnej bazy danych ...............................................................................................9
Przegląd języka SQL ........................................................................................................... 11
Zasady SQL .................................................................................................................................12
Ćwiczenia .....................................................................................................................................25
Funkcje grupowe ................................................................................................................ 28
Stosowanie funkcji grupowych ..................................................................................................29
Klauzula GROUP BY .................................................................................................................30
Wybierane wyrażenia a funkcje grupowe ................................................................................31
Klauzula HAVING......................................................................................................................32
Kolejność występowania klauzul ...............................................................................................33
Ćwiczenia ....................................................................................................................................34
Wybieranie danych z wielu tabel ........................................................................................ 36
Złączenie równościowe ...............................................................................................................37
Złączenia nierównościowe ..........................................................................................................38
Ćwiczenia ....................................................................................................................................40
Inne metody łączenia tabel ................................................................................................. 43
Złączenia zewnętrzne ..................................................................................................................44
Połączenie tabeli samej ze sobą ..................................................................................................44
Operatory zbiorowe ....................................................................................................................45
Podstawy języka SQL
Strona 3
Ćwiczenia ................................................................................................................................... 48
Podzapytania ........................................................................................................................ 50
Podzapytania zagnieżdżone ....................................................................................................... 51
Podzapytania zwracające jeden wiersz .................................................................................... 51
Podzapytania zwracające wiele wierszy ................................................................................... 52
Ćwiczenia .................................................................................................................................... 57
Język definiowania danych ................................................................................................. 58
Struktury danych ORACLE ..................................................................................................... 59
Tworzenie tabel .......................................................................................................................... 59
Warunki integralności ............................................................................................................... 60
Tworzenie tabeli przez zapytanie ............................................................................................. 65
Zmiana definicji tabeli ............................................................................................................... 66
Język manipulowania danymi ............................................................................................. 69
Wstawianie wierszy .................................................................................................................... 70
Modyfikacja wierszy .................................................................................................................. 71
Usuwanie wierszy ....................................................................................................................... 71
Transakcje .................................................................................................................................. 72
Perspektywy ......................................................................................................................... 75
Perspektywy ............................................................................................................................... 76
Tworzenie perspektyw ............................................................................................................... 76
Usuwanie perspektywy .............................................................................................................. 77
Użytkownicy i uprawnienia ................................................................................................. 78
Zarządzanie użytkownikami ..................................................................................................... 79
Uprawnienia w bazie Oracle ..................................................................................................... 80
Role .............................................................................................................................................. 83
Podstawy języka SQL
Strona 4
Zawartość tabel wykorzystywanych na kursie
Podstawy języka SQL
Strona 5
Zawartość tabeli DEPT
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Zawartość tabeli 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
Zawartość tabeli SALGRADE
GRADE LOSAL HISAL
--------- --------- ---------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
Podstawy języka SQL
Strona 6
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 7
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 8
Relacyjny model danych
Podstawy języka SQL
Strona 9
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 10
Tabele w relacyjnej bazie danych mają następujące własności:
wiersze w tabeli muszą być różne,
w tabeli nie ma kolumn o tej samej nazwie,
kolejność wierszy jest nieokreślona,
kolejność kolumn jest nieokreślona,
wartości pól powinny być elementarne.
Podstawy języka SQL
Strona 11
Przegląd języka SQL
Przegląd języka SQL
Podstawy języka SQL
Strona 12
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 13
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 14
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 15
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 16
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 17
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 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 18
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) — 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 (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 19
Można sortować według kilku kolumn, wtedy po słowie kluczowym 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 20
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,
IN (lista),
LIKE,
IS NULL.
Operator BETWEEN...AND
Operator 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 21
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 22
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 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 BETWEEN
nie jest w podanym przedziale
NOT IN
nie jest w zbiorze
NOT LIKE
nie jest zgodny z maską
IS NOT NULL
nie jest NULL
Podstawy języka SQL
Strona 23
Aby wybrać wszystkie osoby, które nie zarabiają więcej niż 2000, napiszemy:
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 (i) oraz OR (lub).
Warunek (X 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 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 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 24
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, IN, LIKE, IS NULL
2. NOT
3. 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.
Podstawy języka SQL
Strona 25
Najczęściej używane polecenia:
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 26
Ć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 27
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 28
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 29
Funkcje grupowe
Podstawy języka SQL
Strona 30
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 ([DISTINCT | ALL] wyrażenie)
wartość średnia wyrażeń, NULL nie jest
uwzględniane
COUNT ([DISTINCT | 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 | ALL] wyrażenie)
maksymalna wartość wyrażenia
MIN ([DISTINCT | ALL] wyrażenie)
minimalna wartość wyrażenia
STDDEV ([DISTINCT | ALL] wyrażenie)
odchylenie standardowe wyrażeń, bez
uwzględniania wartości NULL
SUM ([DISTINCT | ALL] wyrażenie)
suma wartości wyrażeń, bez uwzględniania
wartości NULL
VARIANCE ([DISTINCT | ALL]
wyrażenie)
wariancja wyrażeń, bez uwzględniania wartości
NULL
Kwalifikator DISTINCT ogranicza działanie funkcji grupowych do różnych wartości
argumentów.
Kwalifikator 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 31
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 32
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 33
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 34
Kolejność występowania klauzul
Wymagany porządek klauzul jest następujący:
1. SELECT lista wyrażeń
2. FROM tabela
3. WHERE warunek selekcji wierszy
4. HAVING warunek selekcji grup
5. ORDER BY wyrażenia
6. ;
Podstawy języka SQL
Strona 35
Ć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 36
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 37
Wybieranie danych z wielu tabel
Podstawy języka SQL
Strona 38
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 39
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.
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 40
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 warun
ków łączących = liczba tabel —1
Podstawy języka SQL
Strona 41
Ć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 42
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 43
8. Przygotuj następujące dane o wszystkich pracownikach osiągających dochody do
$36000 (włącznie) oraz tych, którzy pracują na stanowisku „CLERK”.
ENAME JOB ANNUAL_SAL DEPTNO DNAME GRADE
---------- --------- ---------- --------- -------------- ---------
JAMES CLERK 11400 30 SALES 1
SMITH CLERK 9600 20 RESEARCH 1
ADAMS CLERK 13200 20 RESEARCH 1
MILLER CLERK 15600 10 ACCOUNTING 2
FORD ANALYST 36000 20 RESEARCH 4
SCOTT ANALYST 36000 20 RESEARCH 4
KING PRESIDENT 60000 10 ACCOUNTING 5
Podstawy języka SQL
Strona 44
Inne metody łączenia tabel
Inne metody łączenia tabel
Podstawy języka SQL
Strona 45
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 46
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 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 (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.
Podstawy języka SQL
Strona 47
Operator INTERSECT
Operator zbiorowy 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 (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
INTERSECT, potem operatory UNION i 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 48
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 49
Ć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 50
5. Znajdź tych pracowników, którzy zostali zatrudnieniu przed przyjęciem do pracy ich
kierowników.
EMPLOYEE HIREDATE MANAGER HIREDATE
---------- --------- ---------- ---------
BLAKE 01-MAY-81 KING 17-NOV-81
CLARK 09-JUN-81 KING 17-NOV-81
JONES 02-APR-81 KING 17-NOV-81
ALLEN 20-FEB-81 BLAKE 01-MAY-81
WARD 22-FEB-81 BLAKE 01-MAY-81
SMITH 17-DEC-80 FORD 03-DEC-81
6. Podaj inny sposób rozwiązania zadania 1.
DEPTNO DNAME
--------- --------------
40 OPERATIONS
Podstawy języka SQL
Strona 51
Podzapytania
Podzapytania
Podstawy języka SQL
Strona 52
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 53
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 54
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 i ANY można stosować w podzapytaniach zwracających więcej niż jeden
wiersz. Podaje się je w klauzulach WHERE i HAVING razem z operatorami
porównywania.
Operator 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 55
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
i ANY słowo kluczowe DISTINCT.
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO=10;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
CLARK 2450 10
MILLER 1300 10
Operator 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 56
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.
Podstawy języka SQL
Strona 57
Aby za pomocą operatora 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 58
Ć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 59
Język definiowania danych
Podstawy języka SQL
Strona 60
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 61
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 62
Opcja NULL (domyślna) oznacza, że pola tej kolumny mogą przyjmować wartość NULL.
Opcja 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 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 63
Warunek umieszczony po definicjach wszystkich kolumn:
CREATE TABLE nazwa_tablicy
(...
nazwa_kolumny typ (rozmiar),
…
CONSTRAINT nazwa_warunku typ_warunku warunek,
CONSTRAINT nazwa_warunku typ_warunku warunek,
…);
nazwa_warunku — jest identyfikatorem warunku integralności, nie jest
wymagane jego podanie, ale wtedy system nada w
arunkowi 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, PRIMARY KEY, 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 64
Definicja warunku przy definicji kolumny:
kolumna typ rozmiar CONSTRAINT nazwa_warunku PRIMARY 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
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 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 NULL
można do kolumn klucza unikalnego dopisać osobno.
Podstawy języka SQL
Strona 65
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 CASCADE.
Podstawy języka SQL
Strona 66
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 NULL
], …)]
AS 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 67
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 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 TABLE nazwa
ADD nazwa_kolumny typ (rozmiar),
nazwa_kolumny typ (rozmiar),
...);
Przykład
ALTER TABLE emp
ADD adress VARCHAR2(40);
Podstawy języka SQL
Strona 68
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 TABLE nazwa
ADD (CONSTRAINT nazwa_warunku typ_warunku warunek,
...);
Sprawdzanie warunków integralności można włączać i wyłączać:
Włączanie:
ALTER TABLE nazwa ENABLE CONSTRAINT nazwa_warunku;
Wyłączanie:
ALTER TABLE nazwa DISABLE 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 TABLE nazwa
DROP CONSTRAINT nazwa_warunku;
Modyfikacja definicji kolumny
Do modyfikowania definicji kolumny służy klauzula MODIFY:
ALTER TABLE nazwa
MODIFY nazwa_kolumny typ (rozmiar) [NULL | NOT NULL],
...);
Nie można zmienić kolumny w której występują wartości NULL na NOT NULL.
Do niepustej tabeli nie można dodać kolumny o własności NOT NULL.
Nie można zmniejszyć rozmiaru kolumny ani zmienić jej rozmiaru, jeśli kolumna nie jest
pusta.
Podstawy języka SQL
Strona 69
Usuwanie tabel
Do usuwania tabel służy polecenie DROP TABLE.
DROP 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 TABLE stara_nazwa TO nowa_nazwa;
Podstawy języka SQL
Strona 70
Język manipulowania danymi
Język manipulowania danymi
Podstawy języka SQL
Strona 71
Wstawianie wierszy
Polecenie INSERT
Polecenie INSERT służy do wstawiania nowych wierszy do tabeli:
INSERT 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 INTO nazwa_tabeli [lista_kolumn]
SELECT
lista_wyrażeń
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 72
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 73
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 74
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] TO [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 75
Automatyczny COMMIT
W SQL*Plus możemy wymusić automatyczne zatwierdzanie transakcji po każdym
poleceniu INSERT, UPDATE i DELETE.
SET AUTO [COMMIT] ON
Dyrektywę odwołujemy poleceniem:
SET AUTO [COMMIT] OFF
Podstawy języka SQL
Strona 76
Perspektywy
Podstawy języka SQL
Strona 77
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] VIEW nazwa_perspektywy
[(lista_kolumn)]
AS
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 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 78
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 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 z MANAGER na inną wartość.
Usuwanie perspektywy
Perspektywę usuwa się poleceniem DROP VIEW:
DROP VIEW nazwa_perspektywy;
Perspektywa może być usunięta tylko przez jej właściciela lub administratora.
Podstawy języka SQL
Strona 79
Użytkownicy i uprawnienia
Użytkownicy i uprawnienia
Podstawy języka SQL
Strona 80
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
uż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 USER
uż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. Wtedy razem z użytkownikiem usuwane są
utworzone przez niego obiekty.
Podstawy języka SQL
Strona 81
Zmiana hasła
Każdy użytkownik może zmienić swoje hasło. Służy do tego polecenie ALTER USER.
ALTER USER
uż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...] TO
użytkownik;
Polecenie GRANT nadaje nowe uprawnienia użytkownikowi. Uprawnienia nadawane
kolejnymi poleceniami GRANT się kumulują.
Podstawy języka SQL
Strona 82
Odbieranie uprawnień systemowych
Do odbierania uprawnień służy polecenie REVOKE:
REVOKE uprawnienie [, uprawnienie...] FROM
uż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ą WITH ADMIN OPTION.
GRANT uprawnienie [, uprawnienie...]
TO
uż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 uprawnienie [, uprawnienie...]
ON obiekt
TO
uż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
Podstawy języka SQL
Strona 83
ALTER
zmiana definicji kolumn tabeli
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
GRANT OPTION.
GRANT uprawnienie [, uprawnienie...]
ON obiekt
TO
użytkownik
WITH GRANT 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:
GRANT SELECT
ON emp
TO PUBLIC;
Podstawy języka SQL
Strona 84
Odbieranie uprawnień obiektowych
Do odbierania uprawnień obiektowych służy polecenie REVOKE:
REVOKE uprawnienie [, uprawnienie...]
ON obiekt
FROM
uż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.
Podstawy języka SQL
Strona 85
Tworzenie roli
Rolę tworzy się poleceniem CREATE ROLE:
CREATE ROLE rola
[IDENTIFIED BY
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 ROLE rola;
Podstawy języka SQL
Strona 86
Indeks
Indeks
A
ADD
66, 67
ALL
29, 45, 53, 54, 81
ALTER
66, 67, 72, 80, 81
ALTER TABLE
66, 67
ALTER USER
80
AND
20, 23, 24, 38, 44
AS
65, 76, 77
ASCENDING
18
AUTO
73, 74
AVG
29, 30, 31, 32, 34, 51, 54, 55
B
BETWEEN... AND
20
BETWEEN...AND
20
C
CASCADE
68, 79
CASCADE CONTRAINTS
68
CHAR
59, 61
CHECK
62, 77
COMMIT
72, 73, 74
CONSTRAINT
61, 62, 63, 64, 65, 67
CONTRAINT
64
COUNT
29, 30, 34
CREATE
72
CREATE ROLE
83
CREATE SYNONYM
83
CREATE TABLE
60, 61, 62, 63, 64, 65, 70
CREATE USER
79
D
DATE
59, 65
DEFAULT
65
DELETE
64, 65, 71, 73, 81
Podstawy języka SQL
Strona 87
DESC
18, 24, 61, 66
DESCENDING
18
DESCRIBE
61
DISABLE
67
DISTINCT
17, 29, 53, 54
DROP
67, 68, 72, 77, 79, 83, 84
DROP ROLE
84
DROP SYNONYM
83
DROP TABLE
68
DROP USER
79
E
ENABLE
67
EXECUTE
81
EXIST
55
F
FOR
83
FOREIGN KEY
62, 64, 65
FROM12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 29, 30, 31, 32, 33, 37, 38, 44, 45, 46, 47, 51, 52, 53, 54,
55, 56, 65, 70, 71, 76, 77, 81, 82, 83
G
GRANT
80, 81, 82, 84
H
HAVING
32, 33, 53, 54
I
IDENTIFIED BY
79, 80, 83
IN 20, 21, 22, 24, 52, 53
INDEX
81
INSERT
70, 73, 81
INTERSECT
45, 46
INTO
70
IS NULL
20, 22, 24
L
LIKE
20, 21, 22, 24
LONG
59, 60
M
MAX
29, 30, 32, 34, 52, 53
MIN
29, 30, 31, 34, 35, 54
MINUS
46
MODIFY
67
N
NOT
22, 23, 24, 56, 60, 61, 63, 65, 66, 67
NULL 15, 16, 22, 23, 29, 44, 60, 61, 63, 65, 66, 67
NUMBER
16, 59, 60, 61, 63, 65, 66
O
ON
64, 65, 73, 81, 82
Podstawy języka SQL
Strona 88
ON DELETE CASCADE
64, 65
OR
23, 24, 76
ORDER BY
18, 33, 37, 38, 46, 47, 54, 55, 56, 77
P
PRIMARY KEY
62, 63, 65
PUBLIC
82, 83
R
RAW
60
REFERENCES
64, 65, 81
RENAME TABLE
68
REPLACE
76
REVOKE
80, 81, 82, 84
ROLLBACK
72, 73
ROWID
60
S
SAVEPOINT
73
SELECT12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 29, 30, 31, 32, 33, 37, 38, 44, 45, 46, 47, 51, 52, 53, 54,
55, 56, 65, 70, 76, 77, 81, 82, 83
SET
71, 73, 74
STDDEV
29
SUM
29
T
TO
68, 73, 80, 81, 82
U
UNION
45, 46, 47
UNIQUE
62, 63
UPDATE
71, 73, 81
V
VALUES
70
VARCHAR
59, 65
VARCHAR2
59, 60, 61, 62, 63, 66
VARIANCE
29
VIEW
76, 77
W
WHERE19, 20, 21, 22, 23, 30, 32, 33, 37, 38, 44, 45, 46, 51, 52, 53, 54, 55, 56, 65, 71, 76, 77
WITH ADMIN OPTION
81
WITH GRANT OPTION
82
WORK
72, 73
Wyszukiwarka
Podobne podstrony:
Podstawy języka SQL, TYTAN 5
Podstawy języka SQL Kuśnierz
bd ii cw1 podstawy jezyka sql
Podstawy języka SQL
Podstawy języka SQL
więcej podobnych podstron