Oracle 9i Features :

 9i Joins: Supports ANSI/ISO standard Sql 1999 syntax.
 Made easy for Appln s/w tools to understand Sql Queries.
 1. Natural Join        5. Left outer join
 2. Join with Using        6. Right outer join
 3. Join with ON        *7. Full outer join
 4. Inner Join              8. Cross join

 1. > select empno,ename,sal,job,deptno,dname,loc
       from emp natural join dept;

 2. > select empno,ename,sal,job,deptno,dname,loc
       from emp join dept using(deptno);

 3. > select e.empno, e.ename, e.sal, e.job, e.deptno,                d.dname, d.loc from emp e Join dept d
        on(e.deptno = d.deptno) ;

 4. > select e.empno, e.ename, e.sal, e.job, e.deptno,                 d.dname, d.loc from emp e Inner Join dept d
        on(e.deptno = d.deptno) ;

 5. > select e.empno, e.ename, e.sal, e.job, e.deptno,                d.dname, d.loc from emp e left outer join dept d
       on(e.deptno = d.deptno) ;

 6. > select e.empno, e.ename, e.sal, e.job, e.deptno,                 d.dname, d.loc from emp e right outer join dept d
        on(e.deptno = d.deptno) ;

 * 7. > select e.empno, e.ename, e.sal, e.job, e.deptno,                 d.dname, d.loc from emp e full outer join dept d
           on(e.deptno = d.deptno) ;
 ** left outer join  union  right outer join  =  full outer join

 8. > select empno, ename, sal, job, emp.deptno,                     dname, loc from emp cross join dept;  -- cartesian join
---------------------------------------------------------------------------
 

 New Date Functions: * Systimestamp :  Gives date and time including fractional     seconds in SERVER time zone
 * current_timestamp:  Gives date and time including              fractional seconds in CLIENT time zone
 * sysdate:  Gives only date in server time zone
 * current_date:  Gives only date in client time zone
 * Extract : Used to retrieve a particular value from the        given date   ( day / month / year ).
 * to_timestamp(d) : Converts given date into date & time      information with am / pm .
 * dbtimezone : Gives server time zone value
 * Timestamp : Data type
    Automatically stores date and time information
    with am / pm

 >select systimestamp , current_timestamp from dual;
 >select sysdate,current_date from dual;
 >select dbtimezone from dual;
 >select to_timestamp(sysdate) from dual;
      18-Jan-10 5:10:23 pm
 >select extract(day from sysdate) ,
            extract(month from sysdate),
            extract(year from sysdate) from dual;

 > create table temp (c1 timestamp);
 > insert into temp values(sysdate);
 > select * from temp;
    18-Jan-10 5:14:23 pm
---------------------------------------------------------------------------
 

 New General Functions: * Coalesce(expr1,expr2,expr3,.......) --- Picks the first not     null expression result .

 *nullif ( expr1, expr2 ) --- If expr1 and expr2 results are   same it returns NULL value otherwise it return expr1   result .

 * Nvl2(expr1,expr2,expr3) --- If expr1 is null it manipulates     expr3 ,if expr1 is not null it manipulates expr2 .         
    (cond?val1:val2) -- Ternary operator in C

 >select coalesce(100 + null, 128 - null + 1000, 12 * null,
                       225, 2345, 9889) from dual; -- 225

 >select ename, job, coalesce (comm * 2, sal * 1.5) bonus     from emp;

 >select nullif(100,50* 2), nullif(300,30 * 100),
   nullif (600,300 + 300)  from dual;  --  null  300  null

 >select roll, name, nullif(fee,2500) from student
   where course = 'Oracle9i';

 >select ename, job, nvl2(comm,sal + comm,sal) net
   from emp;

* sal + nvl(comm,0)
--------------------------------------------------------------------
 

 Mutiple Inserts: ( DML ) Supports to insert into more than 1 table at a time
 But input must be retrieved from existing table.

 Ex: Make 3 empty tables same as Dept table.
 Create table D1 as select * from dept
 where rownum is null; ( 1 > 2 )
 Create table D2 as select * from dept
 where rownum is null;
 Create table D3 as select * from dept
 where rownum is null;

 Normal Insert: ( 3 Independent Jobs )
 insert into D1 select * from dept;
 insert into D2 select * from dept;
 insert into D3 select * from dept;

 insert all   ( Multiple insert - 1 Transaction )
 into D1 values(deptno,dname,loc)
 into D2 values(deptno,dname,loc)
 into D3 values(deptno,dname,loc)
 select * from dept;

 Conditional Insert: insert all
 when (deptno <= 40) then
 into D1 values (deptno,dname,loc)
 when (deptno <= 90) then
 into D2 values (deptno,dname,loc)
 else
 into D3(dname,loc) values(dname,loc)
 select * from dept;

 insert all when course = 'Oracle9i' then                 
 into stu_oracle values(roll,name,fee)
 when course = 'd6i' then
 into stu_d6i values(roll,name,fee)
 when course = 'unix' then
 into stu_unix values(roll,name,fee)
 select * from student;

  student       stu_oracle      stu_d6i           stu_unix   
  ---------        -------------        ---------         ----------
    Roll             Roll                  Roll              Roll
    name            name        name            name
    course         fee                      fee              fee
     fee           
---------------------------------------------------------------------------
 

 Merge : ( DML ) Used to compare the 2 table contents and makes them  equal. It supports only Update and Insert operations .

 >merge                                        Clauses:
 into Temp T                                  into -- Target
 using emp E                                  Using -- Source
 on ( T.empno = E.empno )              On -- Join condition
 when matched then
 update set t.sal = e.sal,
                t.comm = e.comm,
                t.deptno = e.deptno,
                t.job =  e.job,
                t.mgr  =  e.mgr
 when not matched then
 insert values(e.empno,e.ename,e.sal,e.comm,......);

 Before :
 Emp - 1 Crore rows
 Temp - Copy of emp - 1 Crore rows
 After :
 Emp --- 5000 inserts  & 1000 Updates performed
--------------------------------------------------------------------
 

 Rename Constraint & Column : Alter table emp rename constraint sys_c002325 to  pk_emp;
 Alter table emp rename column ename to emp_name;
--------------------------------------------------------------------
 

 9i Supports :
 Advanced Features of JAVA . Supports XML .
 Improved internal Architecture related to JAVA .
--------------------------------------------------------------------