SQL Server$-$ Assignment $5$

Total Points $85.$

$($Questions $1-3$ are each worth $15$ points. Questions $4-5$ are each worth $20$ points

For this week$$s assignment, you will use the MyGuitarShop database $.$ Type up the scripts for

the following questions as a script file or a document and upload it to Moodle. For each

question, include the script, a comment with the count of rows returned and also include a

snippet of the result set. It could be a screenshot of the first five rows of the result set.

$1.$ Create a view named CustomerAddresses that shows the shipping and billing addresses for each

customer in the MyGuitarShop database.

This view should return these columns from the Customers table: CustomerID, EmailAddress,

LastName and FirstName.

This view should return these columns from the Addresses table: BillLine$1,$ BillLine$2,$ BillCity,

BillState, BillZip, ShipLine$1,$ ShipLine$2,$ ShipCity, ShipState, and ShipZip.

Use the BillingAddressID and ShippingAddressID columns in the Customers table to determine

which addresses are billing addresses and which are shipping addresses.

Hint: You can use two JOIN clauses to join the Addresses table to Customers table twice $($once for

each type of address$).$

$2.$ Write a SELECT statement that returns these columns from the CustomerAddresses view that you

created in exercise $1$: CustomerID, LastName, FirstName, BillLine$1.$

$3.$ Write an UPDATE statement that updates the CustomerAddresses view you created in exercise $1$ so it

sets the first line of the shipping address to $1990$ Westwood Blvd$.$ for the customer with an ID of $8.$

$4.$ Create a view named OrderItemProducts that returns columns from the Orders, OrderItems, and

Products tables.

This view should return these columns from the Orders table: OrderID, OrderDate, TaxAmount, and

ShipDate.

This view should return these columns from the OrderItems table: ItemPrice, DiscountAmount,

FinalPrice $($the discount amount subtracted from the item price$),$ Quantity, and ItemTotal $($the

calculated total for the item$).$

This view should return the ProductName column from the Products table.

$5.$ Create a view named ProductSummary that uses the view you created in exercise $4.$ This view should

return some summary information about each product.

Each row should include these columns: ProductName, OrderCount $($the number of times the product

has been ordered$),$ and OrderTotal $($the total sales for the product$).$

Write a SELECT statement that uses the view that you created to get total sales for the five best

selling products.