create table Manufacturer
(
	ManufName varchar(16) Primary Key
)

	Create table Category
(
   CategoryName varchar(20) Primary Key
   )

   create table Product
(
    ProductID int Primary Key Identity,
	 PMfgPartNo int,
	  PDescription varchar(25),
	   PListPice decimal(19,2),
	   MAnufName varchar(16) Foreign Key references Manufacturer(ManufName),
	   CategoryName varchar(20) Foreign Key references Category(CategoryName)
	   )

create table customer
 ( CustomerID int Primary Key Identity,
 CFName varchar(25) not null,
  CLName varchar (25) not null,
  CPhone int not null,
 CEmail varchar (18) not null,
  CAddress varchar (40),
   CCity varchar (15) not null,
 CState varchar (25) not null,
 CZip int not null
 )

 	Create table Employee
 (
    EmployeeID int not null Primary Key Identity,
	EFName varchar (18) not null,
    ELName varchar (18) not null,
	EPhone int,
	EEmail varchar (16) not null,
	EAddress varchar (14) not null,
	ECity varchar (30),
	EState varchar (17), EZip int not null,
 )

	create table Sale
(
   SaleId int Primary Key Identity,
   SaleDate date not null,
   EstReceiveDate date not null,
   SubTotal decimal(19,2) not null,
   Tax decimal(19,2) null,
   Discount decimal(19,2) not null,
    Total decimal(19,2) not null,
   EmployeeID int Foreign Key references Employee(EmployeeID),
   CustomerID int Foreign Key references Customer(CustomerID)
    )

	Create table Boat
 (
    BoatID int not null Primary Key Identity,
	BBrand varchar(20) not null,
	BYear int not null,
	BNumEngines int not null,
	BLenght int,
	BPrice decimal(19,2) not null,
	BExtended decimal(19,2) not null,
	ManufName varchar (16) Foreign Key references Manufacturer(ManufName),
	CustomerID int foreign key references Customer(CustomerID)
)

	Create Table Engine
(
   EngineId int Primary Key Identity,
   Ebrand varchar (10) not null,
    Eyear int not null,
	 EOutDrive varchar(14),
	  Eprice decimal(19,2) not null,
   EExtended decimal(19,2),
   ManufName varchar (16) Foreign Key references Manufacturer(ManufName),
   BoatID int foreign key references Boat(BoatID)
   )

  	Create table SaleItem
(
    SaleItemID int Primary Key Identity,
	SIQuantity int not null,
	SIExtended decimal(19,2),
	ProductID int Foreign Key references Product(ProductID),
	SaleID int foreign key references Sale(SaleID)
)


Insert into Manufacturer
Values ('Bombardier');

Insert into Manufacturer
Values ('Kawasaki');

Insert into Manufacturer
Values ('Z1 Boats');

Insert into Manufacturer
Values ('Square');

Insert into Manufacturer
values ('Honda');

Insert into Manufacturer
Values ('Corsair');

Insert into Manufacturer
Values ('Boston');

Insert into Manufacturer
Values ('Company');


Insert into Manufacturer
Values ('Malibu');

Insert into Manufacturer
Values ('MasterCraft');



Select *
from Manufacturer

Insert into category
values ('Steering Wheel');

Insert into category
values ('Oil Pump');

Insert into category
values ('Gasket');

Insert into category
values ('Propeller');

Insert into category
values ('Anchor');

Insert into category
values ('Windshield Wiper');

Insert into category
values ('Windshield');

Insert into category
values ('Speed Limiter');

Insert into category
values ('Battery');

Insert into category
values ('Spark Plug');


select *
from Category

insert into Product
values (2,'Good',500.00,'Honda', 'Steering Wheel');

Insert into Product
values (3,'Okay', 400.00, 'Kawasaki','Propeller')

Insert into Product
Values ( 5, 'Bad', 300.00, 'Bombardier', 'Spark Plug');

Insert Into Product
values ( 6, 'Good', 200.00, 'Honda', 'Steering Wheel');

