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;