Oracle FUNCTIONS with examples 5

SELECT DEPTNO,SUM(SAL)FROM EMP GROUP BY DEPTNO;

DEPTNO               SUM(SAL)

30           9400

20           10875

10           8750

SELECT DEPTNO,JOB,SUM(SAL) FROM EMP GROUP BY DEPTNO,JOB;

DEPTNO               JOB        SUM(SAL)

20           CLERK    1900

30           SALESMAN         5600

20           MANAGER          2975

30           CLERK    950

10           PRESIDENT          5000

30           MANAGER          2850

10           CLERK    1300

10           MANAGER          2450

20           ANALYST              6000

SELECT JOB,MIN(SAL),MAX(SAL) FROM EMP WHERE DEPTNO=30 GROUP BY JOB;

JOB        MIN(SAL)            MAX(SAL)

SALESMAN         1250       1600

CLERK    950         950

MANAGER          2850       2850

SELECT DEPTNO,SUM(SAL),MAX(SAL) FROM EMP WHERE JOB='CLERK' GROUP BY DEPTNO;

DEPTNO               SUM(SAL)           MAX(SAL)

30           950             950

20           1900       1100

10           1300       1300

HAVING CLAUSE:

***************

SELECT * FROM EMP HAVING 1=1;

EMPNO                ENAME JOB        MGR      HIREDATE            SAL         COMM DEPTNO

7369       SMITH   CLERK    7902       12/17/1980         800                         20

7499       ALLEN   SALESMAN         7698       2/20/1981            1600       300         30

7521       WARD   SALESMAN         7698       2/22/1981            1250       500         30

7566       JONES   MANAGER          7839       4/2/1981              2975                       20

7654       MARTIN               SALESMAN         7698       9/28/1981            1250       1400       30

7698       BLAKE   MANAGER          7839       5/1/1981              2850                       30

7782       CLARK   MANAGER          7839       6/9/1981              2450                       10

7788       SCOTT   ANALYST              7566       12/9/1982            3000                       20

7839       KING     PRESIDENT                          11/17/1981         5000                       10

7844       TURNER               SALESMAN         7698       9/8/1981              1500       0              30

7876       ADAMS                CLERK    7788       1/12/1983            1100                       20

7900       JAMES  CLERK    7698       12/3/1981            950                         30

7902       FORD     ANALYST              7566       12/3/1981            3000                       20

7934       MILLER CLERK    7782       1/23/1982            1300                       10

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

DEPTNO               COUNT(DEPTNO)

30           6

20           5

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

DEPTNO               COUNT(DEPTNO)

30           6

20           5

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2500;

10  2916.666666666667

SELECT JOB,SUM(SAL) PAYROLL FROM EMP WHERE JOB<>'SALESMAN' GROUP BY JOB HAVING SUM(SAL)>=5000 ORDER BY SUM(SAL) DESC;

JOB        PAYROLL

MANAGER          8275

ANALYST              6000

PRESIDENT          5000

SELECT DEPTNO,MIN(SAL),MAX(SAL),SUM(SAL) FROM EMP WHERE JOB='CLERK' GROUP BY DEPTNO HAVING MIN(SAL)<1000;

DEPTNO               MIN(SAL)            MAX(SAL)           SUM(SAL)

30           950         950         950

20           800         1100       1900

SELECT DEPTNO,AVG(SAL),SUM(SAL),MAX(SAL),MIN(SAL)FROM EMP GROUP BY DEPTNO HAVING COUNT(DEPTNO)>3;

DEPTNO               AVG(SAL)            SUM(SAL)           MAX(SAL)           MIN(SAL)

30           1566.66666666667            9400       2850       950

20           2175       10875    3000       800

SELECT DEPTNO,AVG(SAL),SUM(SAL)FROM EMP GROUP BY DEPTNO HAVING AVG(SAL)>2500;

10 2916.6666666667      8750

SELECT DEPTNO, JOB, AVG(SAL),SUM(SAL)FROM EMP GROUP BY DEPTNO,JOB HAVING AVG(SAL)>2500;

DEPTNO               JOB        AVG(SAL)            SUM(SAL)

20           MANAGER          2975       2975

10           PRESIDENT          5000       5000

30           MANAGER          2850       2850

20           ANALYST              3000       6000

 NESTING OF GROUP FUNCTION:

**************************

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

2916.66666666667

SELECT MAX(SUM(SAL)) FROM EMP GROUP BY DEPTNO;

10875

SELECT MAX(SUM(SAL)),MIN(AVG(SAL))FROM EMP GROUP BY DEPTNO;

10875     1566.666666666667

MISCELLANEOUS FUNCTION:

***********************

SYN:GREATEST(EXPR1,EXPR2,....)

SELECT GREATEST('SIEBEL','CODD','SCOTT')FROM DUAL;

SIEBEL

SELECT GREATEST(1000,2000,3000,4000)FROM DUAL;

4000

SELECT GREATEST('10-JUL-05','20-JUL-05')FROM DUAL;

20-JUL-05

SELECT GREATEST('10-JUL-04','20-JUL-05')FROM DUAL;

20-JUL-05

SELECT GREATEST('K','U','M','A','R') FROM DUAL;

U

LEAST FUNCTION:

****************

SYN:LEAST(EXPR1,EXPR2,.......)

SELECT GREATEST('K','U','M','A','R') FROM DUAL;

A

SELECT LEAST(100,200,300) FROM DUAL;

100

SELECT LEAST(100,-100)FROM DUAL;

-100

SELECT LEAST(1.5,0)FROM DUAL;

1.5

SELECT LEAST(1.5,0)FROM DUAL;

0

SELECT LEAST('01-JAN-12','18-JAN-12')FROM DUAL;

01-JAN-12

USER FUNCTION:

**************

SYN:USER

SELECT USER FROM DUAL;

SYSTEM

UID FUN:

*********

SYN:UID

SELECT USER,UID FROM DUAL;

SYSTEM   5

USERENV FUN:

************

SYN:USERENV(OPTION)

SELECT USERENV('ISDBA') FROM DUAL;

FALSE

SELECT USERENV('LANGUAGE') FROM DUAL;

AMERICAN_AMERICA.WE8MSWIN1252

SELECT USERENV('TERMINAL') FROM DUAL;

KUMAR-PC

SELECT USERENV('SESSIONID') FROM DUAL;

1564

SELECT USERENV('LANG') FROM DUAL;

US

SELECT USERENV('INSTANCE') FROM DUAL;

1

VSIZE FUNCTION:

**************

SYN:VSIZE

SELECT ENAME,VSIZE(ENAME)FROM EMP;

ENAME VSIZE(ENAME)

SMITH   5

ALLEN   5

WARD   4

JONES   5

MARTIN               6

BLAKE   5

CLARK   5

SCOTT   5

KING     4

TURNER               6

ADAMS                5

JAMES  5

FORD     4

MILLER 6

SELECT DEPTNO,VSIZE(DEPTNO)FROM DEPT;

DEPTNO               VSIZE(DEPTNO)

10           2

20           2

30           2

40           2

50           2

SELECT ENAME,HIREDATE,VSIZE(HIREDATE)FROM EMP;

ENAME HIREDATE            VSIZE(HIREDATE)

SMITH   12/17/1980         7

ALLEN   2/20/1981            7

WARD   2/22/1981            7

JONES   4/2/1981              7

MARTIN               9/28/1981            7

BLAKE   5/1/1981              7

CLARK   6/9/1981              7

SCOTT   12/9/1982            7

KING     11/17/1981         7

TURNER               9/8/1981              7

ADAMS                1/12/1983            7

JAMES  12/3/1981            7

FORD     12/3/1981            7

MILLER 1/23/1982            7

SELECT VSIZE('KUMAR')FROM DUAL;

5

SOUNDEX FUNCTION:

****************

SYN:SOUNDX(CHAR)

SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX('SMYTHE');

SMITH

SELECT ENAME,JOB FROM EMP WHERE SOUNDEX(JOB)=SOUNDEX('MANGER');

ENAME JOB

JONES   MANAGER

BLAKE   MANAGER

CLARK   MANAGER

SELECT JOB FROM EMP WHERE SOUNDEX(JOB)=SOUNDEX('CLRK');

JOB

CLERK

CLERK

CLERK

CLERK