SQL code copied to buffer

SQLtest: interactive SQL practice

Sharpen your SQL skills with our interactive exercises!
Practice a wide range of SQL tasks, from basic queries to advanced techniques. Get immediate feedback on your solutions, helping you learn from mistakes and improve your skills. Boost your confidence for job interviews and real-world SQL applications.
RU PT
Task  12: 
From table ProductCategory select list of categories with their sub-categories.
Output the result in two columns Category - the name of root category and SubCategories - a comma-separated, sorted list of all sub-categies names
Sort the result by first column.

Write your request in the field below and click the "Check it!" button.

To write the answer, use SQL Server 2022 syntax. Descriptions of the tables are given in the right panel.

Explore over 280 diverse tasks on our platform.

Log in to save your progress.

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.

AdventureWorks DB ER diagram

The following is a list of these tables:

  • Address - table of addresses.
  • Customer - table of customers
  • CustomerAddress - table of customer tot address relations.
  • Product - table of products.
  • ProductCategory - table of product categories.
  • ProductDescription - table of product descriptions.
  • ProductModel - table of product models.
  • ProductModelProductDescription - table of product models descriptions.
  • SalesOrderDetail - table of product sales orders.
  • SalesOrderHeader - table of product sales orders details.
T-SQL Fundamentals by Itzik Ben-Gan
T-SQL Fundamentals by Itzik Ben-Gan
Master Transact-SQL's fundamentals, and write correct, robust code for querying and modifying data with modern Microsoft data technologies, including SQL Server 2022, Azure SQL Database, and Azure SQL Managed Instance.

Table Address

Columns:
  • AddressID - a unique identifier for each address.
  • AddressLine1 - the first line of the address.
  • AddressLine2 - the second line of the address.
  • StateProvince - city.
  • CountryRegion - country.
  • PostalCode - postal code.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
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
Indexes:
  • PRIMARY KEY, btree (AddressID)

Table Customer

Columns:
  • CustomerID - a unique identifier for each customer.
  • NameStyle - 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. Default: 0.
  • Title - title.
  • FirstName - name.
  • MiddleName - middle name.
  • LastName - last name.
  • Suffix - suffix.
  • CompanyName - company name.
  • SalesPerson - SalesPerson.
  • EmailAddress - E-mail.
  • Phone - phone number.
  • PasswordHash - password hash.
  • PasswordSalt - salt.
  • rowguid - rowguid.
  • ModifiedDate - timestamp of row creation or last update.
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
Indexes:
  • PRIMARY KEY, btree (CustomerID)

Table CustomerAddress

Columns:
  • CustomerID - unique identifier of client in the table Customer.
  • AddressID - unique identifier of address in the table Address.
  • AddressType - address type.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
CustomerID AddressID AddressType rowguid ModifiedDate
29485 1086 Main Office 16765338-DBE4-4421-B5E9-3836B9278E63 2007-09-01 00:00:00.000
Indexes:
  • PRIMARY KEY, btree (CustomerID, AddressID)

Table Product

Columns:
  • ProductID - a unique identifier for each product.
  • Name - product name.
  • ProductNumber - article number.
  • Color - product color.
  • StandardCost - product price.
  • ListPrice - product price in the catalogue.
  • Size - product size.
  • Weight - product weight.
  • ProductCategoryID - foreign key pointing to table ProductCategory - defines the product category.
  • ProductModelID - foreign key pointing to table ProductModel - defines the product model.
  • SellStartDate - timestamp of the sales start date.
  • SellEndDate - timestamp of the sales end date.
  • DiscontinuedDate - timestamp of the sales end date.
  • ThumbNailPhoto - thumbnail photo of the product.
  • ThumbnailPhotoFileName - name of the photo thumbnail file.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
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
Indexes:
  • PRIMARY KEY, btree (ProductID, ProductCategoryID, ProductModelID)

Table ProductCategory

Columns:
  • ProductCategoryID - a unique identifier for each product category.
  • ParentProductCategoryID - identifier of the parent product category.
  • Name - name of the product category.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
ProductCategoryID ParentProductCategoryID Name rowguid ModifiedDate
1 [null] Bikes CFBDA25C-DF71-47A7-B81B-64EE161AA37C 2002-06-01 00:00:00.000
Indexes:
  • PRIMARY KEY, btree (ProductCategoryID)

Table ProductDescription

Columns:
  • ProductDescriptionID - a unique identifier for each product description.
  • Description - product description.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
ProductDescriptionID Description rowguid ModifiedDate
4 Aluminum alloy cups; large diameter spindle. DFEBA528-DA11-4650-9D86-CAFDA7294EB0 2007-06-01 00:00:00.000
Indexes:
  • PRIMARY KEY, btree (ProductDescriptionID)

Table ProductModel

Columns:
  • ProductModelID - a unique identifier for each product model.
  • Name - name of the product model.
  • CatalogDescription - description in XML format.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
ProductModelID Name CatalogDescription rowguid ModifiedDate
1 Classic Vest [null] 29321D47-1E4C-4AAC-887C-19634328C25E 2007-06-01 00:00:00.000
Indexes:
  • PRIMARY KEY, btree (ProductModelID)

Table ProductModelProductDescription

Columns:
  • ProductModelID - unique identifier of client in the table ProductModel.
  • ProductDescriptionID - unique identifier of address in the table ProductDescription.
  • Culture - language code in ISO format.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
ProductModelID ProductDescriptionID Culture rowguid ModifiedDate
1 1199 en 4D00B649-027A-4F99-A380-F22A46EC8638 2007-06-01 00:00:00.000
Indexes:
  • PRIMARY KEY, btree (ProductModelID, ProductDescriptionID)

Table SalesOrderDetail

Columns:
  • SalesOrderID - foreign key referencing table SalesOrderHeader.
  • SalesOrderDetailID - a unique identifier of record in the table.
  • OrderQty - quantity.
  • ProductID - a foreign key referencing the table Product.
  • UnitPrice - price per unit of goods.
  • UnitPriceDiscount - price per unit of product with a discount.
  • LineTotal - Total.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
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
Indexes:
  • PRIMARY KEY, btree (SalesOrderID, SalesOrderDetailID, ProductID)

Table SalesOrderHeader

Columns:
  • SalesOrderID - a unique identifier of record in the table.
  • RevisionNumber - revision number.
  • OrderDate - timestamp for creating the order date.
  • DueDate - timestamp of the order payment date.
  • ShipDate - timestamp of the date the order was shipped.
  • Status - order status.
  • OnlineOrderFlag - online order (yes/no).
  • SalesOrderNumber - order number.
  • PurchaseOrderNumber - purchase number.
  • AccountNumber - account number.
  • CustomerID - foreign key referencing the table Customer - defines the client.
  • ShipToAddressID - foreign key referencing table Address - defines the delivery address.
  • BillToAddressID - foreign key referencing table Address - defines the account address.
  • ShipMethod - delivery method.
  • CreditCardApprovalCode - credit card confirmation code.
  • SubTotal - subtotal.
  • TaxAmt - taxes.
  • Freight - delivery cost.
  • TotalDue - total.
  • Comment - comment.
  • rowguid - guid.
  • ModifiedDate - timestamp of row creation or last update.
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
Indexes:
  • PRIMARY KEY, btree (SalesOrderID, CustomerID, ShipToAddressID, BillToAddressID)
Have a questions? Ask in our telegram chat!
Like the site? Share it!
© 2023-2024 SQLtest.online About Privacy policy Books