drop table sales;
drop table clients;
drop table products;
drop table shops;
drop sequence seq_products;
drop sequence seq_Sales;
drop sequence seq_Clients;
drop procedure insert_shops;
drop procedure insert_clients;
drop procedure insert_products;
drop procedure insert_sales;

/* ******************/
/* execute in Ora01 */
/* ******************/

create table Shops
(ShopID number(4) constraint Shops_PK primary key,
 SName varchar2(20),
 City varchar2(20));

create or replace procedure insert_Shops (i number default 30)
is
  n number default 1;
begin
  for n in 1..i loop
    insert into Shops values (n, 'Shop '||n, 'London');
  end loop;
end;
/

exec insert_shops

update Shops set City='Glasgow' where ShopID between 16 and 30;
create index Shops_City_indx on Shops(City);

/* ******************/
/* execute in Ora02 */
/* ******************/

create table Products
(ProductID number(4) constraint Products_PK primary key,
 PName varchar2(40),
 Category varchar2(20),
 UnitPrice number(6,2));

create sequence seq_Products;

create or replace procedure insert_Products (i number default 2)
is
  n number default 1;
begin
  for n in 1..i loop
          insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'books', 250);
 	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'electronics', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'stationary', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'luxury cosmetics', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'luxury clothes', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'popular cosmetics', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'sport clothes', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'sport gears', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'furnitures', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'computers', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'books', 250);
 	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'electronics', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'stationary', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'luxury cosmetics', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'luxury clothes', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'popular cosmetics', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'sport clothes', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'sport gears', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'furnitures', 250);
	  insert into Products values (seq_Products.nextval, 'Product '||seq_Products.nextval, 'computers', 250);
  end loop;
end;
/

exec insert_products
commit;

create table Sales
(SalesID number(12) constraint Sales_PK primary key,
 SDate date,
 Quantity number(3),
 ProductID number(4) constraint Sales_Prod_FK references Products(ProductID),
 ClientID number(6),
 ShopID number(4));

create sequence seq_Sales;

create or replace procedure insert_Sales (i number default 30)
is
  skl number default 1;
  skl_max number default 30;
  kli number default 1;
  kli_max number default 100;
  pro number default 1;
  pro_max number default 40;
begin
  for skl in 1..skl_max loop
     for pro in 1..pro_max loop
        for kli in 1..kli_max loop
           insert into Sales values (seq_Sales.nextval, sysdate, 10, pro, kli, skl);   
        end loop;
     end loop;
   end loop;
end;
/

exec insert_sales

create index Products_Cat_indx on Products(category);
create index Sales_Cli_indx on Sales(ClientID);
create index Sales_Pro_indx on Sales(ProductID);
create index Sales_Sho_indx on Sales(ShopID);

/* ******************/
/* execute in Ora03 */
/* ******************/

create table Clients
(ClientID number(6) constraint Clients_PK primary key,
 CName varchar2(10),
 City varchar2(20),
 Points number(6,2));

create sequence seq_Clients;

create or replace procedure insert_Clients
is
  n number default 1;
begin
  for n in 1..200 loop
    insert into Clients values (seq_Clients.nextval, 'Client '||n, 'London', 1000);
  end loop;

 for n in 1..200 loop
   insert into Clients values (seq_Clients.nextval, 'Client '||n, 'Edinbourg', 1000);
 end loop;
 
 for n in 1..200 loop
   insert into Clients values (seq_Clients.nextval, 'Client '||n, 'Glasgow', 1000);
 end loop;

 for n in 1..200 loop
   insert into Clients values (seq_Clients.nextval, 'Client '||n, 'Luton', 1000);
 end loop;

 for n in 1..200 loop
   insert into Clients values (seq_Clients.nextval, 'Client '||n, 'Cardiff', 1000);
 end loop;
end;
/

exec insert_clients
commit;