AdventureWorks

Basic Index   Expanded Index

View Name:Sales.vStoreWithDemographics
Description:Stores (names and addresses) that sell Adventure Works Cycles products to consumers.

FieldTypeNulls?
CustomerIDintNo
Namenvarchar (50)No
ContactTypenvarchar (50)No
Titlenvarchar (8)Yes
FirstNamenvarchar (50)No
MiddleNamenvarchar (50)Yes
LastNamenvarchar (50)No
Suffixnvarchar (10)Yes
Phonenvarchar (25)Yes
EmailAddressnvarchar (50)Yes
EmailPromotionintNo
AddressTypenvarchar (50)No
AddressLine1nvarchar (60)No
AddressLine2nvarchar (60)Yes
Citynvarchar (30)No
StateProvinceNamenvarchar (50)No
PostalCodenvarchar (15)No
CountryRegionNamenvarchar (50)No
AnnualSalesmoneyYes
AnnualRevenuemoneyYes
BankNamenvarchar (50)Yes
BusinessTypenvarchar (5)Yes
YearOpenedintYes
Specialtynvarchar (50)Yes
SquareFeetintYes
Brandsnvarchar (30)Yes
Internetnvarchar (30)Yes
NumberEmployeesintYes

CREATE VIEW [Sales].[vStoreWithDemographics] AS
SELECT
s.[CustomerID]
,s.[Name]
,ct.[Name] AS [ContactType]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,at.[Name] AS [AddressType]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,sp.[Name] AS [StateProvinceName]
,a.[PostalCode]
,cr.[Name] AS [CountryRegionName]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet]
,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-
works/StoreSurvey";
(/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees]
FROM [Sales].[Store] s
INNER JOIN [Sales].[StoreContact] sc
ON sc.[CustomerID] = s.[CustomerID]
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = sc.[ContactID]
INNER JOIN [Person].[ContactType] ct
ON sc.[ContactTypeID] = ct.[ContactTypeID]
INNER JOIN [Sales].[CustomerAddress] ca
ON ca.[CustomerID] = s.[CustomerID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = ca.[AddressID]
INNER JOIN [Person].[StateProvince] sp
ON sp.[StateProvinceID] = a.[StateProvinceID]
INNER JOIN [Person].[CountryRegion] cr
ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
INNER JOIN [Person].[AddressType] at
ON ca.[AddressTypeID] = at.[AddressTypeID]
WHERE s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]
FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');