1. Create a view named CustomerAddresses that shows the shipping and billing add
ID: 3553610 • Letter: 1
Question
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: BillLine1,
BillLine2, BillCity, BillState, BillZip, ShipLine1, ShipLine2, ShipCity, ShipState,
and ShipZip.
2. Write a SELECT statement that returns these columns from the CustomerAddresses
view that you created in exercise 1: CustomerID, LastName, FirstName, BillLine1.
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
Explanation / Answer
1.)
CREATE VIEW CustomerAddresses AS
SELECT c.CustomerID, c.EmailAddress, c.LastName, c.FirstName,
a.BillLine1, a.BillLine2, a.BillCity, a.BillState, a.BillZip, a.ShipLine1, a.ShipLine2, a.ShipCity, a.ShipState, a.ShipZip
FROM Customers c, Addresses a
WHERE c.CustomerID = a.CustomerID;
2.)
SELECT CustomerID, LastName, FirstName, BillLine1 FROM CustomerAddresses;
3.)
UPDATE CustomerAddresses
SET ShipLine1 = '1990 Westwood Blvd' WHERE CustomerID = 8;
4.) Assuming that Orders table has primary key OrderID, and OrderItems table also has a OrderID column.
So that we can join two tables with this column.
AND, OrderItems table has a column ProductName and, Products has Primary key ProductName.
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.ProductName 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;
6.) SELECT *
FROM (select * from ProductSummary ORDER BY OrderTotal DESC) suppliers2
WHERE rownum <= 5
ORDER BY rownum;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.