Zawartość tabeli SALGRADE

Materiały szkoleniowe

Podstawy języka SQL


Spis treści

Zawartość tabel wykorzystywanych na kursie____________________________________5

Zawartość tabeli DEPT_________________________________________________________6

Zawartość tabeli EMP__________________________________________________________6

Zawartość tabeli SALGRADE___________________________________________________6

Budowa tabel wykorzystywanych na kursie________________________________________7

Relacyjny model danych_____________________________________________________9

Operatory relacyjne___________________________________________________________10

Własności relacyjnej bazy danych_______________________________________________10

Przegląd języka SQL_______________________________________________________13

Zasady SQL_________________________________________________________________14

Ćwiczenia___________________________________________________________________28

Funkcje grupowe_________________________________________________________31

Stosowanie funkcji grupowych__________________________________________________32

Klauzula GROUP BY_________________________________________________________33

Wybierane wyrażenia a funkcje grupowe_________________________________________34

Klauzula HAVING____________________________________________________________35

Kolejność występowania klauzul________________________________________________36

Ćwiczenia___________________________________________________________________37

Wybieranie danych z wielu tabel_____________________________________________39

Złączenie równościowe_________________________________________________________40

Złączenia nierównościowe______________________________________________________41

Ćwiczenia___________________________________________________________________43

Inne metody łączenia tabel__________________________________________________47

Złączenia zewnętrzne__________________________________________________________48

Połączenie tabeli samej ze sobą__________________________________________________48

Operatory zbiorowe___________________________________________________________49

Strona 2


Ćwiczenia___________________________________________________________________52

Podzapytania_____________________________________________________________55

Podzapytania zagnieżdżone_____________________________________________________56

Podzapytania zwracające jeden wiersz___________________________________________56

Podzapytania zwracające wiele wierszy___________________________________________57

Ćwiczenia___________________________________________________________________62

Język definiowania danych_________________________________________________63

Struktury danych ORACLE____________________________________________________64

Tworzenie tabel_______________________________________________________________64

Warunki integralności_________________________________________________________65

Tworzenie tabeli przez zapytanie________________________________________________70

Zmiana definicji tabeli_________________________________________________________71

Język manipulowania danymi_______________________________________________75

Wstawianie wierszy___________________________________________________________76

Modyfikacja wierszy__________________________________________________________77

Usuwanie wierszy_____________________________________________________________77

Transakcje___________________________________________________________________78

Perspektywy______________________________________________________________81

Perspektywy_________________________________________________________________82

Tworzenie perspektyw_________________________________________________________82

Usuwanie perspektywy________________________________________________________83

Użytkownicy i uprawnienia_________________________________________________87

Zarządzanie użytkownikami____________________________________________________88

Uprawnienia w bazie Oracle____________________________________________________89

Role________________________________________________________________________91

Strona 3


Zawartość tabel wykorzystywanych na kursie

Strona 4


Zawartość tabeli DEPTDEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON

Zawartość tabeli EMPEMPNO 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 SALGRADEGRADE LOSAL HISAL--------- --------- --------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 3000 5 3001 9999

Strona 5


Budowa tabel wykorzystywanych na kursiePodczas 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 OpisDEPTNO Departament number — unikalny numer departamentuDNAME Nazwa departamentu — przechowywana w zapisie dużymi literamiLOC Lokalizacja departamentu (miasto w którym znajduje się departament)

Tabela EMP — wykaz wszystkich pracowników

Kolumna OpisEMPNO Employee number — unikalny numer pracownikaENAME Nazwisko pracownika — przechowywane w zapisie dużymi literamiJOB Etat, stanowisko pracyMGR Identyfikator szefa (czyli EMPNO we wierszu szefa)HIREDATE Data zatrudnieniaSAL PensjaCOMM Prowizja naliczona od początku roku, dotyczy pracowników

zatrudnionych na stanowisku SALESMANDEPTNO Numer departamentu w którym zatrudniony jest pracownik. Wartość

w tym polu musi odpowiadać jednemu i tylko jednemu wierszowi w tabeli DEPT

Strona 6


Tabela SALGRADE — tabela „widełek” zaszeregowania

Kolumna OpisGRADE Numer grupy zaszeregowaniaLOSAL LOW SALARY — dolna granica widełek płacowych dla stawki

