All about SQL PL/SQL and LINUX: Oracle FUNCTIONS with examples 4

GENRAL FUNCTION:

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

NVL(EXPR1,EXPR2)

NVL2(EXPR1,EXPR2,EXPR3)

NULLIF(EXPR1,EXPR2)

COALESCE(EXPR1,EXPR2,....EXPRN)

NVL FUN:(EXPR1,EXPR2)

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

CONVERTS A NULL VALUE TO AN ACTUAL VALUE

THE DATA TYPE OF THE SOURES AND DESTINATION MUST MATCH.

NVL(COMM,0)

NVL(HIRADATE,'01-JAN-12')

NVL(JOB,'NOT ASSIGNED')

SELECT ENAME,SAL,NVL(COMM,0)FROM EMP;

ENAME SAL NVL(COMM,0)

SMITH 800 0

ALLEN 1600 300

WARD 1250 500

JONES 2975 0

MARTIN 1250 1400

BLAKE 2850 0

CLARK 2450 0

SCOTT 3000 0

KING 5000 0

TURNER 1500 0

ADAMS 1100 0

JAMES 950 0

FORD 3000 0

MILLER 1300 0

NOTE:-IF EXPR1 IS NOT NULL THE NVL FUN RETURNS EXPR1,IF EXPR1 IS NULL THE NVL FUN RETURNS EXPR2.

----

SELECT ENAME,SAL,NVL(COMM,0),(SAL*12)+(SAL*12*COMM) AN_SAL FROM EMP;

ENAME SAL NVL(COMM,0) AN_SAL

SMITH 800 0 9600

ALLEN 1600 300 19200

WARD 1250 500 15000

JONES 2975 0 35700

MARTIN 1250 1400 15000

BLAKE 2850 0 34200

CLARK 2450 0 29400

SCOTT 3000 0 36000

KING 5000 0 60000

TURNER 1500 0 18000

ADAMS 1100 0 13200

JAMES 950 0 11400

FORD 3000 0 36000

MILLER 1300 0 15600

SELECT ENAME,SAL,NVL(COMM,0),(SAL*12)+(SAL*12*COMM) AN_SAL FROM EMP;

ENAME SAL NVL(COMM,0) AN_SAL

SMITH 800 0

ALLEN 1600 300 5779200

WARD 1250 500 7515000

JONES 2975 0

MARTIN 1250 1400 21015000

BLAKE 2850 0

CLARK 2450 0

SCOTT 3000 0

KING 5000 0

TURNER 1500 0 18000

ADAMS 1100 0

JAMES 950 0

FORD 3000 0

MILLER 1300 0

NVL2 FUN:(EXPR1,EXPR2,EXPR3)

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

IF EXPR1 IS NOTNULL THE NVL2 RETURNS EXPR2,IF EXPR1 IS NULL THE NVL2 RETURNS EXPR3.THE ARGUMENT EXPR1 CAN HAVE ANY DATA TYPE

SELECT ENAME,SAL,COMM,NVL2(COMM,'SAL+COMM','SAL')FROM EMP;

ENAME SAL COMM NVL2(COMM,'SAL+COMM','SAL')

SMITH 800 SAL

ALLEN 1600 300 SAL+COMM

WARD 1250 500 SAL+COMM

JONES 2975 SAL

MARTIN 1250 1400 SAL+COMM

BLAKE 2850 SAL

CLARK 2450 SAL

SCOTT 3000 SAL

KING 5000 SAL

TURNER 1500 0 SAL+COMM

ADAMS 1100 SAL

JAMES 950 SAL

FORD 3000 SAL

MILLER 1300 SAL

SELECT ENAME,SAL,COMM,NVL2(COMM,'SAL+COMM','SAL')FROM EMP WHERE DEPTNO=10;

ENAME SAL COMM NVL2(COMM,'SAL+COMM','SAL')

CLARK 2450 SAL

KING 5000 SAL

MILLER 1300 SAL

SELECT ENAME,SAL,COMM,NVL2(COMM,'SAL+COMM','SAL')FROM EMP WHERE DEPTNO=20;

ENAME SAL COMM NVL2(COMM,'SAL+COMM','SAL')

SMITH 800 SAL

JONES 2975 SAL

SCOTT 3000 SAL

ADAMS 1100 SAL

FORD 3000 SAL

SELECT ENAME,SAL,COMM,NVL2(COMM,'SAL+COMM','SAL')FROM EMP WHERE DEPTNO=30;

ENAME SAL COMM NVL2(COMM,'SAL+COMM','SAL')

ALLEN 1600 300 SAL+COMM

