Ćwiczenie 3
Cel pracy: Zapoznanie się z dostępnymi funkcjami grupującymi.
Grupowanie danych za pomocą klauzuli GROUP BY.
Dołączanie i odrzucanie grupowanych wierszy za pomocą klauzuli HAVING.
Rodzaje Funkcji Grupujących:
Składnia |
Przeznaczenie |
Przykład |
AVG ([DISTINCT | ALL] wyrażenie) |
wartość średnia wyrażeń, NULL nie jest uwzględniane |
SELECT AVG(sal) „Średnia" |
COUNT ([DISTINCT | ALL] wyrażenie) |
ilość wystąpień wartości wyrażeń różnych od NULL, gwiazdka (*) w miejscu wyrażenia powoduje obliczenia ilości wszystkich wierszy łącznie z duplikatami i wartościami NULL |
SELECT COUNT(comm) "prowizje" FROM emp |
MAX ([DISTINCT | ALL] wyrażenie) |
maksymalna wartość wyrażenia |
SELECT MAX(sal) "Max płaca" |
MIN ([DISTINCT | ALL] wyrażenie) |
minimalna wartość wyrażenia |
SELECT MIN(sal) "Min płaca" |
SUM ([DISTINCT | ALL] wyrażenie) |
suma wartości wyrażeń, bez uwzględniania wartości NULL |
SELECT SUM(sal) "Koszty osobowe" |
STDDEV ([DISTINCT | ALL] wyrażenie) |
odchylenie standardowe wyrażeń, bez uwzględniania wartości NULL |
SELECT STDDEY(sal) "Odchylenie" |
VARIANCE ([DISTINCT | ALL] wyrażenie) |
wariancja wyrażeń, bez uwzględniania wartości NULL |
SELECT VARIANCE(sal) "Wariancja" |
Kwalifikator DISTINCT ogranicza działanie funkcji grupowych do różnych wartości argumentów.
Kwalifikator ALL jest domyślny — funkcje grupowe nie eliminują duplikatów.
Argumentami funkcji grupowych są liczby, a w przypadku funkcji MAX, MIN i COUNT także daty, znaki i ciągi znaków.
Wszystkie funkcje grupowe, za wyjątkiem COUNT(*) ignorują wartości NULL
Do podzielenia wierszy tablicy na grupy używamy klauzuli GROUP BY. Pojedynczą grupę stanowią wszystkie wiersze, dla których wartości podane w klauzuli GROUP BY są identyczne.
Do wybierania interesujących nas grup służy klauzula HAVING. W klauzuli HAVING umieszczamy warunek wyrażony za pomocą funkcji grupowej
Nie wolno używać klauzuli WHERE do odrzucania grup.
Funkcje numeryczne:
ROUND (kolumna | wyrażenie, n) - zaokrągla kolumnę, wyrażenie lub wartość do n miejsc po znaku separacji części dziesiętnej lub gdy n jest pominięte odrzucona zostanie część ułamkowa liczby; Jeśli n jest ujemne to będą zaokrąglane liczby całkowite; np. ROUND(56.837,2); wynik: 56.84
TRUNC (kolumna | wyrażenie, n) - obcina kolumnę, wyrażenie lub wartość do n miejsc po znaku separacji części dziesiętnej, gdy n jest pominięte to zostaje przyjęte 0; np. TRUNC(56.832, 2); wynik: 56.83
MOD (m,n) - zwraca resztę z dzielenia; np. MOD(1300,300); wynik: 100
Wymagany porządek klauzul jest następujący:
SELECT lista wyrażeń
FROM tabela
WHERE warunek selekcji wierszy
GROUP BY wyrażenia
HAVING warunek selekcji grup
ORDER BY wyrażenia
Zadania do zapytań SQL:
Ile rekordów zawiera tabela EMP ?. Użyj funkcji COUNT(*).
Jak wielu pracowników ma nazwisko zakończone literą „D" ?
Utwórz dwa rozwiązania (z LIKE oraz z SUBSTR).
Znajdź minimalną i maksymalna pensję(SAL) w firmie.
Znajdź różnice między najwyższą i najniższą pensją
Znajdź średnią i sumę pensji w firmie. Zaokrąglij wyniki do liczby całkowitej.
Znajdź minimalną, maksymalną, średnią i sumę pensji w firmie.
Zmodyfikuj zapytanie 6, tak aby wyświetlało minimalną i maksymalną pensję oraz średnią i sumę pensji dla każdego stanowiska(JOB)
Znajdź średnią i maksymalną pensję dla każdego departamentu (DEPTNO).
W zadaniu 8 ogranicz liczbę grup do tych departamentów,
w których maksymalna płaca jest większa niż 2900.
Podaj ile osób pracuje na tym samym stanowisku
Podaj ile osób pracuje w każdym z departamentów
Oblicz, ilu pracowników jest kierownikami (MGR).
Podaj najniższe pensje wypłacane podwładnym swoich kierowników.
Wyeliminuj grupy o minimalnych zarobkach niższych niż 1000.
Uporządkuj według pensji.
Wyświetl kody szefów (MGR) oraz płacę (SAL) najniżej zarabiającego pracownika danego szefa. Wyłączając tych, których szef nie jest znany.
Wyłączając którąkolwiek grupę gdzie minimalna płaca jest mniejsza niż 1000.
Posortuj wyniki wg malejącej pensji (SAL).
Znajdź departamenty zatrudniające powyżej trzech pracowników