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 65: 
What is a subquery?

Mark all correct answers and click the "Check!" button

Get hint

AdventureWorks Database (SQL Server)

The AdventureWorks Database is a sample database that demonstrates the capabilities of SQL Server. It includes data about fictional manufacturing company.

ER diagram of the AdventureWorks database AdventureWorks DB ER diagram

The list of tables:

The following is a list of DB 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)
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)
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)
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)
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)
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)
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