Oracle Pseudo columns

Pseudo columns: Automatically filled by oracle. 

 Ex: sysdate, nextval, currval, rowid, rownum, level,sqlcode, sqlerrm, new, old.ORA_ROWSCN

Rowid:

It is an unique value

It is automatically assigned with every row inserted into table.

It is stored permanently in database

It is an 18 Bit Hexa decimal value.

It comprises of Object id, Data file id, Block id & Record id.

select rowid,dname,loc from dept;

select rowid,empno,ename,sal from emp;

AAA    001

AAB    002                       

AAC    003

AAD    004

AAE    005

Removing Duplicate records :

SQL>Delete from emp where rowid not in

          ( select min(rowid) from emp group by empno);

Retrive all rows except Last Record

  SQL> select empno,ename,sal,deptno from emp

              where rowid not in

             (select max(rowid) from emp);

Update  all rows except First row in Table.

 SQL>Update emp set sal = sal + 2500 where rowid not in

   (select min(rowid) from emp);

Rownum :

It is an unique value

It is an dynamic value automatically retrieved along with Select statement output.

It is only for display purpose.

It is not stored in database.

SQL >select rownum,ename,sal from emp;

SQL >select rownum,dname,loc from dept;

Retrieving Top 5 Highly paid Employees

SQL > select rownum,empno,ename,job,sal from

            (select rownum,empno,ename,job,sal from emp

            order by sal desc )

            where rownum <= 5;

Retrieving  Nth maximum salaried employ details (2 max,...)

SQL > select rownum,empno,ename,job,sal from

          (select rownum,empno,ename,job,sal from emp

          order by sal desc )

          group by rownum,empno,ename,job,sal

          having rownum = &N;

Retrieving Alternate rows

SQL>select rownum,empno,ename,job,sal from emp

group by rownum,empno,ename,job,sal

          having mod(rownum,2) = 0; -- EVEN Rows

          [ having mod(rownum,2) != 0; ] -- ODD Rows

Inline view: Select statement provided in place of table name is known as Inline view.

Level :

It will arrange the select statement output in Inverted tree structure ( Hierarichal Tree ) and gives the position of row in Tree. ( Returns Number )

Retrieving the Hierarichy of employees based on their Superior.

SQL >Select Level,empno,ename,sal,job,mgr from emp

 connect by prior empno = mgr start with mgr is null

 order by Level;

Retrieving Nth maximum salary using Level.

( * Duplicates are eliminated )

SQL >Select Level,max(Sal) from emp where Level = &N

           connect by prior sal > sal group by Level;