david@maier:~/lab/data-explorer/r/6e84ce4e-cc88-4f75-a7e0-acd1ef0e6c63$

explore --db adventureworks

Data explorer

Ask the ERP database of a bike maker a question in plain words. A language model explores the tables, and you see every step it takes, the SQL behind the answer and the result.

10 questions left this hour. Model: xiaomi/mimo-v2.6-flash via OpenRouter. Questions are sent to the model provider and kept here for 30 days so that answers can be shared. Please don't type personal data.

Examples

Welche Produkte liegen im Lager unter ihrem Meldebestand?

  1. submit_query SELECT COUNT(*) AS Anzahl, GROUP_CONCAT(Name) AS Beispiele F… 1 row · 3 ms 
    SELECT COUNT(*) AS Anzahl, GROUP_CONCAT(Name) AS Beispiele FROM (SELECT p.Name FROM Product p JOIN ProductInventory i ON i.ProductID = p.ProductID GROUP BY p.ProductID, p.Name, p.ReorderPoint HAVING COALESCE(SUM(i.Quantity),0) < p.ReorderPoint)
  2. submit_query SELECT p.ProductID, p.Name, p.ReorderPoint AS Meldebestand, … 7 rows · 2 ms 
    SELECT p.ProductID, p.Name, p.ReorderPoint AS Meldebestand, SUM(i.Quantity) AS Lagerbestand, SUM(i.Quantity) - p.ReorderPoint AS Defizit FROM Product p JOIN ProductInventory i ON i.ProductID = p.ProductID GROUP BY p.ProductID, p.Name, p.ReorderPoint HAVING SUM(i.Quantity) < p.ReorderPoint ORDER BY Defizit ASC LIMIT 100
  3. text_response answer

Answer

Betragt wurde der Gesamtbestand über alle Lagerorte (SUM(ProductInventory.Quantity)) verglichen mit Product.ReorderPoint. 7 Produkte liegen darunter: Lower Head Race (ID 462): 701 statt 750, Defizit -49 Hex Nut 1 (386): 725 statt 750, -25 HL Mountain Seat/Saddle (910): 355 statt 375, -20 Women's Tights, M (853): 0 statt 3, -3 Half-Finger Gloves, M (859): 0 statt 3, -3 Hitch Rack - 4-Bike (876): 0 statt 3, -3 Short-Sleeve Classic Jersey, M (882): 0 statt 3, -3 Voraussetzung: nur Produkte mit Bestandszeilen; keine Treffer abgeschnitten.

Edit the query and run it again. No model involved.

Result

ProductIDNameMeldebestandLagerbestandDefizit
462 Lower Head Race 750 701 -49
386 Hex Nut 1 750 725 -25
910 HL Mountain Seat/Saddle 375 355 -20
853 Women's Tights, M 3 0 -3
859 Half-Finger Gloves, M 3 0 -3
876 Hitch Rack - 4-Bike 3 0 -3
882 Short-Sleeve Classic Jersey, M 3 0 -3

rows 1–7 · 2 ms CSV

xiaomi/mimo-v2.6-flash · 12226 tokens · $0.0018 · 11.8 s · share: https://personal.dev.davidmaier.at/en/lab/data-explorer/r/6e84ce4e-cc88-4f75-a7e0-acd1ef0e6c63

Schema

The tables the model sees, grouped as in the original database. In SQL, write table names without the group.

