Kurs SQL by km22df0

VIEWS: 22 PAGES: 93

									Materiały szkoleniowe




Podstawy języka SQL
                                                              Podstawy języka SQL




                                                                   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 _____________________________________________________ 12
  Zasady SQL ________________________________________________________________13
  Ćwiczenia __________________________________________________________________27

Funkcje grupowe ________________________________________________________ 30
  Stosowanie funkcji grupowych _________________________________________________31
  Klauzula GROUP BY ________________________________________________________32
  Wybierane wyrażenia a funkcje grupowe ________________________________________33
  Klauzula HAVING___________________________________________________________34
  Kolejność występowania klauzul _______________________________________________35
  Ćwiczenia __________________________________________________________________36

Wybieranie danych z wielu tabel ____________________________________________ 38
  Złączenie równościowe _______________________________________________________39
  Złączenia nierównościowe _____________________________________________________40
  Ćwiczenia __________________________________________________________________42

Inne metody łączenia tabel ________________________________________________ 46
  Złączenia zewnętrzne _________________________________________________________47
  Połączenie tabeli samej ze sobą _________________________________________________47
  Operatory zbiorowe __________________________________________________________48


Strona 2
Podstawy języka SQL


  Ćwiczenia __________________________________________________________________ 51

Podzapytania ____________________________________________________________ 54
  Podzapytania zagnieżdżone ___________________________________________________ 55
  Podzapytania zwracające jeden wiersz __________________________________________ 55
  Podzapytania zwracające wiele wierszy _________________________________________ 56
  Ćwiczenia __________________________________________________________________ 61

Język definiowania danych ________________________________________________ 62
  Struktury danych ORACLE __________________________________________________ 63
  Tworzenie tabel _____________________________________________________________ 63
  Warunki integralności _______________________________________________________ 64
  Tworzenie tabeli przez zapytanie ______________________________________________ 69
  Zmiana definicji tabeli _______________________________________________________ 70

Język manipulowania danymi ______________________________________________74
  Wstawianie wierszy __________________________________________________________ 75
  Modyfikacja wierszy _________________________________________________________ 76
  Usuwanie wierszy ___________________________________________________________ 76
  Transakcje _________________________________________________________________ 77

Perspektywy ____________________________________________________________ 80
  Perspektywy________________________________________________________________ 81
  Tworzenie perspektyw _______________________________________________________ 81
  Usuwanie perspektywy _______________________________________________________ 82

Użytkownicy i uprawnienia ________________________________________________ 84
  Zarządzanie użytkownikami __________________________________________________ 85
  Uprawnienia w bazie Oracle __________________________________________________ 86
  Role _______________________________________________________________________ 89




                                                                             Strona 3
                                        Podstawy języka SQL




           Zawartość tabel wykorzystywanych na kursie




Strona 4
Podstawy języka SQL




                              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




                                                                              Strona 5
                                                               Podstawy języka SQL




            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




Strona 6
Podstawy języka SQL




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




                                                                Strona 7
                     Podstawy języka SQL




           Relacyjny model danych




Strona 8
Podstawy języka SQL



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.




                                                                                   Strona 9
                                                               Podstawy języka SQL


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.




Strona 10
Podstawy języka SQL




                      Strona 11
                   Podstawy języka SQL




            Przegląd języka SQL




Strona 12
Podstawy języka SQL



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


                                                                             Strona 13
                                                               Podstawy języka SQL


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


Strona 14
Podstawy języka SQL


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




                                                                            Strona 15
                                                                Podstawy języka SQL


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




Strona 16
Podstawy języka SQL


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')



                                                                             Strona 17
                                                            Podstawy języka SQL


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



Strona 18
Podstawy języka SQL


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




                                                                            Strona 19
                                                             Podstawy języka SQL


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.




Strona 20
Podstawy języka SQL


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.




                                                                              Strona 21
                                                                Podstawy języka SQL


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




Strona 22
Podstawy języka SQL


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




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

                                                                              Strona 23
                                                             Podstawy języka SQL



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.



Strona 24
Podstawy języka SQL


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.




Najczęściej używane polecenia:

                                                                            Strona 25
                                                      Podstawy języka SQL


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




Strona 26
Podstawy języka SQL




                                     Ć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




                                                                             Strona 27
                                                                 Podstawy języka SQL


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
ALLEN             19500
TURNER            18000


