SQL Practice: Inventory Reorder List
Build an operations list that shows which active products need restocking first.
Inventory queries are where filters and calculated columns become practical. A buyer does not need every product. They need active products that are below their reorder level, with the biggest shortfalls first.
The product table has the stock numbers. The category table adds readable context so the reorder list is easier to scan.
Separate stock rules from display columns
The rule is purely numeric: quantity_on_hand < reorder_level. The calculated units_short column turns that rule into a prioritized work queue.
select item_name,
quantity_on_hand,
reorder_level,
reorder_level - quantity_on_hand as units_short
from inventory
where discontinued = false
and quantity_on_hand < reorder_level
order by units_short descSchema · Garden ShopTable · products9 columns · 24 rows
One row per product, with price, cost, and inventory levels.
Return active products that need restocking. Includeproduct_name, category_name,quantity_on_hand, reorder_level, andunits_short. Sort by units_short descending, then product name.
- Columns: product_name, category_name, quantity_on_hand, reorder_level, units_short.
- Rows: active products below their reorder level.
- The biggest shortfall should appear first.