Functions and Types

Functions and types

1.  NVL FUNCTION
2. NVL2 FUNCTION
3. NULLIF FUNCTION
4. COALESCE FUNCTION 
5. CASE FUNCTION
6. DECODE FUNCTION 
7. Group Functions

----------------- NVL FUNCTION ----------
SELECT ENAME,
  SAL,
  NVL(COMM,0)
FROM EMP;
SELECT ENAME,SAL, (SAL*12) + (SAL*12*COMM) FROM EMP;
SELECT ENAME,SAL, (SAL*12) + (SAL*12*NVL(COMM,0)) FROM EMP;
SELECT ENAME,JOB,NVL(MGR,'NO MANAGER') FROM EMP;
SELECT ENAME,JOB,NVL(TO_CHAR(MGR),'NO MANAGER') MANAGER FROM EMP;
---------------- NVL2 FUNCTION ----------
SELECT ENAME,
  SAL,
  COMM,
  NVL2(COMM,SAL+COMM, SAL) income
FROM EMP;
SELECT ENAME,
  SAL,
  COMM,
  NVL2(COMM,'COMMISSION', 'NO COMMISSION') income
FROM EMP;
---------------- NULLIF FUNCTION ---------
SELECT NULLIF(1,1) FROM DUAL;
SELECT NULLIF(1,2) FROM DUAL;
SELECT NULLIF('IMMENSE','IMMENSE') FROM DUAL;
SELECT NULLIF('IMMENSE','SOURCE') FROM DUAL;
SELECT EMPNO,MGR,NULLIF(EMPNO,MGR) FROM EMP;
SELECT SAL,SAL+NVL(COMM,0),NULLIF(SAL,SAL+NVL(COMM,0)) FROM EMP;
--------------- COALESCE FUNCTION -----------
SELECT ename,
  sal,
  comm,
  COALESCE(comm, sal, 10) comm
FROM emp;
SELECT COALESCE(1,2,3,NULL) FROM DUAL;
SELECT COALESCE(NULL,2,3,4) FROM DUAL;
SELECT COALESCE(NULL,NULL,3,4) FROM DUAL;
SELECT COALESCE(NULL,NULL,NULL,4) FROM DUAL;
SELECT COALESCE(NULL,NULL,NULL,NULL) FROM DUAL;
----------------- CASE FUNCTION --------------
SELECT ENAME,
  job,
  sal,
  CASE job
    WHEN 'MANAGER'
    THEN 1.10*sal
    WHEN 'ANALYST'
    THEN 1.15*sal
    WHEN 'PRESIDENT'
    THEN 1.20*sal
    WHEN 'CLERK'
    THEN 1.30*sal
    WHEN 'SALESMAN'
    THEN 1.40*SAL
    ELSE sal
  END "REVISED_SALARY"
FROM emp;
-----------------DECODE FUNCTION -------------
SELECT ename,
  job,
  sal,
  DECODE(job, 'MANAGER', 1.10*sal, 'ANALYST', 1.15*sal, 'PRESIDENT', 1.20*sal, 'CLERK', 1.15*sal, 'SALESMAN', 1.20*sal, sal) REVISED_SALARY
FROM emp;
---------------- Group Functions ----------------
SELECT AVG(Sal),
  AVG(DISTINCT Sal)
FROM Emp;
SELECT SUM(Sal), SUM(DISTINCT Sal) FROM Emp;
SELECT MAX(Sal), MAX(DISTINCT Sal) FROM Emp;
SELECT MIN(Sal), MIN(DISTINCT Sal) FROM Emp;
SELECT STDDEV(Sal), STDDEV(DISTINCT Sal) FROM Emp;
SELECT VARIANCE(Sal),VARIANCE(DISTINCTSal) FROM Emp;
SELECT COUNT(*) FROM Emp;
SELECT COUNT(Job), COUNT(DISTINCT Job) FROM Emp;
SELECT COUNT(Sal), COUNT(Comm) FROM Emp;
SELECT COUNT(Empno), COUNT(DISTINCT MGR) FROM Emp;
SELECT Deptno FROM Emp GROUP BY Deptno;
SELECT TO_CHAR(HireDate, 'YYYY') YearGroup
FROM Emp
GROUP BY TO_CHAR(HireDate, 'YYYY');
SELECT TO_CHAR(HireDate, 'Month') MonthGroup
FROM Emp
GROUP BY TO_CHAR(HireDate, 'Month');
SELECT Deptno, AVG(Sal) FROM Emp GROUP BY Deptno;
SELECT Deptno, AVG (Sal) FROM Emp GROUP BY Deptno ORDER BY AVG (Sal);
SELECT Deptno, MIN(Sal), MAX(Sal) FROM Emp GROUP BY Deptno;
SELECT Deptno, Job, SUM(Sal) FROM Emp GROUP BY Deptno, Job;
SELECT Deptno,
  MIN(Sal),
  MAX(Sal)
FROM Emp
WHERE Job = 'CLERK'
GROUP BY Deptno;
SELECT Deptno, AVG(Sal) FROM Emp GROUP BY Deptno HAVING MAX(Sal) > 2900;
SELECT Job,
  SUM(Sal) Payroll
FROM Emp
WHERE Job NOT LIKE 'SALES%'
GROUP BY Job
HAVING SUM(Sal) > 5000
ORDER BY SUM (Sal);
SELECT Deptno,
  MIN(Sal),
  MAX(Sal)
FROM Emp
WHERE Job = 'CLERK'
GROUP BY Deptno
HAVING MIN(Sal) < 1000;
SELECT Deptno, SUM(Sal) FROM Emp GROUP BY Deptno HAVING COUNT(Deptno) > 3;
SELECT Deptno,
  AVG(Sal),
  SUM(Sal),
  MAX(Sal),
  MIN(Sal)
FROM Emp
GROUP BY Deptno
HAVING COUNT(*) > 3;
SELECT MAX(AVG(Sal)) FROM Emp GROUP BY Deptno;
SELECT MAX(SUM(Sal)), MIN(SUM(SAL)) FROM Emp GROUP BY Deptno;
SELECT MAX(SUM(Sal)), MIN(AVG(Sal)) FROM Emp GROUP BY Job;
SELECT GREATEST('HARRY', 'HARRIOT') FROM DUAL;
SELECT GREATEST(1000, 2000, 200) FROM DUAL;
SELECT GREATEST('10-JUL-05', '20-JUL-05') FROM DUAL;
SELECT LEAST('HARRY' , 'HARRIOT') FROM DUAL;
SELECT LEAST(1000, 2000, 200 ) FROM DUAL;
SELECT LEAST('10-JUL-05', '20-JUL-05') FROM DUAL;
SELECT USER, UID FROM DUAL;
SELECT USERENV('LANGUAGE') FROM DUAL;
SELECT USERENV('LANG') FROM DUAL;
SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME) = SOUNDEX('SMYTHE');
SELECT ENAME,JOB FROM EMP WHERE SOUNDEX(JOB) = SOUNDEX('CLRK');
SELECT ENAME,JOB FROM EMP WHERESOUNDEX(JOB) =SOUNDEX('manger');