16. GENRAL FUNCTIONS

GENRAL FUNCTIONS

•          GREATEST
•          LEAST
•          USER
•          UID
•          DECODE
•         CASE
•         NVL
•         NVL2
•         NULLIF
•         COALESCE

 GREATEST:

SYS_CONNECT_BY_PATH   (HIERARCHIAL FUNCTION);

EG:  SELECT GREATEST (1, 2, 3), LEAST (1, 2, 3) FROM DUAL;

        SELECT GREATEST (‘A’,’B’,’C’), LEASTA (‘A’,’B’,’C’) FROM DUAL;

        SELECT GREATEST (SYSDATE, HIREDATE) FROM EMP;

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

NVL: We user NVL to handle null values.

            SYN: NVL (EXP1, EXP2); IF EXP1 IS NULL RETURNS EXP2 ELSE EXP1;

SELECT SAL,COMM,SAL+COMM FROM EMP;

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

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

NVL2:  ADVANCE OF NVL

SYN: NVL2 (EXP1, EXP2, EXP3);

SYN: IF EXP1 IS NULL RETURNS EXP3 ELSE EXP2;

EG: SELECT NVL2 (COMM, SAL+COMM, SAL) FROM EMP;

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

NULLIF:

SYN: NULLIF (EXP1,EXP2); IF EXP1=EXP2 THEN RETURNS NULL ELSE EXP1;

EG: SELECT NULLIF(1,1),NULLIF(1,2) FROM DUAL;

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

COALESE: Returns first not null value.

EG: SELECT COALESE (1, 2, NULL, 3, NULL,4) FROM DUAL;

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

GROUP FUNCTIONS OR AGGREGATE FUNCTIONS OR MULTIPLE

ROW FUNCTIONS: They execute only once for list of values or group of values

              1)  SUM

              SYN:SUM ([ALL|DISTINCT] VALUE|EXP|COLUMN);

             2) AVG

              SYN: AVG ([ALL|DISTINCT] VALUE|EXP|COLUMN);

             3)  MIN

SYN: MIN (([ALL|DISTINCT] VALUE|EXP|COLUMN);

4)  MAX

SYN: MAX ([ALL|DISTINCT] VALUE|EXP|COLUMN);

    5)  COUNT:

SYN: COUNT (*|[ALL|DISTINCT] VALUE|EXP|COLUMN);

    6)  STDDEV

    7)   VARIANCE

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

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

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

                        SELECT STDDEV(SAL),VARIANCE(SAL) FROM EMP;

COUNT (*): Count Counts the records.it also consider the null values whereas count column ignore the null values

EG:  SELECT COUNT (*) FROM EMP;

       SELECT COUNT (COMM) FROM EMP;

                  SOME MORE EXAMPLES

         SELECT COUNT(0)  FROM EMP;

         SELECT COUNT(NULL) FROM EMP;

         SELECT SUM(0) FROM EMP;

         SELECT SUM(1) FROM EMP;

NOTE: A concept of null doesn’t work for a row as a whole

          Count never returns null.