Pseudo Columns in Oracle

 Pseudo columns:
   Automatically filled by oracle.
 Ex: sysdate, nextval, currval, rowid, rownum,level,         sqlcode, sqlerrm, new, old .

 Rowid: It is an unique value
 It is automatically assigned with every row inserted  into table.
 It is stored permanantly 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    101
 ........AAB    102           
 ........AAC    103
 ........AAD    104
 ........AAE    105

 ** Removing Duplicate records : > Delete from emp where rowid not in
   ( select min(rowid) from emp group by empno);

 * Retrive all rows except Last Record   > select empno,ename,sal,deptno from emp
      where rowid not in (select max(rowid) from              emp);

 * Update all rows except First row in Table. >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.

 >select rownum,ename,sal from emp;
 >select rownum,dname,loc from dept;

 * Retrieving Top 5 Highly paid Employees  > 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,..)  > 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   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.  >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 )
  >Select Level,max(Sal) from emp where Level =       &N  connect by prior sal > sal group by Level;
---------------------------------------------------------------
 

 Locks : Used to preserve the rows for manipulation     purpose to prevent the other users to access the    same data at the same time.
 They prevent Dead Locks .
 They improve Data concurrency
 ( sharing with multiple users ).

 2 Types  1. Implicit Locks   2. Explicit Locks

 Implicit Locks : Automatically imposed by oracle      whenever  " DML" operations are performed by   user.

 Explicit Locks : They are imposed by user before      manipulating data.  2 Types
 i> Row Level Locks : used to lock the selected   rows of table. It is imposed with " For Update "   clause in select.

 Ex:1
 > select * from emp where empno = 7900
    for update;
   > update emp set sal = sal + 3000
          where empno =  7900;
      > commit;
 Ex:2
  > select * from emp where deptno = 10
     for update;
  > update emp set sal = sal + 1000
     where deptno  = 10;
  > commit;

 ii> Table Level Locks : Used to lock entire table
      > Lock table emp in Exclusive mode;
      > update emp set sal = sal + sal * .25;
      > commit;
 

 Note :
 DML are not allowed by other users when table is     locked by user.  Commit / rollback will release any   type of lock applied.---------------------------------------------------------------
 

 E.F. Codd's Rules: Every DBMS package has to support min 6 / 12    rules to be declared as RDBMS.

 1. Information Representation
 2. Guaranteed Access
 3. Systematic treatment of Null values
 4. View Updation
 5. Comprehensive Data sub language
 6. High level Insert,Update,Delete
 7. Data discription rule
 8. Data distribution rule
 9. Physical Data Independence
 10. Logical Data Independence
 11. Data Integrity rules
 12. Non sub version rule
---------------------------------------------------------------