Miscellaneous Topics

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;

9. OBJECT REFERENCES

9.1        create type exam_type as object

         (examname varchar2(10),examdate date);

9.2        create table exam_table of exam_type;

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

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

9.5        select * from exam_table;

EXAMNAME   EXAMDATE

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

EAMCET     14-MAR-06

ICET       14-MAR-06

9.6        select ref(e) from exam_table e;

REF(E)

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

00002802099DB1F42F32024869BB25B7F08D8D306DD39D399EFC9F4E47A731B228BB6796CE0040DE

BA0000

0000280209761F47DB9BA94BEA85C594952283608DD39D399EFC9F4E47A731B228BB6796CE0040DE

BA0001

9.7        select value(e) from exam_table e;

VALUE(E)(EXAMNAME, EXAMDATE)

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

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

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

9.8        create table stud_exams(roll number(3),

exam_info ref exam_type);

9.9        insert into stud_exams select 1 ,ref(e) from

        exam_table e;

9.10    select * from stud_exams;

9.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')

9.12    delete from exam_table where examname = ‘ICET’;

9.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