zaszeregowania GRADEHISAL HIGH SALARY — górna granica widełek

Strona 7


Relacyjny model danych

Strona 8


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 relacyjneWyróż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 relacjiZŁĄCZENIE połączenie dwóch relacji poprzez pewne kryterium łączace

niektóre wiersze z obu relacjiSUMA ZBIOROWA wszystkie wiersze z obu relacjiCZĘŚĆ WSPÓLNA wiersze wspólne dla obu relacjiRÓŻNICA ZBIOROWA wiersze, które występują w jednej, a nie występują w drugiej

relacji

Własności relacyjnej bazy danychRelacyjna 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


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


Przegląd języka SQL

Strona 11


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, JOBFROM EMP;

ENAME JOB ---------- ---------KING PRESIDENTBLAKE MANAGER CLARK MANAGER JONES MANAGER MARTIN SALESMAN ALLEN SALESMAN TURNER SALESMAN

Strona 12


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 107698 BLAKE MANAGER 7839 81/05/01 2850 307782 CLARK MANAGER 7839 81/06/09 2450 107566 JONES MANAGER 7839 81/04/02 2975 207654 MARTIN SALESMAN 7698 81/09/28 1250 1400 307499 ALLEN SALESMAN 7698 81/02/20 1600 300 307844 TURNER SALESMAN 7698 81/09/08 1500 0 307900 JAMES CLERK 7698 81/12/03 950 307521 WARD SALESMAN 7698 81/02/22 1250 500 307902 FORD ANALYST 7566 81/12/03 3000 207369 SMITH CLERK 7902 80/12/17 800 207788 SCOTT ANALYST 7566 82/12/09 3000 207876 ADAMS CLERK 7788 83/01/12 1100 207934 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*12FROM EMP;

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

Strona 13


JONES 35700MARTIN 15000ALLEN 19200TURNER 18000JAMES 11400WARD 15000FORD 36000SMITH 9600SCOTT 36000ADAMS 13200MILLER 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 PROWIZJAFROM EMP;

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

Strona 14


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 PRACOWNIKFROM 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 DEPARTAMENTFROM 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 15


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+COMMFROM EMP;

ENAME SAL*12+COM---------- ----------KING BLAKE CLARK JONES MARTIN 16400ALLEN 19500TURNER 18000JAMES WARD 15500FORD 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_WYNFROM EMP;

ENAME ROCZNE_WYN---------- ----------KING 60000BLAKE 34200CLARK 29400JONES 35700MARTIN 16400ALLEN 19500TURNER 18000JAMES 11400WARD 15500FORD 36000SMITH 9600SCOTT 36000ADAMS 13200MILLER 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 16


Eliminacja duplikatów

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

SELECT DEPTNOFROM 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 DEPTNOFROM 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, DEPTNOFROM EMP;

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

Strona 17


MANAGER 20MANAGER 30PRESIDENT 10SALESMAN 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, JOBFROM EMPORDER BY ENAME; ENAME JOB ---------- ---------ADAMS CLERK ALLEN SALESMAN BLAKE MANAGER CLARK MANAGER FORD ANALYST JAMES CLERK JONES MANAGER KING PRESIDENTMARTIN 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, HIREDATEFROM EMPORDER 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 18


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, DEPTNOFROM EMPORDER BY DEPTNO, ENAME;

ENAME JOB DEPTNO ---------- --------- ----------CLARK MANAGER 10KING PRESIDENT 10MILLER CLERK 10ADAMS CLERK 20FORD ANALYST 20JONES MANAGER 20SCOTT ANALYST 20SMITH CLERK 20ALLEN SALESMAN 30BLAKE MANAGER 30JAMES CLERK 30MARTIN SALESMAN 30TURNER SALESMAN 30WARD 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 19


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

SELECT ENAME, JOB, DEPTNOFROM EMPWHERE JOB='CLERK'; ENAME JOB DEPTNO ---------- --------- ----------JAMES CLERK 30SMITH CLERK 20ADAMS CLERK 20MILLER CLERK 10

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

SELECT ENAME, JOB, DEPTNOFROM EMPWHERE HIREDATE>'82/01/01' ENAME JOB DEPTNO ---------- --------- ----------SCOTT ANALYST 20ADAMS CLERK 20MILLER 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, DEPTNOFROM EMPWHERE 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 20


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, DEPTNOFROM EMPWHERE SAL BETWEEN 1000 AND 1500;

