Find the products whose in-stock quantities are less than double the average ordered quantity.

SELECT Name, Description
FROM Products WHERE Quantity <  2 * (
   SELECT AVG( Quantity )
   FROM SalesOrderItems );

This is a two-step query: first, find the average number of items requested per order; and then find which products in stock number less than double that quantity.

The Quantity column of the SalesOrderItems table stores the number of items requested per item type, customer, and order. The subquery is

SELECT AVG( Quantity )
FROM SalesOrderItems;

It returns the average quantity of items in the SalesOrderItems table, which is 25.851413.

The next query returns the names and descriptions of the items whose in-stock quantities are less than twice the previously-extracted value.

SELECT Name, Description
FROM Products
WHERE Quantity < 2*25.851413;

Using a subquery combines the two steps into a single operation.

A subquery in the WHERE clause is part of a search condition. The section Querying data describes simple search conditions you can use in the WHERE clause.