Insert into Product
Values ( 7, 'Good', 100.00,'Z1 Boats','Battery');

Insert into Product
values ( 8, 'Good', 50.00, 'Square', 'Speed Limiter');

Insert into Product
values ( 9, 'Fair', 25.00,'Boston', 'Gasket');

Insert into Product
values ( 4, 'Good', 24.00,'Honda', 'Battery');

Insert into Product
values ( 1, 'Fair', 400.00,'Honda', 'Steering Wheel');

Insert into Product
values ( 10, 'Good', 100.00,'Kawasaki', 'Propeller');

Insert into Product
values ( 13, 'Used', 3500.00,'Kawasaki', 'Gasket');

Insert into Product
values ( 14, 'Good', 220.00,'Kawasaki', 'Steering Wheel');

Insert into Product
values ( 15, 'Okay', 1000.00,'Kawasaki', 'Propeller');

Insert into Product
values ( 16, 'New', 7500.00,'Kawasaki', 'Spark Plug');

Insert into Product
values ( 18, 'Fair', 29.00,'Kawasaki', 'Battery');

select *
from product

 set IDENTITY_INSERT customer on

Insert into Customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
values (1, 'William', 'Clifton',8010098, 'eclifton@uvu.edu','1468 N' ,'Orem', 'UT', 84058);

 insert into customer (CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
 values (2, 'Anna', 'Soon', 8014567, 'asoon@yahoocom', '123 Cross', 'Orem', 'UT', 84058);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
 values (3, 'Riley', 'Hall', 8016167, 'rhall@yahoo.com', '616 Lane', 'Provo', 'UT',85064);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
 values (4, 'Vhan', 'Rid', 8016547, 'vrid@gmail.com', '123 Dane Street', 'American Fork','UT', 84003);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
  values (5, 'Kein', 'Tsung', 4358789,  'ktsung@gmail.com', '32 Jade Lane', 'Abraham', 'UT', 84635);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
  values (6, 'Jolly', 'Moore', 4355878, 'jmoore@aol.com', '5009 Duck Ave', 'Arches', 'UT', 84532);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
 values (7, 'Keith', 'Lim', 8011236, 'klim@yahoo.com', '5 Luke street', 'Saratoga', 'UT', 84092);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
 values (8, 'Jim', 'Myer', 4351245, 'jmyer@yahoo.com', '29 Winter Street', 'Bountiful', 'UT', 84010);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
  values (9, 'Ryan', 'Johnson', 8013546, 'rjohnson@yahoo.com', '8001 Lame Street', 'BryceCanyon','UT', 84764);

 insert into customer(CustomerID, CFName,CLName,CPhone,CEmail, CAddress, CCity, CState,CZip)
 values (10, 'Jade', 'Rodrick', 4357895, 'jrodrick@yahoo.com', '432 SummerStreet', 'Brookside', 'UT', 84782);


 set IDENTITY_INSERT customer off



 insert into employee
values ('Chloe', 'Emily', 4351236, 'cemily@yahoo.com','44 Beaver Ln', 'Millard', 'UT', 84007);

insert into employee
values ('Lucas', 'Noah', 8014351, 'lnoah@yahoo.com', '1 Wassach Ave','Garfield', 'UT', 84006);

insert into employee
values ('Jean', 'Lau', 8014235, 'jlau@yahoo.com','2089 Piute Dr', 'Emery', 'UT', 89067);

insert into employee
values ('Zoe', 'Mason', 8014235, 'zmason@yahoo.com','90 Sev St', 'Emery', 'UT', 89067);

insert into employee
values ('Madelyn', 'Liam', 8011245, 'mliam@yahoo.com','908 Springs Dr', 'Emery', 'UT', 89067);

insert into employee
values ('Arianna', 'Johnson', 8014256, 'ajohns@yahoo.com','3321 Box St', 'Emery', 'UT', 89067);

insert into employee
values ('Scarlett', 'Brooklyn', 8014235, 'sbrook@yahoo.com','999 Morgan Ave', 'Summit', 'UT', 89067);

insert into employee
values ('Keira', 'Viole', 8014235, 'kviol@yahoo.com','7652 Cache Rd', 'Wayne', 'UT', 89067);

insert into employee
values ('Johnson', 'Adele', 8014335, 'aj1876@yahoo.com','960 StJohns Pl', 'Summit', 'UT', 89067);

insert into employee
values ('Crane', 'Martin', 8015235, 'martrt@yahoo.com','190 ElliotBay', 'Wayne', 'UT', 89067);



	insert into Boat
	values ('Isuzu',1999, 2, 22, 20000.00, 19000.00 , 'Square',1 );

	insert into Boat
	values ('Kelvin', 2000, 1, 15, 13000.00, 120000.00, 'Square', 2);

	insert into boat
	values ('Mercury', 2001, 1, 19, 12000.00, 11000.00,'Honda', 3);

	insert into boat
	values ('Yarow', 2011, 2, 20, 81000.00, 60000.00, 'Z1 boats', 4);

	insert into boat
	values ('Kawasaki', 2016, 1, 20, 81000.00, 60000.00, 'Kawasaki', 5);

	insert into boat
	values ('Kelvin', 2004, 1, 20, 35000.00, 30000.00, 'Z1 boats', 6);

	insert into boat
	values ('Yarow', 2016, 2, 20, 120000.00, 100000.00, 'Honda', 7);

	insert into boat
	values ('Yarow', 2007, 1, 20, 20000.00, 15000.00, 'Z1 boats', 8);

	insert into boat
	values ('Yarow', 2016, 1, 20, 90000.00, 80000.00, 'Square', 9);

	insert into boat
	values ('Mercury', 2003, 1, 20, 34000.00, 30000.00, 'Z1 boats', 10);


	select * from boat

	insert into Engine
	values ('Honda', 2000,'outdrive',7000.00,6900.00, 'Honda', 1);

	insert into Engine
	values ('Honda', 2003,'non outdrive',10000.00,9600.00, 'Honda', 1);

	insert into Engine
	values ('Honda', 2010,'outdrive',8200.00,8000.00, 'Honda', 2);

	insert into Engine
	values ('Honda', 2005,'outdrive',3500.00,3200.00, 'Honda', 3);

	insert into Engine
	values ('Honda', 2012,'non outdrive',7900.00,7700.00, 'Honda', 4);

	insert into Engine
	values ('Honda', 2001,'outdrive',5400.00,5200.00, 'Honda', 4);

	insert into Engine
	values ('Honda', 2015,'non outdrive',9000.00,8700.00, 'Honda', 5);

	insert into Engine
	values ('Honda', 2001,'outdrive',7900.00, 7600.00, 'Honda', 6);

	insert into Engine
	values ('Honda', 2007,'outdrive',6000.00,5900.00, 'Honda', 7);

	insert into Engine
	values ('Honda', 2014,'non outdrive',5400.00,5000.00, 'Honda', 7);

	insert into Engine
	values ('Honda', 2000,'non outdrive',7000.00,6600.00, 'Honda', 8);

	insert into Engine
	values ('Honda', 2004,'outdrive',6000.00,5900.00, 'Honda', 9);

	insert into Engine
	values ('Honda', 2000,'outdrive',7600.00,7200.00, 'Honda', 10);


	select *
	from Employee

	insert into sale
	values ('2016-12-02', '2016-12-06', 500.00,.10,20.00,528.00, 6, 5);

	insert into sale
	values ('2016-01-12', '2016-01-25', 400.00,.10,10.00, 429.00, 10, 4);

	insert into sale
	values ('2016-01-16', '2016-01-25', 300.00,.10,10.00, 319.00, 10, 2);

	insert into sale
	values ('2016-02-04', '2016-02-09', 150.00,.10, 5.00 ,148.5, 8, 1);

insert into sale
values ('2016-12-01', '2016-12-05', 440.00,.10, 50.00 , 429.00 , 7, 3);

insert into sale
values ('2016-02-15', '2016-02-20', 3524,.10, 504.00 ,3050.20 , 4, 6);

insert into sale
values ('2016-02-19', '2016-02-23', 1025.00,.10, 55.00 , 1067, 6, 7);

insert into sale
values ('2016-03-03', '2016-03-07', 400.00,.1 ,20.00 , 14.00, 6, 8);

insert into sale
values ('2016-03-09', '2016-03-15', 29.00,.10, 4.00 , 27.5, 5, 9);

insert into sale
values ('2016-03-20', '2016-03-25', 7500.00,.10, 7000.00 ,7700.00 , 4, 10);

select * from  Sale
order b

	insert into SaleItem
	values(1, 480.00, 1,11)

	insert into SaleItem
	values(1, 390.00 , 2, 12)

	insert into SaleItem
	values(1, 290.00, 3, 13)

	insert into SaleItem
	values(1, 190.00,4,15)

	insert into SaleItem
	values(1, 45.00 , 5,14)

	insert into SaleItem
	values(1, 20.00, 6,17)

	insert into SaleItem
	values(1, 20.00 ,7,16)

	insert into SaleItem
	values(1, 380.00, 8,18)

	insert into SaleItem
	values(1, 80.00,9,15)

	insert into SaleItem
	values(1, 90.00,10,14)

	insert into SaleItem
	values(1, 3000.00,11 ,16)

	insert into SaleItem
	values(1, 200.00, 12,15)

	insert into SaleItem
	values(1, 950.00,13,17)

	insert into SaleItem
	values(1, 7000.00, 14,20)

	insert into SaleItem
	values(1, 25.00, 15,19)

	select CFName +' '+ CLName as 'Customers Who Bought Propellers in February'
	from Sale as S
		join customer as C on c.CustomerID = s.CustomerID
		join SaleItem as SI on SI.SaleID = S.SaleId
		join Product as P on P.ProductID = SI.ProductID

	where SaleDate like '2016-02%' and CategoryName = 'Propeller'

	select * from Sale
	order by SaleDate

	select CategoryName as 'Parts sold on February 20th'
	from Sale as S
		join SaleItem as SI on S.SaleId = SI.SaleID
		join Product as P on P.ProductID = SI.ProductID
	where SaleDate = '2016-02-15'

	select top 1 PListPice as Price, MAnufName + ' ' + CategoryName as 'Steering Wheel'
	from product
	where CategoryName = 'Steering Wheel'
	order by PListPice desc

	select count(SIQuantity) as 'Parts Sold', P.MAnufName as Manufacturer
	from Manufacturer as M
		join Product as P on M.ManufName = P.MAnufName
		join SaleItem as SI on SI.ProductID = P.ProductID
	group by P.ManufName
	order by 'Parts Sold' desc

	select * from customer
	select * from Product

	select count(CategoryName) as Parts, MAnufName
	from Product
	where CategoryName = 'Gasket'
	group by MAnufName
	order by Parts desc

	select e.EmployeeID, efname + ' ' + elname as Name, sum(total) as 'Total Sales'
	from Employee as E
		join Sale as S on E.EmployeeID = S.EmployeeID
		group by e.EmployeeID, EFName, ELName
		order by 'Total Sales' desc

	select SaleID, subtotal + (subtotal *.06) as 'Total Value With Tax'
	from Sale

	select * from SaleItem

	update SaleItem
 set SIQuantity= 2, SIExtended = 290.00, ProductID = 3, SaleID = 22
 where SaleItemID = 3

Insert into Product
values ( 16, 'Good', 100.00,'Honda', 'Steering Wheel');


	select ManufName, CategoryName
	from product

Select SIExtended
from SaleItem
Select Discount
from Sale

select CLName + ' '+ CFName as Person
from customer
union
select EFName + ' ' + ELName
from Employee



select * from Sale
select * from SaleItem

select SaleID, Total
from Sale
where Discount <
(select avg(Discount)
	from Sale
	)