Strona 28
Podstawy języka SQL


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




                                                                             Strona 29
               Podstawy języka SQL




            Funkcje grupowe




Strona 30
Podstawy języka SQL



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]                  wariancja wyrażeń, bez uwzględniania wartości
wyrażenie)                                  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.




                                                                              Strona 31
                                                              Podstawy języka SQL



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.



Strona 32
Podstawy języka SQL



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.




                                                                             Strona 33
                                                              Podstawy języka SQL


                            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.




Strona 34
Podstawy języka SQL




                   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. ;




                                                    Strona 35
                                                                Podstawy języka SQL




                                       Ć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




Strona 36
Podstawy języka SQL


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




                                                                           Strona 37
                              Podstawy języka SQL




            Wybieranie danych z wielu tabel




Strona 38
Podstawy języka SQL




                            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



                                                                             Strona 39
                                                                 Podstawy języka SQL


            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


Strona 40
Podstawy języka SQL


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 łączących = liczba tabel —1




                                                             Strona 41
                                                             Podstawy języka SQL




                                      Ć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




Strona 42
Podstawy języka SQL


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




                                                                              Strona 43
                                                             Podstawy języka SQL


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




Strona 44
Podstawy języka SQL




                      Strona 45
                         Podstawy języka SQL




            Inne metody łączenia tabel




Strona 46
Podstawy języka SQL




                            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;




                                                                               Strona 47
                                                                Podstawy języka SQL


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.




Strona 48
Podstawy języka SQL


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ę.




                                                                             Strona 49
                                                        Podstawy języka SQL


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.




Strona 50
Podstawy języka SQL




                                     Ć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




                                                                               Strona 51
                                                              Podstawy języka SQL


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




Strona 52
Podstawy języka SQL




                      Strona 53
            Podstawy języka SQL




            Podzapytania




Strona 54
Podstawy języka SQL




                         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


                                                                            Strona 55
                                                             Podstawy języka SQL


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.




Strona 56
Podstawy języka SQL


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.




                                                                              Strona 57
                                                              Podstawy języka SQL


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




Strona 58
Podstawy języka SQL


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.




                                                                               Strona 59
                                                             Podstawy języka SQL


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ść.




Strona 60
Podstawy języka SQL




                                     Ć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




                                                                             Strona 61
                        Podstawy języka SQL




            Język definiowania danych




Strona 62
Podstawy języka SQL




                       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)      ciąg znaków zmiennej długości, parametr n wskazuje maksymalną
VARCHAR (n)       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


                                                                              Strona 63
                                                               Podstawy języka SQL


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],
      …);



Strona 64
Podstawy języka SQL


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],
     …);




                                                                               Strona 65
                                                             Podstawy języka SQL


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 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, 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.




Strona 66
Podstawy języka SQL


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.




                                                                              Strona 67
                                                                Podstawy języka SQL


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.




Strona 68
Podstawy języka SQL


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.




                                                                             Strona 69
                                                          Podstawy języka SQL


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);




Strona 70
Podstawy języka SQL




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.




                                                                             Strona 71
                                                                Podstawy języka SQL


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;




Strona 72
Podstawy języka SQL




                      Strona 73
                          Podstawy języka SQL




            Język manipulowania danymi




Strona 74
Podstawy języka SQL




                           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:




                                                                          Strona 75
                                                               Podstawy języka SQL


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


Strona 76
Podstawy języka SQL



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.


                                                                              Strona 77
                                                                  Podstawy języka SQL


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.




Strona 78
Podstawy języka SQL


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




                                                                  Strona 79
            Podstawy języka SQL




            Perspektywy




Strona 80
Podstawy języka SQL




                                   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';




                                                                                Strona 81
                                                                  Podstawy języka SQL


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.




Strona 82
Podstawy języka SQL




                      Strona 83
                         Podstawy języka SQL




            Użytkownicy i uprawnienia




Strona 84
Podstawy języka SQL



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.




                                                                                  Strona 85
                                                                   Podstawy języka SQL




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ą.




Strona 86
Podstawy języka SQL


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



                                                                             Strona 87
                                                             Podstawy języka SQL


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;




Strona 88
Podstawy języka SQL


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.




                                                                             Strona 89
                                                            Podstawy języka SQL


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;




Strona 90
Podstawy języka SQL




                      Strona 91
                                                                   Podstawy języka SQL




                                                                                  Indeks



