SQL code copied to buffer

SQLtest interactive SQL practice

Sharpen your SQL skills with our interactive exercises and assessments!
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
Explore over 300 diverse tasks on our platform.
Task 43: 
In the previous task you found a list of root categories. This time, find the number of subcategories in each of them.
Form a resulting table with columns corresponding to the names of the root categories in alphabetical order and one row of data containing the number of subcategories in each of them.

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 (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 DB tables:

Address - table of addresses.
  • AddressIDa unique identifier for each address (PK).
  • AddressLine1the first line of the address.
  • AddressLine2the second line of the address.
  • StateProvincecity.
  • 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.
  • CustomerIDa unique 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.
  • ProductIDa unique 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.
  • ProductCategoryIDa unique identifier for each product category (PK).
  • ParentProductCategoryID
    identifier 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.
  • ProductDescriptionIDa unique identifier 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.
  • ProductModelIDa unique identifier for each product model (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.
  • ProductModelIDidentifier of client in the ProductModel table.
  • ProductDescriptionIDidentifier 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.
  • SalesOrderDetailIDa unique 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.
  • SalesOrderIDa unique 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
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.