AdventureWorksLT Database

Introduction

AdventureWorksLT is a new sample database based on the Adventure Works Cycles scenario. See MSDN for more information about this scenario and the other sample databases which are available.

The purpose of the AdventureWorksLT database is to provide a simple, small database sample focused on a product sales scenario.

You can find the schema diagram for AdventureWorksLT and the other sample databases here.

AdventureWorksLT is simpler and smaller in the following ways:
  • The schema design is denormalized compared with AdventureWorks. To obtain useful information either requires no joins, or fewer joins compared with AdventureWorks.
  • The quantity of data is reduced from about 183mb down to 7mb which makes it more practical to download over slow or unreliable Internet connections.
  • Instead of 5 schemas plus dbo in the database, AdventureWorksLT has one schema plus dbo in the database.
  • Instead of 70 tables, AdventureWorksLT has 12 tables.

AdventureWorksLT is specifically designed for developers and ITPros that are just starting to learn about relational database technology. More advanced developers and ITPros who are developing enterprise-class applications should look at the AdventureWorks database.

Installation

See the Releases tab for an windows installer package (MSI). This installer will place the AdventureWorksLT MDF and LDF file on your computer. You can then use SQL Server Management Studio or SQL Server Management Studio Express to attach the database and use it.

There is a SQL Server script in the AWLT folder which you can browse to using the Source Code tab which will build the database. You must have a current copy of the AdventureWorks OLTP database available for the script to operate correctly since it populates the Adventure Works LT database with data contained in the AdventureWorks OLTP database. If you don't have a copy of the AdventureWorks OLTP database then use the windows installer package mentioned above.

Using AdventureWorksLT

Using SQL Server Management Studio, SQL Server Management Studio Express, or SQLCMD you can try the following queries:

SELECT * FROM SalesLT.Customer;

SELECT * FROM SalesLT.Customer WHERE FirstName = N'Mary';

SELECT LastName FROM SalesLT.Customer 
WHERE FirstName = N'Mary';

SELECT LastName FROM SalesLT.Customer 
WHERE FirstName = N'Mary' 
ORDER BY LastName;

SELECT C.FirstName, C.MiddleName, C.LastName, C.CompanyName, 
A.AddressLine1, A.AddressLine2, A.City, A.StateProvince, 
A.CountryRegion, A.PostalCode 
FROM SalesLT.Customer AS C
JOIN SalesLT.CustomerAddress AS CA ON C.CustomerID = CA.CustomerID
JOIN SalesLT.Address AS A ON CA.AddressID = A.AddressID
WHERE C.FirstName = N'Mary';

SELECT C.LastName, Sum(SOH.TotalDue) 
FROM SalesLT.Customer AS C
JOIN SalesLT.SalesOrderHeader AS SOH ON C.CustomerID = SOH.CustomerID
WHERE FirstName = N'Walter'
GROUP BY C.LastName;

SELECT * from SalesLT.vGetAllCategories

Sample Code

There is currently no published sample code which uses the AdventureWorksLT database although there are plans to create samples in future releases. As samples become available this Wiki will be updated.

Last edited Mar 21, 2007 at 3:28 PM by bonniefe, version 3

Comments

No comments yet.