Będziemy kontynuować studiowanie języka SQL. Mianowicie, dokończymy prezentację zapytań rozpoczętą w wykładzie 1. Rozważymy kolejno następujące konstrukcje:
Do określenia danych, które chcemy wydobyć z bazy danych, można użyć kilku zapytań połączonych ze sobą operatorami algebraicznymi zgodnie ze składnią:
| instrukcja_SELECT operator instrukcja_SELECT |
Są trzy takie operatory:
1. UNION, UNION ALL - sumowanie zbiorów wyników (odpowiednio, z eliminacją bądź nie - powtórzeń wierszy),
2. INTERSECT - przecięcie zbiorów wyników,
3. EXCEPT - różnica zbiorów wyników (w Oracle MINUS).
Przykład
| Wypisz numery działów, w których w danej chwili nie są zatrudnieni żadni pracownicy. |
SELECT Dept.Deptno FROM Dept
MINUS
SELECT Emp.Deptno FROM Emp;
DEPTNO
----------
40
Przykład
| Wypisz nazwiska pracowników, zamieszczając przy pracownikach działu numer 10 gwiazdkę. |
SELECT Emp.Ename||'*' FROM Emp
WHERE Emp.Deptno = 10
UNION
SELECT Emp.Ename FROM Emp
WHERE Emp.Deptno <> 10
ORDER BY 1;EMP.ENAME||'*' -------------- ADAMS ALLEN BLAKE CLARK* FORD JAMES JONES KING* MARTIN MILLER* SCOTT SMITH TURNER WARD
Aby móc zastosować operator algebraiczny, liczba i typy kolumn w składowych zapytaniach muszą być takie same.
Według Standardu, tabela wynikowa nie posiada nazw kolumn. Natomiast Oracle, przy wypisywaniu zawartości tabeli wynikowej, jako etykiet kolumn używa wyrażeń z pierwszej instrukcji SELECT.
Klauzula ORDER BY może wystąpić tylko na końcu całego zapytania. W klauzuli ORDER BY do wynikowych kolumn odwołujemy się używając ich kolejnych numerów 1, 2,....
Informacja wypisywana z bazy danych może dotyczyć kilku tabel. Dane z kilku tabel są na ogół złączane korzystając z naturalnych powiązań między wierszami tabel opartych na związkach klucz obcy -> klucz główny. W takich przypadkach najczęściej klucz obcy i klucz główny mają tę samą nazwę. W celu ich odróżnienia konieczne jest użycie konstrukcji poprzedzania nazwy kolumny nazwą tabeli np. Emp.Deptno - czyli kwalifikowania nazwy kolumny nazwą tabeli.
Przykład
| Wypisz wszystkich pracowników i dla każdego z nich podaj nazwę jego działu. |
SELECT Emp.Empno, Emp.Ename, Dept.Dname
FROM Emp, Dept
WHERE Emp.Deptno= Dept.Deptno; EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
Wiersz każdego pracownika zostaje złączony z dokładnie jednym, odpowiadającym mu przez wartość Deptno, wierszem z tabeli Dept.
Predykat Emp.Deptno=Dept.Deptno występujący w powyższej instrukcji
SELECT nazywa się predykatem złączenia, w odróżnieniu od innych predykatów nazywanych
predykatami ograniczającymi np.
Sal>1000
lub
Loc='Warszawa'
Zwróćmy uwagę na istotność podania warunku złączenia. Gdybyśmy go opuścili, otrzymalibyśmy zbiór wszystkich możliwych kombinacji wierszy ze złączanych tabel – nie tylko tych, które są ze sobą powiązane wspólną cechą,
taką jak numer działu w powyższym zapytaniu. Wynik takiego pełnego połączenia tabel nosi nazwę
iloczynu kartezjańskiego tych tabel.
W Standardzie SQL jak i w Oracle od wersji 9i warunek złączenia można także zapisać w klauzuli FROM jako część jednego z operatorów złączenia i to w różny sposób. Dla powyższego zapytania są możliwe następujące zapisy:
SELECT
Emp.Empno, Emp.Ename, Dept.Dname
FROM Emp JOIN Dept ON Emp.Deptno = Dept.Deptno;
(tej postaci z operatorem złączenia JOIN będziemy używać zazwyczaj od tej pory)
albo gdy nazwy kolumn złączenia są takie same
SELECT Emp.Empno, Emp.Ename, Dname
FROM Emp JOIN Dept USING (Deptno);
albo jeszcze krócej
SELECT Empno, Ename, Dname
FROM Emp NATURAL JOIN Dept;
W ostatnim przypadku za kolumy złączenia
przyjmuje się wszystkie wspólne, tzn. mające takie same nazwy, kolumny.
Korzystając ze związku klucz obcy -> klucz główny można dokonać złączenia tabeli z nią samą. Wówczas ta sama tabela występuje w dwóch (lub więcej) rolach wskazywanych przez aliasy dołączane do nazwy tabeli w klauzuli FROM.
Przykład
| Wypisz nazwiska wszystkich pracowników i nazwiska ich kierowników. |
SELECT Prac.Ename, Kier.Ename AS Mgr
FROM Emp Prac JOIN Emp Kier ON Prac.Mgr = Kier.Empno;ENAME MGR ---------- ---------- SMITH FORD ALLEN BLAKE WARD BLAKE JONES KING MARTIN BLAKE BLAKE KING CLARK KING SCOTT JONES TURNER BLAKE ADAMS SCOTT JAMES BLAKE FORD JONES MILLER CLARK
Alias Prac reprezentuje tu wiersz pracownika, a alias Kier wiersz jego kierownika – zapewnienie zachodzenia związku między nimi uzyskuje się dzięki warunkowi, który ma postać równości identyfikatorów:
Prac.Mgr = Kier.EmpnoNajbardziej naturalnym rodzajem złączenia jest złączenie przez związek klucz obcy -> klucz główny. Jednak w ogólności warunek złączenia dwóch (lub więcej) tabel może być zupełnie dowolny. W przypadku naszej przykładowej bazy danych jest jeszcze jedno złączenie mające naturalny charakter, które przedstawimy w następującym zadaniu.
Przykład
| Wypisz wszystkich pracowników podając grupę zaszeregowania ich zarobków. |
SELECT e.Empno, e.Ename, s.Grade
FROM Emp e, Salgrade s
WHERE e.Sal BETWEEN s.Losal AND s.Hisal; EMPNO ENAME GRADE
---------- ---------- ----------
7369 SMITH 1
7876 ADAMS 1
7900 JAMES 1
7521 WARD 2
7654 MARTIN 2
7934 MILLER 2
7499 ALLEN 3
7844 TURNER 3
7566 JONES 4
7698 BLAKE 4
7782 CLARK 4
7788 SCOTT 4
7902 FORD 4
7839 KING 5
Zauważmy, że Sal to kolumna z tabeli Emp, natomiast Losal i
Hisal to kolumny z tabeli Salgrade. Zakładamy tutaj, że
przedziały
określone przez pary wartości
(Losal, Hisal) pokrywają cały dopuszczalny zakres zarobków pracowników. A więc, dla każdej wartości zarobków jest określona dokładnie jedna taka para zawierająca tę wartość
Sal. Wiersz każdego pracownika zostaje złączony z dokładnie jednym wierszem z tabeli
Salgrade, mianowicie z tym, do którego przedziału [Losal, Hisal]
wpadają zarobki danego pracownika. (Jest
pewna niezgodność między wymaganiem pokrycia przestrzeni możliwych wartości
przez przedziały a zastosowaniem predykatu BETWEEN, który sugeruje przedziały
domknięte, ale wtedy punkty graniczne między przedziałami wpadałyby do dwóch
przedziałów i byłyby dwukrotnie zwracane przez warunek BETWEEN. W naszym
przykładzie rozwiązaliśmy ten problem kładąc prawy koniec przedziału o
jeden mniejszym niż lewy koniec następnego przedziału i zakładając, że
zarobki pracowników są liczbami całkowitymi. W ten sposób uzyskujemy rozłączne
pokrycie zbioru wszystkich możliwych zarobków.
Oto semantyka zapytania ze złączeniami tabel.
Zasady wykonywania zapytania z listą tabel w klauzuli FROM (semantyka)
1. Jeśli występuje operator algebraiczny UNION, UNION ALL, INTERSECT
lub EXCEPT, to powtórz kroki 2-5 dla każdego składnika. |
Dane w jednej lub więcej tabel mogą zostać podsumowane przy użyciu jednej z funkcji sumarycznych. W efekcie uzyskujemy jeden wiersz wynikowy. Są następujące jedno-argumentowe funkcje sumaryczne (agregujące):
|
W Oracle dodatkowo są jeszcze STDDEV – standardowe odchylenie i
VARIANCE - wariancja. Argumentem tych funkcji może być
wyrażenie (odpowiedniego typu) lub
DISTINCT wyrażenie
W przypadku COUNT argumentem może być też symbol *. COUNT(*) oznacza liczbę wszystkich wierszy spełniających warunek WHERE.
Jeśli wartością wyrażenia jest Null, to ta konkretna wartość nie jest brana pod uwagę przy obliczaniu wartości funkcji. A więc np. przy obliczaniu
SUM(Sal), nie bierzemy pod uwagę wartości
Null, chyba że kolumna Sal zawiera same wartości Null – w takim przypadku
SUM(Sal)jest
Null.
Przykład
| Wyznacz liczbę pracowników firmy. |
SELECT COUNT(*) AS "Liczba pracowników firmy"
FROM Emp;Liczba pracowników firmy
------------------------
14
Przykład| Wyznacz statystyki zarobków pracowników pracujących w dziale Sprzedaży. |
SELECT MIN(e.Sal) AS "Min zarobki",
MAX(e.Sal) AS "Max zarobki",
MAX(e.Sal)- MIN(e.Sal) AS "Rozp zarobków",
AVG(e.Sal) AS "Śred zarobki"
FROM Emp e JOIN Dept d ON e.Deptno = d.Deptno
WHERE d.Dname = 'SALES';Min zarobki Max zarobki Rozp zarobków Śred zarobki
----------- ----------- ------------- ------------
950 2850 1900 1566.66667
Gdy w obliczeniach wartości podsumowujących chcemy uwzględnić pseudo-wartości
NULL, używamy funkcji NVL. Następujące zapytanie liczy średnią wartość prowizji interpretując
NULL jako 0.
SELECT AVG(NVL(e.comm,0)) "Średnia prowizja"
FROM Emp e;Średnia prowizja
----------------
157.142857
W języku SQL jest możliwość podziału wynikowych wierszy zapytania na grupy i wykonania funkcji sumarycznych na wartościach należących do poszczególnych grup.
| SELECT … FROM … WHERE … GROUP BY wyrażenie,... [HAVING warunek] |
| Podziel pracowników na grupy zaliczając do jednej grupy pracowników pracujących w jednym dziale, następnie dla każdej grupy oblicz ich liczbę i sumaryczne zarobki. |
Wersja 1 (bez załączania nazwy działu)
SELECT e.Deptno Id, COUNT(*) Liczba, SUM(e.Sal) Suma
FROM Emp e
GROUP BY e.Deptno; ID LICZBA SUMA
---------- ---------- ----------
10 3 8750
20 5 10875
30 6 9400
Wersja 2 (z załączeniem nazwy działu)
SELECT d.Deptno Id, d.Dname Nazwa_działu, COUNT(*) Liczba, SUM(e.Sal) Suma
FROM Dept d JOIN Emp e ON d.Deptno = e.Deptno
GROUP BY d.Deptno, d.Dname; ID NAZWA_DZIAŁU LICZBA SUMA
---------- -------------- ---------- ----------
10 ACCOUNTING 3 8750
20 RESEARCH 5 10875
30 SALES 6 9400
W drugim przypadku grupowanie odbywa się po złączeniu obu tabel Dept i
Emp. Kolumny, względem których następuje podział na grupy, nazywają się
kolumnami grupującymi. W powyższych przykładach za pierwszym razem jest to tylko
Dept.Deptno, za drugim razem Dept.Deptno i Dept.Dname.
Kolumna Dept.Dname, chociaż o wartościach zdeterminowanych przez wartości w kolumnie Dept.Deptno, musi zostać wpisana na listę GROUP BY, ponieważ kolumna Dept.Dname znajduje się na liście SELECT – zwróć uwagę na podpunkt poniżej "Ograniczenia".
W kolejnym przykładzie na liście GROUP BY znajdują się dwie kolumny Deptno i
Job. W pierwszej kolejności wiersze są grupowane według wartości Deptno, a w ramach grupy o tych samych wartościach
Deptno dokonujemy jeszcze podziału na grupy względem wartości Job – uzyskując ostateczny podział na grupy. Dla każdej ostatecznej grupy dokonujemy sumowania zarobków pracowników zaliczonych do tej grupy o tych samych wartościach
Deptno i Job
SELECT e.Deptno, e.Job, SUM(e.Sal)
FROM Emp e
GROUP BY e.Deptno, e.Job;
DEPTNO JOB SUM(SAL)
---------- --------- ----------
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
Zwróćmy uwagę na to, że podczas grupowania nastąpiło sortowanie względem wartości kolumn grupujących. Nie należy jednak tego zakładać, ponieważ grupowanie może być także zrealizowane poprzez haszowanie. W następnej wersji serwera, wynik grupowania wcale nie musi być więc posortowany.
Można ograniczyć wypisywane grupy formułując warunek dla grup. Np. aby ograniczyć się do działów zatrudniających co najmniej 5 pracowników, dołączylibyśmy warunek HAVING:
HAVING COUNT(*)>=5SELECT e.Deptno Id, COUNT(*) Liczba, SUM(e.Sal) Suma
FROM Emp e
GROUP BY e.Deptno
HAVING COUNT(*)>=5;
ID LICZBA SUMA
---------- ---------- ----------
20 5 10875
30 6 9400
W wyniku zabrakło jednego wiersza o wartości LICZBA = 3, który występował w wersji bez klauzuli HAVING:
ID LICZBA SUMA
---------- ---------- ----------
10 3 8750
Należy pamiętać, że najpierw jest stosowany warunek WHERE ograniczający rozpatrywane wiersze z połączonych tabel. Otrzymane wiersze są następnie grupowane, po czym jest stosowany warunek HAVING ograniczający wypisywane grupy.
Należy pamiętać o następujących zasadach dotyczących grupowania.
|
Ograniczenia dla klauzuli GROUP BY
|
Oto semantyka zapytania z klauzulami GROUP BY i HAVING.
Zasady wykonywania zapytania grupującego (semantyka)
1. Jeśli występuje operator algebraiczny UNION, UNION ALL, INTERSECT
lub EXCEPT, to powtórz kroki 2-7 dla każdego składnika. |
Wewnątrz klauzul WHERE i HAVING, a także SELECT 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 argument predykatów =, <, <=, >, >=, <>, IN, NOT IN, przy czym w przypadku predykatów =, <, <=, >, >=, <>, powinno określać jedną wartość, a w przypadku wystąpienia po prawej stronie predykatów IN oraz NOT IN listę wartości. (W poprzednich wersjach zarówno Standardu jak i systemu Oracle istniało ograniczenie podzapytania tylko do prawego argumentu.)
W Standardzie SQL w podzapytaniu nie można używać klauzul ORDER BY ani UNION. W przypadku podzapytań w Oracle mogą występować operatory UNION, MINUS i INTERSECT. W podzapytaniu dostępne są nazwy kolumn wprowadzone w głównym zapytaniu.
Wyróżniamy dwa rodzaje podzapytań: zwykłe i skorelowane.
Podzapytanie nazywamy zwykłym jeśli nie zawiera odwołań do kolumn tabel określonych w głównym zapytaniu. Podzapytanie nazywamy skorelowanym jeśli zawiera odwołania do kolumn tabel określonych w głównym zapytaniu.
W podzapytaniu zwykłym zbiór wynikowych wierszy nie zmienia się i nie zależy od wierszy w głównym zapytaniu.
Przykład
| Wypisz osoby, które zarabiają najwięcej ze wszystkich pracowników. |
Rozwiązując to zadanie najpierw liczymy największe Sal za pomocą zapytania:
SELECT Max(e.Sal) FROM Emp e;
SELECT e.Ename, e.Sal
FROM Emp e
WHERE e.Sal = (SELECT Max(f.Sal) FROM Emp f);W klauzuli WHERE może być więcej niż jedno podzapytanie.
Przykład
| 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. |
SELECT e.Ename, e.Job
FROM Emp e
WHERE e.Job = (SELECT f.Job
FROM Emp f WHERE f.Empno = 7369)
AND e.Sal > (SELECT g.Sal
FROM Emp g WHERE g.Empno = 7876);ENAME JOB ---------- --------- MILLER CLERK
Gdy podzapytanie zwraca więcej niż jedną wartość, zamiast operatora = stosuje się operator IN.
Przykład
| Wypisz działy, w których pracują urzędnicy. |
SELECT d.Dname
FROM Dept d
WHERE d.Deptno IN (SELECT e.Deptno FROM Emp e WHERE e.Job = 'CLERK');
DNAME -------------- ACCOUNTING RESEARCH SALESPewne problemy mogą się pojawić przy stosowaniu operatora NOT IN w sytuacji, gdy w wyniku podzapytania występuje pseudo-wartość NULL, gdyż o żadnej wartości nie da się stwierdzić przez porównanie, że jest różna od NULL. Z tego powodu następujące 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);
SELECT Prac.Ename
FROM Emp Prac
WHERE Prac.Empno NOT IN
(SELECT Podw.Mgr FROM Emp Podw
WHERE Podw.Mgr IS NOT NULL);W przeciwieństwie do podzapytania zwykłego w podzapytaniu skorelowanym zbiór wyników podzapytania zależy od wartości występujących w wierszach w głównym zapytaniu.
Przykład
| Dla każdego działu wypisz osobę, która zarabia najwięcej w tym dziale. |
SELECT Max(e.Sal)
FROM Emp e
WHERE e.Deptno= <Deptno określone w głównym zapytaniu>
SELECT e.Deptno, e.Ename, e.Sal
FROM Emp e
WHERE e.Sal = (SELECT Max(f.Sal)FROM Emp f WHERE f.Deptno= e.Deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 BLAKE 2850
20 SCOTT 3000
10 KING 5000
20 FORD 3000
Predykaty porównań można łączyć ze słowami kluczowymi oznaczającymi kwantyfikatory:
SOME
(równoważne
ANY) - odczytywane
"dla pewnego" i ALL - odczytywane "dla wszystkich" lub
"dla każdego", otrzymując predykaty, których prawym argumentem może być lista wyrażeń lub
podzapytanie - objęte nawiasami. Składnia jest następująca:
| wyrażenie operator_porównania [ANY|SOME|ALL][lista_wyrażeń|(podzapytanie)] |
Przykład
10000 >= ALL (SELECT Sal FROM Emp)
1000 >= SOME (SELECT Sal FROM Emp)Są też dwa predykaty EXISTS i NOT EXISTS sprawdzające czy podzapytanie daje pusty zbiór wyników czy nie, np.
EXISTS(SELECT 'x' FROM Emp WHERE Deptno= 10)
Przykład
| Wypisz działy, w których aktualnie nie ma zatrudnionych pracowników. |
SELECT DISTINCT d.Dname
FROM Dept d
WHERE NOT EXISTS (SELECT 'x' FROM Emp e WHERE e.Deptno = d.Deptno);
DNAME -------------- OPERATIONSZa pomocą predykatu NOT EXISTS można rozwiązać problem wyznaczenia wszystkich pracowników, którzy nie są kierownikami - nie ma tu problemu (jak w przypadku predykatu NOT IN) z wystąpieniem pseudo-wartości NULL w wyniku podzapytania.
SELECT e.Empno, e.Ename, e.Job, e.Deptno
FROM Emp e
WHERE NOT EXISTS (SELECT 'x'
FROM Emp f
WHERE f.Mgr = e.Empno);
EMPNO ENAME JOB DEPTNO
---------- ---------- --------- ----------
7369 SMITH CLERK 20
7499 ALLEN SALESMAN 30
7521 WARD SALESMAN 30
7654 MARTIN SALESMAN 30
7844 TURNER SALESMAN 30
7876 ADAMS CLERK 20
7900 JAMES CLERK 30
7934 MILLER CLERK 10
Predykaty EXISTS i NOT EXISTS odpowiadają bezpośrednio odpowiednio kwantyfikatorowi egzystencjalnemu i kwantyfikatorowi uniwersalnemu z negacją objętej nim formuły, gdzie zmienna związana kwantyfikatora jest zmienną wiersza zapytania. Daje to silne narzędzie do rozwiązywania skomplikowanych zadań za pomocą zapytań SQL.
W Standardzie występuje jeszcze predykat:
| UNIQUE (podzapytanie) |
Przykład
Predykat
UNIQUE (SELECT Ename FROM Emp)
daje wartość True, gdy nazwiska pracowników nie powtarzają się, w przeciwnym razie
False.
Podzapytania są na ogół częścią składową warunków w klauzulach WHERE i HAVING. Przypominamy, że klauzula WHERE występuje również w instrukcjach UPDATE i DELETE. Rozważymy przykład podzapytania skorelowanego występującego w instrukcji UPDATE.
Przykład
| Zwiększmy zarobki wszystkim pracownikom pracującym w Dallas. |
UPDATE Emp e
SET e.Sal = e.Sal * 1.1
WHERE 'DALLAS' =
(SELECT d.Loc
FROM Dept d
WHERE e.Deptno = d.Deptno);
Podzapytania mogą również występować w innych miejscach niż warunki. Oto te sytuacje.
Przykład
| Oblicz procentowy udział poszczególnych 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
Konstrukcja ta umożliwia niezależne przeprowadzenie kilku obliczeń, a następnie umieszczenie otrzymanych wyników w głównym zapytaniu!
Przykład
| Dla każdego działu policz ilu pracowników jest w nim zatrudnionych. |
SELECT d.Deptno "Dział",
(SELECT COUNT(*) FROM Emp e WHERE
e.Deptno=d.Deptno) AS "#Pracowników"
FROM Dept d;
Dział #Pracowników
---------- ------------
10 3
20 5
30 6
40 0
Konstrukcja ta umożliwia zastosowanie strukturalnego podejścia przy rozwiązywaniu
problemów. Podproblem "wyznacz liczbę pracowników w danym dziale"
jest rozwiązywany niezależnie od głównego problemu "wypisz działy".
Przykład
| 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.
Przykład
| Wstaw do pomocniczej tabeli wszystkich pracowników zatrudnionych w okresie ostatnich 10 dni. |
INSERT INTO Emp_new
SELECT *
FROM Emp
WHERE Sysdate - Hiredate < 10;W Standardzie podzapytanie jest zawsze ujmowane w nawiasy (w systemie Oracle nie zawsze).
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.
Przykład
| Wypisz wszystkich pracowników podając dla każdego z nich nazwę działu, w którym pracuje. |
Zapytanie:
SELECT e.Empno, e.Ename, d.Dname
FROM Emp e LEFT JOIN Dept d ON e.Deptno = d.Deptno; EMPNO ENAME DNAME
---------- ---------- --------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7788 SCOTT RESEARCH
7839 KING ACCOUNTING
7844 TURNER SALES
7876 ADAMS RESEARCH
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
7799 KOWALSKI
Zwróćmy uwagę na pojawienie się wiersza z nowym pracownikiem, który jako nie zaliczony do żadnego działu, nie pojawiłby się w wyniku zwykłego złączenia wewnętrznego JOIN a nie LEFT JOIN.
Przykład
| Wypisz wszystkie działy dla każdego z nich podając sumaryczne zarobki zatrudnionych w nim pracowników. |
SELECT d.Deptno, d.Dname, SUM(e.Sal)
FROM Emp e RIGHT JOIN Dept d ON e.Deptno = d.Deptno
GROUP BY d.Deptno, d.Dname;
DEPTNO DNAME SUM(SAL)
------ -------------- --------------
10 ACCOUNTING 8750
20 RESEARCH 1087
30 SALES 9400
40 OPERATIONS
Gdy w dziale nie ma żadnego pracownika, zbiór sumowanych wartości składa się z jednej wartości
Null. Zgodnie z przyjętymi zasadami, wynikiem sumowania jest wartość Null, reprezentowana na wydruku przez puste miejsce. Gdybyśmy chcieli w takim przypadku wypisać wartość 0, powinniśmy wyrażenie
SUM(Sal) umieścić jako argument funkcji NVL:
NVL(Sum(Sal),0)
SELECT d.Deptno, d.Dname, TO_CHAR(SUM(e.Sal))
FROM Emp e JOIN Dept d ON e.Deptno = d.Deptno
GROUP BY d.Deptno, d.Dname
UNION
SELECT d.Deptno, d.Dname, NULL
FROM Dept d
WHERE NOT EXISTS (SELECT 'x' FROM Emp f WHERE f.Deptno = d.Deptno);
DEPTNO DNAME TO_CHAR(SUM(SAL))
------ ------------- --------------------
10 ACCOUNTING 8750
20 RESEARCH 10875
30 SALES 9400
40 OPERATIONS
Oprócz wymienionych już operatrów złączeń istnieją jeszcze następujące
(T, U oznaczają tabele):
1. Złączenie krzyżowe T CROSS JOIN U - iloczyn kartezjański - wszystkie kombinacje wierszy:
SELECT *
FROM T CROSS JOIN U;
SELECT *
FROM T,U;
SELECT e.Ename, d.Dname
FROM Emp e FULL JOIN Dept d ON e.Deptno=d.Deptno;zestawia ze sobą nazwiska pracowników z nazwami departamentów, w których są zatrudnieni podając również pracowników, którzy nie zostali przypisani do żadnego departamentu a także nazwy departamentów, w których nie ma zatrudnionych pracowników.
Omawianie konstrukcji języka SQL będziemy kontynuować na następnym wykładzie.
W wykładzie 2 dokończyliśmy prezentację zapytań - czyli różnych postaci instrukcji
SELECT. Rozważyliśmy kolejno następujące konstrukcje: operatory algebraiczne
na zapytaniach, operatory złączenia
(wewnętrznego),
zapytania sumaryczne, podzapytania, operatory złączenia zewnętrznego.
UNION, UNION ALL - operatory sumowania zbiorów wyników zapytań.
INTERSECT - operator przecięcie zbiorów wyników zapytań.
EXCEPT - operator różnicy zbiorów wyników zapytań (w Oracle także MINUS).
złączenie tabel - połączenie ze sobą danych zawartych w dwóch tabelach. Wynikiem jest też tabela.
JOIN - operator złączenia występujący w klauzuli FROM.
zapytanie sumaryczne (podsumowujące) - zapytanie wykonujące operacje sumaryczne na kolumnach wartości. Wynikiem jest tabela złożona z pojedynczego wiersza podsumowań.
zapytanie grupujące - zapytanie wykonujące najpierw podział wierszy na grupy a następnie operacje sumaryczne na kolumnach wartości - osobno dla każdej grupy. Wynikiem jest tabela złożona z tylu wierszy podsumowań ile jest grup.
podzapytanie - zapytanie występujące jako część innego zapytania.
złączenie zewnętrzne - specjalny rodzaj złączenia powodujący, że w wyniku zapytania znajdą się wszystkie wiersze z danej tabeli a nie tylko te które można uzgodnić z wierszami drugiej tabeli.
(na układanie zapytań do baz danych)
Zadanie 1
Dla bazy danych z wykładu:
Zestaw 1
1. Wyznacz pracowników, którzy zarabiają powyżej 2000 i których nazwiska nie zaczynają się na literę A. Uporządkuj według daty zatrudnienia.
2. Wybierz dane o pracownikach zatrudnionych na stanowisku MANAGER w NEW YORK lub CHICAGO. Uporządkuj według malejących zarobków.
3. Wypisz nazwiska i miejsca pracy osób, których zarobki znajdują się w czwartej klasie zaszeregowania. Uporządkuj według miejsca pracy.
4. Wybierz nazwiska pracowników, którzy pracują w tych samych działach co ich kierownicy. Obok nazwiska pracownika zamieść nazwisko jego kierownika i nazwę działu.
5. Wyznacz stanowiska (bez powtórzeń), na których nie ma prowizji. Uporządkuj wynik według liczby liter w stanowisku.
Wskazówka: Użyj funkcji Length(w) – zwracającej długość napisu w, i ewentualnie, w zależności jaki jest typ danych kolumny Ename: CHAR czy VARCHAR2, zastosuj funkcję RTrim(w) - usuwającą z napisu wszystkie kończące go spacje.
6. Ustaw w pary pracowników, którzy pracują w tym samym dziale. Nazwiska w parze powinny być różne i pary nie powinny się powtarzać.
7. Dla każdego działu wyznacz nazwisko pracownika pojawiające się jako pierwsze w porządku alfabetycznym.
8. Wybierz kierowników (pracowników pracujących na stanowisku MANAGER), którzy mają dokładnie dwóch podwładnych. Podaj, gdzie oni pracują i w jakiej klasie zaszeregowania są ich zarobki.
9. Wyznacz nazwę działu, w którym pracownicy zarabiają (sumarycznie) najwięcej oraz nazwę działu, w którym pracownicy zarabiają (sumarycznie) najmniej. Wynik zapisz w postaci jednego zdania:
Pracownicy zarabiają najwięcej w dziale ...., a najmniej w dziale ...... Różnica wynosi ....
Zestaw 2
1. Wyznacz pracowników, którzy zarabiają poniżej 1500 zł i których nazwiska nie kończą się na literę S. Uporządkuj według daty zatrudnienia.
2. Wybierz dane o pracownikach zatrudnionych na stanowisku MANAGER w DALLAS lub BOSTON. Uporządkuj według malejących zarobków.
3. Wypisz nazwiska i miejsca pracy osób, których zarobki znajdują się w trzeciej klasie zaszeregowania. Uporządkuj według nazwy działu.
4. Wybierz nazwiska pracowników, których zarobki są w tej samej klasie zaszeregowania co zarobki ich kierowników. Obok nazwiska pracownika zamieść nazwisko jego kierownika i klasę zaszeregowania zarobków tego kierownika.
5. Wyznacz stanowiska (bez powtórzeń), na których nikt nie dostał prowizji. Uporządkuj wyniki według liczby liter w stanowisku.
6. Ustaw w pary pracowników, którzy mają tego samego kierownika. Nazwiska w parze powinny być różne i pary nie powinny się powtarzać.
7. Dla każdego działu wyznacz nazwisko pracownika ostatnie w porządku alfabetycznym.
8. Wybierz kierowników (pracowników pracujących na stanowisku MANAGER), którzy mają dokładnie jednego podwładnego. Podaj, gdzie oni pracują i w jakiej klasie zaszeregowania są ich zarobki.
9. Znajdź nazwę działu, w którym pracownicy zarabiają średnio najwięcej oraz nazwę działu, w którym pracownicy zarabiają średnio najmniej. Wynik zapisz w postaci jednego zdania:
Pracownicy zarabiają najwięcej w dziale ...., a najmniej w dziale ...... Różnica wynosi ....
Zadanie 2
Baza danych ma następujący schemat:
| Dept(NumDz,NazwaDz,NumKier) Prac(NumP,Nazw,Imię,DataUr,DataZatr,NumDz,Zarob,Piętro) Pracuje(NumP,NumProj,Rola) Projekty(NumProj,NazwaProj,Budżet) |
(Atrybuty NumP, NumDz, NumKier, Piętro, NumProj, Zarob i Budżet są numeryczne, DataUr i DataZatr - datowe, pozostałe - napisowe).
Ułóż zapytania realizujące następujące polecenia:
1. Wypisz nazwiska wszystkich kierowników wraz z nazwą działu, którego są kierownikami.
2. Wypisz nazwiska wszystkich pracowników, którzy pracują w tym samym projekcie w dwóch różnych rolach lub w tej samej roli w dwóch różnych projektach.
3. Sprawdź, czy w firmie w tym samym projekcie pracują dwie różne osoby o tym samym imieniu.
4. Wypisz nazwiska wszystkich pracowników, którzy nie pracują w projekcie o numerze 121.
5. Wypisz nazwiska wszystkich pracowników, którzy pracują w każdym projekcie.
6. Wypisz wszystkie pary pracowników, którzy pracują w tym samym dziale (uwaga: (121,133) i (133,121) określają tę samą parę).
7. Wypisz pełną informację o każdym pracowniku: numer, nazwisko, wiek (w pełnych latach), w którym roku udaje się na emeryturę (według zasady: mężczyźni w wieku 65 lat, kobiety w wieku 60 lat), nazwa działu, nazwisko kierownika, w ilu projektach pracuje i jaki jest łączny budżet tych projektów.
8. Wyznacz ilu pracowników pracuje w projekcie o numerze 121 i jakie są ich średnie zarobki.
9. Sprawdź, czy w każdym projekcie pracują pracownicy tylko jednego działu.
10. Wypisz role pracowników w projektach; dla każdej roli podaj ilu pracowników pełni tę rolę i jakie są ich średnie
zarobki.
Zadanie 3
Dany jest schemat relacyjnej bazy danych:
|
Autorzy(Id_autora,Imię, Nazwisko) Książki(ISBN,Wydawca,Tytuł,Rok) Autorstwa(ISBN,Id_autora) Tematy(Nrtematu,Nazwa,Wyjaśnienie) Na_tematy(ISBN, Nrtematu) |
2. Napisz zapytania:
· wypisujące nazwiska autorów, którzy napisali przynajmniej jedną książkę na temat baz danych;
· znajdujące imię i nazwisko autora, który napisał najwięcej książek na temat baz danych.
3. Biblioteka kupuje nową książkę. Napisz instrukcje SQL, które uaktualniają stan bazy danych w odniesieniu do tabel AUTORZY, AUTORSTWA i KSIAŻKI.
Zadanie 4
Dany jest schemat relacyjnej bazy danych:
| Pacjenci(Nr_pacjenta, Imię, Nazwisko) Choroby(Nr_choroby, Nazwa) Lekarze(Nr_lekarza, Nazwisko) Karty_chorób(Nr_pacjenta, Nr_choroby, Nr_lekarza, Opis) |
1. Napisz instrukcje CREATE TABLE zakładające powyższe tabele i definiujące odpowiednie więzy referencyjne pokazane na rysunku za pomocą strzałek.
2. Napisz zapytania:
· wypisujące nazwiska pacjentów, którzy leczą się na katar;
· znajdujące imię i nazwisko lekarza, który leczy najwięcej chorych.
3. Do kliniki przychodzi nowy pacjent chory na bezsenność. Napisz instrukcje SQL, które uaktualniają stan bazy danych (wpisują jego dane do bazy danych, jak i informację, który lekarz ma go leczyć - określ kryterium wyboru tego lekarza; zakładamy, że bezsenność jest chorobą, którą leczy się w klinice).
Zadanie 5
Dany jest schemat relacyjnej bazy danych:
| Autobusy(Nr_autob, Nr_linii, Nazw_kier) Linie(Nr_linii, Rodzaj) Przystanki(Nr_przyst, Nazwa_ulicy) Prz_linii(Nr_linii, Nr_przyst, Nr_kolejny) |
Zakładamy, że Nazw_kier stanowi klucz jednoznaczny (UNIQUE) w tabeli AUTOBUSY.
1. Napisz instrukcje CREATE TABLE zakładające powyższe tabele i definiujące odpowiednie więzy referencyjne.
2. Napisz zapytania:
· wypisujące nazwy ulic, na których znajdują się przystanki linii obsługiwanej przez autobus prowadzony przez kierowcę o nazwisku 'Kowalski' (w kolejności wzrastających numerów Nr_kolejny);
· znajdujące przystanek (Nr_przyst i Nazwa_ulicy), który jest przystankiem największej liczby linii;
· sprawdzające, czy każdy przystanek jest przystankiem pewnej linii.
Zadanie 6
Baza danych ma następujący schemat:
| Nauczyciele(Num_naucz, Nazwisko, Adres, Id_przedm) Przedmioty(Id_przedm, Opis) Rozkład(Dzień, Godz, Id_klasy, Id_przedm, Num_naucz) Klasy(Id_klasy, Sala, Num_wychowawcy) Uczniowie(Num_ucznia, Nazwisko, Id_klasy) Stopnie(Num_ucznia, Id_przedm, Ocena) |
1. Dla ucznia Józia wypisz jego nauczycieli.
2. Dla każdego nauczyciela oblicz ile ma godzin zajęć tygodniowo.
3. Znajdź najlepszego ucznia w szkole.
4. Wypisz uczniów, których średnia ocen jest wyższa od średniej ocen w szkole.
5. Znajdź klasę, w której średnia ocen jest najwyższa w szkole.
6. Sprawdź, czy każdy nauczyciel o każdej godzinie ma zaplanowane co najwyżej jedno zajęcie.
7. Sprawdź, czy w szkole są nauczyciele, którzy uczą w każdej klasie.
8. Przygotuj tygodniowy plan lekcji dla Józia.
Zadanie 7
Baza danych ma następujący schemat:
| Bywa(Piwosz, Bar) Podają(Bar, Piwo, Cena) Lubi(Piwosz, Piwo) |
1. Wypisz bary, w których jest podawane piwo lubiane przez Kowalskiego.
2. Sprawdź, ile gatunków piwa jest podawanych w barze 'Źródełko'.
3. Znajdź bar, w którym wybór piw jest największy.
4. Sprawdź, czy jest piwosz, który lubi tylko jeden gatunek piwa i chodzi tylko do jednego baru.
5. Wypisz gatunki piwa, których nikt nie lubi.
6. Wypisz piwoszy, którzy chodzą do baru, gdzie podaje się lubiane przez nich piwo.
7. Wypisz piwoszy, którzy odwiedzają wyłącznie bary, w których jest podawane lubiane przez nich piwo.
8. Wypisz piwoszy nie chodzących w ogóle do barów, gdzie podaje się lubiane przez nich piwo.
9. Sprawdź, czy Kowalski chodzi wyłącznie do barów, gdzie jest podawane tylko lubiane przez niego piwo.
10. Każdemu piwoszowi wskaż bar, w którym może się napić najtańszego lubianego przez niego piwa.
11. Wypisz 10 najtańszych gatunków piwa.
12. Wypisz 10 najpopularniejszych gatunków piwa.
13. Wypisz wszystkich piwoszy oznaczając gwiazdką * tych, którzy chodzą do największej liczby barów.
Zadanie 8. (Konta bankowe)
Baza danych ma następujący schemat:
| Banki(Nr_banku,Nazwa,Adres_centrali) OdDept(Nr_banku,Kod_oddz,Adres) Klienci(Nr_banku,Kod_oddz,Nr_kl,Pesel,Imię,Nazw,Adres) Konta(Nr_banku,Kod_oddz,Nr_kl,Nr_konta,Data_otw,Saldo) |
2. Wypisz imiona i nazwiska wszystkich osób opisanych w naszej bazie. Dla każdej z nich podsumuj salda jej kont.
3. Wypisz listę kont prowadzonych przez PKO BP, wraz z personaliami ich właścicieli.
4. Znajdź bank, który ma największą liczbę oddziałów.
5. Oblicz średnie saldo kont otwartych w 1995 roku.
6. Wypisz personalia klientów XIII Oddziału PBK SA.
7. Znajdź najlepszego klienta każdego oddziału, tzn. takiego, który łącznie zgromadził najwięcej pieniędzy na swych kontach.
8. Znajdź oddziały, które nie mają ani jednego klienta.
9. Oblicz liczbę klientów, którzy nie mają otwartego ani jednego konta.
10. Znajdź bank, który prowadzi największą liczbę kont.
11. Wypisz pary różnych oddziałów, które mają takie samo średnie saldo kont.
Zadanie 9 (Biblioteka)
Baza danych ma następujący schemat:
Dzieła(Kod, Tytuł,Imię_autora,Nazwisko_autora) Egzemplarze(Numer,Kod_dzieła,Numer_wypożycz) Czytelnicy(Numer,Imię,Nazwisko,Adres,Quota) |
Kolumny Egzemplarze.Numer, Czytelnik.Numer, Numer_wypożycz i Quota są typu numerycznego, pozostałe atrybuty są napisami. Zakładamy, że Numer_wypożycz ma wartość Null, gdy egzemplarz nie jest wypożyczony. Quota to maksymalna liczba książek, jaką czytelnik może wypożyczyć.
Ułóż zapytania realizujące niżej wymienione zadania. Przy każdym zapytaniu postaraj się wypisać dane w sposób jak najbardziej czytelny dla użytkownika wybierając odpowiednią kolejność kolumn i uporządkowanie wierszy.
1. Wypisz listę numerów wypożyczonych egzemplarzy książek i personalia czytelników, którzy te egzemplarze pożyczyli.
2. Wypisz listę nie wypożyczonych dzieł.
3. Oblicz średnią liczbę egzemplarzy wszystkich dzieł.
4. Znajdź dzieła, których egzemplarzy nie ma i nigdy nie było w bibliotece.
5. Znajdź osoby, które przekroczyły swoje quota.
6. Znajdź imiona i nazwiska osób, które wypożyczyły egzemplarz dzieła o największej liczbie egzemplarzy.
7. Znajdź liczbę egzemplarzy każdego dzieła Adama Mickiewicza.
8. Znajdź czytelników, którzy wypożyczyli egzemplarz dzieła i mają to samo imię i nazwisko co autor tego dzieła.
9. Znajdź pary osób, które wypożyczyły różne egzemplarze tego samego dzieła.