Zajęcia 3 - Podstawy SQL 3

Sekcja: Podstawy SQL 3

60. Funkcje kolumnowe.

Istnieją następujące funkcje kolumnowe, które mogą być używane w klauzulach SELECT i
HAVING:
SUM – funkcja oblicza sumę wartości w określonych kolumnach;
AVG – oblicza średnią wartość w kolumnie;
MIN- znajduje minimalną wartość w kolumnie;
MAX – znajduje maksymalną wartość w kolumnie;
COUNT – służy do zliczania wystąpień pewnej wartości w wierszach.
Poniższe zapytanie wyświetla całkowitą sumę pensji wszystkich pracowników, średnią
pensję, minimalną i maksymalną pensję oraz ilość pracowników.

SELECT SUM (p.pensja) AS Pensja, AVG (p.pensja) AS Srednia,
MIN (p.pensja) AS Pensja_min, MAX (p.pensja) AS Pensja_max, COUNT (*) AS
Ilosc
FROM pracownicy p;

Przykład 60: Funkcje kolumnowe.

61. Zastosowanie funkcji kolumnowej COUNT.

Funkcja COUNT może być używana do zliczania wierszy zawierających powtarzającą się
wartość. W poniższym przykładzie zapytanie zlicza liczbę działów i stanowisk w firmie.

SELECT COUNT(DISTINCT p.dzial) AS Liczba_dzialow, COUNT(DISTINCT
p.stanowisko) AS Liczba_stanowisk
FROM pracownicy p;

Przykład 61: Zastosowanie funkcji kolumnowej COUNT.

62. Funkcje kolumnowe z wyborem wierszy.

Stosowanie funkcji kolumnowych można przeprowadzić również na pewnym podzbiorze
wierszy, stosując klauzulę WHERE. Funkcje kolumnowe dotyczą wtedy tylko tych wierszy
które wybiera warunek występujący w klauzuli WHERE.

SELECT SUM (p.pensja) AS pensja, AVG (p.pensja) AS srednia,

MIN (p.pensja) AS pensja_min, MAX (p.pensja) AS pensja_max, COUNT (*) AS
ilosc
FROM pracownicy p
WHERE p.dzial = 'Obslugi Klienta';

Przykład 62: Funkcje kolumnowe z wyborem wierszy.

63. Funkcje grupujące - klauzula GROUP BY

Klauzula GROUP BY grupuje wiersze o tej samej wartości wyszczególnionych kolumn.
Funkcje agregujące w klauzuli SELECT operują na każdej grupie osobno.

SELECT p.stanowisko, SUM (p.pensja) AS pensja, AVG (p.pensja) AS srednia,
MIN (p.pensja) AS pensja_min, MAX (p.pensja) AS pensja_max, COUNT (*) AS
ilosc
FROM pracownicy p
GROUP BY p.stanowisko
ORDER BY p.stanowisko;

Przykład 63: Funkcje grupujące - klauzula GROUP BY

64. Funkcje grupujące - klauzula HAVING.

Klauzulę HAVING używamy razem z klauzulą GROUP BY w celu ograniczenia
wyświetlanych grup. Warunek szukania musi zawierać funkcję agregującą. Po zgrupowaniu
wierszy przez klauzulę GROUP BY, klauzula HAVING wyświetla tylko te wiersze spośród
zgrupowanych, które spełniają warunki wyszczególnione w klauzuli HAVING.

SELECT p.nazwisko, SUM (w.cena_jedn)
FROM pracownicy p, wypozyczenia w
WHERE p.nr_prac = w.nr_prac_wyd
GROUP BY p.nazwisko
HAVING SUM (w.cena_jedn) > 400
ORDER BY p.nazwisko;

Przykład 64: Funkcje grupujące - klauzula HAVING.

70. Łączenie tabel wynikowych (UNION, INTERSECT, EXCEPT)

Język SQL umożliwia wykonywanie operacji sumy, przekroju oraz różnicy na zbiorach.
Pozwalają one połączyć wyniki dwóch lub więcej zapytań w jedną tabele wynikową.

