USE SQL2008SBS
GO

CREATE VIEW HumanResources.v_Employees
AS
SELECT a.EmployeeID, a.FirstName, a.LastName, a.JobTitle, a.BirthDate, a.HireDate,
c.AddressType, b.AddressLine1, b.AddressLine2, b.AddressLine3, b.City, d.StateProvince,
e.CountryName
FROM HumanResources.Employee a LEFT JOIN HumanResources.EmployeeAddress b ON a.EmployeeID = b.EmployeeID
LEFT JOIN LookupTables.AddressType c ON b.AddressTypeID = c.AddressTypeID
LEFT JOIN LookupTables.StateProvince d ON b.StateProvinceID = d.StateProvinceID
LEFT JOIN LookupTables.Country e ON b.CountryID = e.CountryID
GO
SELECT EmployeeID, FirstName, LastName, JobTitle, BirthDate, HireDate, AddressType, AddressLine1, AddressLine2, AddressLine3, City, StateProvince, CountryName
FROM HumanResources.v_Employees
GO
INSERT INTO HumanResources.v_Employees
(FirstName, LastName, JobTitle, BirthDate, HireDate)
VALUES('Diane','Prescott','Vice-President','4/11/1980','1/2/2008')
GO
SELECT EmployeeID, FirstName, LastName, JobTitle, BirthDate, HireDate, AddressType, AddressLine1, AddressLine2, AddressLine3, City, StateProvince, CountryName
FROM HumanResources.v_Employees
GO