Wyk

ład

4

SQL – język relacyjnych

i obiektowo-relacyjnych

baz danych

Złożone zapytania cd.

Przykładowy schemat z

Oracle

Związek

Emp.Sal

z

wartościami w tabeli

Salgrade

:

Zarobki

Emp.Sal

są

zaliczane do grupy

Salgrade.Grade

takiej, że:

Emp.Sal

należy do

przedziału:

[Salgrade.Losal,
Salgrade.Hisal]

Tabela EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ------- --------- ---- --------- ----- ---- ------
7839 KING PRESIDENT 17-NOV-81 5000 10

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7900 JAMES CLERK 7698 03-DEC-81 950 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7369 SMITH CLERK 7902 17-DEC-80 800 20

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20

7876 ADAMS CLERK 7788 12-JAN-83 1100 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

Tabele DEPT i SALGRADE

DEPT

DEPTNO DNAME LOC

------ ----------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SALGRADE

GRADE LOSAL HISAL

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

Podzapytania



Wewnątrz klauzul WHERE, HAVING i FROM, mogą
wystąpić podzapytania, mające taką samą postać jak
zapytania (tylko są ujęte w nawiasy).



Podzapytanie może wystąpić jako (z reguły) prawy
argument predykatów =, <, <=, >, >=, <>, IN, NOT IN,
przy czym w przypadku predykatów =, <, <=, >, >=, <>,
powinno określać jedną wartość, a w przypadku
predykatów IN oraz NOT IN listę wartości.



W podzapytaniu nie można używać klauzul ORDER BY.



W podzapytaniu dostępne są nazwy kolumn wprowadzone
w głównym zapytaniu.



Podzapytanie zwykłe - zbiór wynikowych wierszy
nie zmienia się i nie zależy od wierszy
w głównym zapytaniu.

Wypisz osoby, które zarabiają najwięcej ze wszystkich
pracowników.

Przykład

SELECT Ename, Sal
FROM Emp
WHERE
Sal = (SELECT Max(Sal) FROM
Emp);

ENAME SAL

-------- ---------
KING 5000

Zapytanie to można z kolei użyć jako podzapytanie (bez
średnika)
w warunku WHERE, wtedy kiedy trzeba przyrównać
zarobki pracownika do maksymalnych zarobków.

Najpierw liczymy największe

Sal

za pomocą zapytania:

SELECT Max(Sal) FROM Emp;

Wypisz pracowników, którzy pracują na tym samym
stanowisku, co pracownik o numerze 7369 i których
zarobki są większe niż zarobki pracownika o
numerze 7876.

Przykład

SELECT Ename, Job
FROM Emp
WHERE Job = (SELECT Job
FROM Emp WHERE
Empno = 7369)
AND Sal > (SELECT Sal
FROM Emp WHERE
Empno = 7876);



W klauzuli WHERE może być więcej niż jedno podzapytanie.

ENAME JOB

------- ------
MILLER CLERK

Wypisz działy, w których pracują urzędnicy.

Przykład

SELECT Dname FROM Dept
WHERE Deptno IN
(SELECT Deptno FROM Emp WHERE Job =
'CLERK');



Gdy podzapytanie zwraca więcej niż jedną wartość zamiast operatora = stosuje się operator IN.

DNAME

------------
ACCOUNTING
RESEARCH
SALES

Podzapytania a NULL



Problemy pojawiają się przy stosowaniu operatora NOT IN
w sytuacji gdy w wyniku podzapytania jest pseudo-wartość
NULL – o żadnej wartości nie da się stwierdzić, że jest różna od
NULL!



Zapytanie, które pozornie liczy wszystkich pracowników, którzy
nie są kierownikami,

SELECT Prac.Ename

FROM Emp Prac

WHERE Prac.Empno NOT IN

(SELECT Podw.Mgr FROM Emp
Podw);

daje w rzeczywistości wynik będący pustą tabelą!

W Oracle wystarczy zamiast
Podw.Mgr użyć
NVL(Podw.Mgr,0)

Kwantyfikatory ALL i SOME

(ANY)

Wyrażenie operator [ANY|SOME|ALL] [lista_wyrażeń|

(podzapytanie)]

 

10000 >=

ALL

(SELECT Sal FROM Emp)

“10000 większe lub równe od zarobków każdego z

pracowników”

1000 >=

SOME

