Lesson 14.2

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 doesn't 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.

Pattern

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 desc

Schema · Garden Shop Tables · products, categories 2 tables

Table · products

One row per product, with price, cost, and inventory levels.

9 columns · 24 rows

product_id int product_name text category_id int supplier_id int price decimal cost decimal quantity_on_hand int reorder_level int discontinued bool

Table · categories

Lookup table of product categories.

2 columns · 8 rows

category_id int category_name text

Your task

Return active products that need restocking. Include product_name, category_name, quantity_on_hand, reorder_level, and units_short. Sort by units_short descending, then product name.

Garden Shop · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

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.

Previous · 14.1 Daily revenue report

Next · 14.3 Customer lifetime revenue →

Reference

SQL WHERE Operators

Comparisons, AND/OR, IN, BETWEEN, and LIKE.

Cheatsheet

SQL Query Basics Cheatsheet

SELECT, WHERE, ORDER BY, and LIMIT at a glance.

Reference

SQL Set Operations

UNION ALL, UNION, INTERSECT, and EXCEPT.

Lesson