SQL code copied to buffer

SQLtest.online
Task : 
Write a query to find pairs of products that are frequently purchased together in the same SalesOrder. Retrieve pairs that have been sold more than 5 times. 
Output the results with columns ProductName1, ProductName2, and PairsCount, ordered by PairsCount in descending order, and then by ProductName1 and ProductName2 in ascending order.

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

Use SQL Server 2022 syntax to write your answer. Descriptions of tables are provided at the bottom of the screen.

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.

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
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.