Create a view named CustomerAddresses that shows the shipping and billing addres

Question

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;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.