Podstawy jzyka SQL - Kolos Wikikolos.math.uni.lodz.pl/~archive/Podstawy baz danych/Podstawy...

�

�

Materiały szkoleniowe

Podstawy j�zyka SQL

Prowadz�cy Anna Pijanowska - Ku�nierz

Paweł �ołnierczyk

� ��

��

��

� � ��

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

� ��

��

�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 ________________________________________________ 85

Zarz�dzanie u�ytkownikami___________________________________________________86

Uprawnienia w bazie Oracle___________________________________________________87

Role _______________________________________________________________________90

��

� � ��

Zawarto�� tabel wykorzystywanych na kursie

� ��

��

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

��

� � ��

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

� ��

��

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

��

� � ��

Relacyjny model danych

� ��

��

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.

��

� � ��

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.

� ��

��

��

� � ��

Przegl�d 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 �� uzyskujemy informacje z bazy. Polecenie ��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

��

� � ��

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

� ��

��

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

��

� � ��

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

� ��

��

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

��

� � ��

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 �� 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

� ��

��

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 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 (��) — 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 �� (��) 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

��

� � ��

Mo�na sortowa� według kilku kolumn, wtedy po słowie kluczowym �� 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.

� ��

��

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:

• ��

• �� (lista), • �� • ��

Operator BETWEEN...AND

Operator �� 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.

��

� � ��

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 �� 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 �� 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

� ��

��

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 �� 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) �� kolumna= nie jest równy �� kolumna> nie jest wi�kszy �� nie jest w podanym przedziale �� nie jest w zbiorze �� nie jest zgodny z mask� �� nie jest NULL

Aby wybra� wszystkie osoby, które nie zarabiaj� wi�cej ni� 2000, napiszemy:

��

� � ��

SELECT ENAME, SAL FROM EMP WHERE NOT SAL>2000; ENAME SAL ---------- ---------- MARTIN 1250 ALLEN 1600 TURNER 1500 JAMES 950 WARD 1250 SMITH 800 ADAMS 1100 MILLER 1300

Aby wybra� osoby, które nie otrzymuj� prowizji, napiszemy: SELECT ENAME, JOB, COMM FROM EMP WHERE COMM IS NOT NULL ENAME JOB COMM ---------- --------- ---------- MARTIN SALESMAN 1400 ALLEN SALESMAN 300 TURNER SALESMAN 0 WARD SALESMAN 500

Tworzenie zło�onych kryteriów

Do budowania warunków zło�onych słu�� operatory �� (i) oraz �� (lub). Warunek (X �� Y) jest prawdziwy wtedy i tylko wtedy, gdy X jest prawdziwy i Y jest prawdziwy. Warunek (X �� Y) jest prawdziwy wtedy i tylko wtedy, gdy X jest prawdziwy lub Y jest prawdziwy. Operator �� ma wi�kszy priorytet ni� operator ��.

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 �� ma wy�szy priorytet ni� operator ��, 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.

� ��

��

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. =, <>, <=, >=, >,<, �� , ��, ��, �� 2. �� 3. �� 4. �� 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:

��

� � ��

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

� ��

��

�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

��

� � ��

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

� ��

��

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

��

� � ��

Funkcje grupowe

� ��

��

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 ([�� | ��] wyra�enie) warto�� rednia wyra�e�, NULL nie jest

