/* CS545 - MP 1 */
/* dump the tables if they already exist */

Drop Table Customers;
Drop Table Orders;
Drop Table StockItems; 
Drop Table Inventory;

/* create the customer table */

Create Table Customers (
  CustNo Integer Not Null Primary Key, 
  CustName Char (20), 
  Address Char (40)
);

/* create the orders table */

Create Table Orders (
  OrderNo Integer Not Null Primary Key, 
  CustNo Integer
);

/* create the stockitems table */

Create Table StockItems (
  StockNo Integer Not Null Primary Key, 
  SName Char (20), 
  SPrice Decimal(8,2)
);

/* create the inventory table */

Create Table Inventory (
  OrderNo Integer Not Null, 
  StockNo Integer Not Null, 
  Amount Integer, 
  Primary Key (OrderNo, StockNo)
);

/* modify format for the order date in the orders table */

Alter Table Orders
  Add OrderDate Date;

/* modify the format for price in the stock items table */ 

Alter Table StockItems
  Change SPrice Sprice Decimal(6,2);

/* modify the stock level format */

Alter Table StockItems
  Add Slevel Integer;

/* put some data into the customer table  */

Insert into Customers Values (1,'P. Jones','Leeds');
Insert into Customers Values (2,'A. Chan','Hong Kong');
Insert into Customers Values (3,'K. Green','Columbia');
Insert into Customers Values (4,'B. Smith','Leeds');
Insert into Customers Values (5,'A. Khan','');

/* put some data into the orders table  */

Insert into Orders Values (1,1,'24-JAN-96');
Insert into Orders Values (2,1,'31-JAN-96');
Insert into Orders Values (3,2,'04-FEB-96');
Insert into Orders Values (4,4,'12-FEB-96');

/* put some data into the stock items table */  

Insert into StockItems Values (1,'Bolts',.15,250);
Insert into StockItems Values (2,'Nuts',.75,6750);
Insert into StockItems Values (3,'Nails',.65,4562);
Insert into StockItems Values (4,'Spanners',4.76,135);
Insert into StockItems Values (5,'Screws',.11,9875);

/* put some data into the inventory table */

Insert into Inventory Values (1,1,55);
Insert into Inventory Values (1,3,124);
Insert into Inventory Values (2,1,24);
Insert into Inventory Values (2,2,35);
Insert into Inventory Values (2,3,12);
Insert into Inventory Values (2,4,125);
Insert into Inventory Values (2,5,33);
Insert into Inventory Values (3,1,45);
Insert into Inventory Values (3,2,12);
Insert into Inventory Values (3,3,234);
Insert into Inventory Values (3,4,13);
Insert into Inventory Values (3,5,145);
Insert into Inventory Values (4,4,32);
Insert into Inventory Values (4,5,125);

/* your turn */