Życie pisze najbardziej oryginalne, najbardziej komiczne, a jednocześnie najbardziej dramatyczne scenariusze.
ENAME DEPTNO HIREDATE ---------- --------- --------- SCOTT 20 09-DEC-82 MILLER 10 23-JAN-82 11. Wybierz nazwiska, roczną pensję oraz prowizję tych wszystkich pracowników, których miesięczna pensja przekracza prowizję. Wyniki posortuj według malejących zarobków. Jeśli dwóch lub więcej pracowników ma taką samą pensję, uporządkuj dane o nich według nazwisk zgodnie z alfabetem. ENAME ANNUAL_SAL COMM ---------- ---------- --------- ALLEN 19200 300 TURNER 18000 0 WARD 15000 500 12. Spowoduj wyświetlenie następujących wyników. Kto, gdzie, kiedy? ------------------------------------------------------------------------------- KING pracuje na stanowisku PRESIDENT w zespole 10 od 17-NOV-81 BLAKE pracuje na stanowisku MANAGER w zespole 30 od 01-MAY-81 CLARK pracuje na stanowisku MANAGER w zespole 10 od 09-JUN-81 JONES pracuje na stanowisku MANAGER w zespole 20 od 02-APR-81 MARTIN pracuje na stanowisku SALESMAN w zespole 30 od 28-SEP-81 ALLEN pracuje na stanowisku SALESMAN w zespole 30 od 20-FEB-81 TURNER pracuje na stanowisku SALESMAN w zespole 30 od 08-SEP-81 JAMES pracuje na stanowisku CLERK w zespole 30 od 03-DEC-81 WARD pracuje na stanowisku SALESMAN w zespole 30 od 22-FEB-81 FORD pracuje na stanowisku ANALYST w zespole 20 od 03-DEC-81 SMITH pracuje na stanowisku CLERK w zespole 20 od 17-DEC-80 SCOTT pracuje na stanowisku ANALYST w zespole 20 od 09-DEC-82 ADAMS pracuje na stanowisku CLERK w zespole 20 od 12-JAN-83 MILLER pracuje na stanowisku CLERK w zespole 10 od 23-JAN-82 Funkcje grupowe Funkcje grupowe służą do działania na grupach wierszy. Wynikiem funkcji grupowej jest pojedyncza wartość dla całej grupy. Jeśli nie wyspecyfikujemy inaczej, wszystkie wiersze tabeli są traktowane jako jedna grupa. Funkcja Wynik funkcji AVG ([DISTINCT | ALL] wyrażenie) wartość średnia wyrażeń, NULL nie jest uwzględniane COUNT ([DISTINCT | ALL] wyrażenie) ilość wystąpień wartości wyrażeń różnych od NULL, gwiazdka (*) użyta w miejscu wyrażenia powoduje obliczenia ilości wszystkich wierszy łącznie z duplikatami i wartościami NULL MAX ([DISTINCT | ALL] wyrażenie) maksymalna wartość wyrażenia MIN ([DISTINCT | ALL] wyrażenie) minimalna wartość wyrażenia STDDEV ([DISTINCT | ALL] wyrażenie) odchylenie standardowe wyrażeń, bez uwzględniania wartości NULL SUM ([DISTINCT | ALL] wyrażenie) suma wartości wyrażeń, bez uwzględniania wartości NULL VARIANCE ([DISTINCT | ALL] wyrażenie) wariancja wyrażeń, bez uwzględniania wartości NULL 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. Stosowanie funkcji grupowych Aby obliczyć średni zarobek w firmie, napiszemy: SELECT AVG(SAL) FROM EMP; AVG(SAL) ---------- 2073,21429 Zwróćmy uwagę, że została zwrócona jedna wartość dla wszystkich wierszy. Cała firma jest tu traktowana jako jedna grupa. Można nałożyć na to polecenie warunek w klauzuli WHERE, np. aby znaleźć minimalny zarobek na stanowisku 'MANAGER', napiszemy: SELECT MIN(SAL) FROM EMP WHERE JOB='MANAGER'; MIN(SAL) ---------- 2450 Aby znaleźć, ilu pracowników pracuje w departamencie 10, napiszemy: SELECT COUNT(*) FROM EMP WHERE DEPTNO=10; COUNT(*) ---------- 3 Klauzula GROUP BY 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. Aby obliczyć średnie zarobki w każdym departamencie, napiszemy: SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO AVG(SAL) ---------- ---------- 10 2916,66667 20 2175 30 1566,66667 Przed grupowaniem możemy wyeliminować pewne wiersze za pomocą klauzuli WHERE: SELECT JOB, MAX(SAL) FROM EMP WHERE JOB<>'CLERK' GROUP BY JOB; JOB MAX(SAL) --------- ---------- ANALYST 3000 MANAGER 2975 PRESIDENT 5000 SALESMAN 1600 W klauzuli GROUP BY można podać kilka wyrażeń, wtedy wiersze będą grupowane w mniejszych grupach. Aby obliczyć minimalny zarobek w każdym departamencie w podziałem na stanowiska, napiszemy: SELECT DEPTNO, JOB, MIN(SAL) FROM EMP GROUP BY DEPTNO, JOB; DEPTNO JOB MIN(SAL) ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 3000 20 CLERK 800 20 MANAGER 2975 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1250 Wybierane wyrażenia a funkcje grupowe Poniższe polecenie spowoduje obliczenie średnich zarobków w departamentach. Nie będzie jednak zbyt użyteczne, bo nie będzie widoczne, która średnia odpowiada któremu departamentowi. SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO; AVG(SAL) ---------- 2916,66667 2175 1566,66667 Aby poprawić to polecenie, umieśćmy na liście wyboru klauzuli SELECT także numer departamentu: SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO; DEPTNO AVG(SAL) ---------- ---------- 10 2916,66667 20 2175 30 1566,66667 Na liście wyboru klauzuli SELECT mogą występować tylko i wyłącznie nazwy kolumn, które są przedmiotem działania klauzuli GROUP BY, chyba, że występują one jako argument funkcji grupującej. Klauzula HAVING Do wybierania interesujących nas grup służy klauzula HAVING. W klauzuli HAVING umieszczamy warunek wyrażony za pomocą funkcji grupowej