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.
Customers Types
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 |
|---|---|---|---|
Individual |
18,484 |
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. |
|
Store |
701 |
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. |
Examples
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
See Also
Concepts
Product Scenario
Purchasing and Vendor Scenario
Manufacturing Scenario
Other Resources
Adventure Works Cycles Business Scenarios