AdventureWorks

Basic Index   Expanded Index

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

FieldTypeNulls?
EmployeeIDintNo
Titlenvarchar (8)Yes
FirstNamenvarchar (50)No
MiddleNamenvarchar (50)Yes
LastNamenvarchar (50)No
Suffixnvarchar (10)Yes
JobTitlenvarchar (50)No
Phonenvarchar (25)Yes
EmailAddressnvarchar (50)Yes
EmailPromotionintNo
AddressLine1nvarchar (60)No
AddressLine2nvarchar (60)Yes
Citynvarchar (30)No
StateProvinceNamenvarchar (50)No
PostalCodenvarchar (15)No
CountryRegionNamenvarchar (50)No
AdditionalContactInfoxmlYes

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];