ENAME JOB DEPTNO ---------- --------- ----------MARTIN SALESMAN 30TURNER SALESMAN 30WARD SALESMAN 30ADAMS CLERK 20MILLER 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, DEPTNOFROM EMPWHERE MGR IN (7839,7902); ENAME JOB DEPTNO ---------- --------- ----------BLAKE MANAGER 30CLARK MANAGER 10JONES MANAGER 20SMITH 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, DEPTNOFROM EMPWHERE ENAME LIKE '_A%'; ENAME JOB DEPTNO ---------- --------- ----------MARTIN SALESMAN 30JAMES CLERK 30WARD SALESMAN 30

Strona 21


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

SELECT ENAME, JOB, DEPTNOFROM EMPWHERE ENAME LIKE '_____'; ENAME JOB DEPTNO ---------- --------- ----------BLAKE MANAGER 30CLARK MANAGER 10JONES MANAGER 20ALLEN SALESMAN 30JAMES CLERK 30SMITH CLERK 20SCOTT ANALYST 20ADAMS 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, DEPTNOFROM EMPWHERE 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ównyNOT kolumna> nie jest większyNOT BETWEEN nie jest w podanym przedzialeNOT IN nie jest w zbiorzeNOT 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 22


SELECT ENAME, SALFROM EMPWHERE NOT SAL>2000;

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

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

SELECT ENAME, JOB, COMMFROM EMPWHERE COMM IS NOT NULL

ENAME JOB COMM ---------- --------- ----------MARTIN SALESMAN 1400ALLEN SALESMAN 300TURNER SALESMAN 0WARD 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, SALFROM EMPWHERE SAL>2000AND JOB='MANAGER'

NAME JOB SAL ---------- --------- ----------BLAKE MANAGER 2850CLARK MANAGER 2450JONES 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 23


SELECT ENAME, JOB, SALFROM EMPWHERE SAL>1000AND JOB='MANAGER' OR JOB='CLERK';

ENAME JOB SAL ---------- --------- ----------BLAKE MANAGER 2850CLARK MANAGER 2450JONES MANAGER 2975JAMES CLERK 950SMITH CLERK 800ADAMS CLERK 1100MILLER 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, SALFROM EMPWHERE SAL>1000AND (JOB='MANAGER' OR JOB='CLERK');

ENAME JOB SAL ---------- --------- ----------BLAKE MANAGER 2850CLARK MANAGER 2450JONES MANAGER 2975ADAMS CLERK 1100MILLER 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 NULL2. NOT3. AND4. ORAby 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 24


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

@plik uruchomienie skryptu pliked plik edycja pliku plik w domyślnym edytorzeSPOOL OFF kończy wysyłanie danych do plikuDESC wyświetla opis struktury tabeliCONNECT zmiana podłączenia do bazyEXIT wyjście z programu

Strona 25


Ćwiczenia1.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 1250ALLEN 30 1600TURNER 30 1500WARD 30 1250ADAMS 20 1100MILLER 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 26


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

JOB---------ANALYSTCLERKMANAGERPRESIDENTSALESMAN

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 CLERKADAMS CLERK

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

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

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

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

Strona 27


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

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

ENAME DEPTNO HIREDATE---------- --------- ---------SCOTT 20 09-DEC-82MILLER 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 300TURNER 18000 0WARD 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-81BLAKE pracuje na stanowisku MANAGER w zespole 30 od 01-MAY-81CLARK pracuje na stanowisku MANAGER w zespole 10 od 09-JUN-81JONES pracuje na stanowisku MANAGER w zespole 20 od 02-APR-81MARTIN pracuje na stanowisku SALESMAN w zespole 30 od 28-SEP-81ALLEN pracuje na stanowisku SALESMAN w zespole 30 od 20-FEB-81TURNER pracuje na stanowisku SALESMAN w zespole 30 od 08-SEP-81JAMES pracuje na stanowisku CLERK w zespole 30 od 03-DEC-81WARD pracuje na stanowisku SALESMAN w zespole 30 od 22-FEB-81FORD pracuje na stanowisku ANALYST w zespole 20 od 03-DEC-81SMITH pracuje na stanowisku CLERK w zespole 20 od 17-DEC-80SCOTT pracuje na stanowisku ANALYST w zespole 20 od 09-DEC-82ADAMS pracuje na stanowisku CLERK w zespole 20 od 12-JAN-83MILLER pracuje na stanowisku CLERK w zespole 10 od 23-JAN-82

