Question

1. Create a view named customer_addresses that shows the shipping and billing addresses for each customer.

This view should return these columns from the Customers table: customer_id, email_address, last_name and first_name.

This view should return these columns from the Addresses table: bill_line1, bill_line2, bill_city, bill_state, bill_zip, ship_line1, ship_line2, ship_city, ship_state, and ship_zip.

The rows in this view should be sorted by the last_name and then first_name columns.

2. Write a SELECT statement that returns these columns from the customer_addresses view that you created in exercise 1: customer_id, last_name, first_name, bill_line1.

3. Create a view named order_item_products that returns columns from the Orders, Order_Items, and Products tables.

This view should return these columns from the Orders table: order_id, order_date, tax_amount, and ship_date.

This view should return these columns from the Order_Items table: item_price, discount_amount, final_price (the discount amount subtracted from the item price), quantity, and item_total (the calculated total for the item).

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

4. Create a view named product_summary that uses the view you created in exercise 4. This view should return summary information about each product.

Each row should include product_name, order_count (the number of times the product has been ordered) and order_total (the total sales for the product).

5. Write a SELECT statement that uses the view that you created in exercise 5 to get total sales for the five best selling products.

Answer 1

Answer given below

Step-by-step explanation:

1.

CREATE VIEW CustomerAddresses AS

SELECT custo. CustomerID, EmailAddress , LastName ,FirstName,

bill.Line1 AS BillLine1, bill.Line2 AS BillLine2, bill.City AS BillCity, bill.State AS BillState, bill.ZipCode AS BillZip,

ship.Line1 AS ShipLine1, ship.Line2 AS ShipLine2, ship.City AS ShipCity, ship.State AS ShipState, ship.ZipCode AS ShipZip

FROM Customers custo , Addresses ship , Addresses bill

WHERE custo. BillingAddressID= bill.AddressID AND custo.ShippingAddressID= ship. AddressID;

2.

SELECT CustomerID, LastName, FirstName, BillLine1 FROM CustomerAddresses;

3.

CREATE VIEW OrderItemProducts

AS

SELECT Orders.OrderID, OrderDate, TaxAmount, ShipDate,

ItemPrice, DiscountAmount, (ItemPrice- DiscountAmount) AS FinalPrice,

Quantity, and (Quantity * (ItemPrice-DiscountAmount)) AS ItemTotal,

ProductName FROM

Orders, OrderItems, Products

WHERE

Orders.OrderID = OrderItems.OrderID AND

OrderItems.ProductID = Products. ProductID;

4.

CREATE VIEW ProductSummary

AS

SELECT distinct

ProductName, COUNT(OrderID) AS OrderCount, SUM(ItemTotal) AS OrderTotal

FROM

OrderItemProducts

GROUP BY ProductName;

5.

SELECT ProductName, OrderTotal

FROM ProductSummary P

WHERE 5> (select count(*) FROM ProductSummary S

WHERE P.OrderTotal<S.OrderTotal)

ORDER BY OrderTotal;