BCS 360 : AssignmentTwo - Programming in SQL
BCS360 - Programming in SQL - 21038
Assignment Two
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.
Use the BillingAddressID and ShippingAddressID columns in the Customers table to
determine which addresses are billing addresses and which are shipping addresses.
Hint: You can use two JOIN clauses to join the Addresses table to the Customers table
twice (once for each type of address).
create view CustomerAddresses
as
select cust.CustomerID, cust.EmailAddress, cust.LastName, cust.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 cust
join Addresses bill on cust.BillingAddressID = bill.AddressID
join Addresses ship on cust.ShippingAddressID = ship.AddressID
2) Write a SELECT statement that returns these columns from the CustomerAddresses
view that you created in exercise 1: CustomerID, LastName, FirstName, BillLine1.
select CustomerID, LastName, FirstName, BillLine1
from CustomerAddresses
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 “1990 Westwood Blvd.”
for the customer with an ID of 8.
update CustomerAddresses
set ShipLine1 = '1990 Westwood Blvd.'
where CustomerID = 8
