drop table customer cascade constraints;
drop TABLE product CASCADE CONSTRAINTS;
drop TABLE time CASCADE CONSTRAINTS;
drop table geography cascade constraints;
drop TABLE todays_special_offers CASCADE CONSTRAINTS;
drop TABLE purchases CASCADE CONSTRAINTS;

---------------------------------------------
-- CUSTOMER Dimension
---------------------------------------------

CREATE TABLE customer
(customer_id            varchar2(10) CONSTRAINT pk_customer PRIMARY KEY,
 town                   varchar2(10),
 county                 varchar2(10),
 postal_code            varchar2(10),
 dob                    date,
 country                varchar2(20),
 occupation             varchar2(10));

---------------------------------------------
-- PRODUCT Dimension
---------------------------------------------

CREATE TABLE product
(product_id             varchar2(8) CONSTRAINT pk_product PRIMARY KEY,
 product_name           varchar2(30),
 category               varchar2(4),
 cost_price             number (6,2) constraint cost_price_not_null  NOT NULL,
 sell_price             number (6,2) constraint sell_price_not_null  NOT NULL,
 weight                 number (4,2),
 shipping_charge        number (5,2) constraint shipping_charge_not_null  NOT NULL,
 manufacturer           varchar2(20),
 supplier               varchar2(10));

---------------------------------------------
-- TIME Dimension
---------------------------------------------

CREATE TABLE time
(time_key               date CONSTRAINT pk_time PRIMARY KEY,
 day                    number (2,0),
 month                  number (2,0),
 quarter                number (2,0),
 year                   number (4,0),
 day_number             number (3,0),
 day_of_the_week        varchar2(10),
 week_number            number (2,0),
 public_holiday         varchar2(1)
   constraint public_holiday CHECK (public_holiday IN ('Y','N')) );

---------------------------------------------
-- GEOGRAPHY Dimension
---------------------------------------------

create table geography 
(state_id   varchar2(2) constraint state_id_pk primary key,
 state_name varchar2(20),
 region     varchar2(10),
 tax_rate   number);

---------------------------------------------
-- TODAYS_SPECIAL_OFFERS Dimension
---------------------------------------------

CREATE TABLE todays_special_offers
(product_id             varchar2(8),
 offer_date             date,
 special_price          number (6,2),
 offer_price            number (6,2),
 CONSTRAINT pk_specials PRIMARY KEY (offer_date,product_id ));

---------------------------------------------
-- Fact Table  PURCHASES
---------------------------------------------

CREATE TABLE purchases
(product_id             varchar2(8) constraint not_null_product_id NOT NULL
                                    constraint fk_product_id  REFERENCES  product(product_id),
 time_key               date constraint not_null_time   NOT NULL
                             constraint fk_time  REFERENCES  time(time_key),
 customer_id            varchar2(10) constraint not_null_customer_id   NOT NULL
                                     constraint fk_customer_id  REFERENCES  customer(customer_id),
 purchase_date          date,
 purchase_time          number(4,0),
 purchase_price         number(6,2),
 shipping_charge        number(5,2),
 state_id               varchar2(2),
 today_special_offer    varchar2(1) constraint special_offer
                                    CHECK (today_special_offer IN ('Y','N'))   )
PARTITION by RANGE  (time_key )
  (partition purchases_jan99
          values less than (TO_DATE('01-02-1999', 'DD-MM-YYYY')),
   partition purchases_feb99
          values less than (TO_DATE('01-03-1999', 'DD-MM-YYYY')),
   partition purchases_mar99
          values less than (TO_DATE('01-04-1999', 'DD-MM-YYYY')),
   partition purchases_apr99
          values less than (TO_DATE('01-05-1999', 'DD-MM-YYYY')));


alter session set nls_language='english';
alter session set nls_date_format='DD-MON-YYYY';

---------------------------------------------
-- TIME Dimension
---------------------------------------------

