Sales and Marketing Scenario
Customer and sales-related information is a significant part of the AdventureWorks sample database. This topic provides details about the customers that are represented in the sample database, a schema of the major customer and sales tables and sample queries that demonstrate table relationships.
As a bicycle manufacturing company, Adventure Works Cycles has two types of customers:
- Individuals. These are consumers who buy products from the Adventure Works Cycles online store.
- Stores. These are retail or wholesale stores that buy products for resale from Adventure Works Cycles sales representatives.
The Customer table contains one record for each customer. The column CustomerType indicates whether the customer is an individual consumer (CustomerType= 'I') or a store (CustomerType= 'S'). Data specific to these customer types is maintained in the Individual and Store tables, respectively.
|Customer type||Major tables||Number of customers||Additional information|
Sales and demographic data have been trended for data mining scenarios.
Demographic data (income, hobbies, number of cars, and so on) is stored as xml data in the Demographics column of the Individual table.
Data has been trended for Analysis Services scenarios.
Stores are categorized by size: large, medium, and small.
Demographic data stored as xml data.
Store contacts are employees of the store who interact with Adventure Works Cycles sales representatives. For example, the store owner or purchasing manager would be typical contacts for Adventure Works Cycles salespeople.
You can use the following queries to view customer data and to become familiar with the customer-table relationships.
A. Viewing individual customers (consumers)
The following example returns the first and last name of each customer who is categorized as an individual consumer (
CustomerType = 'I').
USE AdventureWorks; GO SELECT FirstName, LastName FROM Person.Contact AS C JOIN Sales.Individual AS I ON C.ContactID = I.ContactID JOIN Sales.Customer AS Cu ON I.CustomerID = Cu.CustomerID WHERE Cu.CustomerType = 'I' ORDER BY LastName, FirstName ; GO
B. Viewing individual customer address data
The following example lists the names and addresses of all individual customers.
USE AdventureWorks; GO SELECT I.CustomerID, C.FirstName, C.LastName, A.AddressLine1, A.City, SP.Name AS State, CR.Name AS CountryRegion FROM Person.Contact AS C JOIN Sales.Individual AS I ON C.ContactID = I.ContactID JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = I.CustomerID JOIN Person.Address AS A ON A.AddressID = CA.AddressID JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID JOIN Person.CountryRegion CR ON CR.CountryRegionCode = SP.CountryRegionCode ORDER BY I.CustomerID ; GO
C. Viewing store customers, either retail or wholesale stores
The following example returns the name of each customer that is categorized as a store (
CustomerType = 'S').
USE AdventureWorks; GO SELECT Name FROM Sales.Store AS S JOIN Sales.Customer AS C ON S.CustomerID = C.CustomerID WHERE C.CustomerType = N'S' ORDER BY Name ; GO GO
D. Viewing store contacts by store
The following example returns the name of all store customers and the names and titles of store employees who authorized to purchase Adventure Works Cycles products on behalf of their company.
USE AdventureWorks; GO SELECT S.Name AS Store, C.FirstName, C.LastName, CT.Name AS Title FROM Person.Contact AS C JOIN Sales.StoreContact AS SC ON C.ContactID = SC.ContactID JOIN Person.ContactType AS CT ON CT.ContactTypeID = SC.ContactTypeID JOIN Sales.Store AS S ON S.CustomerID = SC.CustomerID ORDER BY S.Name ; GO
E. Viewing sales by store
The following example lists store customers and their associated sales orders.
USE AdventureWorks; GO SELECT Name, SalesOrderNumber, OrderDate, TotalDue FROM Sales.Store AS S JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID ORDER BY Name, OrderDate ; GO
F. Viewing stores by locations
The following example prints the store-customer name, city, state and country/region.
USE AdventureWorks; GO SELECT S.CustomerID, S.Name AS Store, A.City, SP.Name AS State, CR.Name AS CountryRegion FROM Sales.Store AS S JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID JOIN Person.Address AS A ON A.AddressID = CA.AddressID JOIN Person.StateProvince SP ON SP.StateProvinceID = A.StateProvinceID JOIN Person.CountryRegion CR ON CR.CountryRegionCode = SP.CountryRegionCode ORDER BY S.CustomerID ; GO GO