Ć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;

COUNT(*)

----------

14

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';

COUNT(*)

----------

2

select COUNT(*) from emp where SUBSTR(ename,-1,1)='D';

COUNT(*)

----------

2

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

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

MIN MAX

---------- ----------

800 5000

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

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

MAX(SAL)-MIN(SAL)

-----------------

4200

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

SQL> SELECT TRUNC(AVG (SAL)) AS SREDNIA, TRUNC(SUM(sal)) AS SUMA from emp;

SREDNIA SUMA

---------- ----------

2073 29025

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;

MIN MAX AVG SUM

---------- ---------- ---------- ----------

800 5000 2073,21429 29025

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

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

JOB MIN MAX AVG SUM

--------- ---------- ---------- ---------- ----------

ANALYST 3000 3000 3000 6000

CLERK 800 1300 1037 4150

MANAGER 2450 2975 2758 8275

PRESIDENT 5000 5000 5000 5000

SALESMAN 1250 1600 1400 5600

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;

DEPTNO AVG MAX

---------- ---------- ----------

10 2916 5000

20 2175 3000

30 1566 2850

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;

DEPTNO AVG MAX

---------- ---------- ----------

10 2916 5000

20 2175 3000

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

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

JOB COUNT(JOB)

--------- ----------

ANALYST 2

CLERK 4

MANAGER 3

PRESIDENT 1

SALESMAN 4

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

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

DEPTNO COUNT(DEPTNO)

--------- -------------

10 3

20 5

30 6

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

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

COUNT(MGR)

----------

13

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);

ENAME SAL

---------- ----------

SMITH 800

JAMES 950

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);

MGR SAL

---------- ----------

7902 800

7698 950

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;

MGR MIN(SAL)

---------- ----------

7566 3000

7782 1300

7788 1100

7839 2450

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;

MGR MIN(SAL)

---------- ----------

7788 1100

7782 1300

7839 2450

7566 3000

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;

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

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

DEPTNO

----------

20

30

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

DEPTNO ILU_PRACOWNIKOW

---------- ---------------

20 5

30 6

4

---