SQL Queries and PL/SQL Programs..

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;

9. OBJECT REFERENCES

1. create type exam_type as object

(examname varchar2(10),examdate date);

2. create table exam_table of exam_type;

3. insert into exam_table values(‘EAMCET’,sysdate);

4. insert into exam_table values(‘ICET’,sysdate);

5. select * from exam_table;

EXAMNAME EXAMDATE

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

EAMCET 14-MAR-06

ICET 14-MAR-06

6. select ref(e) from exam_table e;

REF(E)

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

00002802099DB1F42F32024869BB25B7F08D8D306DD39D399EFC9F4E47A731B228BB6796CE0040DE

BA0000

0000280209761F47DB9BA94BEA85C594952283608DD39D399EFC9F4E47A731B228BB6796CE0040DE

BA0001

7. select value(e) from exam_table e;

VALUE(E)(EXAMNAME, EXAMDATE)

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

EXAM_TYPE('EAMCET', '14-MAR-06')

EXAM_TYPE('ICET', '14-MAR-06')

8. create table stud_exams(roll number(3),

exam_info ref exam_type);

9. insert into stud_exams select 1 ,ref(e) from

exam_table e;

10. select * from stud_exams;

11. select roll,deref(exam_info) from stud_exams;

ROLL

----------

DEREF(EXAM_INFO)(EXAMNAME, EXAMDATE)

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

1

EXAM_TYPE('EAMCET', '14-MAR-06')

1

EXAM_TYPE('ICET', '14-MAR-06')

12. delete from exam_table where examname = ‘ICET’;

13. select * from stud_exams

where exam_info is dangling;

10. Using LABELS in PL/SQL Program:

Ex: Display the numbers in below format

1

1 2

1 2 3

1 2 3 4

1 2 3 4 5

Declare

N number(2) := 0;

Ctr number(2) := 0;

Begin

<<outer>> -- Defining a label

loop

ctr := ctr + 1;

n := 0;

<<inner>> -- Defining a label

loop

n := n + 1;

dbms_output.put(n|| ‘ ‘);

exit inner when n >= ctr;

exit outer when ctr > 5;

end loop inner;

dbms_output.put_line(‘ ‘);

end loop outer;

end;

Analytical Functions: (9i)

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;

DEPTNO ENAME SAL Lowest Highest

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

10 MILLER 1300 1300 5000

10 CLARK 2450 1300 5000

10 KING 5000 1300 5000

20 SMITH 800 800 3000

20 ADAMS 1100 800 3000

20 JONES 2975 800 3000

20 SCOTT 3000 800 3000

20 FORD 3000 800 3000

30 JAMES 950 950 2850

30 WARD 1250 950 2850

30 MARTIN 1250 950 2850

30 TURNER 1500 950 2850

30 ALLEN 1600 950 2850

30 BLAKE 2850 950 2850