INSERT INTO time VALUES ( '01-JAN-1999',1,1,1,1999,1,'Sunday',1,'Y');
INSERT INTO time VALUES ( '02-JAN-1999',2,1,1,1999,2,'Monday',1,'Y');
INSERT INTO time VALUES ( '01-FEB-1999',1,2,1,1999,32,'Sunday',5,'N');
INSERT INTO time VALUES ( '02-FEB-1999',2,2,1,1999,33,'Monday',5,'N');
INSERT INTO time VALUES ( '03-FEB-1999',3,2,1,1999,34,'Tuesday',5,'N');
INSERT INTO time VALUES ( '04-FEB-1999',4,2,1,1999,35,'Wednesday',5,'N');
INSERT INTO time VALUES ( '05-FEB-1999',5,2,1,1999,36,'Thursday',5,'N');
INSERT INTO time VALUES ( '01-MAR-1999',1,3,1,1999,60,'Monday',9,'N');
INSERT INTO time VALUES ( '02-MAR-1999',2,3,1,1999,61,'Tuesday',9,'N');
INSERT INTO time VALUES ( '01-APR-1999',1,4,2,1999,92,'Thursday',13,'N');

---------------------------------------------
-- GEOGRAPHY Dimension
---------------------------------------------

insert into geography values ('MA', 'Massachusetts', 'NorthEast', 5.6);
insert into geography values ('NH', 'New Hampshire', 'NorthEast', 0);
insert into geography values ('CA', 'California', 'West', 8.5);
insert into geography values ('WA', 'Washington', 'NorthWest', 4);
insert into geography values ('TX', 'Texas', 'South', 0);
insert into geography values ('OH', 'Ohio', 'MidWest', 3);
insert into geography values ('WI', 'Wisconsin', 'MidWest', 4.5);

commit;
---------------------------------------------
-- Customer
---------------------------------------------

INSERT INTO customer VALUES 
        ('AB123456', 'Eastleigh', 'Hants','SO531LD','16-FEB-1959','UK','CONSULTANT');
INSERT INTO customer VALUES 
        ('AB123457', 'Soton', 'Hants','SO6 1gh','23-AUG-1921','UK','HOUSEWIFE');
INSERT INTO customer VALUES
        ('AB123458', 'Chandlers', 'Hants','SO53 1TZ','23-AUG-1921','UK','HOMEMAKER');
INSERT INTO customer VALUES 
        ('AB123459', 'Soton', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123460', 'Soton2', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123461', 'Soton3', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123462', 'Soton4', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123463', 'Soton5', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123464', 'Soton6', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123465', 'Soton7', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123466', 'Soton8', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123467', 'Soton9', 'Hants','SO11TF','23-SEP-1927' ,'UK','HOUSEWIFE');
INSERT INTO customer VALUES 
        ('AB123477', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123478', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123479', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123480', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123481', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123482', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123483', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123484', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123485', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123486', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123487', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123488', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123489', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123490', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123491', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123492', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123493', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123494', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123495', 'London', 'London','W11QC','14-APR-1956' ,'UK','ENGINEER');
