Język SQL – zapytania zagnieżdżone (podzapytania)

1. Język SQL – zapytania zagnieżdżone(podzapytania)

2. Zapytania zagnieżdżone • Język SQL pozwala na zagnieżdżanie zapytań (podzapytania) • Oznacza to, że wynik jednego zapytania może być wykorzystany przez inne zapytanie, będące jego nadzapytaniem • Inaczej – jedno zapytanie może w klauzuli where zawierać inne, zagnieżdżone zapytanie.

3. Zapytania zagnieżdżone – przykład 1 • Podaj towary z działu, którego kierownikiem jest Kowalski select nazwa from towary where dzial = (select dzial from dzialy where kierownik = ‘Kowalski’) podzapytanie zwraca numer działu, którego kierownikiem jest Kowalski; wartość ta jest następnie wykorzystana w nadzapytaniu w warunku where

4. Zapytania zagnieżdżone c.d. • Standardowym zastosowaniem zapytań zagnieżdżonych jest testowanie przynależności do zbioru, porównywanie zbiorów, oraz sprawdzanie liczebności zbioru. • Operatory należenia do zbioru: • IN (należy) • NOT IN (nie należy)

5. Zapytania zagnieżdżone – przykład 2 • Podaj towary z działów, których kierownikiem jest Kowalski select nazwa from towary where dzial in (select dzial from dzialy where kierownik = ‘Kowalski’) Jeżeli Kowalski jest kierownikiem więcej niż jednego działu, podzapytanie zwróci zbiór wartości. Dlatego zamiast znaku = musimy zastosować operator in

6. Zapytania zagnieżdżone – przykład 3 select nazwisko, imie from pracownicy where pesel in (select peselp from obsada where rok_akad = '2003L') Pracownicy, którzy mają zajęcia na wydziale w semestrze letnim w roku akademickim 2003

7. Zadania • Podaj nazwiska pracowników, którzy pracują w dziale ‘SYSTEMY EKSPERCKIE’ • Podaj nazwiska pracowników, którzy pracują na Piotrowie. • Podaj nazwę działu, w którym nie pracuje żaden pracownik.

8. Zadania • Podaj nazwiska pracowników, którzy pracują na tym samym stanowisku co Hapke. • Podaj nazwisko najlepiej zarabiającego pracownika. • Podaj nazwiska pracowników zarabiających powyżej średniej.

9. 0 5 6 Klauzula SOME • F <porównanie> some r t r : (F <porównanie> t)gdzie <porównanie> to:  (5< some ) = true (read: 5 < some tuple in the relation) 0 ) = false (5< some 5 0 ) = true (5 = some 5 0 (5 some ) = true (bo 0  5) 5 (= some)  in Jednakże, ( some)  not in

10. 0 5 6 Klauzula ALL F <porówn> all r t r (F <porówn> t) (5< all ) = false 6 ) = true (5< all 10 4 ) = false (5 = all 5 4 (5 all ) = true (bo 5  4 i 5  6) 6 (all)  not in Jednakże, (= all)  in

11. Klauzula ALL - przykład select nazwa from towary where cena >= ALL (select cena from towary where rodzaj = ‘AGD’) Wybierz towary, których cena jest wyższa od każdej z cen towarów AGD (można oczywiście w tym przypadku zastąpić MAX)

12. Zadania • Podaj nazwiska i stanowiska pracowników, którzy pracują na stanowiskach nie obsadzonych w dziale 20 (wykorzystaj ALL). • Sprawdź, czy istnieją pracownicy zatrudnieni wcześniej od wszystkich pracowników Systemów Rozproszonych (wykorzystaj ALL). • Sprawdź, czy BRZEZINSKI zarabia więcej od któregokolwiek z innych profesorów (wykorzystaj SOME; jeżeli tak – wypisz ‘TAK’).

13. Podzapytania skorelowane • Dotychczasowe przykłady dotyczyły podzapytań nieskorelowanych – były one obliczane tylko raz, były niezależne od nadzapytania • Podzapytania skorelowane – zależą od tego, jakie wartości są przetwarzane w nadzapytaniu, wykonywane są wielokrotnie dla zmieniających się wartości

14. Przykład • Podaj nazwę najdroższego towaru od każdego z dostawców. select nazwa from towary T1 where cena = (select max(cena) from towary T2 where T2.dostawca = T1.dostawca) Podzapytanie zwraca najwyższą cenę u danego dostawcy. Wynik podzapytania zależy od tego kto jest dostawcą towaru aktualnie analizowanego w nadzapytaniu

15. Zadania • Dla każdego działu podaj nazwisko najmniej zarabiającego pracownika. • Podaj nazwiska pracowników, którzy zarabiają więcej od swojego szefa. • Podaj nazwiska pracowników, których płaca nie mieści się w ‘widełkach’ płacowych na ich stanowisku. • Podaj nazwę działu, w którym pracuje najlepiej zarabiający pracownik.

16. Test istnienia • Konstrukcja existszwraca wartość truejeżeli argument zwracany jako wartość podzapytania jest niepusty. • exists r  r  Ø • not existsr  r = Ø

17. Test istnienia - przykład • Podaj działy, w których nie ma towarów. select * from dzialy d where not exists (select * from towary t where t.dzial = d.dzial)

18. Zadania – wykorzystaj exists/not exists • Podaj działy, w których nie pracuje żaden pracownik. • Podaj nazwiska pracowników, którzy mają chociaż jednego podwładnego. • Podaj działy, w których chociaż jeden profesor zarabia powyżej 1000.

19. Zadanka ekstra • Podaj nazwiska trzech najlepiej zarabiających pracowników (z możliwością uogólnienia na n pracowników) • Znajdź dział zatrudniający najwięcej pracowników. • Sprawdź, czy istnieje pracownik, który ma podwładnych w każdym z działów (oprócz 50 oczywiście). w razie braku pomysłów – wskazówki na następnym slajdzie...

20. Zadanka ekstra - wskazówki • być jednym z trzech najlepiej zarabiających to znaczy, że co najwyżej dwie osoby zarabiają lepiej; Wykorzystaj podzapytanie zliczające te osoby; wynik podzapytania można przyrównać do liczby • nie można zrobić max(count(_)); wykorzystaj ALL • „pracownik, który ma podwładnych w każdym z działów” to inaczej: „nie istnieje dział, który nie zatrudnia podwładnych danego pracownika”; wykorzystaj „not exists”