Friday, June 3, 2016

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;



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect