SQL Documentation

SELECT * FROM users;

SELECT id, name, email
FROM   users;

SELECT   id, name, email
FROM     users
WHERE    active = 1
ORDER BY created_at DESC
LIMIT    10;

-- Equality
SELECT * FROM users WHERE role = 'admin';
-- Comparison
SELECT * FROM products WHERE price > 50;
-- Range
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;
-- Pattern match
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Multiple conditions
SELECT * FROM users WHERE active = 1 AND role = 'editor';
-- NULL check
SELECT * FROM users WHERE deleted_at IS NULL;

-- Sort newest first
SELECT id, name, created_at
FROM   users
ORDER BY created_at DESC;
-- Top 5 most expensive products
SELECT name, price FROM products ORDER BY price DESC LIMIT 5;
-- Paginate: page 2, 10 items per page
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 10;

-- Single row
INSERT INTO users (name, email, role)
VALUES ('Yahya', 'yehiahwary515@gmail.com', 'admin');
-- Multiple rows
INSERT INTO products (name, price, stock)
VALUES ('Widget A', 29.99, 100), ('Widget B', 49.99, 50), ('Widget C', 9.99, 200);

UPDATE users
SET    role = 'moderator'
WHERE  id = 42;

UPDATE users
SET    name = 'Yahya A.', updated_at = NOW()
WHERE  id = 42;

-- Hard delete
DELETE FROM users WHERE id = 42;
-- Soft delete (preferred in production)
UPDATE users SET deleted_at = NOW() WHERE id = 42;
-- Bulk delete expired sessions
DELETE FROM sessions WHERE expires_at < NOW();

SELECT
  o.id        AS order_id,
  u.name      AS customer,
  u.email,
  o.total,
  o.created_at
FROM   orders AS o
INNER JOIN users AS u ON o.user_id = u.id;

SELECT
  u.id,
  u.name,
  COUNT(o.id) AS order_count
FROM      users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY  u.id, u.name
ORDER BY  order_count DESC;

SELECT
  user_id,
  COUNT(*)   AS total_orders,
  SUM(total) AS revenue,
  AVG(total) AS avg_order,
  MAX(total) AS biggest_order
FROM   orders
GROUP BY user_id
ORDER BY revenue DESC;

SELECT user_id, COUNT(*) AS order_count
FROM   orders
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY order_count DESC;

-- Users who placed at least one completed order
SELECT name, email FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders WHERE status = 'completed'
);
-- Products above average price
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
DROP INDEX idx_users_email;

WITH active_users AS (
  SELECT id, name, email
  FROM   users
  WHERE  active = TRUE
    AND  deleted_at IS NULL
)
SELECT *
FROM   active_users
WHERE  email LIKE '%@company.com'
ORDER BY name;

WITH
  order_totals AS (
    SELECT user_id, COUNT(*) AS order_count, SUM(total) AS lifetime_value
    FROM   orders
    WHERE  status = 'completed'
    GROUP BY user_id
  ),
  high_value AS (
    SELECT user_id FROM order_totals
    WHERE lifetime_value > 1000 AND order_count >= 3
  )
SELECT u.name, u.email, ot.order_count, ot.lifetime_value
FROM       users        AS u
INNER JOIN order_totals AS ot ON u.id = ot.user_id
INNER JOIN high_value   AS hv ON u.id = hv.user_id
ORDER BY ot.lifetime_value DESC;

SELECT
  employee_name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY department, salary DESC;

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month)  AS running_total,
  LAG(revenue) OVER (ORDER BY month)  AS prev_month,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100
  , 1) AS growth_pct
FROM monthly_revenue
ORDER BY month;

-- Top 3 best-selling products per category
WITH ranked AS (
  SELECT
    category, product_name, total_sold,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sold DESC) AS rn
  FROM products
)
SELECT category, product_name, total_sold
FROM   ranked
WHERE  rn <= 3
ORDER BY category, total_sold DESC;

BEGIN;

  UPDATE accounts
  SET    balance = balance - 500
  WHERE  id = 1;

  UPDATE accounts
  SET    balance = balance + 500
  WHERE  id = 2;

COMMIT;
-- Both updates happen together, or neither does

BEGIN;

  INSERT INTO orders (user_id, total, status)
  VALUES (42, 199.99, 'pending');

  SAVEPOINT after_order;

  UPDATE inventory SET stock = stock - 1
  WHERE product_id = 7 AND stock > 0;

  -- If something fails, rollback to savepoint (keeps the order insert)
  -- ROLLBACK TO SAVEPOINT after_order;

  INSERT INTO audit_log (action, user_id) VALUES ('order_created', 42);

COMMIT;