Assignment Directions: For this active learning lab:

1. Download and run the Lab7-1-Setup.sql script.

2. Create a View that will select all the stores with their orders by joining tables

3. Create a View that will select all the barista at all the different stores with their total sales by joining tables

4. Create a View that will select the customers favorite products (Most ordered) by joining tables and using the max function.

5. Create a View that will show the products who don't have sales think Outer Join.

Create table Customers

(

CustomerId int primary key identity(1,1),

FirstName nvarchar(50),

LastName nvarchar(50),

Phone char(11),

Email nvarchar(256)

)

Create table Stores

(

StoreId int primary key identity(1,1),

StoreName varchar(150),

Phone char(11),

Email nvarchar(256),

AddressId int,

Manager nvarchar(25)

)

create table Barista

(

BaristaId int primary key identity(1,1),

FirstName nvarchar(20),

LastName nvarchar(30),

ManagerId int,

)

Create table Products

(

ProductId int primary key identity(1,1),

ProductName nvarchar(50),

ProductDescription nvarchar(256),

CurrentPrice decimal(7,2),

UnitOfMeasure varchar(50)

)

Create table suppliers

(

SupplierId int primary key identity(1,1),

SupplierName varchar(150),

Email nvarchar(256),

Phone varchar(12),

StartDate datetime,

Active bit

)

Create table Orders

(

OrderId int primary key Identity(1,1),

StoreId int,

CustomerId int,

BaristaId int,

OrderDate dateTime,

PaymentType varchar(50),

PaymentAmount decimal(7,2),

Tax decimal(7,2),

Total decimal (7,2)

)

create table OrderProducts

(

OrderProductId int primary key identity(1,1),

OrderId int foreign key references Orders(OrderId),

ProductId int foreign key references Products(ProductId),

Price decimal(7,2),

Quantity int Check (Quantity > 0)

)

update orderproducts set price = (select CurrentPrice from products where products.productid = orderProducts.ProductId)

update orders set total = (select price * Quantity from OrderProducts where orders.OrderId = OrderProducts.OrderId)

update orders set tax = total * 0.02

update orders set PaymentAmount = total + tax