INF 282 Dewm Quiz 1

CREATE TABLE stephensow1_Customer(Customer_ID  CHAR(2) PRIMARY KEY,
  Address_Line1 VARCHAR2(128),
  Address_Line2 VARCHAR2(128),
  Address_Line3 VARCHAR2(128));

CREATE TABLE stephensow1_Order(Order_ID CHAR(3) PRIMARY KEY,
  Customer_ID CHAR(2),
  Date_Ordered DATE);

ALTER TABLE stephensow1_Order ADD CONSTRAINT stephensow1_fk1
    FOREIGN KEY (Customer_ID) REFERENCES stephensow1_Customer(Customer_ID)
    INITIALLY DEFERRED DEFERRABLE;

CREATE TABLE stephensow1_Item(Item_ID CHAR(3) PRIMARY KEY,
  Description VARCHAR2(128),
  Unit_Price NUMBER(10,2));

CREATE TABLE stephensow1_Purchase(Purchase_ID CHAR(6) PRIMARY KEY,
  Order_ID CHAR(3),
  Item_ID CHAR(3),
  Quantity NUMBER(4));

ALTER TABLE stephensow1_Purchase ADD CONSTRAINT stephensow1_fk2
    FOREIGN KEY (Order_ID) REFERENCES stephensow1_Order(Order_ID)
    INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE stephensow1_Purchase ADD CONSTRAINT stephensow1_fk3
    FOREIGN KEY (Item_ID) REFERENCES stephensow1_Item(Item_ID)
    INITIALLY DEFERRED DEFERRABLE;

INSERT INTO stephensow1_Customer VALUES ('22', 'Tom''s Hardware', '532 Blue Road', 'Cincinnati, OH');
INSERT INTO stephensow1_Customer VALUES ('50', 'Anandtech', '2642 Biltmore Ave', 'Tallahassee, FL');
INSERT INTO stephensow1_Customer VALUES ('81', 'Sharon''s Thrift Shop', '5122 Alvin Heights', 'Austin, TX');
INSERT INTO stephensow1_Customer VALUES ('33', 'Widget Resellers, Inc.', '743 Evergreen Terrace', 'Springfield, MO');

INSERT INTO stephensow1_Item VALUES ('555', 'Green Widgets', 15.0);
INSERT INTO stephensow1_Item VALUES ('666', 'Red Widgets', 33.0);
INSERT INTO stephensow1_Item VALUES ('777', 'Pink Widgets', 45.0);
INSERT INTO stephensow1_Item VALUES ('888', 'Yellow Widgets', 23.0);

INSERT INTO stephensow1_Order VALUES ('100', '22', '03-MAY-2011');
INSERT INTO stephensow1_Order VALUES ('104', '50', '03-JUN-2011');
INSERT INTO stephensow1_Order VALUES ('33', '81', '10-AUG-2010');
INSERT INTO stephensow1_Order VALUES ('107', '33', '15-JUN-2011');
INSERT INTO stephensow1_Order VALUES ('39', '22', '08-MAY-2011');
INSERT INTO stephensow1_Order VALUES ('103', '22', '03-MAY-2011');

INSERT INTO stephensow1_Purchase VALUES ('100555', '100', '555', 105);
INSERT INTO stephensow1_Purchase VALUES ('100666', '100', '666', 27);
INSERT INTO stephensow1_Purchase VALUES ('100777', '100', '777', 33);

INSERT INTO stephensow1_Purchase VALUES ('104555', '104', '555', 15);
INSERT INTO stephensow1_Purchase VALUES ('104666', '104', '666', 88);
INSERT INTO stephensow1_Purchase VALUES ('104777', '104', '777', 33);
INSERT INTO stephensow1_Purchase VALUES ('104888', '104', '888', 19);

INSERT INTO stephensow1_Purchase VALUES ('033666', '33', '666', 33);
INSERT INTO stephensow1_Purchase VALUES ('033777', '33', '777', 60);

INSERT INTO stephensow1_Purchase VALUES ('107555', '107', '555', 90);
INSERT INTO stephensow1_Purchase VALUES ('107666', '107', '666', 14);
INSERT INTO stephensow1_Purchase VALUES ('107777', '107', '777', 29);

INSERT INTO stephensow1_Purchase VALUES ('039555', '39', '555', 52);
INSERT INTO stephensow1_Purchase VALUES ('039666', '39', '666', 28);

INSERT INTO stephensow1_Purchase VALUES ('103555', '103', '555', 101);
INSERT INTO stephensow1_Purchase VALUES ('103666', '103', '666', 26);
INSERT INTO stephensow1_Purchase VALUES ('103777', '103', '777', 39);