SQL practice projects in your browser

List each customer with net paid revenue. Include customers with no paid orders. Deduct refunds and exclude cancelled orders. Order by net_revenue descending, then name.

SELECT c.name, COALESCE(SUM(CASE WHEN o.status='paid' THEN o.gross-o.refund ELSE 0 END),0) AS net_revenue FROM customers c LEFT JOIN orders o ON o.customer_id=c.id GROUP BY c.id,c.name ORDER BY net_revenue DESC,c.name;

LEFT JOIN preserves customers without orders. The CASE lives inside SUM so cancelled orders do not remove their customer. Refunds reduce paid revenue.

Find items whose stock across all locations is below reorder_point. Include items without stock rows as zero. Output sku, available, reorder_point, ordered by sku.

SELECT i.sku,COALESCE(SUM(s.units),0) AS available,i.reorder_point FROM items i LEFT JOIN stock s ON s.sku=i.sku GROUP BY i.sku,i.reorder_point HAVING COALESCE(SUM(s.units),0)<i.reorder_point ORDER BY i.sku;

Aggregate locations before testing the reorder threshold. HAVING filters groups; COALESCE treats no stock rows as zero. Stock equal to the threshold is not below it.

Find vendor/invoice pairs recorded more than once. Output vendor, invoice, copies, amount_total, ordered by vendor and invoice. Different vendors can use the same invoice number.

SELECT vendor,invoice,COUNT(*) AS copies,SUM(amount) AS amount_total FROM invoices GROUP BY vendor,invoice HAVING COUNT(*)>1 ORDER BY vendor,invoice;

The duplicate key includes vendor and invoice. Grouping only by invoice would incorrectly merge distinct suppliers. Exceptions still need a human review; this does not authorize payments.