(SELECT Sal FROM Emp)

“1000 większe lub równe od zarobków jakiegoś

pracownika”

Przykład

Wybierz nazwiska i zarobki pracowników, którzy
zarabiają więcej od każdego pracownika z działu
30.

SELECT Ename, Sal
FROM Emp
WHERE Sal

> ALL

(SELECT Sal FROM Emp WHERE Deptno =
30)

Przykład

Wybierz nazwiska i zarobki pracowników, którzy
zarabiają więcej od któregokolwiek pracownika
zatrudnionego na stanowisku ‘SALESMAN’.

SELECT Ename, Sal
FROM Emp
WHERE Sal

> ANY

(SELECT Sal FROM Emp WHERE Job
=’SALESMAN' )

Podzapytania skorelowane



Zbiór wyników podzapytania zależy od wartości
występujących w wierszach w głównym zapytaniu.!

Przykład

SELECT a.Deptno, a.Ename, a.Sal
FROM Emp a
WHERE Sal =
(SELECT Max(b.Sal)
FROM Emp b
WHERE b.Deptno= a.Deptno); 

Dla każdego działu wypisz osobę, która zarabia
najwięcej w tym dziale

.

Maksymalne zarobki w danym dziale =

SELECT Max(Sal)
FROM Emp
WHERE Deptno=

<Deptno określony w

głównym zapytaniu>

Predykaty EXISTS i NOT EXISTS

 Są to predykaty sprawdzające czy podzapytanie

daje pusty zbiór wyników czy nie, np.

EXISTS

(SELECT 'x' FROM Emp WHERE

Deptno= 10)

“istnieje co najmniej jeden pracownik
zatrudniony w dziale o numerze 10”.

Dla wyniku nie jest istotne co napiszemy na liście
SELECT
w ramach predykatu EXISTS – najprostsza
obliczeniowo
jest wartość stała, taka jak 'x'.

Przykład

SELECT DISTINCT Dname
FROM Dept
WHERE NOT EXISTS (SELECT 'x' FROM
Emp
WHERE Emp.Deptno=
Dept.Deptno);

Wypisz działy, w których aktualnie nikt nie jest
zatrudniony.

DNAME

--------------
OPERATIONS

Przykład

SELECT Empno, Ename, Job, Deptno
FROM Emp Outer
WHERE NOT EXISTS (SELECT 'x'
FROM Emp Inner
WHERE Inner.Mgr =
Outer.Empno);

 Za pomocą predykatu NOT EXISTS można rozwiązać

problem wyznaczenia wszystkich pracowników, którzy
nie są kierownikami. Problemu tego nie udało się
poprzednio rozwiązać za pomocą predykatu NOT IN z
powodu występowania pseudo-wartości NULL w wyniku
podzapytania.

Korelacja w UPDATE i DELETE

 Podzapytania są częścią składową warunków w

klauzulach WHERE i HAVING.

Zwiększ o 10% zarobki wszystkim pracownikom
pracującym w Dallas.

UPDATE Emp
SET Sal = Sal * 1.1
WHERE
(SELECT Loc FROM Dept
WHERE Emp.Deptno=Dept.Deptno) =
'DALLAS';

Użycie instrukcji SELECT w klauzuli

FROM

Oblicz procentowy udział każdego z działów w
liczbie pracowników i zarobkach w firmie

 SELECT a.Deptno "Dział",
Trunc(100*a.Liczba_Prac/b.Liczba_Prac,1) AS
"%Pracowników",
Trunc(100*a.Suma_zarob/b.Suma_zarob,1) AS
"%Zarobków"
FROM
(SELECT Deptno, COUNT(*) AS Liczba_Prac, SUM(Sal)
AS Suma_zarob
FROM Emp
GROUP BY Deptno
) a,
(SELECT
COUNT(*) AS Liczba_Prac,
SUM(Sal) AS Suma_zarob
FROM Emp
) b;

Dzial %Pracowników %Zarobków

----- ------------ ----------
10 21.4 30.1
20 35.7 37.4
30 42.8 32.3

Klauzula AS instrukcji

CREATE TABLE (tylko w Oracle)

Skopiuj informacje o urzędnikach i umieść je w
nowej tabeli Urzędnicy.

CREATE TABLE Urzędnicy (Empno,
Ename, Sal)
AS SELECT Empno,
Ename, Sal
FROM Emp
WHERE Job =
'CLERK';

Używając tej konstrukcji, na liście kolumn tabeli nie
podajemy nazw typów danych (możemy natomiast
określać więzy spójności i wartości domyślne).
System sam wyprowadza informację o typach
danych kolumn i ich rozmiarach z wyrażeń
występujących na liście SELECT w podzapytaniu.

Instrukcja INSERT

Wstaw do pomocniczej tabeli wszystkich pracowników
zatrudnionych w ciągu ostatnich 10 dni.

INSERT INTO Emp_new

SELECT * FROM Emp
WHERE Sysdate - Hiredate < 10;

Złączenie zewnętrzne (Oracle)

Złączenie zewnętrzne rozszerza rezultat prostego
złączenia (nazywanego wewnętrznym) o te wiersze
z jednej z tabel, dla których w trakcie złączania nie
znaleziono odpowiadających im wierszy w drugiej
tabeli. Warunek złączenia podaje się w postaci
(kolumna1 jest kolumną złączenia z pierwszej tabeli,
kolumna2 jest kolumną złączenia z drugiej tabeli):

kolumna1 = kolumna2 (+)

lub

kolumna1 (+) = kolumna2

Przykład

SELECT Dept.Deptno, Dname, SUM(Sal)
FROM Dept, Emp
WHERE Dept.Deptno = Emp.Deptno(+)
GROUP BY Dept.Deptno, Dname;

Wypisz wszystkie działy dla każdego z nich podając
sumaryczne zarobki zatrudnionych w nim
pracowników.

DEPTNO DNAME SUM(SAL)

-------- ------------ ----------
10 ACCOUNTING 8750
20 RESEARCH

10875

30 SALES

9400

40 OPERATIONS

Przykład c.d.

 Gdy w dziale nie ma żadnego pracownika, zbiór

sumowanych wartości składa się z jednej wartości
Null. Wynikiem sumowania Sum(Sal) jest pseudo-
wartość Null, reprezentowana na wydruku przez
puste miejsce.

 Gdybyśmy chcieli w takim przypadku wypisać

wartość 0:

NVL(Sum(Sal),0)

Przykład – to samo inaczej

Wypisz wszystkie działy, dla każdego z nich podając
sumaryczne zarobki jego pracowników.

SELECT Dept.Deptno, Dname,
TO_CHAR(SUM(Sal))
FROM Emp, Dept
WHERE Emp.Deptno= Dept.Deptno
GROUP BY Dept.Deptno, Dname
UNION
SELECT Dept.Deptno, Dname, NULL
FROM Dept
WHERE NOT EXISTS (SELECT 'x' FROM Emp
WHERE Emp.Deptno =
Dept.Deptno);

DEPTNO DNAME To_Char(SUM(SAL))

-------- ------------ ----------
10 ACCOUNTING 8750
20 RESEARCH

10875

30 SALES

9400

40 OPERATIONS

Drobna zmiana - typ liczbowy

Wypisz wszystkie działy, dla każdego z nich podając
sumaryczne zarobki jego pracowników.

SELECT Dept.Deptno, Dname, SUM(Sal)
FROM Emp, Dept
WHERE Emp.Deptno= Dept.Deptno
GROUP BY Dept.Deptno, Dname
UNION
SELECT Dept.Deptno, Dname, 0
FROM Dept
WHERE NOT EXISTS (SELECT 'x' FROM Emp
WHERE Emp.Deptno =
Dept.Deptno);

DEPTNO DNAME SUM(SAL)

-------- ------------ ----------
10 ACCOUNTING 8750
20 RESEARCH

10875

30 SALES

9400

40 OPERATIONS 0

Wbudowane operatory złączeń

(Standard)

 Złączenie tabel można określać bezpośrednio w
klauzuli
FROM (T, U oznaczają tabele):

 Złączenie krzyżowe

T CROSS JOIN U

- iloczyn

kartezjański - wszystkie kombinacje wierszy.

 Złączenie wewnętrzne

T INNER JOIN U

- złączenie

wierszy obu tabel względem kolumn o tych samych
nazwach.

 Złączenie zewnętrzne

a. lewostronne

T LEFT OUTER JOIN U

b. prawostronne

T RIGHT OUTER JOIN

U

c. pełne

T FULL OUTER JOIN U

(suma wyników

złączenia zewnętrznego lewostronnego i
prawostronnego).