WARD 1250 500 SAL+COMM

MARTIN 1250 1400 SAL+COMM

BLAKE 2850 SAL

TURNER 1500 0 SAL+COMM

JAMES 950 SAL

NULLIF(EXPR1,EXPR2):

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

THE NULLIF FUN COMPARE TWO EXPRS.IF THEY ARE EQUAL,THE FUN RETURNS NULL.IF THEY ARE NOT EQUAL THE FUN RETURNS THE FIRST EXPR

SELECT ENAME,LENGTH(ENAME) "EXPR1",ENAME,LENGTH(ENAME) "EXPR2",NULLIF(LENGTH(ENAME),LENGTH(ENAME)) RESULT FROM EMP;

ENAME EXPR1 ENAME_1 EXPR2 RESULT

SMITH 5 SMITH 5

ALLEN 5 ALLEN 5

WARD 4 WARD 4

JONES 5 JONES 5

MARTIN 6 MARTIN 6

BLAKE 5 BLAKE 5

CLARK 5 CLARK 5

SCOTT 5 SCOTT 5

KING 4 KING 4

TURNER 6 TURNER 6

ADAMS 5 ADAMS 5

JAMES 5 JAMES 5

FORD 4 FORD 4

MILLER 6 MILLER 6

SELECT ENAME,LENGTH(ENAME) "EXPR1",JOB,LENGTH(JOB) "EXPR2",NULLIF(LENGTH(ENAME),LENGTH(JOB)) RESULT FROM EMP;

ENAME EXPR1 JOB EXPR2 RESULT

SMITH 5 CLERK 5

ALLEN 5 SALESMAN 8 5

WARD 4 SALESMAN 8 4

JONES 5 MANAGER 7 5

MARTIN 6 SALESMAN 8 6

BLAKE 5 MANAGER 7 5

CLARK 5 MANAGER 7 5

SCOTT 5 ANALYST 7 5

KING 4 PRESIDENT 9 4

TURNER 6 SALESMAN 8 6

ADAMS 5 CLERK 5

JAMES 5 CLERK 5

FORD 4 ANALYST 7 4

MILLER 6 CLERK 5 6

SELECT ENAME,LENGTH(ENAME) "EXPR1",JOB,LENGTH(JOB) "EXPR2",NULLIF(LENGTH(ENAME),LENGTH(JOB)) RESULT FROM EMP WHERE DEPTNO=10;

ENAME EXPR1 JOB EXPR2 RESULT

CLARK 5 MANAGER 7 5

KING 4 PRESIDENT 9 4

MILLER 6 CLERK 5 6

SELECT ENAME,LENGTH(ENAME) "EXPR1",JOB,LENGTH(JOB) "EXPR2",NULLIF(LENGTH(ENAME),LENGTH(JOB)) RESULT FROM EMP WHERE DEPTNO=20;

ENAME EXPR1 JOB EXPR2 RESULT

SMITH 5 CLERK 5

JONES 5 MANAGER 7 5

SCOTT 5 ANALYST 7 5

ADAMS 5 CLERK 5

FORD 4 ANALYST 7 4

SELECT ENAME,LENGTH(ENAME) "EXPR1",JOB,LENGTH(JOB) "EXPR2",NULLIF(LENGTH(ENAME),LENGTH(JOB)) RESULT FROM EMP WHERE DEPTNO=30;

ENAME EXPR1 JOB EXPR2 RESULT

ALLEN 5 SALESMAN 8 5

WARD 4 SALESMAN 8 4

MARTIN 6 SALESMAN 8 6

BLAKE 5 MANAGER 7 5

TURNER 6 SALESMAN 8 6

JAMES 5 CLERK 5

COALESCE(EXPR1,EXPR2,.....EXPRN):

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

IF THE FIRST EXPR IS NOT NULL,IT RETURNS THAT EXPRESSION;OTERWISE, DOES A COALESCE OF THE REMAINING EXPRESSIONS.

THE ADVANTAGE OF THE COALESCE FUN OVER THE NVL FUN IS THAT THE COALESCE FUN CAN TAKE MULTIPLE ALTERNATIVE VALUES.

SELECT ENAME,COALESCE(COMM,SAL,10)FROM EMP;

ENAME COALESCE(COMM,SAL,10)

SMITH 800

ALLEN 300

WARD 500

JONES 2975

MARTIN 1400

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

TURNER 0

ADAMS 1100

JAMES 950

FORD 3000

MILLER 1300

SELECT ENAME,COALESCE(COMM,SAL,10)FROM EMP ORDER BY COMM;

ENAME COALESCE(COMM,SAL,10)

