Wyk
ład
4
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).