Sales (16)
CountryRegionCurrency 109 rows Cross-reference table mapping ISO currency codes to a country or region.
CountryRegionCode TEXT
ISO code for countries and regions. Foreign key to CountryRegion.CountryRegionCode. → CountryRegion.CountryRegionCode
CurrencyCode TEXT
ISO standard currency code. Foreign key to Currency.CurrencyCode. → Currency.CurrencyCode
ModifiedDate TEXT
Date and time the record was last updated.
Currency 105 rows Lookup table containing standard ISO currencies.
CurrencyCode TEXT
The ISO code for the Currency.
Name TEXT
Currency name.
ModifiedDate TEXT
Date and time the record was last updated.
CurrencyRate 13532 rows Currency exchange rates.
CurrencyRateID INTEGER
Primary key for CurrencyRate records.
CurrencyRateDate TEXT
Date and time the exchange rate was obtained.
FromCurrencyCode TEXT
Exchange rate was converted from this currency code. → Currency.CurrencyCode
ToCurrencyCode TEXT
Exchange rate was converted to this currency code. → Currency.CurrencyCode
AverageRate REAL
Average exchange rate for the day.
EndOfDayRate REAL
Final exchange rate for the day.
ModifiedDate TEXT
Date and time the record was last updated.
Customer 19820 rows Current customer information. Also see the Person and Store tables.
CustomerID INTEGER
Primary key.
PersonID INTEGER
Foreign key to Person.BusinessEntityID → Person.BusinessEntityID
StoreID INTEGER
Foreign key to Store.BusinessEntityID → Store.BusinessEntityID
TerritoryID INTEGER
ID of the territory in which the customer is located. Foreign key to SalesTerritory.SalesTerritoryID. → SalesTerritory.TerritoryID
AccountNumber TEXT
Unique number identifying the customer assigned by the accounting system.
ModifiedDate TEXT
Date and time the record was last updated.
SalesOrderDetail 121317 rows Individual products associated with a specific sales order. See SalesOrderHeader.
SalesOrderID INTEGER
Primary key. Foreign key to SalesOrderHeader.SalesOrderID. → SalesOrderHeader.SalesOrderID
SalesOrderDetailID INTEGER
Primary key. One incremental unique number per product sold.
CarrierTrackingNumber TEXT
Shipment tracking number supplied by the shipper.
OrderQty INTEGER
Quantity ordered per product.
ProductID INTEGER
Product sold to customer. Foreign key to Product.ProductID. → SpecialOfferProduct.ProductID
SpecialOfferID INTEGER
Promotional code. Foreign key to SpecialOffer.SpecialOfferID. → SpecialOfferProduct.SpecialOfferID
UnitPrice REAL
Selling price of a single product.
UnitPriceDiscount REAL
Discount amount.
LineTotal REAL
Per product subtotal. Computed as UnitPrice * (1 - UnitPriceDiscount) * OrderQty.
ModifiedDate TEXT
Date and time the record was last updated.
SalesOrderHeader 31465 rows General sales order information.
SalesOrderID INTEGER
Primary key.
RevisionNumber INTEGER
Incremental number to track changes to the sales order over time.
OrderDate TEXT
Dates the sales order was created.
DueDate TEXT
Date the order is due to the customer.
ShipDate TEXT
Date the order was shipped to the customer.
Status INTEGER
Order current status. 1 = In process; 2 = Approved; 3 = Backordered; 4 = Rejected; 5 = Shipped; 6 = Cancelled
OnlineOrderFlag INTEGER
0 = Order placed by sales person. 1 = Order placed online by customer.
SalesOrderNumber TEXT
Unique sales order identification number.
PurchaseOrderNumber TEXT
Customer purchase order number reference.
AccountNumber TEXT
Financial accounting number reference.
CustomerID INTEGER
Customer identification number. Foreign key to Customer.BusinessEntityID. → Customer.CustomerID
SalesPersonID INTEGER
Sales person who created the sales order. Foreign key to SalesPerson.BusinessEntityID. → SalesPerson.BusinessEntityID
TerritoryID INTEGER
Territory in which the sale was made. Foreign key to SalesTerritory.SalesTerritoryID. → SalesTerritory.TerritoryID
BillToAddressID INTEGER
Customer billing address. Foreign key to Address.AddressID. → Address.AddressID
ShipToAddressID INTEGER
Customer shipping address. Foreign key to Address.AddressID. → Address.AddressID
ShipMethodID INTEGER
Shipping method. Foreign key to ShipMethod.ShipMethodID. → ShipMethod.ShipMethodID
CurrencyRateID INTEGER
Currency exchange rate used. Foreign key to CurrencyRate.CurrencyRateID. → CurrencyRate.CurrencyRateID
SubTotal REAL
Sales subtotal. Computed as SUM(SalesOrderDetail.LineTotal)for the appropriate SalesOrderID.
TaxAmt REAL
Tax amount.
Freight REAL
Shipping cost.
TotalDue REAL
Total due from customer. Computed as Subtotal + TaxAmt + Freight.
Comment TEXT
Sales representative comments.
ModifiedDate TEXT
Date and time the record was last updated.
SalesOrderHeaderSalesReason 27647 rows Cross-reference table mapping sales orders to sales reason codes.
SalesOrderID INTEGER
Primary key. Foreign key to SalesOrderHeader.SalesOrderID. → SalesOrderHeader.SalesOrderID
SalesReasonID INTEGER
Primary key. Foreign key to SalesReason.SalesReasonID. → SalesReason.SalesReasonID
ModifiedDate TEXT
Date and time the record was last updated.
SalesPerson 17 rows Sales representative current information.
BusinessEntityID INTEGER
Primary key for SalesPerson records. Foreign key to Employee.BusinessEntityID → Employee.BusinessEntityID
TerritoryID INTEGER
Territory currently assigned to. Foreign key to SalesTerritory.SalesTerritoryID. → SalesTerritory.TerritoryID
SalesQuota REAL
Projected yearly sales.
Bonus REAL
Bonus due if quota is met.
CommissionPct REAL
Commision percent received per sale.
SalesYTD REAL
Sales total year to date.
SalesLastYear REAL
Sales total of previous year.
ModifiedDate TEXT
Date and time the record was last updated.
SalesPersonQuotaHistory 163 rows Sales performance tracking.
BusinessEntityID INTEGER
Sales person identification number. Foreign key to SalesPerson.BusinessEntityID. → SalesPerson.BusinessEntityID
QuotaDate TEXT
Sales quota date.
SalesQuota REAL
Sales quota amount.
ModifiedDate TEXT
Date and time the record was last updated.
SalesReason 10 rows Lookup table of customer purchase reasons.
SalesReasonID INTEGER
Primary key for SalesReason records.
Name TEXT
Sales reason description.
ReasonType TEXT
Category the sales reason belongs to.
ModifiedDate TEXT
Date and time the record was last updated.
SalesTaxRate 29 rows Tax rate lookup table.
SalesTaxRateID INTEGER
Primary key for SalesTaxRate records.
StateProvinceID INTEGER
State, province, or country/region the sales tax applies to. → StateProvince.StateProvinceID
TaxType INTEGER
1 = Tax applied to retail transactions, 2 = Tax applied to wholesale transactions, 3 = Tax applied to all sales (retail and wholesale) transactions.
TaxRate REAL
Tax rate amount.
Name TEXT
Tax rate description.
ModifiedDate TEXT
Date and time the record was last updated.
SalesTerritory 10 rows Sales territory lookup table.
TerritoryID INTEGER
Primary key for SalesTerritory records.
Name TEXT
Sales territory description
CountryRegionCode TEXT
ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
Group TEXT
Geographic area to which the sales territory belong.
SalesYTD REAL
Sales in the territory year to date.
SalesLastYear REAL
Sales in the territory the previous year.
CostYTD REAL
Business costs in the territory year to date.
CostLastYear REAL
Business costs in the territory the previous year.
ModifiedDate TEXT
Date and time the record was last updated.
SalesTerritoryHistory 17 rows Sales representative transfers to other sales territories.
BusinessEntityID INTEGER
Primary key. The sales rep. Foreign key to SalesPerson.BusinessEntityID. → SalesPerson.BusinessEntityID
TerritoryID INTEGER
Primary key. Territory identification number. Foreign key to SalesTerritory.SalesTerritoryID. → SalesTerritory.TerritoryID
StartDate TEXT
Primary key. Date the sales representive started work in the territory.
EndDate TEXT
Date the sales representative left work in the territory.
ModifiedDate TEXT
Date and time the record was last updated.
SpecialOffer 16 rows Sale discounts lookup table.
SpecialOfferID INTEGER
Primary key for SpecialOffer records.
Description TEXT
Discount description.
DiscountPct REAL
Discount precentage.
Type TEXT
Discount type category.
Category TEXT
Group the discount applies to such as Reseller or Customer.
StartDate TEXT
Discount start date.
EndDate TEXT
Discount end date.
MinQty INTEGER
Minimum discount percent allowed.
MaxQty INTEGER
Maximum discount percent allowed.
ModifiedDate TEXT
Date and time the record was last updated.
SpecialOfferProduct 538 rows Cross-reference table mapping products to special offer discounts.
SpecialOfferID INTEGER
Primary key for SpecialOfferProduct records. → SpecialOffer.SpecialOfferID
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID. → Product.ProductID
ModifiedDate TEXT
Date and time the record was last updated.
Store 701 rows Customers (resellers) of Adventure Works products.
BusinessEntityID INTEGER
Primary key. Foreign key to Customer.BusinessEntityID. → BusinessEntity.BusinessEntityID
Name TEXT
Name of the store.
SalesPersonID INTEGER
ID of the sales person assigned to the customer. Foreign key to SalesPerson.BusinessEntityID. → SalesPerson.BusinessEntityID
ModifiedDate TEXT
Date and time the record was last updated.
Purchasing (5)
ProductVendor 460 rows Cross-reference table mapping vendors with the products they supply.
ProductID INTEGER
Primary key. Foreign key to Product.ProductID. → Product.ProductID
BusinessEntityID INTEGER
Primary key. Foreign key to Vendor.BusinessEntityID. → Vendor.BusinessEntityID
AverageLeadTime INTEGER
The average span of time (in days) between placing an order with the vendor and receiving the purchased product.
StandardPrice REAL
The vendor's usual selling price.
LastReceiptCost REAL
The selling price when last purchased.
LastReceiptDate TEXT
Date the product was last received by the vendor.
MinOrderQty INTEGER
The maximum quantity that should be ordered.
MaxOrderQty INTEGER
The minimum quantity that should be ordered.
OnOrderQty INTEGER
The quantity currently on order.
UnitMeasureCode TEXT
The product's unit of measure. → UnitMeasure.UnitMeasureCode
ModifiedDate TEXT
Date and time the record was last updated.
PurchaseOrderDetail 7937 rows Individual products associated with a specific purchase order. See PurchaseOrderHeader.
PurchaseOrderID INTEGER
Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID. → PurchaseOrderHeader.PurchaseOrderID
PurchaseOrderDetailID INTEGER
Primary key. One line number per purchased product.
DueDate TEXT
Date the product is expected to be received.
OrderQty INTEGER
Quantity ordered.
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID. → Product.ProductID
UnitPrice REAL
Vendor's selling price of a single product.
LineTotal REAL
Per product subtotal. Computed as OrderQty * UnitPrice.
ReceivedQty REAL
Quantity actually received from the vendor.
RejectedQty REAL
Quantity rejected during inspection.
StockedQty REAL
Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
ModifiedDate TEXT
Date and time the record was last updated.
PurchaseOrderHeader 3579 rows General purchase order information. See PurchaseOrderDetail.
PurchaseOrderID INTEGER
Primary key.
RevisionNumber INTEGER
Incremental number to track changes to the purchase order over time.
Status INTEGER
Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeID INTEGER
Employee who created the purchase order. Foreign key to Employee.BusinessEntityID. → Employee.BusinessEntityID
VendorID INTEGER
Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID. → Vendor.BusinessEntityID
ShipMethodID INTEGER
Shipping method. Foreign key to ShipMethod.ShipMethodID. → ShipMethod.ShipMethodID
OrderDate TEXT
Purchase order creation date.
ShipDate TEXT
Estimated shipment date from the vendor.
SubTotal REAL
Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmt REAL
Tax amount.
Freight REAL
Shipping cost.
TotalDue REAL
Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDate TEXT
Date and time the record was last updated.
ShipMethod 5 rows Shipping company lookup table.
ShipMethodID INTEGER
Primary key for ShipMethod records.
Name TEXT
Shipping company name.
ShipBase REAL
Minimum shipping charge.
ShipRate REAL
Shipping charge per pound.
ModifiedDate TEXT
Date and time the record was last updated.
Vendor 104 rows Companies from whom Adventure Works Cycles purchases parts or other goods.
BusinessEntityID INTEGER
Primary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityID → BusinessEntity.BusinessEntityID
AccountNumber TEXT
Vendor account (identification) number.
Name TEXT
Company name.
CreditRating INTEGER
1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average
PreferredVendorStatus INTEGER
0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product.
ActiveFlag INTEGER
0 = Vendor no longer used. 1 = Vendor is actively used.
PurchasingWebServiceURL TEXT
ModifiedDate TEXT
Production (19)
BillOfMaterials 2679 rows Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
BillOfMaterialsID INTEGER
Primary key for BillOfMaterials records.
ProductAssemblyID INTEGER
Parent product identification number. Foreign key to Product.ProductID. → Product.ProductID
ComponentID INTEGER
Component identification number. Foreign key to Product.ProductID. → Product.ProductID
StartDate TEXT
Date the component started being used in the assembly item.
EndDate TEXT
Date the component stopped being used in the assembly item.
UnitMeasureCode TEXT
Standard code identifying the unit of measure for the quantity. → UnitMeasure.UnitMeasureCode
BOMLevel INTEGER
Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty REAL
Quantity of the component needed to create the assembly.
ModifiedDate TEXT
Date and time the record was last updated.
Culture 8 rows Lookup table containing the languages in which some AdventureWorks data is stored.
CultureID TEXT
Primary key for Culture records.
Name TEXT
Culture description.
ModifiedDate TEXT
Date and time the record was last updated.
Location 14 rows Product inventory and manufacturing locations.
LocationID INTEGER
Primary key for Location records.
Name TEXT
Location description.
CostRate REAL
Standard hourly cost of the manufacturing location.
Availability REAL
Work capacity (in hours) of the manufacturing location.
ModifiedDate TEXT
Date and time the record was last updated.
Product 504 rows Products sold or used in the manfacturing of sold products.
ProductID INTEGER
Primary key for Product records.
Name TEXT
Name of the product.
ProductNumber TEXT
Unique product identification number.
MakeFlag INTEGER
0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag INTEGER
0 = Product is not a salable item. 1 = Product is salable.
Color TEXT
Product color.
SafetyStockLevel INTEGER
Minimum inventory quantity.
ReorderPoint INTEGER
Inventory level that triggers a purchase order or work order.
StandardCost REAL
Standard cost of the product.
ListPrice REAL
Selling price.
Size TEXT
Product size.
SizeUnitMeasureCode TEXT
Unit of measure for Size column. → UnitMeasure.UnitMeasureCode
WeightUnitMeasureCode TEXT
Unit of measure for Weight column. → UnitMeasure.UnitMeasureCode
Weight REAL
Product weight.
DaysToManufacture INTEGER
Number of days required to manufacture the product.
ProductLine TEXT
R = Road, M = Mountain, T = Touring, S = Standard
Class TEXT
H = High, M = Medium, L = Low
Style TEXT
W = Womens, M = Mens, U = Universal
ProductSubcategoryID INTEGER
Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID. → ProductSubcategory.ProductSubcategoryID
ProductModelID INTEGER
Product is a member of this product model. Foreign key to ProductModel.ProductModelID. → ProductModel.ProductModelID
SellStartDate TEXT
Date the product was available for sale.
SellEndDate TEXT
Date the product was no longer available for sale.
DiscontinuedDate TEXT
Date the product was discontinued.
ModifiedDate TEXT
Date and time the record was last updated.
ProductCategory 4 rows High-level product categorization.
ProductCategoryID INTEGER
Primary key for ProductCategory records.
Name TEXT
Category description.
ModifiedDate TEXT
Date and time the record was last updated.
ProductCostHistory 395 rows Changes in the cost of a product over time.
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID → Product.ProductID
StartDate TEXT
Product cost start date.
EndDate TEXT
Product cost end date.
StandardCost REAL
Standard cost of the product.
ModifiedDate TEXT
Date and time the record was last updated.
ProductDescription 762 rows Product descriptions in several languages.
ProductDescriptionID INTEGER
Primary key for ProductDescription records.
Description TEXT
Description of the product.
ModifiedDate TEXT
Date and time the record was last updated.
ProductInventory 1069 rows Product inventory information.
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID. → Product.ProductID
LocationID INTEGER
Inventory location identification number. Foreign key to Location.LocationID. → Location.LocationID
Shelf TEXT
Storage compartment within an inventory location.
Bin INTEGER
Storage container on a shelf in an inventory location.
Quantity INTEGER
Quantity of products in the inventory location.
ModifiedDate TEXT
Date and time the record was last updated.
ProductListPriceHistory 395 rows Changes in the list price of a product over time.
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID → Product.ProductID
StartDate TEXT
List price start date.
EndDate TEXT
List price end date
ListPrice REAL
Product list price.
ModifiedDate TEXT
Date and time the record was last updated.
ProductModel 128 rows Product model classification.
ProductModelID INTEGER
Primary key for ProductModel records.
Name TEXT
Product model description.
ModifiedDate TEXT
Date and time the record was last updated.
ProductModelProductDescriptionCulture 762 rows Cross-reference table mapping product descriptions and the language the description is written in.
ProductModelID INTEGER
Primary key. Foreign key to ProductModel.ProductModelID.
ProductDescriptionID INTEGER
Primary key. Foreign key to ProductDescription.ProductDescriptionID. → ProductDescription.ProductDescriptionID
CultureID TEXT
Culture identification number. Foreign key to Culture.CultureID.
ModifiedDate TEXT
Date and time the record was last updated.
ProductReview 4 rows Customer reviews of products they have purchased.
ProductReviewID INTEGER
Primary key for ProductReview records.
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID. → Product.ProductID
ReviewerName TEXT
Name of the reviewer.
ReviewDate TEXT
Date review was submitted.
EmailAddress TEXT
Reviewer's e-mail address.
Rating INTEGER
Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.
Comments TEXT
Reviewer's comments
ModifiedDate TEXT
Date and time the record was last updated.
ProductSubcategory 37 rows Product subcategories. See ProductCategory table.
ProductSubcategoryID INTEGER
Primary key for ProductSubcategory records.
ProductCategoryID INTEGER
Product category identification number. Foreign key to ProductCategory.ProductCategoryID. → ProductCategory.ProductCategoryID
Name TEXT
Subcategory description.
ModifiedDate TEXT
Date and time the record was last updated.
ScrapReason 16 rows Manufacturing failure reasons lookup table.
ScrapReasonID INTEGER
Primary key for ScrapReason records.
Name TEXT
Failure description.
ModifiedDate TEXT
Date and time the record was last updated.
TransactionHistory 112537 rows Record of each purchase order, sales order, or work order transaction year to date.
TransactionID INTEGER
Primary key for TransactionHistory records.
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID. → Product.ProductID
ReferenceOrderID INTEGER
Purchase order, sales order, or work order identification number.
ReferenceOrderLineID INTEGER
Line number associated with the purchase order, sales order, or work order.
TransactionDate TEXT
Date and time of the transaction.
TransactionType TEXT
W = WorkOrder, S = SalesOrder, P = PurchaseOrder
Quantity INTEGER
Product quantity.
ActualCost REAL
Product cost.
ModifiedDate TEXT
Date and time the record was last updated.
TransactionHistoryArchive 89253 rows Transactions for previous years.
TransactionID INTEGER
Primary key for TransactionHistoryArchive records.
ProductID INTEGER
Product identification number. Foreign key to Product.ProductID.
ReferenceOrderID INTEGER
Purchase order, sales order, or work order identification number.
ReferenceOrderLineID INTEGER
Line number associated with the purchase order, sales order, or work order.
TransactionDate TEXT
Date and time of the transaction.
TransactionType TEXT
W = Work Order, S = Sales Order, P = Purchase Order
Quantity INTEGER
Product quantity.
ActualCost REAL
Product cost.
ModifiedDate TEXT
Date and time the record was last updated.
UnitMeasure 38 rows Unit of measure lookup table.
UnitMeasureCode TEXT
Primary key.
Name TEXT
Unit of measure description.
ModifiedDate TEXT
Date and time the record was last updated.
WorkOrder 72591 rows
WorkOrderID INTEGER
ProductID INTEGER
→ Product.ProductID
OrderQty INTEGER
StockedQty INTEGER
ScrappedQty INTEGER
StartDate TEXT
EndDate TEXT
DueDate TEXT
ScrapReasonID INTEGER
→ ScrapReason.ScrapReasonID
ModifiedDate TEXT
WorkOrderRouting 67131 rows
WorkOrderID INTEGER
→ WorkOrder.WorkOrderID
ProductID INTEGER
OperationSequence INTEGER
LocationID INTEGER
→ Location.LocationID
ScheduledStartDate TEXT
ScheduledEndDate TEXT
ActualStartDate TEXT
ActualEndDate TEXT
ActualResourceHrs REAL
PlannedCost REAL
ActualCost REAL
ModifiedDate TEXT
Person (12)
Address 19614 rows Street address information for customers, employees, and vendors.
AddressID INTEGER
Primary key for Address records.
AddressLine1 TEXT
First street address line.
AddressLine2 TEXT
Second street address line.
City TEXT
Name of the city.
StateProvinceID INTEGER
Unique identification number for the state or province. Foreign key to StateProvince table. → StateProvince.StateProvinceID
PostalCode TEXT
Postal code for the street address.
ModifiedDate TEXT
Date and time the record was last updated.
AddressType 6 rows Types of addresses stored in the Address table.
AddressTypeID INTEGER
Primary key for AddressType records.
Name TEXT
Address type description. For example, Billing, Home, or Shipping.
ModifiedDate TEXT
Date and time the record was last updated.
BusinessEntity 20777 rows Source of the ID that connects vendors, customers, and employees with address and contact information.
BusinessEntityID INTEGER
Primary key for all customers, vendors, and employees.
ModifiedDate TEXT
Date and time the record was last updated.
BusinessEntityAddress 19614 rows Cross-reference table mapping customers, vendors, and employees to their addresses.
BusinessEntityID INTEGER
Primary key. Foreign key to BusinessEntity.BusinessEntityID. → BusinessEntity.BusinessEntityID
AddressID INTEGER
Primary key. Foreign key to Address.AddressID. → Address.AddressID
AddressTypeID INTEGER
Primary key. Foreign key to AddressType.AddressTypeID. → AddressType.AddressTypeID
ModifiedDate TEXT
Date and time the record was last updated.
BusinessEntityContact 909 rows Cross-reference table mapping stores, vendors, and employees to people
BusinessEntityID INTEGER
Primary key. Foreign key to BusinessEntity.BusinessEntityID. → BusinessEntity.BusinessEntityID
PersonID INTEGER
Primary key. Foreign key to Person.BusinessEntityID. → Person.BusinessEntityID
ContactTypeID INTEGER
Primary key. Foreign key to ContactType.ContactTypeID. → ContactType.ContactTypeID
ModifiedDate TEXT
Date and time the record was last updated.
ContactType 20 rows Lookup table containing the types of business entity contacts.
ContactTypeID INTEGER
Primary key for ContactType records.
Name TEXT
Contact type description.
ModifiedDate TEXT
Date and time the record was last updated.
CountryRegion 238 rows Lookup table containing the ISO standard codes for countries and regions.
CountryRegionCode TEXT
ISO standard code for countries and regions.
Name TEXT
Country or region name.
ModifiedDate TEXT
Date and time the record was last updated.
EmailAddress 19972 rows Where to send a person email.
BusinessEntityID INTEGER
Primary key. Person associated with this email address. Foreign key to Person.BusinessEntityID → Person.BusinessEntityID
EmailAddressID INTEGER
Primary key. ID of this email address.
EmailAddress TEXT
E-mail address for the person.
ModifiedDate TEXT
Date and time the record was last updated.
Person 19972 rows Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.
BusinessEntityID INTEGER
Primary key for Person records. → BusinessEntity.BusinessEntityID
PersonType TEXT
Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact
NameStyle INTEGER
0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.
Title TEXT
A courtesy title. For example, Mr. or Ms.
FirstName TEXT
First name of the person.
MiddleName TEXT
Middle name or middle initial of the person.
LastName TEXT
Last name of the person.
Suffix TEXT
Surname suffix. For example, Sr. or Jr.
EmailPromotion INTEGER
0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.
ModifiedDate TEXT
Date and time the record was last updated.
PersonPhone 19972 rows Telephone number and type of a person.
BusinessEntityID INTEGER
Business entity identification number. Foreign key to Person.BusinessEntityID. → Person.BusinessEntityID
PhoneNumber TEXT
Telephone number identification number.
PhoneNumberTypeID INTEGER
Kind of phone number. Foreign key to PhoneNumberType.PhoneNumberTypeID. → PhoneNumberType.PhoneNumberTypeID
ModifiedDate TEXT
Date and time the record was last updated.
PhoneNumberType 3 rows Type of phone number of a person.
PhoneNumberTypeID INTEGER
Primary key for telephone number type records.
Name TEXT
Name of the telephone number type
ModifiedDate TEXT
Date and time the record was last updated.
StateProvince 181 rows State and province lookup table.
StateProvinceID INTEGER
Primary key for StateProvince records.
StateProvinceCode TEXT
ISO standard state or province code.
CountryRegionCode TEXT
ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode. → CountryRegion.CountryRegionCode
IsOnlyStateProvinceFlag INTEGER
0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
Name TEXT
State or province description.
TerritoryID INTEGER
ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID. → SalesTerritory.TerritoryID
ModifiedDate TEXT
Date and time the record was last updated.
HumanResources (5)
Department 16 rows Lookup table containing the departments within the Adventure Works Cycles company.
DepartmentID INTEGER
Primary key for Department records.
Name TEXT
Name of the department.
GroupName TEXT
Name of the group to which the department belongs.
ModifiedDate TEXT
Date and time the record was last updated.
Employee 290 rows Employee information such as salary, department, and title.
BusinessEntityID INTEGER
Primary key for Employee records. Foreign key to BusinessEntity.BusinessEntityID. → Person.BusinessEntityID
LoginID TEXT
Network login.
OrganizationLevel INTEGER
The depth of the employee in the corporate hierarchy.
JobTitle TEXT
Work title such as Buyer or Sales Representative.
BirthDate TEXT
Date of birth.
MaritalStatus TEXT
M = Married, S = Single
Gender TEXT
M = Male, F = Female
HireDate TEXT
Employee hired on this date.
SalariedFlag INTEGER
Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
VacationHours INTEGER
Number of available vacation hours.
SickLeaveHours INTEGER
Number of available sick leave hours.
CurrentFlag INTEGER
0 = Inactive, 1 = Active
ModifiedDate TEXT
Date and time the record was last updated.
EmployeeDepartmentHistory 296 rows Employee department transfers.
BusinessEntityID INTEGER
Employee identification number. Foreign key to Employee.BusinessEntityID. → Employee.BusinessEntityID
DepartmentID INTEGER
Department in which the employee worked including currently. Foreign key to Department.DepartmentID. → Department.DepartmentID
ShiftID INTEGER
Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. → Shift.ShiftID
StartDate TEXT
Date the employee started work in the department.
EndDate TEXT
Date the employee left the department. NULL = Current department.
ModifiedDate TEXT
Date and time the record was last updated.
EmployeePayHistory 316 rows Employee pay history.
BusinessEntityID INTEGER
Employee identification number. Foreign key to Employee.BusinessEntityID. → Employee.BusinessEntityID
RateChangeDate TEXT
Date the change in pay is effective
Rate REAL
Salary hourly rate.
PayFrequency INTEGER
1 = Salary received monthly, 2 = Salary received biweekly
ModifiedDate TEXT
Date and time the record was last updated.
Shift 3 rows Work shift lookup table.
ShiftID INTEGER
Primary key for Shift records.
Name TEXT
Shift description.
StartTime TEXT
Shift start time.
EndTime TEXT
Shift end time.
ModifiedDate TEXT
Date and time the record was last updated.