INSERT INTO customer VALUES 
        ('AB123496', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123497', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123498', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123499', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123500', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123501', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123502', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123503', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123504', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123505', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123506', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123507', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123508', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');
INSERT INTO customer VALUES 
        ('AB123509', 'Liverpool', 'Mersey','LV11QT','17-JUN-1963' ,'UK','FITTER');

commit;
---------------------------------------------
-- PRODUCT
---------------------------------------------
INSERT INTO product VALUES
        ('SP1000', 'Digital  Camera','ELEC', 45.67, 67.23, 15.00, 4.50, 'Ricoh','Ricoh' );

INSERT INTO product VALUES 
        ('SP1001', 'APS  Camera','ELEC', 24.67, 36.23,5.00, 4.50, 'Ricoh','Ricoh' );
INSERT INTO product VALUES ('SP1010', 'Camera','ELEC', 35.67, 47.89, 5.00,
4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1011', 'Camera','ELEC', 35.67, 47.89, 5.00,
4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1012', 'Camera','ELEC', 35.67, 47.89, 5.00,
4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1013', 'Camera SX1','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1014', 'Camera SX2','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1015', 'Camera SX3','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1016', 'Camera SX4','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1017', 'Camera SX5','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1018', 'Camera SX6','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1019', 'Camera SX7','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1020', 'Camera SX8','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1021', 'Camera SX9','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1022', 'Camera SX45','ELEC', 35.67, 47.89,
5.00, 4.50, 'Agfa','Agfa' );
INSERT INTO product VALUES ('SP1023', 'Telescope1','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1024', 'Telescope2','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1025', 'Telescope3','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1026', 'Telescope4','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1027', 'Telescope5','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1028', 'Telescope6','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1029', 'Telescope7','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1030', 'Telescope8','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1031', 'Telescope9','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1032', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1033', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1034', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1035', 'Telescope','ELEC', 100.04, 156.45,
12.00, 6.95, 'Meade','BCF' );
INSERT INTO product VALUES ('SP1036', 'Computer LX1','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1037', 'Computer LX2','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1038', 'Computer LX3','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1039', 'Computer LX4','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1040', 'Computer LX5','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1041', 'Computer LX6','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1042', 'Computer LX7','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1043', 'Computer LX8','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1044', 'Computer LX9','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1045', 'Computer SX1','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1046', 'Computer SX2','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1047', 'Computer SX3','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1048', 'Computer SX4','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1049', 'Computer SX5','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1050', 'Computer SX6','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1051', 'Computer SX7','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1052', 'Computer SX8','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1053', 'Computer SX9','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1054', 'Computer TX1','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1055', 'Computer TX2','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1056', 'Computer TX3','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1057', 'Computer TX4','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1058', 'Computer TX5','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1059', 'Computer TX6','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1060', 'Computer TX7','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1061', 'Computer TX8','ELEC', 876.67,
1267.89, 12.00, 6.95, 'Dell','D-Dell' );
INSERT INTO product VALUES ('SP1062', 'Lamp LX1','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1063', 'Lamp LX2','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1064', 'Lamp LX3','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1065', 'Lamp LX4','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1066', 'Lamp LX5','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1067', 'Lamp LX6','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1068', 'Lamp LX7','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1069', 'Lamp LX8','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1070', 'Lamp LX9','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1071', 'Lamp SX1','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1072', 'Lamp SX2','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1073', 'Lamp SX3','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1074', 'Lamp SX4','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1075', 'Lamp SX5','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1076', 'Lamp SX6','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1077', 'Lamp SX7','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1078', 'Lamp SX8','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1080', 'Lamp SX9','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1081', 'Lamp TX1','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1082', 'Lamp TX2','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1083', 'Lamp TX3','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1084', 'Lamp TX4','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1085', 'Lamp TX5','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1086', 'Lamp TX6','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1087', 'Lamp TX7','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1088', 'Lamp TX8','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1089', 'Lamp TX9','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1090', 'Lamp TX0','ELEC', 15.67, 24.99, 2.5,
2.95, 'RTG','Touch Inc' );
INSERT INTO product VALUES ('SP1092', 'Kitchen LX1','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1093', 'Kitchen LX2','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1094', 'Kitchen LX3','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1095', 'Kitchen LX4','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1096', 'Kitchen LX5','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1097', 'Kitchen LX6','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1098', 'Kitchen LX7','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1099', 'Kitchen LX8','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1100', 'Kitchen LX9','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1101', 'Kitchen SX1','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1102', 'Kitchen SX2','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1103', 'Kitchen SX3','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1104', 'Kitchen SX4','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1105', 'Kitchen SX5','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1106', 'Kitchen SX6','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1107', 'Kitchen SX7','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1111', 'Kitchen SX8','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1110', 'Kitchen SX9','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1112', 'Kitchen TX2','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1113', 'Kitchen TX3','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1114', 'Kitchen TX4','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1115', 'Kitchen TX5','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1116', 'Kitchen TX6','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1117', 'Kitchen TX7','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1118', 'Kitchen TX8','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1119', 'Kitchen TX9','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
INSERT INTO product VALUES ('SP1200', 'Kitchen TX0','ELEC', 45.56, 72.87,
2.5, 4.50, 'RTG','Tefal' );
commit;
INSERT INTO product VALUES ('SP1212', 'Mop LX1','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1213', 'Mop LX2','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1214', 'Mop LX3','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1215', 'Mop LX4','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1216', 'Mop LX5','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1217', 'Mop LX6','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1218', 'Mop LX7','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1219', 'Mop LX8','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1220', 'Mop LX9','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1221', 'Mop SX1','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1222', 'Mop SX2','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1223', 'Mop SX3','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1224', 'Mop SX4','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1225', 'Mop SX5','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1226', 'Mop SX6','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1227', 'Mop SX7','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1231', 'Mop SX8','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1122', 'Mop SX9','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1232', 'Mop TX2','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1233', 'Mop TX3','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1234', 'Mop TX4','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1235', 'Mop TX5','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1236', 'Mop TX6','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1237', 'Mop TX7','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1238', 'Mop TX8','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
INSERT INTO product VALUES ('SP1239', 'Mop TX9','HDRW', 8.90, 15.67, 2.5,
4.50, 'RTG','Mop Inc' );
commit;
INSERT INTO product VALUES ('SP1242', 'CD LX1','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1243', 'CD LX2','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1244', 'CD LX3','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1245', 'CD LX4','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1246', 'CD LX5','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1247', 'CD LX6','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1248', 'CD LX7','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1249', 'CD LX8','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1250', 'CD LX9','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1251', 'CD SX1','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1252', 'CD SX2','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1253', 'CD SX3','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1254', 'CD SX4','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1255', 'CD SX5','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1256', 'CD SX6','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1257', 'CD SX7','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1258', 'CD SX8','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1260', 'CD SX9','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1261', 'CD TX1','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1262', 'CD TX2','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1263', 'CD TX3','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1264', 'CD TX4','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1265', 'CD TX5','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1266', 'CD TX6','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1267', 'CD TX7','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1268', 'CD TX8','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
INSERT INTO product VALUES ('SP1269', 'CD TX9','MUSC', 8.90, 15.67, 2.5,
2.95, 'RTG','CD Inc' );
commit;

---------------------------------------------
-- TODAYS_SPECIAL_OFFERS
---------------------------------------------

INSERT INTO todays_special_offers  VALUES ('SP1001', '1-JAN-1999', 28.01, 36.23);
INSERT INTO todays_special_offers  VALUES ('SP1032', '2-JAN-1999', 125.67, 156.45);
INSERT INTO todays_special_offers  VALUES ('SP1057', '1-FEB-1999', 901.23, 1267.89);
INSERT INTO todays_special_offers  VALUES ('SP1200', '2-FEB-1999', 59.78, 72.87);
INSERT INTO todays_special_offers  VALUES ('SP1239', '1-MAR-1999', 11.34, 15.67);
INSERT INTO todays_special_offers  VALUES ('SP1269', '2-MAR-1999', 10.56, 15.67);

---------------------------------------------
-- PURCHASES
---------------------------------------------

INSERT INTO purchases VALUES ( 'SP1001', '1-JAN-1999', 'AB123456', '1-JAN-1999', 0024, 28.01, 4.50, 'MA', 'Y');
INSERT INTO purchases VALUES ( 'SP1001', '1-JAN-1999', 'AB123457', '1-JAN-1999', 1024, 28.01, 4.50, 'NH', 'Y');

INSERT INTO purchases VALUES ( 'SP1001', '2-JAN-1999', 'AB123457', '2-JAN-1999', 0024, 28.01, 4.50, 'NH', 'N');
INSERT INTO purchases VALUES ( 'SP1001', '2-JAN-1999', 'AB123457', '2-JAN-1999', 1024, 28.01, 4.50, 'NH', 'N');

INSERT INTO purchases VALUES ( 'SP1011', '2-JAN-1999', 'AB123458', '2-JAN-1999', 1024, 50, 5, 'CA', 'N');

INSERT INTO purchases VALUES ( 'SP1012', '2-JAN-1999', 'AB123458', '2-JAN-1999', 1024, 45, 5, 'CA', 'N');
INSERT INTO purchases VALUES ( 'SP1013', '2-JAN-1999', 'AB123458', '2-JAN-1999', 1024, 32, 5, 'CA', 'N');


INSERT INTO purchases VALUES ( 'SP1061', '1-FEB-1999', 'AB123456', '1-FEB-1999', 0024, 60, 4.50, 'MA', 'Y');
INSERT INTO purchases VALUES ( 'SP1062', '1-FEB-1999', 'AB123457', '1-FEB-1999', 1024, 12, 4.50, 'NH', 'Y');
INSERT INTO purchases VALUES ( 'SP1011', '1-FEB-1999', 'AB123460', '1-FEB-1999', 1024, 50, 5, 'WA', 'N');


INSERT INTO purchases VALUES ( 'SP1063', '2-FEB-1999', 'AB123457', '2-FEB-1999', 0024, 11, 4.50, 'NH', 'N');
INSERT INTO purchases VALUES ( 'SP1064', '2-FEB-1999', 'AB123457', '2-FEB-1999', 1024, 31, 4.50, 'NH', 'N');

INSERT INTO purchases VALUES ( 'SP1065', '3-FEB-1999', 'AB123460', '3-FEB-1999', 1024, 11, 4.50, 'TX', 'N');
INSERT INTO purchases VALUES ( 'SP1066', '4-FEB-1999', 'AB123460', '4-FEB-1999', 1024, 12, 4.50, 'TX', 'N');
INSERT INTO purchases VALUES ( 'SP1067', '5-FEB-1999', 'AB123460', '5-FEB-1999', 1024, 13, 4.50, 'TX', 'N');


INSERT INTO purchases VALUES ( 'SP1212', '1-MAR-1999', 'AB123457', '1-MAR-1999', 0024, 11, 4.50, 'NH', 'N');
INSERT INTO purchases VALUES ( 'SP1213', '1-MAR-1999', 'AB123457', '1-MAR-1999', 1024, 31, 4.50, 'NH', 'N');

INSERT INTO purchases VALUES ( 'SP1220', '2-MAR-1999', 'AB123457', '2-MAR-1999', 0024, 11, 4.50, 'NH', 'N');
INSERT INTO purchases VALUES ( 'SP1212', '2-MAR-1999', 'AB123457', '2-MAR-1999', 1024, 31, 4.50, 'NH', 'N');

INSERT INTO purchases VALUES ( 'SP1220', '1-APR-1999', 'AB123457', '1-APR-1999', 0024, 11, 4.50, 'NH', 'N');
INSERT INTO purchases VALUES ( 'SP1064', '1-APR-1999', 'AB123457', '1-APR-1999', 1024, 31, 4.50, 'NH', 'N');

------------------------------------------------
-- Now load up lots of purchases

DECLARE
   p_id      varchar2(8);
   s_price   number(6,2);
   ship      number(5,2);
   c_id      varchar2(10);
   buyat     NUMBER := 1001;
  CURSOR all_buys IS
    SELECT  product_id, sell_price, shipping_charge  FROM product;
  CURSOR all_customers  IS SELECT customer_id FROM customer;
  BEGIN
  OPEN  all_buys;
  LOOP
    FETCH all_buys INTO p_id, s_price, ship;
  OPEN all_customers;
   LOOP
      FETCH all_customers INTO c_id;
      INSERT INTO PURCHASES VALUES 
      (p_id,
       '1-JAN-1999',
       c_id,
       '1-JAN-1999',
       buyat,
       s_price,
       ship,
       null ,
       'N');
      buyat:= buyat +1;
  EXIT WHEN all_customers%NOTFOUND;
   END LOOP;
   CLOSE all_customers;
   buyat:= 1001;
  EXIT WHEN all_buys%NOTFOUND;
  END LOOP;
  CLOSE all_buys;
  END;
/

commit;

-- Now load up lots of purchases

DECLARE
   p_id      varchar2(8);
   s_price   number(6,2);
   ship      number(5,2);
   c_id      varchar2(10);
   buyat     NUMBER := 1001;
  CURSOR all_buys IS
    SELECT  product_id, sell_price, shipping_charge  FROM product;
  CURSOR all_customers  IS SELECT customer_id FROM customer;
  BEGIN
  OPEN  all_buys;
  LOOP
    FETCH all_buys INTO p_id, s_price, ship;
  OPEN all_customers;
   LOOP
      FETCH all_customers INTO c_id;
      INSERT INTO PURCHASES VALUES
      (p_id, '2-FEB-1999',c_id, '1-JAN-1999', buyat , s_price, ship, null, 'N');
      buyat:= buyat +1;
  EXIT WHEN all_customers%NOTFOUND;
   END LOOP;
   CLOSE all_customers;
   buyat:= 1001;
  EXIT WHEN all_buys%NOTFOUND;
  END LOOP;
  CLOSE all_buys;
  END;
/

update purchases set state_id = 'MA' where rownum < 3000 ;
update purchases set state_id = 'NH' where state_id is null and rownum < 3000 ;
update purchases set state_id = 'CA' where state_id is null and rownum < 6000 ;
update purchases set state_id = 'WI' where state_id is null and rownum < 3000 ;
update purchases set state_id = 'OH' where state_id is null and rownum < 100 ;
update purchases set state_id = 'TX' where state_id is null and rownum < 200 ;
update purchases set state_id = 'WA' where state_id is null;

commit;