Which SQL statement retrieves the latest last_stocked_date for each category where stock_quantity is less than 50?

Which SQL statement retrieves the latest last_stocked_date for each category where stock_quantity is less than 50?

• A. SELECT category, MAX(last_stocked_date) FROM inventory WHERE stock_quantity < 50 GROUP BY category
• B. SELECT category, MAX(last_stocked_date) FROM inventory WHERE stock_quantity <= 50 GROUP BY category
• C. SELECT category, MAX(last_stocked_date) FROM inventory WHERE stock_quantity < 50 GROUP BY last_stocked_date
• D. SELECT category, MAX(last_stocked_date) FROM inventory

Answer: (C)

To get the most recent last_stocked_date for each category while only including items with stock_quantity under 50, combine a filter with an aggregate by category. The correct statement uses MAX(last_stocked_date) to pick the latest date, filters with stock_quantity < 50, and groups by category: SELECT category, MAX(last_stocked_date) AS latest_stocked FROM inventory WHERE stock_quantity < 50 GROUP BY category; MAX on a date type returns the latest date, and the GROUP BY category ensures you get one result per category. The WHERE clause enforces the strict inequality, so items with exactly 50 are excluded. Using ≤ 50 would include 50, which isn’t allowed, and grouping by last_stocked_date would produce rows keyed by dates rather than by category. Without a GROUP BY, you'd aggregate across the whole table instead of per category.

To get the most recent last_stocked_date for each category while only including items with stock_quantity under 50, combine a filter with an aggregate by category. The correct statement uses MAX(last_stocked_date) to pick the latest date, filters with stock_quantity < 50, and groups by category:

SELECT category, MAX(last_stocked_date) AS latest_stocked FROM inventory WHERE stock_quantity < 50 GROUP BY category;

MAX on a date type returns the latest date, and the GROUP BY category ensures you get one result per category. The WHERE clause enforces the strict inequality, so items with exactly 50 are excluded. Using ≤ 50 would include 50, which isn’t allowed, and grouping by last_stocked_date would produce rows keyed by dates rather than by category. Without a GROUP BY, you'd aggregate across the whole table instead of per category.