-- To run this file, issue the following command:
-- db2 -svtf create-db.sql

create database reportdb;

connect to reportdb;

CREATE TABLE "SAMPLE"."CUSTOMER"  (
		  "CUSTOMER_ID" INTEGER NOT NULL,
		  "FIRST_NAME" VARCHAR(50) NOT NULL , 
		  "LAST_NAME" VARCHAR(50) NOT NULL , 
		  "CITY" VARCHAR(46) NOT NULL , 
		  "STATE" CHAR(2) NOT NULL,
		  "AGE" SMALLINT NOT NULL,
		  "EMAIL" VARCHAR(128) NOT NULL , 
          PRIMARY KEY (CUSTOMER_ID)); 
          
CREATE TABLE "SAMPLE"."CUSTOMER_ORDER"  (
		  "CUSTOMER_ID" INTEGER NOT NULL, 
		  "ORDER_ID" INTEGER NOT NULL,  
		  "ORDER_DATE" DATE NOT NULL,
		  "STATUS" VARCHAR(50) NOT NULL,
		  "PLACED_BY" VARCHAR(50) NOT NULL,
		  PRIMARY KEY (ORDER_ID)); 
		  
CREATE TABLE "SAMPLE"."CUSTOMER_REFERRAL" (
	"CUSTOMER_ID" INTEGER NOT NULL, 
	"REFERRED_CUSTOMER_ID" INTEGER NOT NULL, 
	PRIMARY KEY (REFERRED_CUSTOMER_ID));

ALTER TABLE "SAMPLE"."CUSTOMER_REFERRAL"
    ADD CONSTRAINT "CUSTOMER_ID_FK1" FOREIGN KEY
        ("CUSTOMER_ID")
    REFERENCES "SAMPLE"."CUSTOMER"
        ("CUSTOMER_ID")
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

ALTER TABLE "SAMPLE"."CUSTOMER_REFERRAL"
    ADD CONSTRAINT "CUSTOMER_ID_FK2" FOREIGN KEY
        ("REFERRED_CUSTOMER_ID")
    REFERENCES "SAMPLE"."CUSTOMER"
        ("CUSTOMER_ID")
    ON DELETE CASCADE
    ON UPDATE NO ACTION;    

-- Populate the customer table
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (1, 'John', 'Smith', 'Austin', 'TX', 29, 'john@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (2, 'Phil', 'Collins', 'Austin', 'TX', 50, 'phil@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (3, 'Steve', 'Whelan', 'Austin', 'TX', 45, 'steve@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (4, 'Aimee', 'Hanson', 'Austin', 'TX', 25, 'aimee@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (5, 'Kevin', 'White', 'Austin', 'TX', 27, 'kevin@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (6, 'Brian', 'Comer', 'Austin', 'TX', 50, 'brian@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (7, 'David', 'Fisher', 'Dallas', 'TX', 59, 'david@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (8, 'Marlene', 'Rodriguez', 'Dallas', 'TX', 22, 'marlene@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (9, 'Mark', 'Stevens', 'Dallas', 'TX', 29, 'mark@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (10, 'Sarah', 'Juric', 'Dallas', 'TX', 33, 'sarah@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (11, 'Sasha', 'Nashi', 'Dallas', 'TX', 29, 'sasha@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (12, 'Craig', 'Carnell', 'New York', 'NY', 29, 'craig@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (13, 'Juan', 'Santiago', 'New York', 'NY', 35, 'juan@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (14, 'Omar', 'Arjona', 'New York', 'NY', 47, 'omar@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (15, 'Tony', 'Banks', 'New York', 'NY', 45, 'tony@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (16, 'Thomas', 'Cuan', 'San Francisco', 'CA', 55, 'tom@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (17, 'James', 'Braker', 'San Francisco', 'CA', 50, 'james@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (18, 'Sam', 'Rivera', 'San Francisco', 'CA', 39, 'sam@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (19, 'Alexander', 'Berry', 'San Francisco', 'CA', 19, 'alex@company.com');
INSERT INTO SAMPLE.CUSTOMER (CUSTOMER_ID, FIRST_NAME, LAST_NAME, CITY, STATE, AGE, EMAIL) VALUES (20, 'Rod', 'Feit', 'San Francisco', 'CA', 19, 'alex@company.com');


-- Populate the order table
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (1, 23422, DATE('2004-05-26'), 'COMPLETED', 'ONLINE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (1, 24676, DATE('2004-03-16'), 'COMPLETED', 'ONLINE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (1, 62535, DATE('2004-10-01'), 'PROCESSING', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (1, 17834, DATE('2004-08-05'), 'CANCELLED', 'MAIL');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (1, 92348, DATE('2004-02-23'), 'COMPLETED', 'FAX');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (1, 43435, DATE('2004-10-05'), 'PROCESSING', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (1, 83483, DATE('2004-04-13'), 'COMPLETED', 'PHONE');

INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (2, 60834, DATE('2004-04-13'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (2, 10000, DATE('2004-03-15'), 'COMPLETED', 'PHONE');

INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (3, 10001, DATE('2004-05-13'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (3, 10002, DATE('2004-07-26'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (3, 10003, DATE('2004-08-24'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (3, 10004, DATE('2004-09-18'), 'COMPLETED', 'FAX');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (3, 10005, DATE('2004-10-01'), 'PROCESSING', 'PHONE');

INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (4, 10006, DATE('2004-02-22'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (4, 10007, DATE('2004-04-29'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (4, 10008, DATE('2004-04-23'), 'COMPLETED', 'FAX');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (4, 10009, DATE('2004-06-13'), 'COMPLETED', 'ONLINE');

INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (5, 10010, DATE('2004-01-05'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (5, 10011, DATE('2004-01-17'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (5, 10012, DATE('2004-02-14'), 'COMPLETED', 'FAX');

INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (6, 10013, DATE('2004-02-02'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (6, 10014, DATE('2004-02-16'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (6, 10015, DATE('2004-04-13'), 'CANCELLED', 'FAX');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (6, 10016, DATE('2004-05-13'), 'COMPLETED', 'ONLINE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (6, 10017, DATE('2004-06-19'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (6, 10018, DATE('2004-07-18'), 'COMPLETED', 'MAIL');

INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (7, 10019, DATE('2004-01-05'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (7, 10020, DATE('2004-01-15'), 'CANCELLED', 'MAIL');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (7, 10021, DATE('2004-01-29'), 'COMPLETED', 'ONLINE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (7, 10022, DATE('2004-03-05'), 'COMPLETED', 'PHONE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (7, 10023, DATE('2004-03-25'), 'COMPLETED', 'ONLINE');
INSERT INTO SAMPLE.CUSTOMER_ORDER (CUSTOMER_ID, ORDER_ID, ORDER_DATE, STATUS, PLACED_BY) VALUES (7, 10024, DATE('2004-04-06'), 'COMPLETED', 'PHONE');