drop table customers;
Create table
    customers 
        (  id            number,
           location      varchar2(40) , /* 'LONDON' */
	   customer_data varchar2(40),
           CONSTRAINT 
           customers_pk  UNIQUE (id)
        )  ;
insert into customers select rownum, owner, object_name 
from all_objects where rownum < 1000;
update customers set location='LONDON' where location='PUBLIC' and rownum < 60;
--update customers set location='LONDON' where location='PUBLIC' ;

drop table orders;
Create table
    orders 
        (  id            number,
           id_customer   number,
           date_placed   date not null ,
	       order_data    varchar2(40),
           CONSTRAINT 
           orders_pk     UNIQUE (id)
        ) ;
insert into orders select
       rownum,
       mod(rownum,998) +1 ,
--(sysdate - dbms_random.value(0,100)), 
 (sysdate - dbms_random.value(0,2000)), 
       object_name
     from all_objects ;
--from all_objects where rownum < 10000;
--create index odate_i on orders(date_placed);

drop table order_lines ;
Create table
    order_lines 
        (  id_order        number,
           id_product      number,
	   order_line_data varchar2(40)
        ) ;
insert into order_lines 
select mod(rownum,10000),
       mod(rownum,200)+1, /* don't include all products */
       object_name
from all_objects;
commit;

drop table products ;
Create table
    products 
        (  id              number,
           id_supplier     number,
	   product_data    varchar2(40),
           CONSTRAINT 
           products_pk UNIQUE (id)
	) ;
insert into products 
select rownum,
       mod(rownum,100),
       object_name
from all_objects where rownum < 2000;

drop table suppliers ;
Create table
    suppliers 
        (  id             number,
           location       varchar2(40),/* 'LEEDS' */
	   supplier_data  varchar2(40),
           CONSTRAINT 
           suppliers_pk UNIQUE (id)
        ) pctfree 85;
insert into suppliers 
select rownum,
       owner,
       object_name
from all_objects where rownum <100;
 update suppliers set location='LEEDS' where rownum < 50;


drop table alternatives   ; 
Create table
    alternatives    
        (  
	   id_product   number,
	   id_product_sub   number
        )pctfree 99;
insert into alternatives 
    select
       rownum,
       rownum+10
     from all_objects where rownum < 200;
     commit;
create unique index alt_i on alternatives(id_product);

create or replace view v_alternatives as (
	select  alt.id_product
	FROM  alternatives    alt
      INNER JOIN products prd2   ON prd2.id = alt.id_product_sub
      INNER JOIN suppliers sup2  ON sup2.id = prd2.id_supplier
    WHERE    sup2.location != 'LEEDS');

BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,'SUPPLIERS'); END;
BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,'PRODUCTS'); END;
BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,'ORDERS'); END;
BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,'ORDER_LINES'); END;
BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,'CUSTOMERS'); END;
BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,'ALTERNATIVES'); END;