Początki języka SQL3
Początki języka SQL3
• Konsekwencja przenikania się koncepcji
obiektowych do systemów relacyjnych.
• Ulepszenie poprzednich wersji SQL.
Po co nam nowa wersja?
Kto z tego korzysta?
• Adresatem SQL3 są systemy określane
jako “obiektowo-relacyjne”. Czyli takie
które z systemów relacyjnych przechodzą
w kierunku obiektowości.
• Przykłady:
1. Sybase Adaptive Server (firmy SYBASE)
2. Oracle8 (firmy ORACLE)
Status standaryzacji SQL3
Kto dba o standaryzację?
• SQL3 jest kontynuacją linii SQL-92
• Rozwijana przez:
• ANSI (American National Standard Institute)
• ISO (International Standard Organization)
• Komitet X3H2
SQL3 w stosunku do SQL-92
• Obiektowość – dodaje niektóre cechy
obiektowości do tablic znanych z systemów
relacyjnych. Jest to podejście „hybrydowe”.
• Rozszerzalność – umożliwienie użytkownikom
definiowania własnych typów.
• Niekonwencjonalne typy danych –
multimedialne, przestrzenne, temporalne.
Kluczowe fakty
Opóźnienia
• Planowane zakończenie prac: IV kwartał
1998r.
• Oficjalne zakończenie prac nad SQL3
(zwanego także SQL:99): początek 1999r.
• Powody opóźnienia:
– Głównym powodem jest fakt, że standard jest
ogromny, według różnych szacunków posiada
1200 - 1600 stron, zaś jego poszczególne
części są niezbyt spójne.
Hierarchia priorytetów
To prosta matematyka!
• Opiera się na podstawowych
działaniach matematycznych.
• Aby zmienić hierarchię obliczeń
należy wstawić nawiasy kwadratowe.
•
Operatory
• Wszystkie operatory porównywania i operatory
SQL mają jednakowy, najwyższy priorytet:
=, !=, <, >, <=, >=, BETWEEN, AND, …, IN,
LIKE, IS NULL
• AND, przedostatni operator w hierarchii.
• OR, operator o najniższym priorytecie.
Zalecane jest używanie nawiasów przy skomplikowanych złożeniach
operatorów.
Operatory cz. 2
SELECT
*
FROM
Pracownicy
WHERE
Stanowisko = ’Manager’
OR
(
Stanowisko = ’Sprzedawca’
AND
Departament =
10
)
;
• Przykład działania hierarchii operatorów:
Warunki złożone
Jak są zbudowane?
• Warunki złożone zbudowane są z
warunków prostych powiązanych
spójnikami logicznymi AND i OR.
Typy zapytań do baz
danych
Najważniejsze zapytania
• SELECT – pobranie z bazy danych,
• INSERT – umieszczanie danych w bazie,
• UPDATE – zmiana danych,
• DELETE – usunięcie danych z bazy.
Zapytania z listą tabel w
klauzuli FROM
Przykład 1
• Przy pobieraniu danych z więcej niż jednej
tabeli NALEŻY nadać każdej tabeli alias, a
następnie odwoływać się do pól tej tabeli
przez nadany alias.
SELECT
[
alias tabeli 1
]
.
[
nazwa kolumny
]
,
[
alias tabeli 2
]
.
[
nazwa kolumny
]
,
[
alias tabeli 3
]
.
[
nazwa kolumny
]
,
…
FROM
[
alias tabeli 1
]
.
[
nazwa kolumny
]
,
[
alias tabeli 2
]
.
[
nazwa kolumny
]
,
[
alias tabeli 3
]
.
[
nazwa kolumny
]
,
…
Przykład 2
I
d
Imię
Nazwis
ko
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrows
ki
4 Magdale
na
Krzyżak
I
d
Id
Klienta
Treść
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok, do
zobacz…
4 1
Będę za 15
min
Imię
Nazwis
ko
I
d
Treść
Michał
Kowalsk
i
1
Siema
stary…
Magdale
na
Krzyżak 2
Jest
problem…
Michał
Kowalsk
i
3
Ok, do
zobacz…
Agata
Nowak
4
Będę za 15
min
Kod źródłowy do przykładu
2
SELECT
k.Imie, k. Nazwisko, s.Id, s.Tresc
FROM
Klienci k, Smsy s
WHERE
k.Id = s.IdKlienta
Iloczyn kartezjański
Przykład 1
I
d
Imię
Nazwis
ko
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrows
ki
4 Magdale
na
Krzyżak
I
d
Id
Klienta
Treść
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok, do
zobacz…
4 1
Będę za 15
min
Imię
Nazwis
ko
I
d
Treść
Agata
Nowak
1
Siema
stary…
Michał
Kowalski 1
Siema
stary…
Cezary
Dąbrows
ki
1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Kod źródłowy do przykładu
1
SELECT
k.Imie, k. Nazwisko, s.Id, s.Tresc
FROM
Klienci k, Smsy s
WHERE
k.Id = s.IdKlienta
Przykład 2
I
d
Imię
Nazwis
ko
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrows
ki
4
Magdale
na
Krzyżak
I
d
Id
Klienta
Treść
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok, do
zobacz…
4
1
Będę za 15
min
Imię
Nazwis
ko
I
d
Treść
Agata
Nowak
1
Siema
stary…
Michał
Kowalski 1
Siema
stary…
Cezary
Dąbrows
ki
1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Agata
Nowak
2
Jest
problem…
Michał
Kowalski 2
Jest
problem…
Cezary
Dąbrows
ki
2
Jest
problem…
Magdale
na
Krzyżak
2
Jest
problem…
Kod źródłowy do przykładu
2
SELECT
k.Imie, k. Nazwisko, s.Id, s.Tresc
FROM
Klienci k, Smsy s
Przykład 3
I
d
Imię
Nazwis
ko
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrows
ki
4
Magdale
na
Krzyżak
I
d
Id
Klienta
Treść
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok, do
zobacz…
4
1
Będę za 15
min
Imię
Nazwis
ko
I
d
Treść
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrows
ki
1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Agata
Nowak
2
Jest
problem…
Michał
Kowalski
2
Jest
problem…
Cezary
Dąbrows
ki
2
Jest
problem…
Magdale
na
Krzyżak
2
Jest
problem…
Agata
Nowak
3
Ok, do
zobacz…
Michał
Kowalski
3
Ok, do
zobacz…
Cezary
Dąbrows
ki
3
Ok, do
zobacz…
Magdale
na
Krzyżak
3
Ok, do
zobacz…
Przykład 4
Id
Imię
Nazwisko
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrowski
4
Magdalena
Krzyżak
I
d
Id Klienta
Treść
1
2
Siema stary…
2
4
Jest problem…
3
2
Ok, do zobacz…
4
1
Będę za 15 min
Imię
Nazwisko
Id
Treść
Agata
Nowak
1
Siema stary…
Michał
Kowalski
1
Siema stary…
Cezary
Dąbrowski
1
Siema stary…
Magdalena
Krzyżak
1
Siema stary…
Agata
Nowak
2
Jest problem…
Michał
Kowalski
2
Jest problem…
Cezary
Dąbrowski
2
Jest problem…
Magdalena
Krzyżak
2
Jest problem…
Agata
Nowak
3
Ok, do zobacz…
Michał
Kowalski
3
Ok, do zobacz…
Cezary
Dąbrowski
3
Ok, do zobacz…
Magdalena
Krzyżak
3
Ok, do zobacz…
…
…
…
…
Instrukcje nadające i
odbierające uprawnienia
Jak działają?
• Instrukcje zabezpieczeń w języku SQL, które są
wobec siebie symetryczne.
• GRANT – nadaje uprawnienia użytkownikom
natomiast REVOKE je odbiera.
• Obie instrukcje podlegają pod DCL (Data
Control Language zawierająca się w Data
Definition Language) czyli podgrupa języka SQL
odpowiadającą za kontrolę na danymi w bazie.
Uprawnienia
• Użytkownik ma pełne prawa do wszelkich
utworzonych przez siebie obiektów w
bazie. Praw tych nie można mu
odebrać!
• Uprawnienia nadawane w języku SQL
dzielą się na dwie grupy:
– uprawnienia systemowe.
– uprawnienia do dostępu.
Uprawnienia systemowe
• Podlegają pod grupę DDL (Data Definition
Language) odpowiedzialną za definiowanie
danych języka SQL. Uprawnienia te
umożliwiają użycie instrukcji CREATE,
ALTER i DROP dla danego obiektu.
• Dostęp do uprawnienia CREATE [object]
automatycznie daje użytkownikowi
przywileje ALTER i DROP.
Uprawnienia do dostępu
• Podlegają pod grupę DML (Data Manipulation
Language) odpowiedzialną za modyfikacje
danych języka SQL. Podstawowe instrukcje z
tej grupy uprawnień to:
– SELECT (wybieranie)
– INSERT (wstawianie)
– UPDATE (aktualizowanie)
– DELETE (usuwanie)
– EXECUTE (wykonywanie przygotowanej
instrukcji)
GRANT
GRANT
lista_przywilejów
ON
nazwa_obiektu
TO
lista_użytkowników
[WITH GRANT OPTION]
;
Lista przywilejów
SELECT – odczytanie danych z tabeli.
INSERT [nazwy kolumny] – wstawianie danych do tabeli.
UPDATE [nazwy kolumny] – modyfikowanie danych w tabeli.
DELETE – usunięcie danych z tabeli.
REFERENCE – odwoływanie się do innych tabel.
EXECUTE – pozwala użytkownikowi na uruchomienie procedur.
CREATE – tworzenie nowych tabel i baz danych.
DROP – usuwanie tabel oraz baz danych.
ALTER – uaktualnienie struktury tabeli.
ALL PRIVILEGES – wszystkie dostępne uprawnienia.
INDEX – do wykonywania instrukcji CREATE INDEX.
GRANT cz. 2
GRANT
lista_przywilejów
ON
nazwa_obiektu
TO
lista_użytkowników
[WITH GRANT OPTION]
;
Obiektem może być tabela, baza,
perspektywa, sesja (połączenie z bazą
danych), trigger, sekwencja lub procedura.
GRANT cz. 3
GRANT
lista_przywilejów
ON
nazwa_obiektu
TO
lista_użytkowników
[WITH GRANT OPTION]
;
Podajemy nazwy użytkowników, którym
chcemy nadać prawa
lub
PUBLIC – nadanie praw wszystkim
użytkownikom.
GRANT cz. 4
GRANT
lista_przywilejów
ON
nazwa_obiektu
TO
lista_użytkowników
[WITH GRANT OPTION]
;
[WITH GRANT OPTION]
– dodanie tej
instrukcji na końcu zapytania umożliwi
nadawanie wcześniej przyznanych praw do
wybranego obiektu innym użytkownikom.
REVOKE
Budowa
REVOKE
<lista_przywilejów>
ON
<nazwa_obiektu>
TO
<lista_uzytkowników>
[WITH GRANT OPTION]
;
Odbieranie praw
• Podobne jak w przypadku GRANT w klauzuli REVOKE
mogą wystąpić ALL PRIVILEGES oraz PUBLIC.
• Jeżeli „Janek” otrzymał takie samo prawo od wielu
użytkowników to odebranie praw przez jednego
użytkownika nie powoduje odebrania tego prawa.
Dopiero, gdy wszystkie osoby, od których otrzymał dane
prawo odbiorą mu je, wtedy zostanie ono utracone.
• Jeśli użytkownik przekazał prawo, które uzyskał z klauzulą
GRANT OPTION, to odebranie mu tej opcji powoduje, że
prawo, które przekazał innym, jest im odbierane.
Przykład użycia
BIZNES
STUDENT
ADMIN
Ma wszystkie
przywileje
SELECT
INSERT
INSERT
REVOKE INSERT
ON
klienci
FROM
STUDENT
BIZNES:
Student ma
nadal przywileje
INSERT.
Ponieważ dostał
go również od
Admina.
Ponownie sytuacja początkowa
BIZNES
STUDENT
Ma wszystkie
przywileje
SELECT
INSERT
INSERT
REVOKE INSERT
ON
klienci
FROM
BIZNES
ADMIN:
ADMIN
REVOKE – przykład użycia
BIZNES
STUDENT
Ma wszystkie
przywileje
SELECT
INSERT
REVOKE INSERT
ON
klienci
FROM
STUDENT
ADMIN:
ADMIN
Dopiero teraz
STUDENT został
pozbawiony
przywileju
INSERT.
Nadanie praw wszystkim
użytkownikom ALL AD HOC
BIZNES
STUDENT
Ma wszystkie
przywileje
SELECT
INSERT
SELECT
GRAND SELECT
ON
klienci
TO
PUBLIC
ADMIN:
ADMIN
Przykład wykorzystujący obie
instrukcje
• Chcemy usunąć wszystkim użytkownikom
wszystkie uprawnienia z tabeli
Nauczyciele, następnie nadać uprawnienia
SELECT i INDEX każdemu użytkownikowi
oraz zaktualizować uprawnienia w
poszczególnych kolumnach dla
konkretnych użytkowników (z
możliwością nadawania przez nich
uprawnień innym użytkownikom).
Kod źródłowy
REVOKE ALL
ON
Nauczyciele
FROM PUBLIC
;
GRANT SELECT
,
INDEX
ON
Nauczyciele
TO PUBLIC
;
GRANT UPDATE(
Title, City, SupNo
)
ON
Nauczyciele
TO
Jan, Katarzyna
WITH GRANT OPTION
;
Funkcje agregujące
Jak one działają?
• Funkcje agregujące działają na zbiorze
rekordów - jako argument funkcji podaje
się pojedynczą kolumnę.
• Funkcja agregująca zwraca zawsze
pojedynczą wartość, obliczoną na
podstawie zbioru wartości, odczytywanych
z kolumny podanej jako argument funkcji.
Funkcje agregujące – lista
• COUNT() – zwraca liczbę rekordów.
• SUM() – zwraca sumę rekordów.
• MIN() – zwraca wartość minimalną.
• MAX() – zwraca wartość maksymalną.
• AVG() – zwraca wartość średnią.
Przykład użycia funkcji COUNT()
TabelaZam
Id DataZam
CenaZa
m
ImieKlien
ta
1
2013/04/
20
5432
Piotr
2
2013/03/
27
63
Jerzy
3
2013/04/
01
354
Piotr
4
2013/02/
28
73
Paweł
5
2013/01/
16
25
NULL
6
2013/04/
06
7364
Eryk
SELECT
COUNT(
ImieKlienta
) AS
KlientPiotr
FROM
TabelaZam
WHERE
ImieKlienta = 'Piotr';
Odpowiedź
KlientPi
otr
2
Przykład użycia funkcji COUNT() cz.
2
TabelaZam
Id DataZam
CenaZa
m
ImieKlien
ta
1
2013/04/
20
5432
Piotr
2
2013/03/
27
63
Jerzy
3
2013/04/
01
354
Piotr
4
2013/02/
28
73
Paweł
5
2013/01/
16
25
NULL
6
2013/04/
06
7364
Eryk
SELECT
COUNT(
*
) AS
LiczbaKlientow
FROM
TabelaZam;
Odpowiedź
LiczbaKlien
tow
6
Przykład użycia funkcji SUM()
TabelaZam
Id DataZam
CenaZa
m
ImieKlien
ta
1
2013/04/
20
5432
Piotr
2
2013/03/
27
63
Jerzy
3
2013/04/
01
354
Piotr
4
2013/02/
28
73
Paweł
5
2013/01/
16
25
NULL
6
2013/04/
06
7364
Eryk
SELECT
SUM(
CenaZam
) AS
SumaZam
FROM
TabelaZam;
Odpowiedź
SumaZ
am
13311
Przykład użycia funkcji MIN()
TabelaZam
Id DataZam
CenaZa
m
ImieKlien
ta
1
2013/04/
20
5432
Piotr
2
2013/03/
27
63
Jerzy
3
2013/04/
01
354
Piotr
4
2013/02/
28
73
Paweł
5
2013/01/
16
25
NULL
6
2013/04/
06
7364
Eryk
SELECT
MIN(
CenaZam
) AS
MinZam
FROM
TabelaZam;
Odpowiedź
MinZa
m
25
Przykład użycia funkcji MAX()
TabelaZam
Id DataZam
CenaZa
m
ImieKlien
ta
1
2013/04/
20
5432
Piotr
2
2013/03/
27
63
Jerzy
3
2013/04/
01
354
Piotr
4
2013/02/
28
73
Paweł
5
2013/01/
16
25
NULL
6
2013/04/
06
7364
Eryk
SELECT
MAX(
CenaZam
) AS
MaxZam
FROM
TabelaZam;
Odpowiedź
MaxZ
am
7364
Przykład użycia funkcji AVG()
TabelaZam
Id DataZam
CenaZa
m
ImieKlien
ta
1
2013/04/
20
5432
Piotr
2
2013/03/
27
63
Jerzy
3
2013/04/
01
354
Piotr
4
2013/02/
28
73
Paweł
5
2013/01/
16
25
NULL
6
2013/04/
06
7364
Eryk
SELECT
AVG (
CenaZam
) AS
SredniaZam
FROM
TabelaZam;
Odpowiedź
SredniaZ
am
2218.5
Zastosowanie metody
GROUP BY
Budowa
• [GROUP BY <listakolumn>]
• Fraza GROUP BY - służy do otrzymywania
wartości sumarycznych dla poszczególnych grup.
• Po słowie kluczowym GROUP BY występuje
wyrażenie grupujące - zestaw nazw kolumn,
które definiują grupowanie.
• Nazwy te muszą wystąpić we frazie SELECT.
Przykład
Samochody
Id Marka Model Spee
d
1
HOND
A
Accor
d
217
2
HOND
A
Civic
204
3
MAZD
A
323
199
4
AUDI
A4
260
5
MAZD
A
MX-5
210
6
MAZD
A
RX-8
240
7
HOND
A
Crx
229
8
AUDI
TT
280
9
AUDI
A6
236
1
0
MAZD
A
MX-6
234
Przykład cz. 2
SELECT
Marka,
MAX
(
Speed
)
AS
TopSpeed,
AVG
(
Speed
)
AS
SredniaSpeed,
MIN
(
Speed
)
AS
MinSpeed
FROM
Samochody
GROUP BY
Marka;
Odpowiedź
Mark
a
TopSpe
ed
SredniaSp
eed
MinSpe
ed
AUDI
280
258.6
236
HOND
A
229
216.6
204
MAZD
A
240
220.75
199
Funkcja HAVING
Budowa
• [HAVING <warunek>]
• HAVING - określa warunki wyboru dla
wierszy powstałych w wyniku działania
frazy GROUP BY.
Przykład
TabelaZam
Id DataZam
CenaZa
m
Nick
1
2013/04/
20
1000
Killer
2
2013/03/
27
1600
Jimmy
3
2013/04/
01
700
Killer
4
2013/02/
28
300
Killer
5
2013/01/
16
2000
Woz
6
2013/04/
06
100
Jimmy
SELECT
Nick,
SUM
(CenaZam)
FROM
TabelaZam
GROUP BY
Nick
HAVING SUM(
CenaZam
) <
2000
Odpowiedź
Nick
SUM(CenaZ
am)
Jimm
y
1700
Przykład 2
SELECT
Nick,
SUM
(CenaZam)
FROM
TabelaZam
WHERE
Nick = ’Killer’
OR
Nick = ’Woz’
GROUP BY
Nick
HAVING SUM(
CenaZam
) >
1500
Odpowiedź
Nick
SUM(CenaZ
am)
Killer
2000
Woz
2000
Operatory SQL
Lista operatorów
• WHERE zmienna IN (zbiór wartości…) sprawdza czy
zmienna znajduje się w zbiorze wartości.
• WHERE zmienna NOT IN (zbiór wartości…) sprawdza
czy zmienna NIE znajduje się w zbiorze wartości.
• WHERE EXISTS (podzapytanie) warunek jest spełniony,
gdy podzapytanie zwróci jakikolwiek wiersz.
• WHERE NOT EXISTS (podzapytanie) warunek jest
spełniony, gdy podzapytanie nie zwróci żadnej wartości.
Przykład użycia operatora IN
SELECT
*
FROM
Informacje
WHERE
Nazwisko
IN
(
’Nowak’, ’Lewandowski’
)
;
Informacje
Id Nazwisko
Imie
Ulica
Miasto
1
Nowak
Ada
m
Trybunals
ka
Warsza
wa
2
Kwiatkow
ski
Jan
Czysta
Tychy
3
Grochows
ki
Jaku
b
Dolna
Konin
Odpowiedź
I
d
Nazwis
ko
Imie Ulica
Miasto
1 Nowak
Ada
m
Trybunals
ka
Warsza
wa
Podzapytania
Budowa
• Zapytania w SQL mogą operować na wynikach
innych zapytań. Dokonuje się wówczas tzw.
zagnieżdżenia zapytań.
• Podzapytanie musi być objęte nawiasami.
• zapytanie nadrzędne (zewnętrzne) – zawierające
podzapytanie.
• zapytanie podrzędne (wewnętrzne) –
podzapytanie.
Rodzaje połączeń
Podzapyta
nia
Nie
skorelowa
ne
Skorelowa
ne
Rodzaje połączeń cz. 2
• Podzapytanie nie skorelowane (niepowiązane) –
wykonuje się tylko raz, przed zapytaniem zewnętrznym,
a jego wyniki są przekazywane do zapytania
zewnętrznego. Charakterystyczne dla zapytań
niepowiązanych jest to, że zapytanie wewnętrzne można
wykonać jako osobną instrukcję.
• Podzapytanie skorelowane (powiązane) – wymaga
danych z zapytania zewnętrznego, zanim może być
wykonane. Dane zwrócone przez podzapytanie
powiązane wprowadzane są z powrotem do zapytania
zewnętrznego, a podzapytanie wykonywane jest tyle
razy, ile wierszy zwróciło zapytanie zewnętrzne.
Podzapytanie
nieskorelowane
Kolejność wykonywania działań
• W przypadku podzapytania zwykłego
(nieskorelowanego) interpreter SQL
wykonuje zapytania począwszy od
najbardziej zagłębionego podzapytania,
aby móc użyć jego wyniku w zapytaniu
zewnętrznym.
Podzapytanie zwykłe –
niepowiązane
Opady
Id
Data
Miasto
IloscOpad
ow
1
10-05-
25
Warsza
wa
5
2
10-05-
26
Warsza
wa
6
3
10-05-
25
Łódź
7
4
10-05-
26
Łódź
11
5
10-05-
25
Gdańsk
13
6
10-05-
26
Gdańsk
15
7
10-05-
25
Zakopa
ne
0
8
10-05-
26
Zakopa
ne
14
9
10-05-
25
Szczecin 0
1
0
10-05-
26
Szczecin 0
Wybrać miasto z największą ilością
opadów:
SELECT
Miasto,
IloscOpadow
FROM
Opady
WHERE
IloscOpadow =
(SELECT MAX(
IloscOpadow
)
FROM
Opady
)
;
Najpierw wykonuje się podzapytanie
SELECT
Miasto,
IloscOpadow
FROM
Opady
WHERE
IloscOpadow =
15
;
Odpowiedź
Miast
o
IloscOpad
ow
Gdań
sk
15
Podzapytania skorelowane
Jak to działa?
• Cechą charakterystyczną podzapytań skorelowanych jest
odwołanie wewnątrz podzapytania, do atrybutu z relacji, którą
przegląda zapytanie zewnętrzne. To odwołanie nosi nazwę
korelacji.
• Podzapytanie skorelowane wykonywane jest dla każdego wiersza
wyniku zapytania zewnętrznego i może być z nim porównywane.
• Podzapytanie powiązane, można łatwo rozpoznać po tym, że
kolumna (kolumny) wyniku podzapytania jest porównywana z
kolumną (kolumnami) wyniku zapytania zewnętrznego.
• Niezależne wykonanie wewnętrznego zapytania (podzapytania)
jest w tym przypadku niemożliwe.
Przykład
Wybrać nazwiska pracowników zarabiających więcej niż
średnia płaca w grupie do której należą.
SELECT
nazwisko
FROM
pracownicy p
WHERE
pensja
>
(SELECT AVG(
pensja
)
FROM
pracownicy
WHERE
grupa = p.grupa
)
;
• Ponieważ zarówno w zapytaniu zewnętrznym, jak i w
podzapytaniu operujemy na tej samej tabeli
PRACOWNICY, konieczne jest zdefiniowanie aliasu
(„p”) dla tabeli PRACOWNICY, przeglądanej w
zapytaniu zewnętrznym.
Przykład cz. 2
SELECT
nazwisko
FROM
pracownicy p
WHERE
pensja
>
(SELECT AVG(
pensja
)
FROM
pracownicy
WHERE
grupa =
p.grupa
)
;
1. Zapytanie zewnętrzne
wybiera pierwszy rekord.
2. Podzapytanie
wylicza średnią
pensję dla grupy
SPRZEDAWCA (3000zł).
3. Sprawdzany jest warunek czy
pensja Nowickiego (2000zł)
jest większa od średniej
pensji grupy do której należy
(3000zł).
4. Odpowiedź brzmi NIE, także
pierwszy rekord nie wejdzie
do wyniku.
5. Teraz pobierany jest kolejny
rekord z tablicy
pracownicy p
.
Przykład cz. 3
pracownicy p
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
Przykład cz. 4
SELECT
nazwisko
FROM
pracownicy p
WHERE
pensja
>
(SELECT AVG(
pensja
)
FROM
pracownicy
WHERE
grupa =
p.grupa
)
;
1. Zapytanie zewnętrzne
wybiera
drugi rekord.
2. Podzapytanie
wylicza średnią
pensję dla grupy
SPRZEDAWCA (3000zł).
3. Sprawdzany jest warunek czy
pensja Kowalskiego (4000zł)
jest większa od średniej pensji
grupy do której należy
(3000zł).
4. Odpowiedź brzmi TAK, także
drugi rekord wejdzie do
wyniku.
5. Następnie pobierany jest
kolejny rekord z tablicy
pracownicy p
.
Przykład cz. 5
pracownicy p
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
Przykład cz. 6
SELECT
nazwisko
FROM
pracownicy p
WHERE
pensja
>
(SELECT AVG(
pensja
)
FROM
pracownicy
WHERE
grupa =
p.grupa
)
;
1. Zapytanie zewnętrzne
wybiera
trzeci rekord.
2. Podzapytanie
wylicza średnią
pensję dla grupy MENAGER
(4000zł).
3. Sprawdzany jest warunek czy
pensja Górskiego (3000zł) jest
większa od średniej pensji
grupy do której należy
(4000zł).
4. Odpowiedź brzmi NIE, także
pierwszy rekord nie wejdzie do
wyniku.
5. Następnie pobierany jest
kolejny rekord z tablicy
pracownicy p
.
Przykład cz. 7
pracownicy p
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
Przykład cz. 8
SELECT
nazwisko
FROM
pracownicy p
WHERE
pensja
>
(SELECT AVG(
pensja
)
FROM
pracownicy
WHERE
grupa =
p.grupa
)
;
1. Zapytanie zewnętrzne
wybiera czwarty rekord.
2. Podzapytanie
wylicza
średnią pensję grupy
MENAGER (4000zł).
3. Sprawdzany jest warunek
czy pensja Wesołowskiego
(5000zł) jest większa od
średniej pensji grupy do
której należy (4000zł).
4. Odpowiedź brzmi TAK,
także ten rekord nie
wejdzie do wyniku.
Przykład cz. 9
pracownicy p
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
IdPra
c
Grupa
Nazwisko
Pensj
a
1
Sprzedaw
ca
Nowicki
2000
2
Sprzedaw
ca
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
Podsumowanie przykładu
• Zapytanie zewnętrzne przejrzało cztery
rekordy.
• Podzapytanie zostało wykonane
czterokrotnie.
• Do zbioru wynikowego trafiły dwa rekordy,
które spełniły warunek.
Zapytanie skorelowane z
predykatem NOT EXISTS
Przykład
1. Zapytanie zewnętrzne
wybiera po kolei wszystkie
zespoły z tabeli ZESPOLY.
2. Dla każdego wybranego zespołu wywoływane
jest
podzapytanie
szukające w tabeli
PRACOWNICY osób, które należą do wybranego
przez
zapytanie zewnętrzne
zespołu (
z.id_zesp
).
3. Jeżeli
podzapytanie
nie znajdzie takich osób
(zwróci zbiór pusty) to warunek NOT EXISTS
zostanie spełniony i grupa wybrana przez
zapytanie zewnętrzne
zostanie dodana do zbioru
wynikowego.
Kod źródłowy przykładu
SELECT
id_zesp
, nazwa
FROM
zespoly z
WHERE
NOT EXISTS
(SELECT
*
FROM
pracownicy
WHERE
id_zesp =
z.id_zesp
)
;
Zapytanie ma na celu wybranie zespołów, nie
posiadających pracowników.
Operator ANY
•
WHERE zmienna [>,<,…] ANY (zbiór wartości…)
warunek ten jest spełniony jeżeli wyrażenie jest zgodne z
co najmniej jedną wartością zawartą w zbiorze wartości.
SELECT
nazwisko
FROM
pracownicy
WHERE
pensja
>
ANY
(SELECT
pensja
FROM
pracownicy
WHERE
id_zesp =
7
)
;
• Wybieramy nazwiska pracowników, których
pensja jest większa od pensji któregokolwiek z
pracowników zespołu 7.
Operator ALL
• WHERE zmienna [>,<,…] ALL (zbiór wartości…)
warunek ten jest spełniony jeżeli wyrażenie jest zgodne ze
wszystkimi wartościami zawartymi w zbiorze wartości.
SELECT
nazwisko
FROM
pracownicy
WHERE
pensja
>
ALL
(SELECT
pensja
FROM
pracownicy
WHERE
id_zesp =
7
)
;
• Wybieramy nazwiska pracowników, których
pensja jest większa od pensji wszystkich
pracowników z zespołu 7.
Thanks for your attention!
Łukasz Grzybowski i Jerzy
Jurczak.