A                                              DESCRIBE, 66
ADD, 71, 72                                    DISABLE, 72
ALL, 32, 49, 58, 59, 89                        DISTINCT, 19, 32, 58, 59
ALTER, 71, 72, 78, 87, 89                      DROP, 72, 73, 78, 83, 86, 90, 91
ALTER TABLE, 71, 72                            DROP ROLE, 91
ALTER USER, 87                                 DROP SYNONYM, 90
AND, 23, 25, 26, 41, 48                        DROP TABLE, 73
AS, 70, 71, 82, 83                             DROP USER, 86
ASCENDING, 20
AUTO, 80                                       E
AVG, 32, 33, 34, 35, 37, 56, 59, 60            ENABLE, 72
                                               EXECUTE, 89
B                                              EXIST, 60, 61
BETWEEN... AND, 22
                                               F
C                                              FOR, 90
CASCADE, 73, 86                                FOREIGN KEY, 67, 69, 70
CASCADE CONTRAINTS, 73                         FROM, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24,
CHAR, 64, 66                                     25, 26, 32, 33, 34, 35, 36, 40, 41, 48, 49, 50,
                                                 51, 56, 57, 58, 59, 60, 61, 71, 76, 77, 78, 82,
CHECK, 67, 83                                    83, 88, 90
COMMIT, 78, 79, 80
CONSTRAINT, 66, 67, 68, 69, 70, 72             G
COUNT, 32, 33, 37
                                               GRANT, 87, 88, 89, 91
CREATE, 78
CREATE ROLE, 91
CREATE SYNONYM, 90
                                               H
CREATE TABLE, 65, 66, 67, 68, 69, 70, 71, 76   HAVING, 35, 36, 58, 59, 60
CREATE USER, 86
                                               I
D                                              IDENTIFIED BY, 86, 87, 91
DATE, 64, 70                                   IN, 22, 23, 24, 26, 57, 58
DEFAULT, 70                                    INDEX, 89
DELETE, 69, 70, 77, 78, 80, 88                 INSERT, 76, 77, 80, 88
DESC, 20, 27, 66, 71                           INTERSECT, 50
DESCENDING, 20                                 INTO, 76, 77



Strona 92
Podstawy języka SQL


IS NULL, 22, 24, 26                                ROLLBACK, 78, 79
                                                   ROWID, 65
L
LIKE, 22, 23, 24, 26                               S
LONG, 65                                           SAVEPOINT, 79
                                                   SELECT, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23,
M                                                    24, 25, 26, 32, 33, 34, 35, 36, 40, 41, 48, 49,
                                                     50, 51, 56, 57, 58, 59, 60, 61, 70, 71, 76, 77,
MAX, 32, 33, 35, 37, 57, 58                          82, 83, 88, 89, 90
MIN, 32, 33, 34, 37, 38, 60                        SET, 77, 80
MINUS, 50                                          STDDEV, 32
MODIFY, 72                                         SUM, 32

N                                                  T
NOT, 24, 25, 26, 61, 65, 66, 68, 70, 71, 72        TO, 73, 79, 87, 88, 89
NULL, 18, 24, 25, 32, 48, 65, 66, 68, 70, 71, 72
NUMBER, 18, 64, 65, 66, 68, 70, 71                 U
                                                   UNION, 49, 50, 51
O                                                  UNIQUE, 67, 68, 69
ON, 69, 70, 80, 88, 89, 90                         UPDATE, 77, 80, 88
ON DELETE CASCADE, 69, 70
OR, 25, 26, 82                                     V
ORDER BY, 20, 21, 36, 40, 41, 50, 51, 59, 60,
                                                   VALUES, 76
  61, 83
                                                   VARCHAR, 64, 70
                                                   VARCHAR2, 64, 65, 66, 67, 69, 71
P
                                                   VARIANCE, 32
PRIMARY KEY, 67, 68, 70                            VIEW, 82, 83
PUBLIC, 89, 90
                                                   W
R
                                                   WHERE, 21, 22, 23, 24, 25, 26, 33, 35, 36, 40,
RAW, 65                                              41, 48, 49, 50, 56, 57, 58, 59, 60, 61, 71, 77,
REFERENCES, 69, 70, 89                               78, 82, 83
RENAME TABLE, 73                                   WITH ADMIN OPTION, 88
REPLACE, 82                                        WITH GRANT OPTION, 89
REVOKE, 88, 90, 91                                 WORK, 78, 79




                                                                                          Strona 93

								
To top