
CREATE TABLE "CUSTOMER" ("CUSTOMER_ID" NUMBER(5, 0) NOT NULL, 
  "FIRSTNAME" VARCHAR2(20) NOT NULL,
  "LASTNAME" VARCHAR2(20) NOT NULL, 
  "ADDRESS" VARCHAR2(100) NOT NULL, 
  "COUNTRY" VARCHAR2(20) NOT NULL, 
  "TELEPHONE" VARCHAR2(18) NOT NULL, 
  "EMAIL" VARCHAR2(40) NOT NULL, "STATE" VARCHAR2(2));

CREATE TABLE "SDORDER" ("SDNUMBER" NUMBER(5, 0) NOT NULL, 
  "ORDERDATE" DATE, 
 "TOTAL_PRICE" NUMBER(6, 2) NOT NULL,
 "KAGI_PERCENT" NUMBER(6, 2),
 "COST" NUMBER(6, 2),
 "PROFIT" NUMBER(6, 2),  
 "COMMENTS" VARCHAR2(250),  
 "CUSTOMER_ID" NUMBER(5, 0) NOT NULL);

ALTER TABLE "SDORDER" ADD  CONSTRAINT "SDNUMBER_PK-T" PRIMARY KEY ("SDNUMBER");

CREATE TABLE "ORDER_PROD" ("SDNUMBER" NUMBER(5, 0) NOT NULL, 
 "NAME" VARCHAR2(15) NOT NULL, 
 "QUANTITY" NUMBER(2, 0) NOT NULL);

ALTER TABLE "ORDER_PROD" ADD  CONSTRAINT "SDNUMBER_NAME_PK" PRIMARY KEY ("SDNUMBER", "NAME"); 


CREATE TABLE "PRODUCT" ("NAME" VARCHAR2(15) NOT NULL, 
 "DESCRIPTION" VARCHAR2(100) NOT NULL, 
 "PRICE" NUMBER(5, 2) NOT NULL, 
 "WEIGHT" NUMBER(4, 2) NOT NULL); 

CREATE TABLE "KAGI_NO" ("KAGI_NO" NUMBER(5, 0) NOT NULL, "SDNUMBER" NUMBER(5, 0) NOT NULL);


insert into customer
(CUSTOMER_ID, FIRSTNAME, LASTNAME, ADDRESS, COUNTRY, TELEPHONE, EMAIL, STATE)
values(1, 'Jim', 'Twost', '123 My Street, London 98E-f34', 'UK', '+11-3-876-3346644', 'jim@tallmen.com', '');
insert into customer
(CUSTOMER_ID, FIRSTNAME, LASTNAME, ADDRESS, COUNTRY, TELEPHONE, EMAIL, STATE)
values(2, 'Steve', 'J', '456 Right Here, San Fran CA 12345', 'USA', '(383) 634-3423', 'steve@apple.com', 'CA');
insert into customer
(CUSTOMER_ID, FIRSTNAME, LASTNAME, ADDRESS, COUNTRY, TELEPHONE, EMAIL, STATE)
values(3, 'Ping', 'Traceroute', 'Als Fish and Chips, Liverpool 9F3-IR2 UK', 'UK', '+11 324 234 232', 'ping@ucl.ac.uk', '');
insert into customer
(CUSTOMER_ID, FIRSTNAME, LASTNAME, ADDRESS, COUNTRY, TELEPHONE, EMAIL, STATE)
values(4, 'Fred ', 'Bush', '1313 Rightwing St. Houston TX 46662', 'USA', '(667) 5476 345', 'fred@saveyoursoul.org', 'TX');
insert into customer
(CUSTOMER_ID, FIRSTNAME, LASTNAME, ADDRESS, COUNTRY, TELEPHONE, EMAIL, STATE)
values(5, 'Bobbi', 'Sekis', 'Whoknowswhere', 'Mongolia', '1312345704958458', 'bunny@hotmail.com', '');
insert into customer
(CUSTOMER_ID, FIRSTNAME, LASTNAME, ADDRESS, COUNTRY, TELEPHONE, EMAIL, STATE)
values(6, 'Benjamin', 'Dover', '745 Siesta Blvd, Mexico City', 'Mexico', '123456789', 'bend@msn.com', '');

