Use Master
Go
--drop database if it exists
--and recreate it
If exists
	(Select name from sys.Databases 
	  Where name = 'PetesPizza')
Begin
Drop database PetesPizza
end
Create Database PetesPizza
Go
Use PetesPizza
Go
--create tables
Create table Customer
(
  CustomerKey int identity(1,1) primary key,
  CustomerLastName nvarchar(50) not null,
  CustomerFirstName nvarchar(50) null,
  CustomerAddress nvarchar(100) not null,
  CustomerZip nchar(9) not null,
  CustomerPhone nchar(10) not null,
  
)

create table Product
(
  Productkey int identity(1,1) primary key,
  ProductName nvarchar(200) not null,
  ProductUnitSize nvarchar(50),
  ProductPricePerUnit money not null
)

create table Employee
(
   EmployeeKey int identity(1,1) primary key,
   EmployeeLastName nvarchar(50) not null,
   EmployeeFirstName nvarchar(50)
)

Create table PizzaSale
(
  PizzaSaleKey int identity(1,1) primary key,
  PizzaSaleDate Datetime not null,
  EmployeeKey int Foreign Key references Employee(EmployeeKey),
  CustomerKey int Foreign Key references Customer(CustomerKey),
)

Create table SaleDetail
(
  SaleDetailKey int identity(1,1) primary Key,
  PizzaSaleKey int foreign key references PizzaSale(PizzaSaleKey),
  ProductKey int foreign key references Product(productKey),
  Quantity int not null,
  UnitChargedPrice money not null
)

--inserts into Customer
Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Falconer', 'Terry','12001 Bradley Street','2065551234','98100') --1

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Lamont', 'Lisa','400 South Blvd','2065553245','98100')--2

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Brown', 'Neville','200 West street','2065550923','98101')--3

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Taylor', 'Lenei','300 Side Avenue','2065550981','98100')--4

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Abner', 'Tessa','1300 12th Avenue','2065558053','98101')--5

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Hardy', 'Debbie','980 Tandy way','2065552348','98102')--6

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Sanders', 'Paul','840 South 8th','2065550091','98101')--7

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Patterson', 'Sherry','200 Waterway drive','2065557620','98102')--8

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Lewis', 'Ron','11 south way','2065552310','98102')--8
--
Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Tam', 'li','30 Seltice Drive','2065550912','98102')--9

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Land', 'Leonore','130 Providence Drive','2065551101','98101')--10

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Storm', 'Ned','101 Black Stone','2065550120','98100')--11

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Pasteur', 'Roger','200 Third Street apt 201','206555111','98102')--12

Insert into Customer(CustomerLastName,CustomerFirstName,CustomerAddress,CustomerPhone,CustomerZip)
Values('Burton', 'Tom','111 Northgate way','2065559834','98101')--13


--inserts for Product
Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Bread Sticks', '6 sticks', 2.98);--1

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Soda', '2 Liters', 3.50);--2

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Small Cheese', '10 inches', 6.25);--3

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Small Pepperoni', '10 inches', 7.30);--4

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Medium Cheese', '14 inches', 12.35);--5

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Medium Pepperoni', '14 inches', 13.20);--6

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Large Cheese', '16 inches', 15.50);--7

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Large Pepperoni', '16 inches', 16.10);--8

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Medium Three topping special', '14 inches', 15.50);--9

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('Large three topping special', '16 inches', 17.00)--10

Insert into Product(ProductName,ProductUnitSize, ProductPricePerUnit)
Values('single topping', '1 cup', 1.00);--11


Insert into Employee(EmployeeLastName, EmployeeFirstName)
Values('khai', 'Tracy')

Insert into Employee(EmployeeLastName, EmployeeFirstName)
Values('Tanner', 'Bill')

Insert into Employee(EmployeeLastName, EmployeeFirstName)
Values('Sloan', 'Pam')

Insert into Employee(EmployeeLastName, EmployeeFirstName)
Values('Turner', 'Sue')

Insert into Employee(EmployeeLastName, EmployeeFirstName)
Values('Anderson', 'Mark')

--inserts into sales and saledetails
Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/10/2009',1,1)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(1,1,1,2.98)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(1,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(1,9,1,17.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/10/2009',2,2)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(1,2,1,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(2,5,2,12.35)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/10/2009',3,3)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(3,1,1,2.98)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(3,8,3,13.50)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/10/2009',5,4)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(4,1,1,2.98)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(4,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(4,9,1,17.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/10/2009',1,5)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(5,1,1,2.98)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(5,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(5,5,1,17.00)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(5,11,4,1.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/11/2009',5,6)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(6,9,1,17.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/11/2009',3,7)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(7,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(7,9,1,17.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/11/2009',2,8)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(8,4,1,7.30)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/12/2009',3,9)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(9,1,1,2.98)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(9,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(9,9,1,17.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/12/2009',3,10)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(10,5,2,12.35)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/12/2009',1,11)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(11,6,2,10.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/12/2009',4,12)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(12,5,1,10.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/13/2009',3,13)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(9,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(13,6,3,10.00)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/14/2009',4,2)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(14,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(14,6,1,13.20)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/14/2009',3,7)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(15,2,1,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(15,9,1,17.50)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/14/2009',3,9)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(16,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(16,7,1,15.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(16,11,4,1)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/15/2009',1,3)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(17,7,1,15.50)

Insert into PizzaSale( PizzaSaleDate,EmployeeKey,CustomerKey)
values('4/15/2009',3,1)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(18,2,2,3.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(18,7,1,15.50)
insert into SaleDetail(PizzaSaleKey,ProductKey,quantity,UnitChargedPrice)
Values(18,11,4,1)