Mariusz Wyszomierski – Wyższa Szkoła Informatyki Stosowanej i
Zarządzania
Mariusz Wyszomierski – Wyższa Szkoła Informatyki Stosowanej i
Zarządzania
W danym wyrażeniu operacje są wykonywane zgodnie z
priorytetem operatorów, począwszy od operatorów o
najwyższym priorytecie.
Jeśli operatory o jednakowym priorytecie występują jeden
obok drugiego, obowiązuje kolejność od lewej do prawej.
1.
Wszystkie operatory porównywania i operatory SQL mają
jednakowy, najwyższy priorytet:
= , !=, <, >, <=, >=, BETWEEN … AND
…, IN, LIKE, IS NULL.
2.
NOT - odwrócenie wyniku porównania, np.
WHERE NOT ZAROBKI>2 000
3.
AND, przedostatni operator w hierarchii.
4.
OR, operator o najniższym priorytecie.
Zaleca się stosowanie nawiasów we wszystkich
przypadkach, gdzie kolejność wykonywania przez program
skomplikowanych złożeń operatorów nie jest łatwa do
przewidzenia.
Aby np. wybrać wszystkich pracowników na stanowisku
MANAGER ze wszystkich departamentów wraz ze wszystkimi
pracownikami zatrudnionymi na stanowisku SPRZEDAWCA w
departamencie 10, napiszemy dla pewności:
SELECT *
FROM PRACOWNICY
WHERE STANOWISKO = 'MANAGER'
OR
(STANOWISKO = ‘SPRZEDAWCA' AND DEPARTAMENT = 10);
W rzeczywistości nawiasy są tu niepotrzebne, ponieważ
operator AND jest silniejszy od operatora OR, są one
wpisane jedynie dla większej czytelności.
Warunki złożone zbudowane są z
warunków prostych powiązanych
spójnikami logicznymi AND i OR.
Główne zapytania do bazy to:
SELECT - pobranie z bazy danych,
INSERT - umieszczenie danych w bazie,
UPDATE - zmiana danych,
DELETE - usunięcie danych z bazy.
W prawdziwych aplikacjach bazodanowych nie wystarczy
pobieranie danych z jednego źródła (tabeli). Język SQL umożliwia
nam wybranie danych z wielu tabel na raz.
Przy pobieraniu danych z więcej niż jednej tabeli musimy 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
[nazwa tabeli 1] [alias tabeli 1],
[nazwa tabeli 2] [alias tabeli 2],
[nazwa tabeli 3] [alias tabeli 3],
…
Id
Imie
Nazwisk
o
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrowsk
i
4
Magdale
na
Krzyżak
Id
IdKlien
ta
Tresc
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok do
zobacz..
4
1
Będę za 15
min
Pobranie imienia
Klienci
Smsy
SELECT
Imie,
WHERE
k.Id=s.IdKlienta
FROM
Klienci , Smsy
Nazwisko, Id, Tresc
,
nazwiska
, id smsa
oraz treści wysłanej wiadomości:
k
s
k.
k.
s.
s.
Imie
Nazwisko
Id
Tresc
Michał
Kowalski
1
Siema
stary…
Magdale
na
Krzyżak
2
Jest
problem…
Michał
Kowalski
3
Ok do
zobacz..
Agata
Nowak
4
Będę za 15
min
Wynik zapytania:
Id
Imie
Nazwisk
o
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrowsk
i
4
Magdale
na
Krzyżak
Id
IdKlien
ta
Tresc
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok do
zobacz..
4
1
Będę za 15
min
Klienci
Sms
y
SELECT
k.Imie, k.Nazwisko, s.Id, s.Tresc
FROM
Klienci k, Smsy s
WHERE
k.Id=s.IdKlienta
Imie
Nazwisko
Id
Tresc
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrowski 1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Wynik zapytania:
Id
Imie
Nazwisk
o
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrowsk
i
4
Magdale
na
Krzyżak
Id
IdKlien
ta
Tresc
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok do
zobacz..
4
1
Będę za 15
min
Klienci
Sms
y
SELECT
k.Imie, k.Nazwisko, s.Id, s.Tresc
FROM
Klienci k, Smsy s
Wynik zapytania:
Imie
Nazwisko
Id
Tresc
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrowski 1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Agata
Nowak
2
Jest
problem…
Michał
Kowalski
2
Jest
problem…
Cezary
Dąbrowski 2
Jest
problem…
Magdale
na
Krzyżak
2
Jest
problem…
Imie
Nazwisko
Id
Tresc
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrowski 1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Id
Imie
Nazwisk
o
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrowsk
i
4
Magdale
na
Krzyżak
Id
IdKlien
ta
Tresc
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok do
zobacz..
4
1
Będę za 15
min
Klienci
Sms
y
SELECT
k.Imie, k.Nazwisko, s.Id, s.Tresc
FROM
Klienci k, Smsy s
Wynik zapytania:
Imie
Nazwisko
Id
Tresc
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrowski 1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Agata
Nowak
2
Jest
problem…
Michał
Kowalski
2
Jest
problem…
Cezary
Dąbrowski 2
Jest
problem…
Magdale
na
Krzyżak
2
Jest
problem…
Agata
Nowak
3
Ok. do
zobacz…
Michał
Kowalski
3
Ok. do
zobacz…
Cezary
Dąbrowski 3
Ok. do
zobacz…
Magdale
na
Krzyżak
3
Ok. do
zobacz…
Imie
Nazwisko
Id
Tresc
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrowski 1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Agata
Nowak
2
Jest
problem…
Michał
Kowalski
2
Jest
problem…
Cezary
Dąbrowski 2
Jest
problem…
Magdale
na
Krzyżak
2
Jest
problem…
Id
Imie
Nazwisk
o
1
Agata
Nowak
2
Michał
Kowalski
3
Cezary
Dąbrowsk
i
4
Magdale
na
Krzyżak
Id
IdKlien
ta
Tresc
1
2
Siema
stary…
2
4
Jest
problem…
3
2
Ok do
zobacz..
4
1
Będę za 15
min
Klienci
Sms
y
SELECT
k.Imie, k.Nazwisko, s.Id, s.Tresc
FROM
Klienci k, Smsy s
Wynik zapytania:
Imie
Nazwisko
Id
Tresc
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrowski 1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Agata
Nowak
2
Jest
problem…
Michał
Kowalski
2
Jest
problem…
Cezary
Dąbrowski 2
Jest
problem…
Magdale
na
Krzyżak
2
Jest
problem…
Agata
Nowak
3
Ok. do
zobacz…
Michał
Kowalski
3
Ok. do
zobacz…
Cezary
Dąbrowski 3
Ok. do
zobacz…
Magdale
na
Krzyżak
3
Ok. do
zobacz…
..
..
…
…
Imie
Nazwisko
Id
Tresc
Agata
Nowak
1
Siema
stary…
Michał
Kowalski
1
Siema
stary…
Cezary
Dąbrowski 1
Siema
stary…
Magdale
na
Krzyżak
1
Siema
stary…
Agata
Nowak
2
Jest
problem…
Michał
Kowalski
2
Jest
problem…
Cezary
Dąbrowski 2
Jest
problem…
Magdale
na
Krzyżak
2
Jest
problem…
Agata
Nowak
3
Ok. do
zobacz…
Michał
Kowalski
3
Ok. do
zobacz…
Cezary
Dąbrowski 3
Ok. do
zobacz…
Magdale
na
Krzyżak
3
Ok. do
zobacz…
Iloczyn kartezjański – otrzymaliśmy wszystkie
Kombinacje między wierszy z dwóch tabel
Należy zawsze pamiętać o warunku WHERE
Łączącym tabele (klucz główny z kluczem obcym)
Funkcje agregujące działają na
zbiorze rekordów - jako argument
funkcji podajemy pojedynczą kolumnę
Funkcja agregująca zwraca zawsze
pojedynczą wartość, obliczoną na
podstawie zbioru wartości,
odczytywanych z kolumny podanej
jako argument funkcji
COUNT() – zwraca liczbę rekordów
SUM() – zwraca sumę rekordów
MIN() – zwraca wartość minimalną
MAX() – zwraca wartość maksymalną
AVG() – zwraca wartość średnią
Warto wiedzieć: funkcje agregujące pomijają przy obliczeniach wartości puste (NULL)
Specjalnym użyciem funkcji count() jest:
COUNT(*)
Zwraca ona liczbę wszystkich rekordów w tabeli
Sprawdzenie ile klientów nie podało telefonu:
SELECT COUNT(*)-COUNT(telefon)
FROM Klienci;
Id Marka
Model
Speed
1
HONDA
Accord
217
2
HONDA
Civic
204
3
MAZDA
323
199
4
AUDI
A4
260
5
MAZDA
MX-5
210
6
MAZDA
RX-8
240
7
HONDA
Crx
229
8
AUDI
TT
280
9
AUDI
A6
236
10 MAZDA
MX-6
234
Samochody
SELECT MAX(Speed) as TopSpeed,
AVG(Speed) as SredniaSpeed,
MIN(Speed) as MinSpeed
FROM
Samochody;
Wynik zapytania:
TopSpeed
SredniaSpee
d
MinSpeed
280
230.9
199
Id Marka
Model
Speed
1
HONDA
Accord
217
2
HONDA
Civic
204
3
MAZDA
323
199
4
AUDI
A4
260
5
MAZDA
MX-5
210
6
MAZDA
RX-8
240
7
HONDA
Crx
229
8
AUDI
TT
280
9
AUDI
A6
236
10 MAZDA
MX-6
234
Podane dane są tylko przykładowe
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.
Id Marka
Model
Speed
1
HONDA
Accord
217
2
HONDA
Civic
204
3
MAZDA
323
199
4
AUDI
A4
260
5
MAZDA
MX-5
210
6
MAZDA
RX-8
240
7
HONDA
Crx
229
8
AUDI
TT
280
9
AUDI
A6
236
10 MAZDA
MX-6
234
Samochody
SELECT Marka,
MAX(Speed) as TopSpeed,
AVG(Speed) as SredniaSpeed,
MIN(Speed) as MinSpeed
FROM
Samochody
GROUP BY
Marka;
Wynik zapytania:
Marka
TopSpeed SredniaSpe
ed
MinSpe
ed
AUDI
280
258.6
236
Id Marka
Model
Speed
1
HONDA
Accord
217
2
HONDA
Civic
204
3
MAZDA
323
199
4
AUDI
A4
260
5
MAZDA
MX-5
210
6
MAZDA
RX-8
240
7
HONDA
Crx
229
8
AUDI
TT
280
9
AUDI
A6
236
10 MAZDA
MX-6
234
Marka
TopSpeed SredniaSpe
ed
MinSpe
ed
AUDI
280
258.6
236
HONDA
229
216.6
204
Marka
TopSpeed SredniaSpe
ed
MinSpe
ed
AUDI
280
258.6
236
HONDA
229
216.6
204
MAZDA
240
220.75
199
Podane dane są tylko przykładowe
Id Data
Miasto
IloscOpado
w
1
10-05-
25
Warszawa
5
2
10-05-
26
Warszawa
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
Zakopane
0
8
10-05-
26
Zakopane
14
9
10-05-
25
Szczecin
0
1
0
10-05-
26
Szczecin
0
Wybrać miasta gdzie suma opadów
jest większa równa 15mm
SELECT
Miasto,
SUM(IloscOpadow) as SumaOpadow
FROM
Opady
WHERE SumaOpadow >= 15
GROUP BY Miasto;
Opady
Nie wolno używać funkcji agregujących
w klauzuli WHERE!
Id Data
Miasto
IloscOpado
w
1
10-05-
25
Warszawa
5
2
10-05-
26
Warszawa
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
Zakopane
0
8
10-05-
26
Zakopane
14
9
10-05-
25
Szczecin
0
1
0
10-05-
26
Szczecin
0
Wybrać miasta gdzie suma opadów
jest większa równa 15mm
SELECT
Miasto,
SUM(IloscOpadow) as SumaOpadow
FROM
Opady
GROUP BY Miasto
HAVING SUM(IloscOpadow)>=15
Opady
Klauzula HAVING pozwala określić kryteria
wybrania pogrupowanych rekordów.
W klauzuli HAVING mogą się znaleźć
tylko wyrażenia grupujące oraz
funkcje agregujące!
HAVING Miasto=’Warszwa’ or Miasto=’Łódź’
HAVING SUM(IloscOpadow)>=15
Najprościej mówiąc, podzapytanie jest to
zapytanie zagnieżdżone w innym zapytaniu SQL
Podzapytania mogą występować w klauzuli
WHERE oraz HAVING, rzadziej, ale również mogą
występować w klauzulach SELECT oraz FROM
Podzapytanie musi być objęte nawiasami
zapytanie nadrzędne (zewnętrzne )–
zawierające podzapytanie
zapytanie podrzędne (wewnętrzne) –
podzapytanie
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.
Interpreter SQL wykonuje zapytania
począwszy od najbardziej
zagłębionego podzapytania, aby móc
użyć jego wyniku w zapytaniu
zewnętrznym
Id Data
Miasto
IloscOpado
w
1
10-05-
25
Warszawa
5
2
10-05-
26
Warszawa
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
Zakopane
0
8
10-05-
26
Zakopane
14
9
10-05-
25
Szczecin
0
1
0
10-05-
26
Szczecin
0
Opady
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
Wynik zapytania:
Miasto
Ilosc opadow
Gdańsk
15
Pokazane w przykładzie podzapytanie zwraca
tylko jeden wiersz z jedną wartością.
Wybrać miasto z największą ilością opadów – porównać wyniki ze StacjiMeto1 i StacjiMeteo2:
SELECT
Miasto,
IloscOpadow
FROM
StacjaMeteo1
WHERE
(IloscOpadow,Data) =
(SELECT
MAX(IloscOpadow),
Data
FROM StacjaMeteo2)
Wynik zapytania:
Miasto
Ilosc opadow
Gdańsk
15
Jak radzić sobie z podzapytaniem , które zwraca jeden wiersz, ale dwie wartości?
15, 10-05-26
Teraz zajmiemy się podzapytaniem zwracającym wiele wierszy.
SELECT *
FROM Zamowienia
WHERE klient_id =
(SELECT klient_id
FROM Klienci
WHERE nazwisko = ‘Kowalski');
SELECT *
FROM Zamowienia
WHERE klient_id
IN
(SELECT klient_id
FROM Klienci
WHERE nazwisko = ‘Kowalski');
• Podzapytanie zwróci nam ID klientów o nazwisku „Kowalski”.
• Może się tak zdarzyć, że w bazie będzie kilku klientów, wtedy zapytanie zwróci kilka wierszy.
•
Warunek WHERE klient_id=(zbiór ID)
jest nie dopuszczalny!
• Nie można porównywać jednej wartości ze zbiorem wartości.
• W wypadku porównywania zawierającego wiele wierszy należy używać operatorów:
IN, NOT IN, EXISTS, NOT EXISTS, ANY, ALL
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
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
To jest równoznaczne z wybraniem pracowników, których pensja jest większa
od minimalnej pensji w zespole 7
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
To jest równoznaczne z wybraniem pracowników, których pensja jest większa
od maksymalnej pensji w zespole 7
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.
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.
Korelacja jest realizowana przez porównanie kolumny
GRUPA w podzapytaniu (GRUPA =
P.GRUPA
).
SELECT nazwisko
FROM
pracownicy p
WHERE pensja >
(SELECT AVG(pensja)
FROM
pracownicy
WHERE grupa =
p.grupa
);
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy p
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
1. Zapytanie zewnętrzne
wybiera pierwszy rekord
2. Podzapytanie
wylicza średnią pensję dla grupy
SPRZEDAWCA (3000 zł)
3. Sprawdzany jest warunek czy pensja
Nowackiego (2000 zł) jest większa od średniej pensji
grupy do której należy (3000 zł)
4. Odpowiedź brzmi nie, także pierwszy rekord
nie wejdzie do wyniku
5. Teraz pobierany jest kolejny rekord
z tablicy
pracownicy p
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
SELECT nazwisko
FROM
pracownicy p
WHERE pensja >
(SELECT AVG(pensja)
FROM
pracownicy
WHERE grupa =
p.grupa
);
pracownicy p
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
1. Zapytanie zewnętrzne
wybiera drugi rekord
2. Podzapytanie
wylicza średnią pensję dla grupy
SPRZEDAWCA (3000 zł)
3. Sprawdzany jest warunek czy pensja
Kowalskiego (4000zł) jest większa od średniej pensji
grupy do której należy (3000 zł)
4. Odpowiedź brzmi TAK, także drugi rekord
wejdzie do wyniku
5. Teraz pobierany jest kolejny rekord
z tablicy
pracownicy p
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
SELECT nazwisko
FROM
pracownicy p
WHERE pensja >
(SELECT AVG(pensja)
FROM
pracownicy
WHERE grupa =
p.grupa
);
pracownicy p
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
1. Zapytanie zewnętrzne
wybiera trzeci rekord
2. Podzapytanie
wylicza średnią pensję dla grupy
MENAGER (4000 zł)
3. Sprawdzany jest warunek czy pensja
Górskiego (3000zł) jest większa od średniej pensji
grupy do której należy (4000 zł)
4. Odpowiedź brzmi NIE, także ten rekord
nie wejdzie do wyniku
5. Następnie pobierany jest kolejny rekord
z tablicy
pracownicy p
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
SELECT nazwisko
FROM
pracownicy p
WHERE pensja >
(SELECT AVG(pensja)
FROM
pracownicy
WHERE grupa =
p.grupa
);
pracownicy p
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
pracownicy
1. Zapytanie zewnętrzne
wybiera czwarty rekord
2. Podzapytanie
wylicza średnią pensję dla grupy
MENAGER (4000 zł)
3. Sprawdzany jest warunek czy pensja
Wesołowskiego (5000zł) jest większa od średniej pensji
grupy do której należy (4000 zł)
4. Odpowiedź brzmi TAK, także ten rekord
wejdzie do wyniku
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
IdPr
ac
Grupa
Nazwisko
Pensja
1
Sprzedawc
a
Nowacki
2000
2
Sprzedawc
a
Kowalski
4000
3
Menager
Górski
3000
4
Menager
Wesołows
ki
5000
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
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.
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.
Klauzula GRANT służy do nadawania
uprawnień użytkownikom
Klauzula REVOKE służy do
odbierania uprawnień użytkownikom
Użytkownik ma pełne prawa do
obiektów, które utworzył. Tych praw
nie można mu odebrać.
GRANT
lista_przywilejów
ON nazwa_obiektu
TO lista_użytkowników
[WITH GRANT OPTION];
Nazwy 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
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 lista_przywilejów
ON nazwa_obiektu
TO
lista_użytkowników
[WITH GRANT OPTION];
PUBLIC – nadanie praw wszystkim
użytkownikom
lub
Podajemy nazwy użytkowników, którym chcemy
nadać prawa
lub
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
ADMIN
BIZNES
STUDENT
Ma wszystkie
przywileje
ADMIN:
GRANT SELECT
ON
klienci
TO
BIZES
SELECT
ADMIN
BIZNES
STUDENT
Ma wszystkie
przywileje
ADMIN:
GRANT INSERT
ON
klienci
TO
BIZES
WITH GRANT OPTION
SELECT
INSERT
ADMIN
BIZNES
STUDENT
Ma wszystkie
przywileje
BIZNES:
GRANT INSERT
ON
klienci
TO
STUDENT
SELECT
INSERT
INSERT
ADMIN
BIZNES
STUDE
NT
Ma wszystkie
przywileje
ADMIN:
GRANT INSERT
ON
klienci
TO
STUDENT
SELECT
INSERT
INSERT
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.
ADMIN
BIZNES
STUDE
NT
Ma wszystkie
przywileje
BIZNES:
REVOKE INSERT
ON
klienci
FROM STUDENT
SELECT
INSERT
INSERT
STUDENT ma nadal przywilej INSERT,
Ponieważ dostał go również od ADMINa.
ADMIN
BIZNES
STUDE
NT
Ma wszystkie
przywileje
ADMIN:
REVOKE INSERT
ON
klienci
FROM BIZNES
SELECT
INSERT
INSERT
ADMIN
BIZNES
STUDE
NT
Ma wszystkie
przywileje
ADMIN:
REVOKE INSERT
ON
klienci
FROM STUDENT
SELECT
INSERT
Dopiero teraz STUDENT został
pozbawiony przywileju INSERT
ADMIN
BIZNES
STUDENT
Ma wszystkie
przywileje
ADMIN:
GRANT SELECT
ON
klienci
TO
PUBLIC
SELECT
SELECT
PUBLIC – jest to tzw. rola czyli
grupa użytkowników. Do tej
grupy należą wszyscy
użytkownicy. Każdy nowo
utworzony użytkownik należy
również do roli PUBLIC.