Ć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" FROM emp
COUNT ([OISTINCT ] ALL] wyrażenie)	ilość wystąpień wartości wyrażeń różnych od NU LL. gwiazdka {*} w miejscu wyrażenia powoduje obliczenia ilości wszystkich wierszy łącznie z duplikatami i wartościami NULL	SELECT COUNT(comm) "prowizje" FROMemp SELECT COUNTC) "Wszystko" FROM emo
MAX ([DISTINCT | ALL] wyrażenie)	maksymalna wartość wyrażenia	SELECT MAX(sal) "Max phca" FROM enip
MIN ([DISTINCT | ALL] wyrażenie)	minimalna wartość wyrażenia	SELECT MIN(sal) "Min płaca" FROMemp
SUM ([DISTINCT | ALL] wyrażenie)	suma wartości wyrażeń, bez uwzględniania wartości NULL	SELECT SUM(sal) "Koszty osobowe" FROM enip
STDDEV ([DISTINCT i ALL] wyrażenie)	odchylenie standardowe wyrażeń, bez uwzyli;dnianl.i wartości NULL	SELECT STDDEY(sal) "Odchylenie" FROM cmp
YARIANCE ([DISTINCT | ALL] wyrażenie)	wariancja wyrażeń, bez uwzględniania wartości NULL	SELECT VARlANCE(sal) "Wariancja" FROM emp

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ętne) 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. ROIIND(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

GROU P BY wyrażenia

HAVING warunek selekcji grup

ORDER BY wyrażenia

Zadania do zapytań SQL:

1. Ile rekordów zawiera tabela EMP ?. Użyj funkcji COUNT(*).

SELECT COUNT (*) FROM EMP;

2. Jak wielu pracowników ma nazwisko zakończone literą „D" ? Utwórz dwa rozwiązania (z LIK.F. oraz z SUBSTR).

SELECT COUNT (*) FROM EMP WHERE ENAME LIKE '%D';

3. Znajdź minimalną i maksymalna pensję(SAL) w firmie.

SELECT MIN (SAL) AS MIN, MAX (SAL) AS MAX FROM EMP;

4. Znajdź różnice między najwyższą i najniższą pensją

SELECT MAX (SAL) - MIN (SAL) FROM EMP;

5. Znajdź średnią i sumę pensji w firmie. Zaokrąglij wyniki do liczby całkowitej.

SELECT TRUNC(AVG (SAL)) AS ROZNICA FROM EMP;

6. Znajdź minimalną, maksymalną, średnią i sumę pensji w firmie.

SELECT MIN (SAL) AS MIN, MAX (SAL) AS MAX, AVG (SAL) AS AVG, SUM (SAL) AS SUM FROM EMP;

7. Zmodyfikuj zapytanie 6, tak aby wyświetlało minimalną i maksymalną pensję oraz średnią sumę pensji dla każdego stanowiska(JOB)

SELECT MIN (SAL) AS MIN, MAX (SAL) AS MAX, TRUNC(AVG (SAL)) AS AVG, SUM (SAL) AS SUM FROM EMP GROUP BY JOB;

8. Znajdź średnią i maksymalną pensję dla każdego departamentu (DEPTNO).

SELECT DEPTNO, TRUNC(AVG (SAL)) AS AVG, MAX (SAL) AS MAX FROM EMP GROUP BY DEPTNO;

9. W zadaniu 8 ogranicz liczbę grup do tych departamentów, w których maksymalna płaca jest większa niż 2900.

SELECT DEPTNO, TRUNC(AVG (SAL)) AS AVG, MAX (SAL) AS MAX FROM EMP GROUP BY DEPTNO HAVING MAX(SAL)>2900;

10. Podaj ile osób pracuje na tym samym stanowisku

SELECT JOB, COUNT(JOB) FROM EMP GROUP BY JOB;

11. Podaj ile osób pracuje w każdym z departamentów

SELECT DEPTNO, COUNT(DEPTNO) FROM EMP GROUP BY DEPTNO;

12. Oblicz, ilu pracowników jest kierownikami (MGR).

SELECT COUNT(MGR) FROM EMP WHERE MGR IS NOT NULL;

13. 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.

SELECT ENAME, SAL FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY MGR HAVING MIN(SAL)<1000);

14. Wyświetl kody szefów (MGR) oraz płacę (SAL) najniżej zarabiającego pracownika danego szefa.

SELECT MGR, SAL FROM EMP WHERE SAL IN (SELECT MIN(SAL) FROM EMP GROUP BY MGR HAVING MIN(SAL)<1000);

Wyłączając tych, których szef nie jest znany

SELECT MGR, MIN(SAL) FROM EMP WHERE MGR IS NOT NULL GROUP BY MGR HAVING MIN(SAL)>1000;

Wyłączając którąkolwiek grupę gdzie minimalna płaca jest mniejsza niż 1000.

SELECT MGR, MIN(SAL) FROM EMP WHERE MGR IS NOT NULL GROUP BY MGR HAVING MIN(SAL)>1000 ORDER BY MIN(SAL) ASC;

Posortuj wyniki wg malejącej pensji (SAL).

SELECT MGR, MIN(SAL) FROM EMP WHERE MGR IS NOT NULL GROUP BY MGR HAVING MIN(SAL)>1000 ORDER BY MIN(SAL) ASC;

SELECT MGR, MIN(SAL) FROM EMP WHERE MGR IS NOT NULL GROUP BY MGR HAVING MIN(SAL)>1000 ORDER BY MIN(SAL) ASC;

15. Znajdź departamenty zatrudniające powyżej trzech pracowników

SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING (COUNT(ENAME))>3;

SELECT DEPTNO, COUNT(ENAME) AS ILU_PRACOWNIKOW FROM EMP GROUP BY DEPTNO HAVING (COUNT(ENAME))>3;

1

---