Example queries against this table

1. Get all products with their category names

   SQLCopy

   SELECT
     p.product_id,
     p.name AS product_name,
     c.name AS category_name,
     p.price
   FROM products p
   JOIN categories c ON p.cat_id = c.cat_id
   ORDER BY c.name, p.name;

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. Products sold per category

   SQLCopy

   SELECT
     c.name AS category,
     COUNT(DISTINCT p.product_id) AS product_count,
     SUM(oi.qty) AS total_units_sold,
     SUM(oi.qty * oi.unit_price) AS total_revenue
   FROM categories c
   LEFT JOIN products p ON c.cat_id = p.cat_id
   LEFT JOIN order_items oi ON p.product_id = oi.product_id
   GROUP BY c.cat_id, c.name
   ORDER BY total_revenue DESC NULLS LAST;