SQL Project: Shop Report
A shop report is a summary read from the shop's own tables. One script adds a new product and totals the money from every order.
Label the stock
CASE gives each product a stock label. The label depends on the stock count in the same row.
SELECT name, CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 50 THEN 'Low' ELSE 'In stock' END AS stock_label FROM products ORDER BY product_id;Add the new product
The script adds a Travel Mug to products. The SELECT shows the new row with its stock label.
INSERT INTO products VALUES (9, 'Travel Mug', 'Kitchen', 11.50, 25);
SELECT name, CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 50 THEN 'Low' ELSE 'In stock' END AS stock_label FROM products WHERE product_id = 9;Example
INSERT INTO products VALUES (9, 'Travel Mug', 'Kitchen', 11.50, 25);
SELECT category, ROUND(SUM(p.price * o.quantity), 2) AS revenue FROM orders o JOIN products p ON p.product_id = o.product_id GROUP BY category HAVING revenue > 50 ORDER BY revenue DESC;Key points
- A report query joins the tables it needs.
- GROUP BY totals the joined rows per group.
- HAVING keeps only the groups that pass its test.
- ORDER BY can sort by an alias from the SELECT list.
In this example, the script first adds the Travel Mug to products. The SELECT joins orders to products, so each order line gains its price. SUM totals price times quantity for each category, and HAVING keeps the categories above 50. ORDER BY revenue DESC puts the biggest total first.
Try it Yourself
Edit the code, then press Run.
Running is simulated · DB Fiddle
Exercise
In the report query, what does HAVING revenue > 50 do?