SELECT 
  product_name,
  price,
  price - (SELECT AVG(price) FROM products) as diff_from_avg
 FROM products;

SELECT dept_name, avg_salary
 FROM (
  SELECT department as dept_name, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department
 ) dept_averages
 WHERE avg_salary > 50000;

SELECT e.name, e.salary, e.department
 FROM employees e
 WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department
 );

SELECT * FROM customers
 WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders o
  INNER JOIN order_items oi ON o.id = oi.order_id
  WHERE oi.product_id = 'X'
 );

SELECT * FROM products p
 WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.product_id = p.id
 );

SELECT * FROM employees
 WHERE salary > ALL (
  SELECT salary FROM employees
  WHERE title = 'Manager'
 );

SELECT 
  s.sale_id,
  s.product_id,
  s.amount,
  pa.avg_amount,
  s.amount - pa.avg_amount as diff_from_avg
 FROM sales s
 INNER JOIN (
  SELECT product_id, AVG(amount) as avg_amount
  FROM sales
  GROUP BY product_id
 ) pa ON s.product_id = pa.product_id;