Cheatsheet SQL: Referensi Cepat Query, Index, dan Database Design

-- SELECT
SELECT col1, col2 FROM table WHERE id = 1;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT DISTINCT category FROM products;
SELECT name AS nama, price * 1.1 AS harga_ppn FROM products;

-- INSERT
INSERT INTO users (name, email) VALUES ('Budi', 'budi@mail.com');
INSERT INTO users (name, email) VALUES ('Andi', 'andi@mail.com'), ('Sari', 'sari@mail.com');

-- UPDATE
UPDATE users SET name = 'Budi Updated' WHERE id = 1;
UPDATE orders SET status = 'done' WHERE created_at < '2026-01-01';

-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM sessions WHERE expires_at < NOW();

SELECT o.id, u.name, o.total
FROM orders o
INNER JOIN users u ON o.user_id = u.id;    -- Only matching

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;    -- All users

SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;   -- All orders

SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id; -- All both

SELECT category, COUNT(*) as total, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;

-- Row number
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- Running total
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- Partition by
SELECT department, name, salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

WITH active_users AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, a.order_count
FROM users u
JOIN active_users a ON u.id = a.user_id
WHERE a.order_count > 5;

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Explain query
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@mail.com';

-- Show indexes on a table
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Subquery in WHERE
SELECT name FROM users WHERE id IN (
  SELECT user_id FROM orders WHERE total > 100000
);

-- EXISTS (faster for large datasets)
SELECT u.name FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 100000
);

-- Correlated subquery
SELECT name, salary, department FROM employees e1
WHERE salary > (
  SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department
);