Oracle SQL Queries

Miscellaneous Topics

1. SELECT TO_CHAR('01-FEB-81', 'DD-MON-RRRR'), TO_CHAR('01-FEB-81', 'DD-MON-YYYY')  FROM DUAL;

          01-FEB-1981           10-FEB-2081

2. Display Salary in words:

select ename,sal, to_char(to_date(sal,'jsp'),'jsp') "In words" from emp;

Ram   5000   five thousand

·                    JSP - Julian Spell out

3. Retrieving Nth Maximum salary using Level:

- Duplicates are Suppressed

select Level,max(sal) from emp where level = &N

connect by prior sal > sal  Group by level;

4. Retrieving Cumulative Salary:

select ename,sal,(select sum(sal) from emp where rowid <= e.rowid ) "cumsal" from emp e;

5. Grouping Sets: (8.0)

select hiredate,mgr,job,count(*) from emp

group by grouping sets(hiredate,mgr,job);

6. Escape option in LIKE:

Used to search for % and _ (under score) characters in String

Search for employ names having _ .

Select ename from emp where

Ename like '%A\_B%' escape '\'; 

7. Matrix Query

Select job,sum(decode(deptno,10,sal)) dept10,

sum(decode(deptno,20,sal)) dept20,

sum(decode(deptno,30,sal)) dept30,

sum(decode(deptno,40,sal)) dept40  from emp

group by job;

8. Unused Columns(8.0)

Alter table emp set unused column comm;

Alter table emp set unused column mgr;

Alter table emp drop unused columns;

1. LAG:

select ename,hiredate,sal,lag(sal,1,0) over (order by hiredate) as "prevsal" from emp where job = 'CLERK';

ENAME      HIREDATE         SAL    PREVSAL

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

SMITH      17-DEC-80        800          0

JAMES      03-DEC-81        950        800

MILLER     23-JAN-82       1300        950

2. LEAD:

select ename,hiredate,lead(hiredate,1) over (order by hiredate) as "nexthired" from emp where deptno = 30;

ENAME        HIREDATE     NEXTHIRED

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

ALLEN       20-FEB-81     22-FEB-81

WARD       22-FEB-81     01-MAY-81

BLAKE       01-MAY-81    08-SEP-81

3. RANK:

select deptno,ename,sal, rank() over(partition by deptno order by sal) "RANK"  from emp where deptno = 30;

  

   19000  1

   19000  1

   18000  3

4. DENSE_RANK:

select deptno,ename,sal, dense_rank() over(partition by deptno order by sal) "RANK" from emp where deptno = 30;

19000 1

19000 1

18000 2

5. FIRST & LAST:

select deptno,ename,sal,min(sal) keep (dense_rank FIRST order by sal )over

(partition by deptno) "Lowest",max(sal) keep (dense_rank LAST order by sal) over

(partition by deptno) "Highest" from emp order by deptno,sal;