TURNER 0

ALLEN 300

WARD 500

MARTIN 1400

SCOTT 3000

KING 5000

ADAMS 1100

JAMES 950

FORD 3000

MILLER 1300

BLAKE 2850

JONES 2975

SMITH 800

CLARK 2450

SELECT ENAME,COALESCE(COMM,SAL,10)FROM EMP WHERE DEPTNO=10 ORDER BY SAL;

ENAME COALESCE(COMM,SAL,10)

MILLER 1300

CLARK 2450

KING 5000

SELECT ENAME,COALESCE(COMM,SAL,10)FROM EMP WHERE DEPTNO=20;

ENAME COALESCE(COMM,SAL,10)

SMITH 800

JONES 2975

SCOTT 3000

ADAMS 1100

FORD 3000

SELECT ENAME,COALESCE(COMM,SAL,10)FROM EMP WHERE DEPTNO=30 ORDER BY (COMM);

ENAME COALESCE(COMM,SAL,10)

TURNER 0

ALLEN 300

WARD 500

MARTIN 1400

JAMES 950

BLAKE 2850

CONDITIONAL EXPRESSIONS:

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

USE TWO METHODS

CASE EXPRESSION

DECODE FUN

TWO METHODS USED TO IMPLEMENTE THE CONDITIONAL PROCESSING(IF-THEN-ELSE LOGIC) WITH IN A SQL STATEMENTARE

THE CASE EXPRESSION AND THE DECODE FUN.

CASE EXPRESSION:

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

SELECT ENAME,SAL,EMPNO, CASE ENAME WHEN 'SMITH' THEN JOB

WHEN 'BLAK' THEN JOB

WHEN 'FORD' THEN JOB

ELSE JOB END

"RESERVED SAL" FROM EMP;

ENAME SAL EMPNO RESERVED SAL

SMITH 800 7369 CLERK

ALLEN 1600 7499 SALESMAN

WARD 1250 7521 SALESMAN

JONES 2975 7566 MANAGER

MARTIN 1250 7654 SALESMAN

BLAKE 2850 7698 MANAGER

CLARK 2450 7782 MANAGER

SCOTT 3000 7788 ANALYST

KING 5000 7839 PRESIDENT

TURNER 1500 7844 SALESMAN

ADAMS 1100 7876 CLERK

JAMES 950 7900 CLERK

FORD 3000 7902 ANALYST

MILLER 1300 7934 CLERK

SELECT ENAME,SAL,EMPNO, DECODE (ENAME, 'SMITH', JOB,

'BLAK' , JOB,

'FORD', JOB)

JOB

FROM EMP;

ENAME SAL EMPNO JOB

SMITH 800 7369 CLERK

ALLEN 1600 7499

WARD 1250 7521

JONES 2975 7566

MARTIN 1250 7654

BLAKE 2850 7698

CLARK 2450 7782

SCOTT 3000 7788

KING 5000 7839

TURNER 1500 7844

ADAMS 1100 7876

JAMES 950 7900

FORD 3000 7902 ANALYST

MILLER 1300 7934

SELECT ENAME,SAL,DECODE(TRUNC(SAL/2000,0),

0,0.00,

1,0.9,

2,0.20,

3,0.30,

4,0.40,

5,0.42,

6,0.44,

0,46)TAX_RATE FROM EMP WHERE DEPTNO=20;

ENAME SAL TAX_RATE

SMITH 800 0

JONES 2975 0.9

SCOTT 3000 0.9

ADAMS 1100 0

FORD 3000 0.9

AGGREGATING OR GROUP FUNCTIONS:

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

SYN:GROUP_FUNCTION(DISTINCT/ALL COLUMN)

1.AVERAGE

2.SUM

3.MINIMUM

4.MAX

5.COUNT

6.STANDARD DEVIATION

AVERAGE FUN:

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

SYN:AVG(DISTINCT/ALL COLUMN)

SELECT AVG(SAL),AVG(DISTINCT SAL) FROM EMP;

2073.21428571429 2064.583333333

SELECT AVG(COMM) FROM EMP;

550

SELECT AVG(DISTINCT COMM)FROM EMP;

550

SUM FUN:

*********

SYN:SUM(DISTINCT/ALL COLUMN)

SELECT SUM(SAL) FROM EMP;

29025

SELECT SUM(DISTINCT SAL) FROM EMP;

24775

SELECT SUM(COMM) FROM EMP;

2200

SELECT SUM(DISTINCT COMM) FROM EMP;

2200

MAXIMUM FUN:

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

SYN:MAX(DISTINCT/ALL COLUMN)

SELECT MAX(SAL) FROM EMP;

5000

SELECT MAX(DISTINCT SAL) FROM EMP;

5000

SELECT MAX(ENAME) FROM EMP;

WARD

SELECT ENAME, ASCII(ENAME) FROM EMP;

ENAME ASCII(ENAME)

SMITH 83

ALLEN 65

WARD 87

JONES 74

MARTIN 77

BLAKE 66

CLARK 67

SCOTT 83

KING 75

TURNER 84

ADAMS 65

JAMES 74

FORD 70

MILLER 77

MINIMUM FUN:

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

SYN:MIN(DISTINCT/ALL COLUMN)

SELECT MIN(SAL) FROM EMP;

800

SELECT MIN(DISTINCT SAL) FROM EMP;

800

SELECT MIN(COMM),MIN(DISTINCT SAL) FROM EMP;

0 800

SELECT MIN(ENAME) FROM EMP;

ADAMS

SELECT MIN(HIREDATE),MAX(HIREDATE) FROM EMP;

12/17/1980 1/12/1983

STANDARD DEVIATION FUN:

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

SYN:STDDEV(DISTINCT/ALL COLUMN)

SELECT STDDEV(SAL) FROM EMP;

1182.50322351627

SELECT STDDEV(DISTINCT SAL) FROM EMP;

1229.95095677047

SELECT STDDEV (COMM)FROM EMP;

602.771377334171

SELECT STDDEV(DISTINCT COMM) FROM EMP;

602.771377334171

VARIANCE FUN:

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

SYN:VARIANCE(DISTINCT/ALL COLUMN)

SELECT VARIANCE(SAL) FROM EMP;

1398313.87362637

SELECT VARIANCE(DISTINCT SAL) FROM EMP;

1512779.35606061

SELECT VARIANCE(COMM),VARIANCE(DISTINCT COMM)FROM EMP;

363333.333333333 363333.3333333333

COUNT FUN:

**********

SYN:COUNT(*/DISTINCT/ALL COLUMN)

SELECT COUNT(EMPNO) FROM EMP;

SELECT COUNT(SAL),COUNT(DISTINCT SAL) FROM EMP;

14 12

SELECT COUNT(JOB),COUNT(DISTINCT JOB) FROM EMP;

14 5

SELECT COUNT(*) FROM EMP;

SELECT COUNT(EMPNO),COUNT(DISTINCT MGR) FROM EMP;

14 6

SELECT COUNT(JOB),COUNT(DISTINCT MGR) FROM EMP WHERE DEPTNO=20;

5 4

GROUP BY CLAUSE:

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

SELECT JOB FROM EMP GROUP BY JOB;

JOB

CLERK

SALESMAN

PRESIDENT

MANAGER

ANALYST

SELECT DEPTNO FROM EMP GROUP BY DEPTNO;

DEPTNO

SELECT MGR FROM EMP GROUP BY MGR;

MGR

7839

7782

7698

7902

7566

7788

SELECT TO_CHAR(HIREDATE,'YYYY')YEARGROUP FROM EMP GROUP BY TO_CHAR(HIREDATE,'YYYY');

YEARGROUP

1980

1983

1982

1981

SELECT TO_CHAR(HIREDATE,'MONTH') MONTHGROUP FROM EMP GROUP BY TO_CHAR(HIREDATE,'MONTH');

MONTHGROUP

FEBRUARY

JANUARY

APRIL

JUNE

NOVEMBER

DECEMBER

SEPTEMBER

MAY

SELECT TO_CHAR(HIREDATE,'DD')DAYGROUP FROM EMP GROUP BY TO_CHAR(HIREDATE,'DD');

DAYGROUP

SELECT TO_CHAR(HIREDATE,'MONTH')MONTHGROUP FROM EMP WHERE TO_CHAR(HIREDATE,'MON')<>'DEC' GROUP BY TO_CHAR(HIREDATE,'MONTH');

MONTHGROUP

FEBRUARY

JANUARY

APRIL

JUNE

NOVEMBER

SEPTEMBER

MAY

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

DEPTNO COUNT(*)

30 6

20 5

10 3

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO ORDER BY AVG(SAL);

DEPTNO AVG(SAL)

30 1566.66666666667

20 2175

10 2916.66666666667

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

DEPTNO MIN(SAL) MAX(SAL)

30 950 2850

20 800 3000

10 1300 5000

SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO;

MAX(SAL)

2850

3000

5000

All about SQL PL/SQL and LINUX

Tuesday, 7 August 2012

Oracle FUNCTIONS with examples 4

No comments:

Post a Comment

Expose like a sun.