Fundamentals Refresher

SELECT product_name, unit_price
FROM products
WHERE category_id = 1
ORDER BY unit_price DESC
LIMIT 5;

SELECT category_id, AVG(unit_price) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(unit_price) > 50;

SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;

-- Approach 1: LEFT JOIN + IS NULL
SELECT a.account_id
FROM account a
LEFT JOIN transaction_history th ON a.account_id = th.account_id
WHERE th.account_id IS NULL;

-- Approach 2: NOT EXISTS
SELECT a.account_id
FROM account a
WHERE NOT EXISTS (
  SELECT 1
  FROM transaction_history th
  WHERE th.account_id = a.account_id
);

SELECT
  category,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
  COUNT(*) AS total
FROM orders
GROUP BY category;