Strona 28


Funkcje grupowe

Strona 29


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 funkcjiAVG ([DISTINCT | ALL] wyrażenie) wartość średnia wyrażeń, NULL nie jest

uwzględnianeCOUNT ([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żeniaMIN ([DISTINCT | ALL] wyrażenie) minimalna wartość wyrażeniaSTDDEV ([DISTINCT | ALL] wyrażenie) odchylenie standardowe wyrażeń, bez

uwzględniania wartości NULLSUM ([DISTINCT | ALL] wyrażenie) suma wartości wyrażeń, bez uwzględniania

wartości NULLVARIANCE ([DISTINCT | ALL] wyrażenie)

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

Kwalifikator DISTINCT ogranicza działanie funkcji grupowych do różnych wartości argumentów. Kwalifikator ALL jest domyślny — funkcje grupowe nie eliminują duplikatów.Argumentami funkcji grupowych są liczby, a w przypadku funkcji MAX, MIN i COUNT także daty, znaki i ciągi znaków.Wszystkie funkcje grupowe, za wyjątkiem COUNT(*) ignorują wartości NULL.

Stosowanie funkcji grupowychAby 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 30


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 EMPWHERE JOB='MANAGER';

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

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

SELECT COUNT(*)FROM EMPWHERE DEPTNO=10;

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

Klauzula GROUP BYDo 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 EMPGROUP 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 EMPWHERE JOB<>'CLERK'GROUP BY JOB;

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

W klauzuli GROUP BY można podać kilka wyrażeń, wtedy wiersze będą grupowane w mniejszych grupach.

Strona 31


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

SELECT DEPTNO, JOB, MIN(SAL)FROM EMPGROUP 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 grupowePoniż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 EMPGROUP BY DEPTNO;

AVG(SAL) ----------2916,66667 21751566,66667

Aby poprawić to polecenie, umieśćmy na liście wyboru klauzuli SELECT także numer departamentu:

SELECT DEPTNO, AVG(SAL)FROM EMPGROUP 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 32


Klauzula HAVINGDo 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 EMPGROUP BY JOBHAVING MAX(SAL)>2000;

JOB AVG(SAL) --------- ----------ANALYST 3000MANAGER 2758,33333PRESIDENT 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 EMPHAVING JOB<>'CLERK'GROUP BY JOB;

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

lub

SELECT JOB, AVG(SAL)FROM EMPWHERE 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 33


Kolejność występowania klauzulWymagany porządek klauzul jest następujący:1. SELECT lista wyrażeń2. FROM tabela3. WHERE warunek selekcji wierszy4. HAVING warunek selekcji grup5. ORDER BY wyrażenia6. ;

Strona 34


Ć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 3000CLERK 800 1300MANAGER 2450 2975PRESIDENT 5000 5000SALESMAN 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 35


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 36


Wybieranie danych z wielu tabel

Strona 37


Złączenie równościoweChcemy 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, DNAMEFROM EMP, DEPTWHERE 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, DNAMEFROM EMP, DEPTWHERE EMP.DEPTNO=DEPT.DEPTNOORDER BY DEPT.DEPTNO;

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

Strona 38


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, DNAMEFROM EMP E, DEPT DWHERE E.DEPTNO=D.DEPTNOORDER 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ścioweZłą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.GRADEFROM EMP E, SALGRADE SWHERE SAL BETWEEN LOSAL AND HISAL;

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

Strona 39


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

Reguły łączenia tabel

Ogólna zasada łączenia tabel:

Minimalna liczba warunków łączących = liczba tabel —1

Strona 40


Ć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 41


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 42


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 43


Inne metody łączenia tabel

Strona 44


Złączenia zewnętrznePodczas łą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, DNAMEFROM EMP E, DEPT DWHERE 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_SZFROM EMP E, EMP MWHERE E.MGR=M.EMPNO AND E.SAL<M.SAL;

Strona 45


NAZW_PR PENS_PR NAZW_SZ PENS_SZ ---------- ---------- ---------- ----------BLAKE 2850 KING 5000CLARK 2450 KING 5000JONES 2975 KING 5000MARTIN 1250 BLAKE 2850ALLEN 1600 BLAKE 2850TURNER 1500 BLAKE 2850JAMES 950 BLAKE 2850WARD 1250 BLAKE 2850SMITH 800 FORD 3000ADAMS 1100 SCOTT 3000MILLER 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 zbioroweOperatory 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 EMPWHERE DEPTNO=30UNIONSELECT JOB FROM EMPWHERE DEPTNO=10 JOB ---------CLERK MANAGER PRESIDENTSALESMAN

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 46


Operator INTERSECT

Operator zbiorowy INTERSECT (przecięcie zbiorów) powoduje wybranie wierszy wspólnych dla wyników obu zapytań SELECT.

SELECT JOB FROM EMPWHERE DEPTNO=30INTERSECTSELECT JOB FROM EMPWHERE 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 EMPWHERE DEPTNO=30MINUSSELECT JOB FROM EMPWHERE 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 47


SELECT EMPNO, ENAME, SALFROM EMPUNIONSELECT ID, NAME, SALARY FROM EMP_HISTORYORDER BY 2;

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

Strona 48


Ć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 49


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 50


Podzapytania

Strona 51


Podzapytania zagnieżdżonePodzapytanie to polecenie SELECT zagnieżdżone w innym poleceniu SELECT.

SELECT kolumna_1, kolumna_2, …FROM tabelaWHERE 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 wierszPodzapytania 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, SALFROM EMPWHERE SAL>(wynik zapytania z punktu 1)

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

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

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

Strona 52


FORD ANALYST 3000SCOTT ANALYST 3000

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

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

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

Podzapytania zwracające wiele wierszyJeś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, JOBFROM EMPWHERE 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, DEPTNOFROM EMPWHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

ENAME SAL DEPTNO ---------- ---------- ----------BLAKE 2850 30FORD 3000 20SCOTT 3000 20KING 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 53


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

Spróbujmy przerobić powyższe zapytanie:

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

ENAME SAL DEPTNO ---------- ---------- ----------BLAKE 2850 30FORD 3000 20SCOTT 3000 20KING 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, DEPTNOFROM EMPWHERE SAL > ANY (SELECT DISTINCT SAL FROM EMP WHERE DEPTNO=10);

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

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

Strona 54


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, DEPTNOFROM EMPWHERE DEPTNO=10;

ENAME SAL DEPTNO ---------- ---------- ----------KING 5000 10CLARK 2450 10MILLER 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, DEPTNOFROM EMPWHERE SAL > ALL (SELECT DISTINCT SAL FROM EMP WHERE DEPTNO=30)ORDER BY SAL;

ENAME SAL DEPTNO ---------- ---------- ----------JONES 2975 20FORD 3000 20SCOTT 3000 20KING 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 EMPHAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE JOB='MANAGER')GROUP BY JOB;

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

Strona 55


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

SELECT JOB, AVG(SAL)FROM EMPHAVING 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 EWHERE SAL < (SELECT AVG(SAL) FROM EMP WHERE JOB=E.JOB)ORDER BY JOB; ENAME SAL DEPTNO ---------- ---------- ----------JAMES 950 30SMITH 800 20CLARK 2450 10MARTIN 1250 30WARD 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 56


Aby za pomocą operatora EXIST znaleźć pracowników, którzy mają podwładnych, napiszemy:

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

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

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

SELECT DNAME, DEPTNOFROM DEPT DWHERE 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 57


Ćwiczenia1. 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 58


Język definiowania danych

Strona 59


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 tabelNazwa tabeli musi być zgodna regułami nazewnictwa obiektów bazy danych Oracle:

nazwa obiektu musi zaczynać się od litery; nazwa może zawierać litery, cyfry oraz znak podkreślenia. Możliwe, ale nie zalecanie

jest używanie znaków $ i #; małe i duże litery są równoważne; długość nazwy nie może przekraczać 30 znaków; w obrębie bazy danych nie mogą istnieć dwa obiekty o tej samej nazwie — nazwa

musi być unikalna; nazwa nie może być nazwą zastrzeżoną dla języka; jeśli nazwa nie spełnia tych zaleceń to musi być otoczona podwójnymi cudzysłowami.

Wtedy rozróżniane są małe i wielkie litery.

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

Typy kolumn

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

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

VARCHAR2 (n)VARCHAR (n)

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

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

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

LONG ciąg znaków zmiennej długości o maksymalnym zakresie 2 GB,

Strona 60


w tabeli może być tylko jedna kolumna typu LONGRAW (n) ciąg bajtów o długości do n bajtów, maksymalne n=2000LONG RAW ciąg bajtów o maksymalnej długości do 2 GB, w tabeli może być tylko

jedna kolumna typu LONG RAWROWID 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ładTworzenie tabeli DEPT:

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

Warunki integralnościPodczas 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],…);

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