•

Suma dwóch tabel A i B to tabela zawierająca wiersze występujące w tabeli A, w
tabeli B albo w obu tabelach jednocześnie. Do wykonania operacji sumy stosujemy
słowo kluczowe UNION pomiędzy dwoma poleceniami SELECT.

•

Przekrój dwóch tabel A i B to tabela zawierające wiersze, które są jednocześnie w
tabeli A i w tabeli B. Do wykonania operacji przekroju stosujemy słowo kluczowe
INTERSECT pomiędzy dwoma poleceniami SELECT.

•

Różnica dwóch tabel A i B to tabela, zawierająca wszystkie wiersze występujące w
tablicy A, które nie występują w tablicy B. Do wykonania operacji różnicy stosujemy
słowo kluczowe EXCEPT pomiędzy dwoma poleceniami SELECT.

Jeżeli zastosujemy po nazwie operacji słowo kluczowe ALL to wynik operacji będzie
zawierał powtarzające się wiersze, jeśli takie występują.

70a. Zastosowanie operacji UNION.

Operacja UNION łączy dwa lub więcej polecenia SELECT w jedną tabelę wynikową.
Zapytania SELECT muszą zwracać tę samą liczbę kolumn. Kolumny pokrywające muszą
mieć tę samą szerokość i typ danych. Nazwy kolumn mogą być różne. Operacja UNION
łączy dwa ( lub więcej) zestawy wyników w jeden i jednocześnie usuwa duplikaty.
Zapytanie zwraca dane o imieniu i nazwisku wszystkich klientów i pracowników, których
nazwiska kończą się na „ski”. Ponieważ duplikaty są usuwane tylko jedna osoba o nazwisku
Jan Kowalski będzie na liście.

SELECT imie, nazwisko
FROM klienci
WHERE nazwisko LIKE '%ski'
UNION
SELECT imie, nazwisko
FROM pracownicy
WHERE nazwisko LIKE '%ski';

Przykład 70a: Zastosowanie operacji UNION.

71. Zastosowanie operacji UNION - zmiana porządku sortowania.

Operacja UNION wyświetla wyniki uporządkowane rosnąco ale w obrębie tabeli. Jeśli
chcemy zmienić porządek sortowania dla całej sumu musimy klauzulę ORDER BY umieścić
na końcu.

SELECT imie, nazwisko
FROM klienci
WHERE nazwisko LIKE '%ski'
UNION
SELECT imie, nazwisko

FROM pracownicy
WHERE nazwisko LIKE '%ski'
ORDER BY nazwisko DESC;

Przykład 71: Zastosowanie operacji UNION - zmiana porządku sortowania.

72. Zastosowanie operacji UNION ALL.

Różnica pomiędzy operacją UNION a UNION ALL polega na tym, że wynik łączenia
zapytań operacją UNION ALL zawiera powtarzające się wiersze. Operacja UNION ALL
działa szybciej niż UNION.

SELECT imie, nazwisko
FROM klienci
WHERE nazwisko LIKE '%ski'
UNION ALL
SELECT imie, nazwisko
FROM pracownicy
WHERE nazwisko LIKE '%ski'
ORDER BY nazwisko;

Przykład 72: Zastosowanie operacji UNION ALL.

73. Zastosowanie operacji INTERSECT.

Operacja INTERSECT (różnica zbiorów pracownicy i klienci) wyświetli nam nazwiska i
imiona osób które występują jednocześnie w tabeli pracownicy i klienci.

SELECT nazwisko, imie
FROM pracownicy
INTERSECT
SELECT nazwisko, imie
FROM klienci
ORDER BY nazwisko, imie

Przykład 73: Zastosowanie operacji INTERSECT.

74. Zastosowanie operacji EXCEPT.

Operacja EXCEPT (różnica zbiorów pracownicy i klienci) wyświetli nam nazwiska i imiona
osób które występują w tabeli pracownicy a nie występują w tabeli klienci.

