General Functions in Oracle

----------------- 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(DISTINCT Sal) 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 WHERE SOUNDEX(JOB) = SOUNDEX('manger');