Strona 61


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

Warunek umieszczony po definicjach wszystkich kolumn:

Strona 62


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.

Definicja warunku przy definicji kolumny:

Strona 63


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


kolumna typ rozmiar CONSTRAINT nazwa_warunku UNIQUE


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.

Przykład

Strona 64


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.


kolumna typ rozmiar CONSTRAINT nazwa_warunku REFERENCES nazwa_tabeli lista_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.

Przykład

Strona 65


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

Przykład

Strona 66


Aby utworzyć tabelę EMP_CLERK, napiszemy:

CREATE TABLE EMP_CLERKASSELECT EMPNO, ENAME, JOB, SALFROM EMPWHERE 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 tabeliDo 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 nazwaADD nazwa_kolumny typ (rozmiar), nazwa_kolumny typ (rozmiar), ...);

Przykład

ALTER TABLE empADD adress VARCHAR2(40);

Strona 67


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 nazwaADD (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 nazwaDROP CONSTRAINT nazwa_warunku;

Modyfikacja definicji kolumny

Do modyfikowania definicji kolumny służy klauzula MODIFY:

ALTER TABLE nazwaMODIFY 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 68


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 69


Język manipulowania danymi

Strona 70


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ładAby skopiować do tabeli MANAGER wszystkich pracowników pracujących na stanowisku MANAGER napiszemy:

Strona 71


INSERT INTO managerSELECT (empno, ename, sal, job, hiredate)FROM empWHERE 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 deptSET 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 72


DELETE FROM empWHERE 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 73


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 74


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 75


Perspektywy

Strona 76


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)]ASSELECT …

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ładAby utworzyć perspektywę zawierająca niektóre dane o pracownikach zatrudnionych na stanowisku 'MANAGER', napiszemy:

