Wednesday, October 23, 2013

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');

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect