---------- Relating data through Join concept---------

------ Simple Join

--- Equi Join

         select odate,del_date,itemcode from order_master om, order_detail od where om.orderno= od.orderno;

--- Non equi join

 select itemdesc,max_level,qty_ord,qty_deld from itemfile i,order_detail o where

         ( ( i.max_level < o.qty_ord ) and i.itemcode=o.itemcode );

------ Self Join

  select a.itemcode,a.itemdesc,b.max_level from itemfile a,itemfile b where a.qty_hand < b.max_level

     and a.itemcode=b.itemcode;

------ Outer Join

          select a.orderno,vencode,qty_ord from order_master a, order_detail b where a.orderno=b.orderno(+);

 ---------------- Subqueries --------------------

 select * from order_detail where orderno=( select orderno from order_master where

        del_date='25-may-98' and vencode='v001');

 --- subqueries that return several values

      ( any , all , in , not in )  

 select * from order_detail where qty_deld < any (select qty_hand from itemfile where itemrate

              between 50 and 120);

select * from order_detail where qty_ord > all

    (select qty_hand from itemfile where itemrate < 200 );

   ( notes: )

         " =any "  -> in

         " !=all " -> not in      

--- correlated subquery

  select distinct(a.orderno) from order_detail a where 3 <= ( select count(itemcode) from

             order_detail where a.orderno=orderno );

 -------------Set Operators---------------------

select orderno from order_master union select orderno from order_detail;

select orderno from order_master union all select orderno from order_detail;

select qty_hand,max_level from itemfile union

select qty_ord,qty_deld from order_detail order by 2;

select orderno from order_master intersect select orderno from order_detail;

select orderno from order_master minus select orderno from order_detail;

 ------------ Constraints -----------------------

--- domain integrity constraints ( not null & check )

 create table emp ( eno varchar2(10) [constraint x1] not null, ......)

 alter table emp modify eno not null;

 create table emp( ...,sal number(10) [constraint x2] check(sal < 10000);

 create table order_master(....,o_status varchar2(1) check( o_status in('p','c') );

--- entity integrity constraints ( unique & primary key )

create table emp(eno varchar2(10) unique,...);

create table emp(eno varchar2(10) primary key,....);

(notes:)  unique constraint accepts null value.

--- referential integrity constraints

 create table order_detail( orderno varchar2(10) constraint x references order_master(orderno),....);

 alter table order_detail add constraint x3 foreign key(orderno) references order_master(orderno);

 alter table order_detail add constraint x3 foreign key(orderno) references order_master(orderno) on delete cascade;

 (notes:)

    * deferrable initially immediate -> at the time of insert

   * deferrable initially deferred  -> at the time of commit

  set constraints all immediate;

  set constraints all deferred;

 Oracle 8 has an option to enforce a constraint rather than enable it.

alter table <tablename> enforce constraint <constraintname>;

 

alter table <tablename> drop constraint <constraintname>;

 

----------------- Locks -------------------

--- row level lock

        select * from order_master where vencode='v002' for update of odate,del_date;  

 --- table level lock

 * Share lock

      lock table order_master in share mode;

  * Share update lock

          lock table order_master in share update mode;

  * exclusive lock

lock table order_master in exclusive mode;

lock table order_master in exclusive mode nowait;

 -------------- Table Partitions ---------------

 create table order_master( orderno varchar2(10),.......)

       partition by range(orderno)

        (

          partition p1 values less than('o010'),

          partition p2 values less than('o020')

        );

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

 create table order_master( orderno varchar2(10),.......)

       partition by range(orderno)

        (

          partition p1 values less than('o010'),

          partition p2 values less than('o020'),

          partition p3 values less than(maxvalue)

        );

            select * from order_master partition(p1);

-- Moving partition    

alter table order_master move partition p1 tablespace student;

-- Add partition  

                    alter table order_master add partition p3 values less than('o030');  -> without maxvalue ;

-- Split partition

                  alter table order_master split partition p3 at('o025') into( partition p31,partition p32);

-- Drop partition  

                alter table order_master drop partition p1;

-- Exchanging table partitions

              alter table order_master exchange partition p2 with table temp; --> both should have same structure

 --------------- Database Objects -----------------

  ----- Synonym

    create synonym vmast for vendor_master;

    select * from vmast;

              USER_SYNONYMS

 ---- Sequences

    create sequence venseq

             increment by 1

             start with 1

             maxvalue 10

             minvalue 1

             cycle/nocycle

             cache 4; /nocache

      insert into vendor_master(vencode,venname) values('v'||venseq.nextval,'vijay');

      select venseq.currval from dual;

 

      alter sequence venseq maxvalue 15;

 

             USER_SEQUENCES  

--------------- View --------------------

 create view ven_view as select * from vendor_master;

 create view pen_view as select * from order_master where o_status='p';

 update pen_view set o_status='c' where o_status='p';

   The above update command will work fine but this will produce rows which are not viewable through the view.

  to avoid this,

 create view pen_view as select * from order_master where o_status='p' with check option constraint x;

 create or replace view pen_view as select * from order_master with read only;  

 -- DML statements and join views

 create view orders as select o.orderno,odate,itemcode, qty_ord from order_master o,order_detail d 

where o.orderno=d.orderno;  

 --  Key-Preserved Tables

  A table is key preserved if every key of the table can also be a key of the result of the join.

  In our case orderno of the order_master is the key to the view. so order_master table is key-preserved

  table whose keys are preserved through a join.

    update orders set odate=odate+1 where orderno='a10'; --> wrong

 

 ----------------- Index -------------------------

  create index x on order_detail(orderno);

 

 create index x on order_detail(orderno,itemcode);   

© 2004-2005 Mercury Digital Media