AdventureWorks - HumanResources.vEmployee

AdventureWorks

View Name:	HumanResources.vEmployee
Description:	Employee names and addresses.

Field	Type	Nulls?
EmployeeID	int	No
Title	nvarchar (8)	Yes
FirstName	nvarchar (50)	No
MiddleName	nvarchar (50)	Yes
LastName	nvarchar (50)	No
Suffix	nvarchar (10)	Yes
JobTitle	nvarchar (50)	No
Phone	nvarchar (25)	Yes
EmailAddress	nvarchar (50)	Yes
EmailPromotion	int	No
AddressLine1	nvarchar (60)	No
AddressLine2	nvarchar (60)	Yes
City	nvarchar (30)	No
StateProvinceName	nvarchar (50)	No
PostalCode	nvarchar (15)	No
CountryRegionName	nvarchar (50)	No
AdditionalContactInfo	xml	Yes

CREATE VIEW [HumanResources].[vEmployee]
AS
SELECT
e.[EmployeeID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,e.[Title] AS [JobTitle]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,c.[AdditionalContactInfo]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [HumanResources].[EmployeeAddress] ea
ON e.[EmployeeID] = ea.[EmployeeID]
INNER JOIN [Person].[Address] a
ON ea.[AddressID] = a.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode];