AdventureWorks LT database: schema, tables and SQL exercises

AdventureWorks LT is the Microsoft SQL Server sample database of a bicycle manufacturer: customers, products, product categories and sales orders. On SQLtest.online you can query it right in your browser: solve exercises with automatic checking and run your own queries in the playground, with nothing to install.

• 10 main tables
• 847 customers
• 295 products
• 34 SQL exercises

What is AdventureWorks

AdventureWorks is the sample database Microsoft ships for SQL Server and Azure SQL. It describes Adventure Works Cycles, a fictional company that makes and sells bicycles, parts and accessories.

The site uses AdventureWorks LT, the lightweight edition: the same business in about ten tables instead of dozens. It is a good place to practice T-SQL, including TOP, self-joins on a category tree and many-to-many relationships.

ER diagram

The diagram shows the AdventureWorks tables and the foreign keys between them. Click it to open the full-size version.

What's inside

The tables fall into three groups.

All 32 orders in this edition are dated 1 June 2008. Product descriptions are linked to models through ProductModelProductDescription, which also stores the language (culture) of each description. The service tables BuildVersion, ErrorLog and sysdiagrams are not used in the exercises.

How much data the tables hold:

Table structure

Click a table to see its columns, a sample row and its keys.

Sample queries

These queries show how the data is connected. Copy any of them and run it in the playground.

A customer and their addresses: a many-to-many link through CustomerAddress.

SELECT TOP 3 c.FirstName, c.LastName, ca.AddressType, a.City
FROM Customer c
JOIN CustomerAddress ca ON ca.CustomerID = c.CustomerID
JOIN Address a ON a.AddressID = ca.AddressID
ORDER BY c.CustomerID;

An order and its lines: from the order header to the products.

SELECT TOP 3 h.SalesOrderID, h.OrderDate, p.Name, d.OrderQty, d.UnitPrice
FROM SalesOrderHeader h
JOIN SalesOrderDetail d ON d.SalesOrderID = h.SalesOrderID
JOIN Product p ON p.ProductID = d.ProductID
ORDER BY h.SalesOrderID, d.SalesOrderDetailID;

SQL exercises by topic

There are 34 exercises on the AdventureWorks database, from simple filters to analytics on orders and products. Solutions are checked automatically on a real SQL Server. The number on the right is how many exercises a topic has; the colored dots show its difficulty range.

• Analytical queries 7

Where to start

The first exercises on the AdventureWorks database:

1. Product Categories
2. Product List
3. Filtered list of products
4. Ten heaviest products
5. Get list of tables (SQL Server)
6. Even-Numbered Customers
7. Customers by Phone Prefix
8. Duplicate Phone Numbers
9. List Unique Customers
10. Duplicate Emails

All AdventureWorks exercises →