Create The Tables

Use this script to create your small test tables

— Use the name of your database
Use BruceDBTest2;

— Invoice depends on foreign keys in the other customers and products
— Delete it first
IF OBJECT_ID(‘dbo.Invoice’,’U’) IS NOT NULL
DROP TABLE dbo.Invoice;

IF OBJECT_ID(‘dbo.SupplyOrders’, ‘U’) IS NOT NULL
DROP TABLE dbo.SupplyOrders;

IF OBJECT_ID(‘dbo.Customer’, ‘U’) IS NOT NULL
DROP TABLE dbo.Customer;

IF OBJECT_ID(‘dbo.Product’, ‘U’) IS NOT NULL
DROP TABLE dbo.Product;

Create Table Product
(
ProductId int not null primary key identity(1,1),
ProductName varchar(30) not null,
Cost Money not null,
QuantityOnHand int default(0),
Backordered int default(0),
);

Create Table customer
(
custId int not null primary key identity(1,1),
Name varchar(30) not null,
referredby int null
);

— There will be a trigger created on this table
Create Table Invoice
(
invoiceId int not null primary key identity(1,1),
custid int references customer (custId),
salesdate DateTime Not null,
ProductId int references Product (ProductId),
quan int not null
);

–Order here, when it comes in post amount recieved and
–update product table could use a trigger
–So there are 2 posts, one to order and one to recieve
–For now we assume at supply order doesn’t get backorded by the supplier
Create Table SupplyOrders
(
id int identity(1,1) primary Key,
Orderdate DateTime Not null,
RecievedDate DateTime null,
ProductId int references Product (ProductId),
OrderedQuan int not null,
Recieved int default 0
);

–Products
insert into product values
(‘Frog’,10.00,20,0),(‘Duck’,12.00, 10,0), (‘Pig’,15.00,5,0), (‘Monkey’,25.00, 0,0),(‘Rhino’,20.00,10,0)

–Customers
insert into customer
values
(‘John McCrackin’,null),(‘Cindy Lemons’,1),(‘Steven Lucky’,null),(‘Nancy Montage’,2),(‘Bob Randolph’,3)

–fill with data
Insert into invoice
values
(1,’12/1/2016′,1,2), (2,’12/1/2016′,2,1), (4,’12/2/2016′,2,2)
,(3,’12/2/2016′,1,1),(2,’12/4/2016′,2,1), (1,’12/5/2016′,1,3)
,(1,’12/5/2016′,1,1),(3,’12/6/2016′,1,2), (4,’12/6/2016′,4,8)