PODZAPYTANIA-KOMENTARZE-PRZYKLADY.pdf

(54 KB) Pobierz
PRZYKŁADY
I
KOMENTARZE
PODZAPYTAŃ
DOTYCZĄCE
1.
Podzapytania
to instrukcje SELECT zagnieŜdŜone w innych instrukcjach SQLowych.
MOGĄ BYĆ UśYTE DO:
- konstrukcji wyraŜeń w warunkach w WHERE, HAVING (SELECT, UPDATE, DELETE)
- określenie tabeli w: (skorelowanie z zewnętrznym zapytaniem niedozwolone)
klauzuli FROM instrukcji SELECT
INSERT, UPDATE, DELETE
- określenia jednej lub wielu wartości w instrukcji UPDATE
- określenia wartości w instrukcji INSERT
- określenia perspektywy (VIEW)
- określenia wierszy, które mają być dodane w instrukcji INSERT oraz CREATE TABLE
W podzapytaniach mogą występować podzapytania (nie ma ograniczeń na zagnieŜdŜanie)
2. Podzapytania mogą występować wszędzie tam gdzie w składni SQLa występują (równieŜ
tabelowe – czyli np. we frazie FROM instrukcji SELECT - czy po słowie kluczowym IN we frazie
WHERE)
Typy podzapytań:
(wg tego co zwracają)
tabelowe, wierszowe (w UPDATE), kolumnowe (z operatorem IN), skalarne
(wg powiązania bądź jego braku z zewnętrznym zapytaniem)
skorelowane (correlated), nieskorelowane (noncorrelated)
Instrukcja SELECT zwraca zawsze tabelę ! W przypadku podzapytań tabelowych właśnie tabela
jest oczekiwana w miejscu wystąpienia podzapytania (np. we frazie FROM instrukcji SELECT).
W przypadku podzapytania kolumnowego tabela zwracana przez podzapytanie musi mieć
dokładnie jedną kolumnę, której zawartość traktowana jest jako lista wartości (np. po operatorze
IN we frazie WHERE). Podzapytania wierszowe, to takie, które zwracają tabelę z co najwyŜej
jednym wierszem (uŜywane w specyficznej postaci instrukcji UPDATE). Podzapytania skalarne
zwracają tabelę o dokładnie jednej kolumnie i co najwyŜej jednym wierszu. Tabela ta utoŜsamiana
jest z pojedynczą wartością w niej występującą (w tej jedynej kolumnie i jedynym wierszu). Jeśli
zwracana tabela jest pusta (zero wierszy), to utoŜsamia się ja z pseudowartością NULL.
Podzapytania zapisujemy w nawiasach okrągłych: (SELECT …).
3. Nieskorelowane, skalarne
Wypisać wszystkie zamówienia przyjęte po zamówieniu nr 3
SELECT *
FROM zamowienia
WHERE data >
(SELECT data FROM zamowienia WHERE IDZam=3);
4. Przykład nieskorelowanego, kolumnowego w klauzuli WHERE
Wypisać nazwy firm klientów, którzy byli obsługiwani przez pracownika o numerze 1:
SELECT firma FROM klienci
WHERE IDKlienta IN
(SELECT IDKlienta FROM zamowienia WHERE IDPracownika=1);
Ten sam wynik moŜna często uzyskać poprzez odpowiednie złączenie tabel:
SELECT firma
FROM klienci INNER JOIN zamowienia ON
klienci.IDKlienta = zamowienia.IDKlienta
WHERE zamowienia.IDPracownika=1;
5. Przykład podzapytaniaskorelowanego
Wypisać imiona, nazwiska i wynagrodzenia pracowników zarabiających więcej niŜ
średnia
w ich
dziale
SELECT imie,nazwisko,wynagrodzenie
FROM pracownicy
WHERE wynagrodzenie >
(SELECT AVG(wynagrodzenie)
FROM pracownicy p1
WHERE IDDzialu=pracownicy.IDDzialu)
lub inaczej z tym samym efektem
SELECT imie,nazwisko,wynagrodzenie
FROM pracownicy p1
WHERE wynagrodzenie >
(SELECT AVG(wynagrodzenie)
FROM pracownicy
WHERE IDDzialu=p1.IDDzialu)
6. Kolumny występujące w podzapytaniu są najpierw poszukiwane w jego tabelach - jeśli ich tam nie
ma, to w zapytaniu (ogólniej: instrukcji) nadrzędnym (czyli tym, w którym zagnieŜdŜone jest
podzapytanie). Jeśli więc w instrukcji nadrzędnej i w podzapytaniu występuje tabela o tej samej
nazwie, to aby odwołać się do kolumny z tabeli w instrukcji nadrzędnej musimy uŜyć aliasów
nazw tabel i kwalifikowanych nazw kolumn – jak w powyŜszym przykładzie.
7. Inny przykład na skorelowane
Wypisać pracowników, którzy mają współpracowników:
SELECT *
FROM pracownicy pr
WHERE IDDzialu IN
(SELECT IDDzialu FROM pracownicy WHERE IDPracownika<>pr.IDpracownika)
8. MoŜna to zapisać bardziej intuicyjnie uŜywając operatora (kwantyfikatora) EXISTS
SELECT *
FROM pracownicy pr
WHERE EXISTS
(SELECT IDDzialu FROM pracownicy WHERE IDPracownika<>pr.Idpracownika AND
IDDzialu=pr.IDDzialu);
9.
Wypisać dane pracowników, którzy zarabiają najwięcej w swoich działach
SELECT * from pracownicy p
WHERE NOT EXISTS (SELECT * FROM pracownicy WHERE
wynagrodzenie>p.wynagrodzenie AND iddzialu=p.iddzialu)
10. Podzapytanie kolumnowe, skalarne z operatorami (kwantyfikatorami)
SOME|ANY
i
ALL
Wypisać wszystkie towary, które są droŜsze od wszystkich towarów zamówionych w zamówieniu 2
SELECT *
FROM towary
WHERE cena > ALL (
SELECT cena FROM zamszcz zm INNER JOIN towary t
ON zm.IDTowaru=t.IDTowaru WHERE zm.IDZam=2);
PowyŜej, warunek we frazie WHERE jest spełniony jeśli wartość wyraŜenia występującego przed
ALL (tu: cena) jest w relacji podanej przed ALL (tu: >) z wartościami wszystkich wyraŜeń
podanych na liście po ALL (tu listę tę zastępuje zawartość kolumny w tabeli zwróconej przez
podzapytanie).
MoŜemy zamienić ALL na ANY bądź SOME aby dostać towary droŜsze od jakiegokolwiek z
zamówienia o numerze 2
To samo zadanie z podzapytaniem skalarnym bez operatora ALL ?
SELECT *
FROM towary
WHERE cena > (
SELECT MAX(cena) FROM zamszcz zm INNER JOIN towary t
ON zm.IDTowaru=t.IDTowaru WHERE zm.IDZam=2);
ALE TAK NAPRAWDĘ TO NIE JEST RÓWNOWAśNE ZE WZGLĘDU NA
MOśLIWOŚĆ WYSTĘPOWANIA PSEUDOWARTOŚCI NULL W KOLUMNIE
cena
zapytanie z ALL zakończy się niepowodzeniem, bo wynikiem porównania
dowolnej ceny z pseudowartością NULL jest trzecia wartość logiczna nieokreślone
(UNKNOWN) i warunek nie jest spełniony dla wszystkich porównań, czego wymaga
operator ALL. W drugim przykładzie, funkcja agregująca MAX ignoruje psudowartości
NULL, w związku z czym warunek we frazie WHERE moŜe być spełniony dla pewnych
towarów o odpowiednio wysokiej cenie.
11. Podzapytania we frazie HAVING instrukcji SELECT
Wypisać numery towarów i ich maksymalną zamówioną ilość ale tylko dla tych, które były
zamawiane więcej razy niŜ towar o numerze 1
SELECT IDTowaru, MAX(ilosc) AS "Maks ilosc"
FROM zamszcz
GROUP BY IDTowaru
HAVING COUNT(*)> (SELECT COUNT(*) FROM zamszcz WHERE IDTowaru=1);
12. Podzapytanie we frazie FROM instrukcji SELECT.
Podaj imiona, nazwiska i wynagrodzenie pracowników oraz
średnie
wynagrodzenie w ich
działach
SELECT imie, nazwisko, wynagrodzenie, srwyn
FROM pracownicy
INNER JOIN
(SELECT IDDzialu, AVG(wynagrodzenie) srwyn
FROM pracownicy
GROUP BY IDDzialu) srednie
ON pracownicy.IDDzialu=srednie.IDDzialu;
Z uŜyciem podzapytania skonstruowaliśmy tabelę zawierającą
średnie
wynagrodzenia w
poszczególnych działach i złączyliśmy ją stosownie z tabelą
pracownicy
13. Podzapytanie skorelowane we frazie SELECT instrukcji SELECT:
Podaj numery numery zamowień i nazwy firm klientów je składających:
SELECT idzam, (SELECT firma FROM klienci WHERE idklienta=z.idklienta)
„Nazwa firmy klienta”
FROM zamowienia z;
14. Podzapytanie w instrukcji UPDATE, skorelowane:
Ustal wynagrodzenia pracowników na
średnie
w ich działach:
UPDATE pracownicy
SET wynagrodzenie =
(SELECT AVG(wynagrodzenie)
FROM pracownicy p
WHERE p.IDDzialu=pracownicy.IDDzialu);
15.
Podzapytania wierszowe w instrukcji UPDATE
Podnieś cenę towarów o 1 PLN i ustal maksymalną dopuszczalną bonifikatę (zawartość kolumny
maxbon
w tabeli towary) na dotychczas maksymalną uŜytą (maksymalna zawartość kolumny
bonifikata
w tabeli zamszcz, po wierszach dotyczących danego towaru)
UPDATE towary
SET (cena,maxbon)=
(SELECT cena+1, MAX(bonifikata)
FROM zamszcz
WHERE idtowaru=towary.IDtowaru);

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin