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
- create type exam_type as object
(examname
varchar2(10),examdate date);
- create table exam_table of exam_type;
- insert into exam_table values(‘EAMCET’,sysdate);
- insert into exam_table values(‘ICET’,sysdate);
- select * from exam_table;
EXAMNAME
EXAMDATE
----------
---------
EAMCET
14-MAR-06
ICET
14-MAR-06
- select ref(e) from exam_table e;
REF(E)
-----------------------------------------------------------------
00002802099DB1F42F32024869BB25B7F08D8D306DD39D399EFC9F4E47A731B228BB6796CE0040DE
BA0000
0000280209761F47DB9BA94BEA85C594952283608DD39D399EFC9F4E47A731B228BB6796CE0040DE
BA0001
- select value(e) from exam_table e;
VALUE(E)(EXAMNAME,
EXAMDATE)
-----------------------------------------------
EXAM_TYPE('EAMCET',
'14-MAR-06')
EXAM_TYPE('ICET',
'14-MAR-06')
- create table stud_exams(roll number(3),
exam_info
ref exam_type);
- insert into stud_exams select 1 ,ref(e) from
exam_table
e;
- select * from stud_exams;
- 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')
- delete from exam_table where examname = ‘ICET’;
- 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)
- 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
No comments:
Post a Comment