OLTP Three normal forms example

IBSN	Title	Authors	Publisher
12345	Book First	Author A, Author B	Publisher Name, City, Street, Zip Code
57890	Book Second	Author C	Publisher Name, City, Street, Zip Code

First Normal For Remove repeating groups We have two authors in one cell

BOOKS Table

IBSN	Title	Publisher
12345	Book First	Publisher Name, City, Street, Zip Code
57890	Book Second	Publisher Name, City, Street, Zip Code

AUTHORS table

Author	IBSN
Author A	12345
Author A	12345
Author B	57890

Second Normal Form

BOOKS Table – Remove information which not depends on primary key, in this case Publisher address is not depend on IBSN

IBSN	Title	Publisher
12345	Book First	Publisher Name
57890	Book Second	Publisher Name

PUBLISHERS table

PUBLISHER	Street	City	State
Publisher Name	Street	City	State

Third Normal Form –remove columns which depend on each other: street exist in only one city, city can be only in one state …

PUBLISHERS table

Publisher	Address Code
Publisher Name	123

ADDRESSES table

Address Code	Street	City	State
123	Street	City	State

Interesting behaviour of SYSDATE in oracle

Everybody knows that SYSDATE if the funtion which shows current date in Oracle

select sysdate from dual

But let's try to use SYSDATE in PL/SQL

declare

sysdate integer;

begin

  sysdate:=1;

  dbms_output.put_line(sysdate);

  end;

Wery confusing. Is not it. How SYSDATE can be integer and how date can equals 1.

But in fact this block returs 1 and number succesfully initialize to SYSDATE variable.Yes in this context SYSDATE is just a varible name. This is becuse PL/SQL enjine does not have it's own SYSDATE and it have to ask SQL for the result. In this pease of code we are not using SQL just PL so SYSDATE is an integer and block returns 1.

Oracle Continue statement

Oracle 11G provides new feature for the loops calling CONTINUE.
This statment allows to exit the loop if CONTINUE is passing with out any conditions or skip loop iteration and gows to the next one when the contition is met.
Here is a very simple examle for printing the numbers from 1 to 10 which are not devided by 2

begin

  for i in 1..10  loop

    continue when mod(i,2)=0;

    dbms_output.put_line(i);

  end loop; 

  end;

Here is the same code but without a WHEN statment in continue

  begin

  for i in 1..10  loop

    if  mod(i,2)=0 then continue;end if;

    dbms_output.put_line(i);

  end loop; 

  end;

Umfortunatelly 10G is not supoort CONTINUE but we can rewrite it using GOTO  and labels

declare

i integer default 0;

begin

  while i<9 loop

    <<mylabel>>

    i:=i+1;

    if mod(i,2)=0 then goto mylabel;end if;

    dbms_output.put_line(i);

  end loop;

end;  

OBIEE Administration tool hangs

OBIEE 11g Client Admin Tool Connection To Datasource In Offline Mode Fails With Error "The Connection Has Failed" [ID 1296370.1]

Oracle outer partition join

with my_tab as --subquery_factoring_clause

(

select  extract( day from arrivaldate) d_num  ,h.name hotel_name,rt.name room_name,count(*) cnt  from reservation r, block b, hotel h,roomtype rt

where r.blockid = b.blockid

and b.hotelid = h.hotelid

and b.roomtypeid = rt.roomtypeid

and b.hotelid = rt.hotelid

and b.eventid  = rt.eventid

and arrivaldate >=  to_date('01.01.2012','dd.mm.yyyy')

and arrivaldate <=  to_date('01.02.2012','dd.mm.yyyy')

group by extract( day from arrivaldate) ,h.name,rt.name

),

d_days as

(

  select level d_num from dual connect by level <=31

)

select d_days.d_num,my_tab.hotel_name,my_tab.room_name,nvl(my_tab.cnt,0)

from    d_days left outer join my_tab

on d_days.d_num= my_tab.d_num

order by 2

There would be data only for that hotel and rooms where we have reservations on ohter columns it would be NULL

with my_tab as --subquery_factoring_clause

(

select  extract( day from arrivaldate) d_num  ,h.name hotel_name,rt.name room_name,count(*) cnt  from reservation r, block b, hotel h,roomtype rt

where r.blockid = b.blockid

and b.hotelid = h.hotelid

and b.roomtypeid = rt.roomtypeid

and b.hotelid = rt.hotelid

and b.eventid  = rt.eventid

and arrivaldate >=  to_date('01.01.2012','dd.mm.yyyy')

and arrivaldate <=  to_date('01.02.2012','dd.mm.yyyy')

group by extract( day from arrivaldate) ,h.name,rt.name

),

d_days as

(

  select level d_num from dual connect by level <=31

)

select d_days.d_num,my_tab.hotel_name,my_tab.room_name,nvl(my_tab.cnt,0)

from    d_days left outer join my_tab

partition by (my_tab.hotel_name,my_tab.room_name) -- partition outer join

on d_days.d_num= my_tab.d_num

order by 2