CREATE VIEW Emp_ManagerASSELECT empno, ename, sal, deptnoFROM empWHERE job='MANAGER';

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

Strona 77


SELECT *FROM Emp_ManagerORDER 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_ManagerASSELECT empno, ename, sal, job, deptnoFROM empWHERE job='MANAGER'WITH CHECK OPTION;

System nie pozwoli teraz zmienić w wierszu pola job z MANAGER na inną wartość.

Usuwanie perspektywyPerspektywę usuwa się poleceniem DROP VIEW:

DROP VIEW nazwa_perspektywy;

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

Strona 78


Użytkownicy i uprawnienia

Strona 79


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żytkownikamiZa 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żytkownikIDENTIFIED 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 80


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ładJeśli użytkownik Adams chce zmienić hasło na 'tygrys', to pisze:

ALTER USER Adams IDENTIFIED BY tygrys;

Uprawnienia w bazie OracleW 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 OracleRESOURCE możliwość tworzenia tabel, sekwencji, indeksów i innych obiektówDBA 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 81


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żytkownikWITH 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 obiektTO użytkownik [, użytkownik];

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

Prawo ObiektSELECT wybieranie danych z tabeli lub perspektywyINSERT wstawianie wierszy do tabeli lub perspektywyUPDATE modyfikacje wierszy lub nieokreślonych kolumn tabeli lub perspektywyDELETE usuwanie wierszy z tabeli lub perspektywyALTER zmiana definicji kolumn tabeli

Strona 82


INDEX indeksowanie tabeliREFERENCES odwołanie do tabeli w obcych kluczachALL wszystkie prawaEXECUTE prawo wykonywania procedur, funkcji i pakietów

