AI Hints
Homeโ€บSQL Tutorialโ€บProject: The Stock Alert
SQL ยท Lesson 87 of 126

Project 3: The Stock Alert

Which books to reorder.

โฑ 2 min readโ–ถ Video at 2:03:07๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Nothing annoys a customer more than a book that's out of stock. But ordering too many copies ties up Helen's money on the shelves. She needs to know which books will run out soon, and which ones are just sitting there. Let's build a stock alert.

Project 3: The Stock Alert
From the video: Project 3: The Stock Alert
  • Out of stock loses sales
  • Too much stock wastes money
  • Build a stock alert

The lowest stock

Start simple. Which books have the fewest copies left? Run it. The Quiet Kitchen has sold out completely. Spice Roads has only two left. But low stock alone doesn't tell the whole story.

query.sqlsql
SELECT title, stock
FROM books
ORDER BY stock
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚ stock โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Quiet Kitchen   โ”‚ 0     โ”‚
โ”‚ Spice Roads         โ”‚ 2     โ”‚
โ”‚ Data for Everyone   โ”‚ 3     โ”‚
โ”‚ The Last Lighthouse โ”‚ 5     โ”‚
โ”‚ The Painted Door    โ”‚ 8     โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Money on the shelves

How much money is sitting on Helen's shelves? Stock times price, added up. Run it. About fourteen thousand two hundred dollars' worth of books.

query.sqlsql
SELECT ROUND(SUM(stock * price), 2) AS stock_value
FROM books;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ stock_value โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 14211.45    โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

What is selling now?

Low stock only matters if a book is selling. So let's look at sales in the last three months, October to December. Run it. Dragons of Ember Bay sold forty two copies, but only twenty eight are left. That's a warning sign. The Last Lighthouse sold twenty two, with only five left.

query.sqlsql
SELECT b.title,
       b.stock,
       SUM(oi.quantity) AS sold_last_3_months
FROM books AS b
JOIN order_items AS oi ON oi.book_id  = b.book_id
JOIN orders      AS o  ON o.order_id  = oi.order_id
WHERE o.order_date >= '2025-10-01'
  AND o.status IN ('delivered', 'shipped')
GROUP BY b.book_id
ORDER BY sold_last_3_months DESC
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title         โ”‚ stock โ”‚ sold_last_3_months โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Dragons of Ember Bay โ”‚ 28    โ”‚ 42                 โ”‚
โ”‚ Code of the River    โ”‚ 26    โ”‚ 26                 โ”‚
โ”‚ Stars Over Lagos     โ”‚ 47    โ”‚ 23                 โ”‚
โ”‚ Crown of Ash         โ”‚ 31    โ”‚ 22                 โ”‚
โ”‚ The Last Lighthouse  โ”‚ 5     โ”‚ 22                 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Thinking like a buyer

Before we build the alert, think like Helen's stock buyer. A book with low stock and fast sales is urgent. A book with high stock and slow sales is wasted money. A book with low stock and slow sales is fine. And a book with high stock and fast sales is perfect. Our query needs both numbers, stock and speed.

Low stock, fast salesreorder now
High stock, slow salesput on sale
Low stock, slow salesfine for now
High stock, fast salesperfect

Summary

Stock tells you what's left, and recent sales tell you how fast it's going. Next, combining them into one alert.

Key takeaways
  • Low stock
  • Stock value
  • Recent sales
Next up: Months of Stock Left โ€” the reorder alert