For each screenshot, I need the SQL code and results. Create an updatable view n
ID: 3827396 • Letter: F
Question
For each screenshot, I need the SQL code and results. Create an updatable view named customer addresses that shows the shipping and billing addresses for each customer. (I need a screenshot of the SQL code and View created.) 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, and bill zip. (Alternate Extra Credit - This view should return these columns from the Addresses table: bill line 1, 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. 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 line 1. (Screenshot) Write an UPDATE statement that updates the Customers table using the customer addresses view you created in exercise 1. Set the first line of the billing address to "1990 Westwood Blvd." for the customer with an ID of 8. (I need a screenshot of the SQL code and View created.) Create a view named order item products that returns columns from the Orders, Order items. and Products tables. (I need a screenshot of the SQL code and View created.) 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. Create a view named product summary that uses the view you created in exercise. This view should return summary information about each product. (I need a screenshot of the SQL code and View created.) 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). Write a SELECT statement that uses the view that you created in exercise 5 to get total sales for the five best selling products. (Screenshot)Explanation / Answer
1) CREATE VIEW CustomerAddresses as
SELECT c.CustomerID, c.EmailAddress, c.LastName, c.FirstName, sa.Line1 AS ShipLine1, sa.Line2 AS ShipLine2, sa.City AS ShipCity, sa.State AS ShipState, sa.Zipcode AS ShipZip,
ba.Line1 AS BillLine1, ba.Line2 AS BillLine2, ba.City AS BillCity, ba.State AS BillState, ba.Zipcode AS BillZip
FROM Customers as c
LEFT JOIN Addresses as ba
on ba.AddresessID=c.BillingAddressID
LEFT JOIN Addresses as sa
on sa.AddressID=c.ShippingAddressID
2)
3) UPDATE CustomerAddresses
SET ShipLine1='1990 WestWood blvd' WHERE CustomerID = 8.
4) CREATE VIEW OrderItemProducts AS
SELECT o.OrderID, o.OrderDate, o.TaxAmount, o.ShipDate,oi.ItemPrice, oi.DiscountAmount, (oi.ItemPrice-oi.DiscountAmount) AS FinalPrice,
oi.Quantity, and (oi.Quantity*(oi.ItemPrice-oi.DiscountAmount)) AS ItemTotal, p.ProduceName FRom
Orders o, OrderItems oi, Products p
WHERE
o.OrderID=oi.OrderID AND
oi.ProductName=p.ProductName;
5) CREATE VIEW ProductSummary
AS
SELECT distinct
ProductName, COUNT(ProductName) over (partition by ProductName) AS
OrderCount, SUM(itemTotal) over (partition by ProductName) AS OrderTotal
FROM OrderItemProducts;
5) SELECT *FROM(SELECT *from ProductSummary ORDER BY OrderTotal DESC) suppliers
WHERE roenum <=5
ORDER BY rownum;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.