Subqueries

-- Scalar subquery
SELECT product_name, price,
       (SELECT AVG(price) FROM products) AS avg_price
FROM products;

-- IN subquery
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- EXISTS for correlated checks
SELECT c.name FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'completed'
);

-- Derived table
SELECT t.customer_id, t.total
FROM (
  SELECT customer_id, SUM(total) AS total
  FROM orders GROUP BY customer_id
) t
WHERE t.total > 5000;