How to Query the AdventureWorks Database

This page explains how to ask Avalon questions about the AdventureWorks sample database, what kind of questions work best, and how the data is organized.

1. What Avalon Can Do

  • Turn natural language questions into read-only SQL (SELECT queries only).
  • Run the query against the AdventureWorks database.
  • Show results in a grid and explain the SQL if you click Explain this query.
Important: Avalon is read only. It cannot INSERT, UPDATE, DELETE, or change your data. Any attempt to do this will be blocked.

2. How the AdventureWorks Data Is Organized

Some of the most commonly used schemas and tables:

People & Customers (Person / Sales)

  • Person.Person – basic person info (FirstName, LastName, etc.).
  • Person.Address – addresses.
  • Person.StateProvince – state / province information.
  • Sales.Customer – customer records (links people or stores to orders).

Example questions: "List customers and their state," or "Show top customers by total sales with their city and state."

Sales (Sales)

  • Sales.SalesOrderHeader – one row per order (OrderDate, TotalDue, CustomerID, etc.).
  • Sales.SalesOrderDetail – one row per line item on an order.
  • Sales.SalesTerritory – sales territories.

Example questions: "Show monthly sales totals for the last 2 years," or "Find the 10 customers with the most sales orders."

Products (Production)

  • Production.Product – product master list (Name, ProductNumber, Color, ListPrice, etc.).
  • Production.ProductCategory – high-level categories (e.g., Bikes, Components).
  • Production.ProductSubcategory – more detailed subcategories under each category.

Example questions: "Which product categories have the highest total sales?" or "List the top 20 products by quantity sold, including category and subcategory."

Employee & HR (HumanResources / Person)

  • HumanResources.Employee – employee records (JobTitle, HireDate, etc.).
  • HumanResources.EmployeeDepartmentHistory – which department an employee worked in and when.
  • HumanResources.Department – departments (e.g., Sales, Engineering, Finance).
  • Person.Person – names and basic person info for employees as well.

Example questions: "Show employees with their job title and current department," "Count employees by department," or "List employees hired in the last 12 months."

Product & Inventory (Production)

  • Production.ProductInventory – on-hand inventory quantities by product and location.
  • Production.Location – physical locations where inventory is stored.

Example questions: "Show low stock products and where they are stored," "List total on-hand quantity per product," or "Which locations have the lowest inventory for each product?"

Customer & Sales (Person / Sales)

  • Sales.Customer – connects people or stores to sales orders.
  • Sales.SalesOrderHeader – overall order information (dates, totals, customer).
  • Sales.SalesOrderDetail – line items (product, quantity, line total).
  • Person.Address, Person.StateProvince – geographic context for billing/shipping.

Example questions: "Show the top 20 customers by total sales amount including their state," or "List total sales by state or province."

Production & Manufacturing (Production)

  • Production.WorkOrder – manufacturing work orders (what is being produced, quantities, dates).
  • Production.BillOfMaterials – components required to build finished products.
  • Production.Product – finished goods and components.

Example questions: "Show recent work orders with product name and order quantity," "List products with the highest work order quantities," or "Summarize average order quantity by product category."

3. Explore the Schema with SQL

If you want to understand what’s in the database before asking questions, you can run these example queries directly (or ask Avalon to generate something similar).

3.1. List all tables in the database

This query shows every user table, grouped by schema. It’s a good way to see what’s available: 


SELECT 
    s.name AS SchemaName,
    t.name AS TableName
FROM sys.tables AS t
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
ORDER BY 
    s.name,
    t.name;

Tip: Many of the tables you’ll use with Avalon live in schemas like Person, Sales, Production, and HumanResources.

3.2. See which tables are used the most (example)

The query below uses SQL Server’s dynamic management views (DMVs) to estimate which tables are read the most, based on index usage: 


SELECT TOP 20
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(COALESCE(us.user_seeks, 0)
      + COALESCE(us.user_scans, 0)
      + COALESCE(us.user_lookups, 0)
      + COALESCE(us.user_updates, 0)) AS TotalAccessCount
FROM sys.dm_db_index_usage_stats AS us
JOIN sys.indexes AS i
    ON us.object_id = i.object_id
   AND us.index_id = i.index_id
JOIN sys.tables AS t
    ON us.object_id = t.object_id
JOIN sys.schemas AS s
    ON t.schema_id = s.schema_id
WHERE us.database_id = DB_ID()
GROUP BY
    s.name,
    t.name
ORDER BY
    TotalAccessCount DESC;
Note: This example may require extra permissions such as VIEW DATABASE STATE. If you see a "VIEW DATABASE PERFORMANCE STATE permission denied" error, you’ll need a DBA or admin to grant that permission, or you can skip this query and just focus on table metadata from sys.tables instead.

4. Good Question Patterns for Avalon

Avalon works best when questions are:

  • Specific about time ranges (e.g., “in 2013”, “last year”).
  • Clear about grouping (per customer, per state, per product, per department, etc.).
  • Focused on the data that exists in AdventureWorks.

Examples you can try

  1. Customers with the most sales:
    "Show the top 10 customers by total sales amount from Sales.SalesOrderHeader."
  2. Customers by state:
    "List the states and the number of customers in each state."
  3. Top products:
    "Which 10 products have the highest total sales quantity?"
  4. Sales over time:
    "Show total sales amount by month for the last 2 years."
  5. Customer detail with location:
    "Show customer name, city, state, and total sales amount for the top 20 customers."
  6. Employees and departments:
    "List employees with their job title and current department, sorted by department."
  7. Inventory and stock:
    "Show products that have low inventory, including location and quantity on hand."
  8. Work orders and production:
    "Show recent work orders with product name, order quantity, and start/end dates."

5. Things to Avoid

  • Made-up tables or columns – stick to tables you know exist (like Person.Person, Sales.SalesOrderHeader, Sales.SalesOrderDetail, etc.).
  • Table names that sound like summaries – e.g. TopCustomer, SalesOrderCount, CustomerDetails are not real tables in AdventureWorks. Instead phrase the request as:
    "Find the customers with the highest count of sales orders and show their name and state."
  • Data changes – Avalon will refuse to run any query that modifies data (INSERT, UPDATE, DELETE, etc.).

6. Tips for Better Results

  • Mention real table names when you know them (e.g., “using Sales.SalesOrderHeader and Sales.SalesOrderDetail”).
  • Say how you want to group or sort (e.g., “top 10 by total sales amount”).
  • Include filters (year, product category, region, department) to narrow things down.

If a query fails because of an “unknown table or column” error, try rephrasing using the table names listed above or ask something like:
"Show me example columns available in Sales.SalesOrderHeader and Sales.SalesOrderDetail."