Example queries against this table

1. Get order items with product names and order info

   SQLCopy

   SELECT
     o.order_id,
     c.name AS customer_name,
     p.name AS product_name,
     oi.qty,
     oi.unit_price,
     (oi.qty * oi.unit_price) AS line_total
   FROM order_items oi
   JOIN orders o ON oi.order_id = o.order_id
   JOIN customers c ON o.cust_id = c.cust_id
   JOIN products p ON oi.product_id = p.product_id;

2. 4-table JOIN: order details with all info

   SQLCopy

   SELECT
     o.order_id,
     cu.name AS customer,
     cu.city,
     p.name AS product,
     cat.name AS category,
     oi.qty,
     oi.unit_price,
     (oi.qty * oi.unit_price * (1 - oi.discount/100)) AS net_amount
   FROM orders o
   JOIN customers cu  ON o.cust_id = cu.cust_id
   JOIN order_items oi ON o.order_id = oi.order_id
   JOIN products p    ON oi.product_id = p.product_id
   JOIN categories cat ON p.cat_id = cat.cat_id
   WHERE o.status = 'completed'
   ORDER BY o.order_date DESC;

3. 5-table JOIN: full order report

   SQLCopy

   SELECT
     o.order_id,
     cu.name AS customer,
     e.first_name || ' ' || e.last_name AS sales_rep,
     d.dept_name,
     p.name AS product,
     cat.name AS category,
     oi.qty,
     oi.unit_price,
     o.order_date,
     o.status
   FROM orders o
   JOIN customers cu     ON o.cust_id = cu.cust_id
   JOIN order_items oi   ON o.order_id = oi.order_id
   JOIN products p       ON oi.product_id = p.product_id
   JOIN categories cat   ON p.cat_id = cat.cat_id
   LEFT JOIN employees e ON o.emp_id = e.emp_id  -- nullable FK
   LEFT JOIN departments d ON e.dept_id = d.dept_id
   ORDER BY o.order_date DESC
   LIMIT 100;

4. Find products never ordered (LEFT anti-join)

   SQLCopy

   SELECT
     p.product_id,
     p.name,
     p.price,
     p.stock
   FROM products p
   LEFT JOIN order_items oi ON p.product_id = oi.product_id
   WHERE oi.item_id IS NULL
   ORDER BY p.stock DESC;