Data Manipulation Language (DML)

SELECT
  p.id,
  p.name,
  p.price,
  c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.price > 10.00
  AND p.stock > 0
ORDER BY p.price DESC
LIMIT 20 OFFSET 40;

SELECT
  category_id,
  COUNT(*)          AS product_count,
  AVG(price)        AS avg_price,
  SUM(stock * price) AS inventory_value
FROM products
GROUP BY category_id
HAVING AVG(price) > 25.00
ORDER BY inventory_value DESC;

-- INNER JOIN: rows present in both tables
SELECT o.id, u.email
FROM orders o
INNER JOIN users u ON u.id = o.user_id;

-- LEFT JOIN: all orders, user data where available
SELECT o.id, u.email
FROM orders o
LEFT JOIN users u ON u.id = o.user_id;

-- RIGHT JOIN: all users, order data where available
SELECT o.id, u.email
FROM orders o
RIGHT JOIN users u ON u.id = o.user_id;

-- CROSS JOIN: cartesian product
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- Correlated subquery: products more expensive than their category average
SELECT id, name, price
FROM products p
WHERE price > (
  SELECT AVG(price)
  FROM products
  WHERE category_id = p.category_id
);

-- EXISTS subquery
SELECT id, name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);

-- Non-recursive CTE
WITH high_value_orders AS (
  SELECT user_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 5000
)
SELECT u.email, hvo.lifetime_value
FROM users u
JOIN high_value_orders hvo ON hvo.user_id = u.id
ORDER BY hvo.lifetime_value DESC;

-- Recursive CTE: traverse a category hierarchy
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT id, CONCAT(REPEAT('  ', depth), name) AS indented_name
FROM category_tree
ORDER BY depth, name;

-- Insert a single row
INSERT INTO products (sku, name, price, stock)
VALUES ('WIDGET-001', 'Blue Widget', 9.99, 100);

-- Insert multiple rows
INSERT INTO products (sku, name, price, stock)
VALUES
  ('WIDGET-002', 'Red Widget',   11.49, 50),
  ('WIDGET-003', 'Green Widget', 10.99, 75);

-- Insert from a SELECT
INSERT INTO archive_orders (id, user_id, total, created_at)
SELECT id, user_id, total, created_at
FROM orders
WHERE created_at < '2023-01-01';

INSERT IGNORE INTO products (sku, name, price)
VALUES ('WIDGET-001', 'Duplicate', 0.00);

INSERT INTO inventory (product_id, warehouse_id, qty)
VALUES (42, 7, 100)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

-- Basic update
UPDATE products
SET price = price * 1.05, updated_at = NOW()
WHERE category_id = 3;

-- Update with a JOIN
UPDATE orders o
JOIN users u ON u.id = o.user_id
SET o.discount = 0.10
WHERE u.tier = 'premium'
  AND o.created_at >= CURDATE();

-- Basic delete
DELETE FROM sessions
WHERE expires_at < NOW();

-- Delete with a JOIN: remove orders belonging to deactivated users
DELETE o
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.status = 'deactivated';

REPLACE INTO product_cache (product_id, cached_data, refreshed_at)
VALUES (42, '{"price":9.99}', NOW());

SELECT
  id,
  user_id,
  total,
  created_at,
  ROW_NUMBER()  OVER (PARTITION BY user_id ORDER BY created_at)       AS order_seq,
  RANK()        OVER (PARTITION BY user_id ORDER BY total DESC)        AS rank_by_value,
  LAG(total, 1) OVER (PARTITION BY user_id ORDER BY created_at)       AS prev_total,
  SUM(total)    OVER (PARTITION BY user_id ORDER BY created_at
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;