insert into KAGI_NO (KAGI_NO, SDNUMBER)
values(1, 1);
insert into KAGI_NO (KAGI_NO, SDNUMBER)
values(2, 2);
insert into KAGI_NO (KAGI_NO, SDNUMBER)
values(3, 3);
insert into KAGI_NO (KAGI_NO, SDNUMBER)
values(4, 4);
insert into KAGI_NO (KAGI_NO, SDNUMBER)
values(5, 6);

insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(1, 'mediumhookah', 1);
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(1, 'regapple', 2);
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(2, 'regapple', 5);
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(3, 'largehookah', 1 );
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(4, 'premapple', 5 );
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(5, 'largehookah', 1 );
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(5, 'lightrose', 1 );
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(6, 'regmint', 3 );
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(7, 'largehookah', 2 );
insert into ORDER_PROD (SDNUMBER, NAME, QUANTITY)
values(7, 'regorange', 10);

insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('smallhookah', 'Small Hookah', 105, 1.5);
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('mediumhookah', 'Medium Hookah', 135, 3);
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('largehookah', 'Large Hookah ', 165, 6 );
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('regapple', 'Regular Shisha: Apple', 14, .25 );
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('regstrawb', 'Regular Shisha: Strawberry', 14, .25 );
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('regmint', 'Regular Shisha: Mint', 14, .25 );
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('regorange', 'Regular Shisha: Orange', 17, .25 );
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('premapple', 'Premium Shisah: Apple', 18, .25);
insert into product (NAME, DESCRIPTION, PRICE, WEIGHT)
values('lightrose', 'Light Shisha: Rose', 29, .5);

insert into sdorder (SDNUMBER, ORDERDATE, TOTAL_PRICE, Kagi_Percent, cost, profit, comments, customer_id)
values(1, '03-MAR-00', '163', '12.2', '140.8', '10', 'First order.', 1);
insert into sdorder (SDNUMBER, ORDERDATE, TOTAL_PRICE, Kagi_Percent, cost, profit, comments, customer_id)
values(2, '15-MAR-00', '70', '7', '38', '25', '', 2 );
insert into sdorder (SDNUMBER, ORDERDATE, TOTAL_PRICE, Kagi_Percent, cost, profit, comments, customer_id)
values(3, '07-JUL-00', '165', '10', '103', '52', '', 3);
insert into sdorder (SDNUMBER, ORDERDATE, TOTAL_PRICE, Kagi_Percent, cost, profit, comments, customer_id)
values(4, '31-JUL-00', '90', '9', '38', '43', 'Nice guy--repeat order.', 2);
insert into sdorder (SDNUMBER, ORDERDATE, TOTAL_PRICE, Kagi_Percent, cost, profit, comments, customer_id)
values(5, '06-AUG-00', '194', '', '150', '44', 'Kind of a nut.', 4);
insert into sdorder (SDNUMBER, ORDERDATE, TOTAL_PRICE, Kagi_Percent, cost, profit, comments, customer_id)
values(6, '15-SEP-00', '42', '6.2', '41', '-5.2', 'Ouch.Expenisve to ship to Mongolia', 5);
insert into sdorder (SDNUMBER, ORDERDATE, TOTAL_PRICE, Kagi_Percent, cost, profit, comments, customer_id)
values(7, '01-DEC-00', '510', '', '315', '195', 'Probably a drug dealer.', 6);

ALTER TABLE "CUSTOMER" ADD  CONSTRAINT "CUSTOMER_ID_PK" PRIMARY KEY (Customer_ID);
ALTER TABLE "PRODUCT" ADD  CONSTRAINT "NAME_PK" PRIMARY KEY (Name);
ALTER TABLE "KAGI_NO" ADD  CONSTRAINT "KAGI_NO_PK" PRIMARY KEY (Kagi_No); 
ALTER TABLE "SDORDER" ADD CONSTRAINT "CUSTOMER_ID_PK-T" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMER";
ALTER TABLE "ORDER_PROD" ADD CONSTRAINT "SDNUMBER_FK" FOREIGN KEY ("SDNUMBER") REFERENCES "SDORDER";
ALTER TABLE "ORDER_PROD" ADD CONSTRAINT "NAME_FK" FOREIGN KEY ("NAME") REFERENCES "PRODUCT";
ALTER TABLE "KAGI_NO" ADD CONSTRAINT "SDNUMBER_K_PK" FOREIGN KEY ("SDNUMBER") REFERENCES "SDORDER";