uwzgl�dniane COUNT ([�� | ��] 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 ([�� | ��] wyra�enie) maksymalna warto�� wyra�enia MIN ([�� | ��] wyra�enie) minimalna warto�� wyra�enia STDDEV ([�� | ��] wyra�enie) odchylenie standardowe wyra�e�, bez

uwzgl�dniania warto�ci NULL SUM ([�� | ��] wyra�enie) suma warto�ci wyra�e�, bez uwzgl�dniania

warto�ci NULL VARIANCE ([�� | ��] wyra�enie)

wariancja wyra�e�, bez uwzgl�dniania warto�ci NULL

Kwalifikator �� ogranicza działanie funkcji grupowych do ró�nych warto�ci argumentów. Kwalifikator �� 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.

��

� � ��

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.

� ��

��

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.

��

� � ��

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.

� ��

��

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

��

� � ��

�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

� ��

��

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

��

� � ��

Wybieranie danych z wielu tabel

� ��

��

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

��

� � ��

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

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

� ��

��

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

��

� � ��

�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

� ��

��

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

��

� � ��

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

� ��

��

��

� � ��

Inne metody ł�czenia tabel

� ��

��

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;

��

� � ��

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� ��. 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 �� jest u�ywany do tworzenia sumy dwóch zbiorów wierszy (wyników zapytania ��). Aby otrzyma� wszystkie, wzajemnie ró�ne wiersze zwracane w wyniku dwóch polece� ��, napiszemy: SELECT JOB FROM EMP WHERE DEPTNO=30 UNION SELECT JOB FROM EMP WHERE DEPTNO=10 JOB --------- CLERK MANAGER PRESIDENT SALESMAN

Operacja �� (podobnie jak inne operatory zbiorowe) powoduje automatyczn� eliminacj� duplikatów. Aby otrzyma� w wyniku wszystkie wiersze z obu tabel nale�y u�y� operatora ��.

� ��

��

Operator INTERSECT

Operator zbiorowy �� (przeci�cie zbiorów) powoduje wybranie wierszy wspólnych dla wyników obu zapyta� ��. SELECT JOB FROM EMP WHERE DEPTNO=30 INTERSECT SELECT JOB FROM EMP WHERE DEPTNO=20 JOB --------- CLERK MANAGER

Operator MINUS

Operator zbiorowy �� (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� �� poł�czonych operatorami zbiorowymi. W takim przepadku najwy�szy priorytet ma operator ��, potem operatory �� i �� (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� �� nazwy mog� by� ró�ne, w klauzuli ORDER BY specyfikuje si� nie nazw� kolumny, tylko jej pozycj�.

��

� � ��

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.

� ��

��

�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

��

� � ��

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

� ��

��

��

� � ��

Podzapytania

� ��

��

Podzapytania zagnie�d�one

Podzapytanie to polecenie �� zagnie�d�one w innym poleceniu ��.

�� kolumna_1, kolumna_2, … �� tabela �� kolumna = (�� kolumna �� tabela �� warunek);

Podzapytanie to wewn�trzny �� 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

��

� � ��

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.

� ��

��

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 �� i �� 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 �� — 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.

��

� � ��

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 �� i �� słowo kluczowe ��. SELECT ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=10; ENAME SAL DEPTNO ---------- ---------- ---------- KING 5000 10 CLARK 2450 10 MILLER 1300 10

Operator �� — 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

� ��

��

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

��

� � ��

Aby za pomoc� operatora �� 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��.

� ��

��

�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

��

� � ��

J�zyk definiowania danych

� ��

��

Struktury danych ORACLE

• Nowe tabele mog� by� tworzone tak�e podczas pracy u�ytkowników z baz� danych. • Pami�� dla tabel jest przydzielana w miar� potrzeb. Pami�� dla bazy danych jako

cało�ci przydzielana jest z góry, ale mo�liwe jest rozszerzanie jej rozmiarów przy zastosowaniu odpowiednich opcji.

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

• U�ytkownicy mog� zakłada� własne, prywatne struktury.

Tworzenie tabel

Nazwa tabeli musi by� zgodna regułami nazewnictwa obiektów bazy danych Oracle: • nazwa obiektu musi zaczyna� si� od litery; • nazwa mo�e zawiera� litery, cyfry oraz znak podkre�lenia. Mo�liwe, ale nie

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

musi by� unikalna; • nazwa nie mo�e by� nazw� zastrze�on� dla j�zyka; • je�li nazwa nie spełnia tych zalece� to musi by� otoczona podwójnymi

cudzysłowami. Wtedy rozró�niane s� małe i wielkie litery. Definiuj�c tabel� musimy poda� list� kolumn opisywan� przez nazw� kolumny, jej typ i czasami długo�� przechowywanej warto�ci.

Typy kolumn

Ka�da kolumna musi mie� okre�lony typ danych:

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

VARCHAR2 (n) VARCHAR (n)

ci�g znaków zmiennej długo�ci, parametr n wskazuje maksymaln� długo�� ci�gu

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

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

��

� � ��

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 �� :

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

�� nazwa_tablicy (nazwa_kolumny typ (rozmiar) [�� | �� ], nazwa_kolumny typ (rozmiar) [�� | �� ], …);

� ��

��

Opcja �� (domy�lna) oznacza, �e pola tej kolumny mog� przyjmowa� warto�� NULL. Opcja �� 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:

�� [�� ] 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� �� 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:

�� nazwa_tablicy (... nazwa_kolumny typ (rozmiar) �� nazwa_warunku typ_warunku [warunek], …);

��

� � ��

Warunek umieszczony po definicjach wszystkich kolumn:

�� nazwa_tablicy (... nazwa_kolumny typ (rozmiar), … �� nazwa_warunku typ_warunku warunek, �� 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: ��, �� , ��, �� .

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

�� (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:

kolumna typ rozmiar �� nazwa_warunku ��

Definicja warunku po definicji wszystkich kolumn:

�� nazwa_warunku �� (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 �� . 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 ��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 �� nazwa_warunku ��


�� nazwa_warunku ��(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 �� mo�na do kolumn klucza unikalnego dopisa� osobno.

��

� � ��

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.


kolumna typ rozmiar �� nazwa_warunku �� nazwa_tabeli lista_kolumn;


�� nazwa_warunku �� (lista kolumn tabeli definiowanej) �� 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� �� .

� ��

��

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) �� 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�.

�� nazwa_tabeli [(nazwa_kolumny [��|��], …)] ��zapytanie;

Lista nazw nowej tabeli mo�e by� pomini�ta, je�li s� poprawnie wskazane nazwy kolumn w poleceniu ��. 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 ��.

��

� � ��

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

�� nazwa ��nazwa_kolumny typ (rozmiar), nazwa_kolumny typ (rozmiar), ...);

Przykład ALTER TABLE emp ADD adress VARCHAR2(40);

� ��

��

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.

�� nazwa ��(��nazwa_warunku typ_warunku warunek, ...);

Sprawdzanie warunków integralno�ci mo�na wł�cza� i wył�cza�:

Wł�czanie:

�� nazwa �� nazwa_warunku;

Wył�czanie:

�� nazwa �� 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:

�� nazwa ��nazwa_warunku;

Modyfikacja definicji kolumny

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

�� nazwa ��nazwa_kolumny typ (rozmiar) [��| ��], ...);

Nie mo�na zmieni� kolumny w której wyst�puj� warto�ci NULL na ��. Do niepustej tabeli nie mo�na doda� kolumny o własno�ci ��. Nie mo�na zmniejszy� rozmiaru kolumny ani zmieni� jej rozmiaru, je�li kolumna nie jest pusta.

��

� � ��

Usuwanie tabel

Do usuwania tabel słu�y polecenie ��.

�� 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 �� :

�� stara_nazwa �� nowa_nazwa;

� ��

��

��

� � ��

J�zyk manipulowania danymi

� ��

��

Wstawianie wierszy

Polecenie INSERT

Polecenie � ��słu�y do wstawiania nowych wierszy do tabeli:

��nazwa_tabeli [(lista_kolumn)] ��(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 �� . 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 � ��mo�na wstawi� tylko jeden wiersz. W poleceniu � �� 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 ��.

��nazwa_tabeli [lista_kolumn] ��lista_wyra�e� ��...

W ten sposób mo�emy za pomoc� pojedynczego polecenia � �� wstawi� wiele wierszy.

Przykład Aby skopiowa� do tabeli MANAGER wszystkich pracowników pracuj�cych na stanowisku MANAGER napiszemy:

��

� � ��

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

��nazwa_tabeli [alias] ��kolumna = { wyra�enie | podzapytanie } [ , kolumna= { wyra�enie | podzapytanie } ...] [��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 �� 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 ��.

��[��] tabela [ ��warunek ];

Warunek w klauzuli WHERE okre�la, które wiersze b�d� usuni�te. Przykład

� ��

��

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 ��. Zmiany staj� si� nieodwracalne i widoczne dla innych u�ytkowników;

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

• wykonania polecenia DDL (��, ��, ��). 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.

�� [��];

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.

��

� � ��

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

Polecenie ROLLBACK

Słu�y do jawnego wycofywania transakcji.

�� [��];

Polecenie �� 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.

�� 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:

�� [��] �� [��] 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.

� ��

��

Automatyczny COMMIT

W SQL*Plus mo�emy wymusi� automatyczne zatwierdzanie transakcji po ka�dym poleceniu � ��, �� i ��.

�� [��] ��

Dyrektyw� odwołujemy poleceniem:

�� [��] ��

��

� � ��

Perspektywy

� ��

��

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 ��, • 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 ��:

�� [��] �� nazwa_perspektywy [(lista_kolumn)] �� …

Lista_kolumn to lista nazw kolumn perspektywy, musz� one odpowiada� pozycjom wyboru z listy ��. Je�li perspektywa o danej nazwie ju� istnieje, to aby utworzy� na jej miejsce now� musimy usun�� star� lub u�y� opcji ��.

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

��

� � ��

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� ��, 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 ��:

��nazwa_perspektywy;

Perspektywa mo�e by� usuni�ta tylko przez jej wła�ciciela lub administratora.

� ��

��

��

� � ��

U�ytkownicy i uprawnienia

� ��

��

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 ��:

��u�ytkownik �� 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 ��:

��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 ��. Wtedy razem z u�ytkownikiem usuwane s� utworzone przez niego obiekty.

��

� � ��

Zmiana hasła

Ka�dy u�ytkownik mo�e zmieni� swoje hasło. Słu�y do tego polecenie ��.

��u�ytkownik �� 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 �� :

��uprawnienie [, uprawnienie...] ��u�ytkownik;

Polecenie �� nadaje nowe uprawnienia u�ytkownikowi. Uprawnienia nadawane kolejnymi poleceniami �� si� kumuluj�.

� ��

��

Odbieranie uprawnie� systemowych

Do odbierania uprawnie� słu�y polecenie ��:

��uprawnienie [, uprawnienie...] �� u�ytkownik;

Polecenie �� 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� ��.

��uprawnienie [, uprawnienie...] �� u�ytkownik ��;

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:

��uprawnienie [, uprawnienie...] �� obiekt �� 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 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� ��

��.

��uprawnienie [, uprawnienie...] �� obiekt �� u�ytkownik ��;

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 �� : GRANT SELECT ON emp TO PUBLIC;

Odbieranie uprawnie� obiektowych

Do odbierania uprawnie� obiektowych słu�y polecenie ��:

� ��

��

��uprawnienie [, uprawnienie...] �� obiekt �� 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:

��nazwa synonimu �� [wła�ciciel.]nazwa_obiektu;

Synonimy usuwa si� poleceniem �� :

��[�� ] �� nazwa_synonimu;

Role

Rola to zestaw uprawnie�, jaki mo�na przyznawa� u�ytkownikom bazy. Rola mo�e zawiera� zarówno uprawnienia systemowe jak i obiektowe. Dzi�ki rolom ułatwione jest administrowanie baz�. Je�li przykładowo dla kierowników działów zdefiniowana jest rola KIEROWNICY, to chc�c wszystkim kierownikom nada� jakie� uprawnienie wystarczy doda� je do roli KIEROWNICY.

Tworzenie roli

Rol� tworzy si� poleceniem ��:

��

� � ��

��rola [�� 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 �� i ��, 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 ��:

��rola;

� ��

��

��

� � ��

Indeks

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

B BETWEEN... AND, 22

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

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

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

E ENABLE, 72 EXECUTE, 89 EXIST, 60, 61

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

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

G GRANT, 87, 88, 89, 91

H HAVING, 35, 36, 58, 59, 60

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

� ��

��

IS NULL, 22, 24, 26

L LIKE, 22, 23, 24, 26 LONG, 65

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

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

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

61, 83

P PRIMARY KEY, 67, 68, 70 PUBLIC, 89, 90

R RAW, 65 REFERENCES, 69, 70, 89 RENAME TABLE, 73 REPLACE, 82 REVOKE, 88, 90, 91

ROLLBACK, 78, 79 ROWID, 65

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

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

SET, 77, 80 STDDEV, 32 SUM, 32

T TO, 73, 79, 87, 88, 89

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

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

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

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

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

Podstawy jzyka SQL - Kolos Wikikolos.math.uni.lodz.pl/~archive/Podstawy baz danych/Podstawy...

Documents

Transcript of Podstawy jzyka SQL - Kolos Wikikolos.math.uni.lodz.pl/~archive/Podstawy baz danych/Podstawy...