Answer:
Answer given below
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;
