delete from line_items;
delete from stock_info;
delete from purchase_order;
delete from customer_info;
CREATE TABLE customer_info (
custno NUMBER,
custname VARCHAR2(200),
street VARCHAR2(200),
city VARCHAR2(200),
state CHAR(2),
zip VARCHAR2(20),
phone1 VARCHAR2(20),
phone2 VARCHAR2(20),
phone3 VARCHAR2(20),
PRIMARY KEY (custno)
) ;
CREATE TABLE purchase_order (
pono NUMBER,
custno NUMBER REFERENCES customer_info,
orderdate DATE,
shiptodate DATE,
shiptostreet VARCHAR2(200),
shiptocity VARCHAR2(200),
shiptostate CHAR(2),
shiptozip VARCHAR2(20),
PRIMARY KEY (pono)
) ;
CREATE TABLE stock_info (
stockno NUMBER PRIMARY KEY,
cost NUMBER,
tax_code NUMBER
) ;
CREATE TABLE line_items (
lineitemno NUMBER,
pono NUMBER REFERENCES purchase_order,
stockno NUMBER REFERENCES stock_info,
quantity NUMBER,
discount NUMBER,
PRIMARY KEY (pono, lineitemno)
) ;
INSERT INTO customer_info
VALUES (1, 'Jean Nance', '2 Avocet Drive',
'Redwood Shores', 'CA', '95054',
'415-555-1212', NULL, NULL) ;
INSERT INTO customer_info
VALUES (2, 'John Nike', '323 College Drive',
'Edison', 'NJ', '08820',
'609-555-1212', '201-555-1212', NULL) ;