SELECT nazwisko, imie
FROM pracownicy
EXCEPT
SELECT nazwisko, imie
FROM klienci
ORDER BY nazwisko, imie

Przykład 73: Zastosowanie operacji EXCEPT.

Sekcja: Podzapytania w języku SQL

80. Rodzaje podzapytań w języku SQL.

Język SQL umożliwia tworzenie wielopoziomowych zapytań umożliwiających w strukturalny
sposób wybieranie danych z bazy. Podzapytania możemy stosować w różnych klauzulach.
Przeanalizujemy teraz zastosowanie pełnego zapytania SELECT umieszczonego w innym
zapytaniu SELECT. Wynik takiego wewnętrznego zapytania pomaga w określeniu
końcowego wyniku, obliczanego jako wynik zapytania zewnętrznego. Zapytania wewnętrzne
możemy wykorzystywać w klauzulach WHERE i HAVING zewnętrznego zapytania
SELECT. Nazywamy je wtedy podzapytaniami lub zapytaniami zagnieżdżonymi. Poziom
zagnieżdżania może być dowolny. Należy pamiętać jednak, ze każdy poziom zagnieżdżenia
spowalnia wykonanie zapytania. Zapytania wewnętrzne mogą występować również w
poleceniach INSERT, UPDATE i DELETE.
Możemy wyodrębnić w języku SQL trzy rodzaje podzapytań:

•

Podzapytania skalarne zwracające jedną wartość (jedna kolumna, jeden wiersz). Mogą
być zawsze stosowane gdy wymagana jest jedna wartość.

•

Podzapytania krotkowe zwracające kilka wierszy i tylko jedną kolumnę.

•

Podzapytania tabelowe zwracające jedną lub więcej kolumn i wiele wierszy.

81. Rozwiązanie problemu za pomocą dwóch zapytań.

Chcemy wyszukać pracowników, którzy otrzymują wynagrodzenie w kwocie wyższej niż
ś

rednia. Sprawdzamy za pomocą pierwszego zapytania jaka jest średnia pensja w naszym

przedsiębiorstwie. Następnie za pomocą drugiego zapytania szukamy pracowników
zarabiających powyżej średniej.

-- pierwsze zapytanie wyliczenie

redniej

SELECT AVG (p.pensja)

FROM pracownicy p;
-- drugie zapytanie zwracaj

ce pensje pracowników wi

ksze ni

rednia

SELECT p.imie, p.nazwisko, p.dzial, p.stanowisko, p.pensja
FROM pracownicy p
WHERE p.pensja > 1530;

Przykład 81: Rozwiązanie problemu za pomocą dwóch zapytań.

82. Podzapytanie z funkcją agregującą.

To samo możemy osiągnąć stosując w klauzuli WHERE podzapytanie zwracające wartość
ś

rednią pensji w przedsiębiorstwie. Należy pamiętać aby podzapytanie ująć w nawiasy ().

Stosując podzapytania należy pamiętać, że w przypadku dużych baz czas wykonania
wielopoziomowego zapytania może być znaczny.

SELECT p.imie, p.nazwisko, p.dzial, p.stanowisko, p.pensja
FROM pracownicy p
WHERE p.pensja > (SELECT AVG(p.pensja)FROM pracownicy p);

Przykład 81: Podzapytanie z funkcją agregującą.

83. Podzapytanie z zastosowaniem IN.

Słowo kluczowe IN pozwala na zidentyfikowanie wszystkich elementów ze zbioru A, które
występują w zbiorze B.
Poniższe zapytanie wyświetla listę samochodów (zbiór A), których zostały choć raz
wypożyczone klientom. Zapytanie wybiera te samochody, które znajdują się w tabeli
wypożyczenia (zbiór B).

SELECT s.nr_samochodu, s.marka, s.model
FROM samochody s
WHERE s.nr_samochodu
IN (SELECT w.nr_samochodu FROM wypozyczenia w)
ORDER BY nr_samochodu;

Przykład 83: 83. Podzapytanie z zastosowaniem IN.

