Chitika Add

Friday, December 13

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

No comments: