• Article

AdventureWorks to pubs Table Comparison

Some tables in the AdventureWorks sample database are similar in structure and content to tables in the pubs sample database. You can use the following table to convert queries that use pubs to queries that use AdventureWorks by selecting columns from the appropriate AdventureWorks tables. For example, if a query references the discounts table in pubs, a similar query can be written for AdventureWorks by using the Sales.SpecialOffer table. Notice that AdventureWorks uses schema names other than dbo. The schema names are included with the table names and must be specified when performing queries against the tables. For more information, see Schemas in AdventureWorks.

pubs

AdventureWorks

Comments

authors

Purchasing.Vendor

 

discounts

Sales.SpecialOffer

 

employee

HumanResources.Employee

 

jobs

HumanResources.Employee

See the Title column in Employee.

pub_info

Production.ProductPhoto

Production.ProductDescription

 

publishers

Sales.Store

Person.Address

Sales.CustomerAddress

Person.CountryRegion

Person.StateProvince

The following query provides the equivalent information in the publishers table.

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 CA

ON CA.CustomerID =  S.CustomerID

JOIN Person.Address AS A ON  A.AddressID = CA.AddressID

JOIN Person.StateProvince AS SP

ON SP.StateProvinceID =  A.StateProvinceID

JOIN Person.CountryRegion AS CR

ON CR.CountryRegionCode = SP.CountryRegionCode

GROUP BY S.CustomerID, S.Name,

    A.City, SP.Name, CR.Name

ORDER BY S.CustomerID;

roysched

Sales.SpecialOffer

See the MinQty and MaxQty columns.

sales

Sales.SalesOrderHeader

Sales.SalesOrderDetail

 

stores

Sales.Store

 

titleauthor

Production.ProductVendor

titleauthor is an associative table that maps authors to titles. Production.ProductVendor maps vendors to the products they sell to Adventure Works Cycles.

titles

Production.Product