84. Podzapytanie z zastosowaniem NOT IN.

Słowo kluczowe NOT IN pozwala na zidentyfikowanie wszystkich elementów ze zbioru A,
które nie występują w zbiorze B.
Poniższe zapytanie wyświetla listę samochodów (zbiór A), które nigdy nie były wypożyczone
klientom . Zapytanie wybiera te samochody, które nie znajdują się w tabeli wypożyczenia
(zbiór B).

SELECT s.nr_samochodu, s.marka, s.model
FROM samochody s
WHERE s.nr_samochodu
NOT IN (SELECT w.nr_samochodu FROM wypozyczenia w)
ORDER BY nr_samochodu;

Przykład 84: Podzapytanie z zastosowaniem NOT IN.

85. Podzapytanie z zastosowaniem równości.

W zapytaniu chcemy wyszukać wszystkich pracowników pracujących w oddziale (tabela
miejsca) mieszczących się w określonym mieście (np. Warszawa). W poniższym przykładzie
wewnętrzne zapytanie zwraca nam nr_miejsca oddziału znajdującego się w Warszawie.
Należy pamietać, że w tym przykładzie zapytanie wewnętrzne może zwrócić tylko jeden
wynik, gdyż używamy w warunku klausuli WHERE znaku równości.

SELECT p.nr_prac, p.imie,p.nazwisko,p.dzial
FROM pracownicy p
WHERE nr_miejsca=(SELECT nr_miejsca FROM miejsca WHERE miasto='Warszawa');

Przykład 85: Podzapytanie z zastosowaniem równości.

86. Podzapytanie z zastosowaniem ALL.

Słowo ALL może być używane z podzapytaniami, które dają w wyniku pojedyńczą kolumnę
liczb. Jeśli przed podzapytaniem występuje słowo kluczowe ALL, warunek będzie prawdziwy
gdy spełniają go wszystkie wartości otrzymane jako wynik podzapytania.
W poniższym przykładzie chcemy otrzymać listę pracowników, których pensja jest wyższa
od wszystkich średnich pensji w ramach działów.
Zapytanie będzie wykonywane w dwóch krokach. Najpierw wykonywane jest pod-zapytanie,
które znajduje średnią pensję w każdym dziale. Następnie każda pensja pracownika,
porównywana jest z listą średnich pensji. Wyświetleni zostaną pracownicy, których pensja
jest wyższa od wszystkich średnich pensji obliczonych w pod-zapytaniu.

SELECT p.imie, p.nazwisko, p.dzial, p.stanowisko, p.pensja
FROM pracownicy p
WHERE p.pensja > ALL ( SELECT AVG (p.pensja) FROM pracownicy p GROUP BY
p.dzial);

Przykład 86: Podzapytanie z zastosowaniem ALL.

87. Podzapytanie z zastosowaniem ANY.

Słowo ANY (SOME) może być używane z podzapytaniami, które dają w wyniku pojedyńczą
kolumnę liczb. Jeśli przed podzapytaniem występuje słowo kluczowe ANY, warunek będzie
prawdziwy o ile spełnia go chociaż jedna wartość otrzymane jako wynik podzapytania.
W poniższym przykładzie chcemy otrzymać listę pracowników, których pensja jest wyższa
od dowolnej średnej pensji w ramach działów.
Zapytanie będzie wykonywane w dwóch krokach. Najpierw wykonywane jest pod-zapytanie,
które znajduje średnią pensję w każdym dziale. Następnie każda pensja pracownika,
porównywana jest z listą średnich pensji. Wyświetleni zostaną pracownicy, których pensja
jest wyższa od dowolnej średniej pensji obliczonejh w pod-zapytaniu.

SELECT p.imie, p.nazwisko, p.dzial, p.stanowisko, p.pensja
FROM pracownicy p
WHERE p.pensja > ANY ( SELECT AVG (p.pensja) FROM pracownicy p GROUP BY
p.dzial);

Przykład 87: Podzapytanie z zastosowaniem ANY.