PrzykładAby nadać użytkownikowi Scott prawa wyboru do swojej tabeli emp, użytkownik Adams napisze:

GRANT SELECTON empTO 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 obiektTO użytkownikWITH GRANT OPTION;

PrzykładAby nadać użytkownikowi Scott prawa wyboru do swojej tabeli emp z prawem przekazywania tego uprawnienia dalej, użytkownik Adams napisze:

GRANT SELECTON empTO ScottWITH GRANT OPTION;

Uprzywilejowanie typu PUBLIC

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

GRANT SELECTON empTO PUBLIC;

Strona 83


Odbieranie uprawnień obiektowych

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

REVOKE uprawnienie [, uprawnienie...]ON obiektFROM 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ładAby 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 synonimuFOR [właściciel.]nazwa_obiektu;

Synonimy usuwa się poleceniem DROP SYNONYM:

DROP [PUBLIC] SYNONYM nazwa_synonimu;

RoleRola 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 84


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 85


Indeks

AADD, 71, 72ALL, 32, 49, 58, 59, 89ALTER, 71, 72, 78, 87, 89ALTER TABLE, 71, 72ALTER USER, 87AND, 23, 25, 26, 41, 48AS, 70, 71, 82, 83ASCENDING, 20AUTO, 80AVG, 32, 33, 34, 35, 37, 56, 59, 60

BBETWEEN... AND, 22

CCASCADE, 73, 86CASCADE CONTRAINTS, 73CHAR, 64, 66CHECK, 67, 83COMMIT, 78, 79, 80CONSTRAINT, 66, 67, 68, 69, 70, 72COUNT, 32, 33, 37CREATE, 78CREATE ROLE, 91CREATE SYNONYM, 90CREATE TABLE, 65, 66, 67, 68, 69, 70, 71, 76CREATE USER, 86

DDATE, 64, 70DEFAULT, 70DELETE, 69, 70, 77, 78, 80, 88DESC, 20, 27, 66, 71DESCENDING, 20

DESCRIBE, 66DISABLE, 72DISTINCT, 19, 32, 58, 59DROP, 72, 73, 78, 83, 86, 90, 91DROP ROLE, 91DROP SYNONYM, 90DROP TABLE, 73DROP USER, 86

EENABLE, 72EXECUTE, 89EXIST, 60, 61

FFOR, 90FOREIGN KEY, 67, 69, 70FROM, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24,

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

GGRANT, 87, 88, 89, 91

HHAVING, 35, 36, 58, 59, 60

IIDENTIFIED BY, 86, 87, 91IN, 22, 23, 24, 26, 57, 58INDEX, 89INSERT, 76, 77, 80, 88INTERSECT, 50

Strona 86


INTO, 76, 77IS NULL, 22, 24, 26

LLIKE, 22, 23, 24, 26LONG, 65

MMAX, 32, 33, 35, 37, 57, 58MIN, 32, 33, 34, 37, 38, 60MINUS, 50MODIFY, 72

NNOT, 24, 25, 26, 61, 65, 66, 68, 70, 71, 72NULL, 18, 24, 25, 32, 48, 65, 66, 68, 70, 71, 72NUMBER, 18, 64, 65, 66, 68, 70, 71

OON, 69, 70, 80, 88, 89, 90ON DELETE CASCADE, 69, 70OR, 25, 26, 82ORDER BY, 20, 21, 36, 40, 41, 50, 51, 59, 60,

61, 83

PPRIMARY KEY, 67, 68, 70PUBLIC, 89, 90

RRAW, 65REFERENCES, 69, 70, 89RENAME TABLE, 73REPLACE, 82

REVOKE, 88, 90, 91ROLLBACK, 78, 79ROWID, 65

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

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

SET, 77, 80STDDEV, 32SUM, 32

TTO, 73, 79, 87, 88, 89

UUNION, 49, 50, 51UNIQUE, 67, 68, 69UPDATE, 77, 80, 88

VVALUES, 76VARCHAR, 64, 70VARCHAR2, 64, 65, 66, 67, 69, 71VARIANCE, 32VIEW, 82, 83

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

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

WITH ADMIN OPTION, 88WITH GRANT OPTION, 89WORK, 78, 79

Strona 87

Zawartość tabeli SALGRADE

Documents

Transcript of Zawartość tabeli SALGRADE