Question

Write a query to display (product_class_desc, product_id, product_desc, product_quantity_avail ) and Show inventory status of products as below as per their available quantity: a. For Electronics and Computer categories, if available quantity is <= 10, show 'Low stock', 11 <= qty <= 30, show 'In stock', >= 31, show 'Enough stock' b. For Stationery and Clothes categories, if qty <= 20, show 'Low stock', 21 <= qty <= 80, show 'In stock', >= 81, show 'Enough stock' c. Rest of the categories, if qty <= 15 – 'Low Stock', 16 <= qty <= 50 – 'In Stock', >= 51 – 'Enough stock' For all categories, if available quantity is 0, show 'Out of stock'. Hint: Use case statement.

Answer 1

Assuming we have the following tables:

products: contains information about products

product_classes: contains information about product classes and their descriptions

inventory: contains information about the inventory status of each product

Here is the SQL query to display the requested information:

Step-by-step explanation:

SELECT pc.product_class_desc, p.product_id, p.product_desc, i.product_quantity_avail,

CASE pc.product_class_desc

WHEN 'Electronics' THEN

CASE

WHEN i.product_quantity_avail = 0 THEN 'Out of stock'

WHEN i.product_quantity_avail <= 10 THEN 'Low stock'

WHEN i.product_quantity_avail <= 30 THEN 'In stock'

ELSE 'Enough stock'

END

WHEN 'Computer' THEN

CASE

WHEN i.product_quantity_avail = 0 THEN 'Out of stock'

WHEN i.product_quantity_avail <= 10 THEN 'Low stock'

WHEN i.product_quantity_avail <= 30 THEN 'In stock'

ELSE 'Enough stock'

END

WHEN 'Stationery' THEN

CASE

WHEN i.product_quantity_avail = 0 THEN 'Out of stock'

WHEN i.product_quantity_avail <= 20 THEN 'Low stock'

WHEN i.product_quantity_avail <= 80 THEN 'In stock'

ELSE 'Enough stock'

END

WHEN 'Clothes' THEN

CASE

WHEN i.product_quantity_avail = 0 THEN 'Out of stock'

WHEN i.product_quantity_avail <= 20 THEN 'Low stock'

WHEN i.product_quantity_avail <= 80 THEN 'In stock'

ELSE 'Enough stock'

END

ELSE

CASE

WHEN i.product_quantity_avail = 0 THEN 'Out of stock'

WHEN i.product_quantity_avail <= 15 THEN 'Low stock'

WHEN i.product_quantity_avail <= 50 THEN 'In stock'

ELSE 'Enough stock'

END

END AS inventory_status

FROM products p

JOIN product_classes pc ON p.product_class_id = pc.product_class_id

JOIN inventory i ON p.product_id = i.product_id;