Hi! Could we please enable some services and cookies to improve your experience and our website?

Privacy & Cookie Policy.
SQL code copied to buffer

SQLtest.online

Practice

Lessons

Test

RU PT FR
Task 198:

What bought Jon Grande?

Find all products ordered by Jon Grande anytime. Get sorted list of unique product names.

Use SQL Server 2022 syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.
Get hint
Copy code Clear editor

AdventureWorks Database: table structure and schema overview

The AdventureWorks database (SQL Server) is a sample dataset that models business processes of a fictional manufacturing company.

This page presents table structure, key columns, and relationships used for practical SQL learning and query practice.

The AdventureWorks database contains 10 main tables.

ER diagram of the AdventureWorks database showing table relationships AdventureWorks DB ER diagram

List of tables

Address - table of addresses.
  • AddressIDunique identifier for each address (PK)
  • AddressLine1the first line of the address
  • AddressLine2the second line of the address
  • Citycity
  • StateProvincestate or province
  • CountryRegioncountry
  • PostalCodepostal code
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (AddressID)
AddressID AddressLine1 AddressLine2 City StateProvince CountryRegion PostalCode rowguid ModifiedDate
9 8713 Yosemite Ct. null Bothell Washington United States 98011 268AF621-76D7-4C78-9441-144FD139821A 2006-07-01 00:00:00.000
Customer - table of customers.
  • CustomerIDunique identifier for each customer (PK)
  • NameStyle0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order. Default: 0
  • Titletitle
  • FirstNamename
  • MiddleNamemiddle name
  • LastNamelast name
  • Suffixsuffix
  • CompanyNamecompany name
  • SalesPersonSalesPerson
  • EmailAddressE-mail
  • Phonephone number
  • PasswordHashpassword hash
  • PasswordSaltsalt
  • rowguidrowguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (CustomerID)
CustomerID NameStyle Title FirstName MiddleName LastName Suffix CompanyName SalesPerson EmailAddress Phone PasswordHash PasswordSalt rowguid ModifiedDate
1 0 Mr. Orlando N. Gee [null] A Bike Store adventure-works\pamela0 orlando0@adventure-works.com 245-555-0173 L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w= 1KjXYs4= 3F5AE95E-B87D-4AED-95B4-C3797AFCB74F 2005-08-01 00:00:00.000
CustomerAddress - customer to address relations.
  • CustomerIDidentifier of client in the Customer table
  • AddressIDidentifier of address in the Address table
  • AddressTypeaddress type
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (CustomerID, AddressID)
  • FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
  • FOREIGN KEY (AddressID) REFERENCES Address(AddressID)
CustomerID AddressID AddressType rowguid ModifiedDate
29485 1086 Main Office 16765338-DBE4-4421-B5E9-3836B9278E63 2007-09-01 00:00:00.000
Product - table of products.
  • ProductIDunique identifier for each product (PK)
  • Nameproduct name
  • ProductNumberarticle number
  • Colorproduct color
  • StandardCostproduct price
  • ListPriceproduct price in the catalogue
  • Sizeproduct size
  • Weightproduct weight
  • ProductCategoryIDforeign key pointing to ProductCategory table
  • ProductModelIDforeign key pointing to ProductModel table
  • SellStartDatetimestamp of the sales start date
  • SellEndDatetimestamp of the sales end date
  • DiscontinuedDatetimestamp of the sales end date
  • ThumbNailPhotothumbnail photo of the product
  • ThumbnailPhotoFileName
    name of the photo thumbnail file
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (ProductID, ProductCategoryID, ProductModelID)
  • FOREIGN KEY (ProductCategoryID) REFERENCES ProductCategory(ProductCategoryID)
  • FOREIGN KEY (ProductModelID) REFERENCES ProductModel(ProductModelID)
ProductID Name ProductNumber Color StandardCost ListPrice Size Weight ProductCategoryID ProductModelID SellStartDate SellEndDate DiscontinuedDate ThumbNailPhoto ThumbnailPhotoFileName rowguid ModifiedDate
680 HL Road Frame - Black, 58 FR-R92B-58 Black 1059.3100 1431.5000 58 1016.04 18 6 2002-06-01 00:00:00.000 [null] [null] [binary] no_image_available_small.gif 43DD68D6-14A4-461F-9069-55309D90EA7E 2008-03-11 10:01:36.827
ProductCategory - table of product categories.
  • ProductCategoryIDunique identifier for each product category (PK)
  • ParentProductCategoryIDID of the parent product category
  • Namename of the product category
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (ProductCategoryID)
  • FOREIGN KEY (ParentProductCategoryID) REFERENCES ProductCategory(ProductCategoryID)
ProductCategoryID ParentProductCategoryID Name rowguid ModifiedDate
1 [null] Bikes CFBDA25C-DF71-47A7-B81B-64EE161AA37C 2002-06-01 00:00:00.000
ProductDescription - table of product descriptions.
  • ProductDescriptionIDunique ID for record (PK)
  • Descriptionproduct description
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (ProductDescriptionID)
ProductDescriptionID Description rowguid ModifiedDate
4 Aluminum alloy cups; large diameter spindle. DFEBA528-DA11-4650-9D86-CAFDA7294EB0 2007-06-01 00:00:00.000
ProductModel - table of product models.
  • ProductModelIDunique ID for each record (PK)
  • Namename of the product model
  • CatalogDescriptiondescription in XML format
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (ProductModelID)
ProductModelID Name CatalogDescription rowguid ModifiedDate
1 Classic Vest [null] 29321D47-1E4C-4AAC-887C-19634328C25E 2007-06-01 00:00:00.000
ProductModelProductDescription - table of product models descriptions.
  • ProductModelIDID of client in the ProductModel table
  • ProductDescriptionIDID of address in the ProductDescription table
  • Culturelanguage code in ISO format
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (ProductModelID, ProductDescriptionID)
  • FOREIGN KEY (ProductModelID) REFERENCES ProductModel(ProductModelID)
  • FOREIGN KEY (ProductDescriptionID) REFERENCES ProductDescription(ProductDescriptionID)
ProductModelID ProductDescriptionID Culture rowguid ModifiedDate
1 1199 en 4D00B649-027A-4F99-A380-F22A46EC8638 2007-06-01 00:00:00.000
SalesOrderDetail - table of sales orders details.
  • SalesOrderIDforeign key referencing the SalesOrderHeader table
  • SalesOrderDetailIDunique identifier of record in the table
  • OrderQtyquantity
  • ProductIDa foreign key referencing the Product table
  • UnitPriceprice per unit of goods
  • UnitPriceDiscountprice per unit of product with a discount
  • LineTotaltotal amount by line
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (SalesOrderID, SalesOrderDetailID, ProductID)
  • FOREIGN KEY (SalesOrderID) REFERENCES SalesOrderHeader(SalesOrderID)
  • FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
SalesOrderID SalesOrderDetailID OrderQty ProductID UnitPrice UnitPriceDiscount LineTotal rowguid ModifiedDate
71774 110562 1 836 356.8980 .0000 356.898000 E3A1994C-7A68-4CE8-96A3-77FDD3BBD730 2008-06-01 00:00:00.000
SalesOrderHeader - product sales orders.
  • SalesOrderIDunique identifier of record in the table (PK)
  • RevisionNumberrevision number
  • OrderDatetimestamp for creating the order date
  • DueDatetimestamp of the order payment date
  • ShipDatetimestamp of the date the order was shipped
  • Statusorder status
  • OnlineOrderFlagonline order (yes/no)
  • SalesOrderNumberorder number
  • PurchaseOrderNumberpurchase number
  • AccountNumberaccount number
  • CustomerIDforeign key referencing the Customer table
  • ShipToAddressIDforeign key referencing the Address table defines the delivery address
  • BillToAddressIDforeign key referencing the Address table defines the account address
  • ShipMethoddelivery method
  • CreditCardApprovalCode
    credit card confirmation code
  • SubTotalsubtotal
  • TaxAmttaxes
  • Freightdelivery cost
  • TotalDuetotal
  • Commentcomment
  • rowguidguid
  • ModifiedDatetimestamp of row creation or last update
  • PRIMARY KEY, btree (SalesOrderID, CustomerID, ShipToAddressID, BillToAddressID)
  • FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
  • FOREIGN KEY (ShipToAddressID) REFERENCES Address(AddressID)
  • FOREIGN KEY (BillToAddressID) REFERENCES Address(AddressID)
SalesOrderID RevisionNumber OrderDate DueDate ShipDate Status OnlineOrderFlag SalesOrderNumber PurchaseOrderNumber AccountNumber CustomerID ShipToAddressID BillToAddressID ShipMethod CreditCardApprovalCode SubTotal TaxAmt Freight TotalDue Comment rowguid ModifiedDate
71774 2 2008-06-01 00:00:00.000 2008-06-13 00:00:00.000 2008-06-08 00:00:00.000 5 0 SO71774 PO348186287 10-4020-000609 29847 1092 1092 CARGO TRANSPORT 5 [null] 880.3484 70.4279 22.0087 972.7850 [null] 89E42CDC-8506-48A2-B89B-EB3E64E3554E 2008-06-08 00:00:00.000
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
SQL Essentials for Data Analysis: A 50-Day Hands-on Challenge Book by Benjamin Bennett Alexander
If you’re wondering whether you should learn SQL, the answer is simple: absolutely. SQL (Structured Query Language) is the language of data, and data powers every modern business. Whether you’re a data analyst, data scientist, business analyst, software engineer, financial analyst, product manager, or marketing professional, SQL helps you retrieve, analyze, and understand the information that drives decisions.