-- Change to your lab folder
-- use cs474lab;

-- Clears any old data
DROP TABLE IF EXISTS OrderItem;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS Customer;

-- Creates the tables
CREATE TABLE Customer
(cID CHAR(5) PRIMARY KEY, 
name VARCHAR(50), 
address VARCHAR(255), 
totalOrders MONEY);

CREATE TABLE Product
(pid CHAR(7) PRIMARY KEY, 
description VARCHAR(50),
orderThreshold INTEGER(5) DEFAULT 150, 
quantityOH INTEGER (5), 
price MONEY);

CREATE TABLE Orders 
(onum INTEGER(10) PRIMARY KEY, 
oDate DATE,
customer CHAR(5), 
orderTotal MONEY,
shipDate DATE);

CREATE TABLE OrderItem
(onum INTEGER (10),
seq INTEGER (3),
product CHAR(7),
quantity DOUBLE,
PRIMARY KEY(onum, seq));

-- Add in some data
INSERT INTO Customer VALUES(1111, "Phil's Plumbing", '123 Anywhere Lane', 0);
INSERT INTO Customer VALUES(1112, "Green Grocer", '52 Smithland Rd', 0);
INSERT INTO Customer VALUES(1113, "Zenith Vision", '472 S. Main', 0);
INSERT INTO Customer VALUES(1114, "K-mart", '567 E. Market', 0);

INSERT INTO Product VALUES("P23-22", "Wingnuts", 150, 1095, .50);

INSERT INTO Product VALUES("P16-44", "Copper tubing", 150, 1095, .50);
INSERT INTO Product VALUES("P34-22", "Hershey bars", 150, 1400, 2.00);
INSERT INTO Product VALUES("P54-420", "Hex bolt 1/4-1820", 200, 159, 1.00);
INSERT INTO Product VALUES("P54-516", "Hex bolt 5/16-18", 250, 275, .65);
INSERT INTO Product VALUES("P65-122", "8D Common nail", 1000, 1500, .25);

INSERT INTO Orders VALUES(123, '2010-03-31', 1111, 25.00, NULL);
INSERT INTO Orders VALUES(091, '2010-02-23', 1112, 106.25, '2010-2-24');
INSERT INTO Orders VALUES(100, '2010-02-28', 1111, 124.00, '2010-03-02');

INSERT INTO OrderItem VALUES(123, 01, 'P23-22', 50);
INSERT INTO OrderItem VALUES(91, 01, 'P16-44', 25.25);
INSERT INTO OrderItem VALUES(91, 02, 'P65-122', 225);
INSERT INTO OrderItem VALUES(100, 01, 'P54-420', 100);
INSERT INTO OrderItem VALUES(100, 02, 'P54-516', 100);