SQL Cheat Sheet

-- Integer
INT          -- -2147483648 to 2147483647
BIGINT        -- Angka lebih besar
SMALLINT       -- -32768 to 32767
TINYINT        -- 0 to 255 (MySQL)
 
-- Decimal
DECIMAL(10,2)     -- 10 digit total, 2 di belakang koma
NUMERIC(10,2)     -- Sama kayak DECIMAL
FLOAT         -- Floating point
DOUBLE        -- Double precision

-- Fixed length
CHAR(10)       -- Fixed 10 karakter
 
-- Variable length (hemat storage)
VARCHAR(255)     -- Max 255 karakter
VARCHAR(MAX)     -- Max size (SQL Server)
 
-- Text panjang
TEXT         -- Unlimited text
MEDIUMTEXT      -- 16MB max (MySQL)
LONGTEXT       -- 4GB max (MySQL)

-- Date only
DATE         -- YYYY-MM-DD
 
-- Time only
TIME         -- HH:MM:SS
 
-- Date + Time
DATETIME       -- YYYY-MM-DD HH:MM:SS
TIMESTAMP       -- Sama, tapi auto timezone aware
 
-- Year only
YEAR         -- YYYY

-- Boolean
BOOLEAN        -- TRUE/FALSE (PostgreSQL)
TINYINT(1)      -- 0/1 (MySQL)
 
-- JSON
JSON         -- Store JSON data
JSONB         -- Binary JSON (PostgreSQL, faster)
 
-- Binary
BLOB         -- Binary data
VARBINARY(255)    -- Variable binary

-- Create database
CREATE DATABASE my_database;
 
-- Drop database
DROP DATABASE my_database;
 
-- Use database
USE my_database;
 
-- Show databases
SHOW DATABASES;     -- MySQL
\l            -- PostgreSQL

-- Create table
CREATE TABLE users (
 id INT PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(100) NOT NULL,
 email VARCHAR(255) UNIQUE NOT NULL,
 age INT,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Drop table
DROP TABLE users;
 
-- Drop jika exist
DROP TABLE IF EXISTS users;
 
-- Show tables
SHOW TABLES;       -- MySQL
\dt           -- PostgreSQL
 
-- Describe table structure
DESCRIBE users;     -- MySQL
\d users         -- PostgreSQL
 
-- Rename table
ALTER TABLE users RENAME TO customers;

-- Tambah kolom
ALTER TABLE users ADD phone VARCHAR(20);
 
-- Hapus kolom
ALTER TABLE users DROP COLUMN phone;
 
-- Ubah tipe kolom
ALTER TABLE users MODIFY age BIGINT;      -- MySQL
ALTER TABLE users ALTER COLUMN age TYPE BIGINT; -- PostgreSQL
 
-- Rename kolom
ALTER TABLE users RENAME COLUMN name TO full_name;
 
-- Add constraint
ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- Semua kolom
SELECT * FROM users;
 
-- Kolom tertentu
SELECT name, email FROM users;
 
-- Alias kolom
SELECT name AS full_name, email AS contact FROM users;
 
-- Distinct values
SELECT DISTINCT city FROM users;
 
-- Limit results
SELECT * FROM users LIMIT 10;          -- First 10
SELECT * FROM users LIMIT 10 OFFSET 20;     -- Skip 20, take 10

-- Basic filter
SELECT * FROM users WHERE age > 18;
 
-- Multiple conditions
SELECT * FROM users WHERE age > 18 AND city = 'Jakarta';
SELECT * FROM users WHERE age > 18 OR age < 13;
 
-- NOT
SELECT * FROM users WHERE NOT city = 'Jakarta';
 
-- IN
SELECT * FROM users WHERE city IN ('Jakarta', 'Bandung', 'Surabaya');
 
-- BETWEEN
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
 
-- LIKE (pattern matching)
SELECT * FROM users WHERE name LIKE 'And%';   -- Mulai dengan "And"
SELECT * FROM users WHERE name LIKE '%son';   -- Berakhir dengan "son"
SELECT * FROM users WHERE name LIKE '%and%';  -- Contain "and"
SELECT * FROM users WHERE name LIKE '_____';  -- Exactly 5 characters
 
-- Case insensitive (PostgreSQL)
SELECT * FROM users WHERE name ILIKE 'john%';
 
-- NULL check
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Ascending (default)
SELECT * FROM users ORDER BY name;
SELECT * FROM users ORDER BY name ASC;
 
-- Descending
SELECT * FROM users ORDER BY created_at DESC;
 
-- Multiple columns
SELECT * FROM users ORDER BY city ASC, name DESC;
 
-- Order by position
SELECT name, age FROM users ORDER BY 2 DESC;  -- Order by 2nd column (age)

-- Group dengan aggregate
SELECT city, COUNT(*) FROM users GROUP BY city;
 
-- Multiple columns
SELECT city, status, COUNT(*) FROM users GROUP BY city, status;
 
-- Dengan aggregate functions
SELECT city, AVG(age) as avg_age FROM users GROUP BY city;
 
-- Order by aggregate
SELECT city, COUNT(*) as total FROM users GROUP BY city ORDER BY total DESC;

-- Filter grouped data
SELECT city, COUNT(*) as total
FROM users
GROUP BY city
HAVING COUNT(*) > 100;
 
-- Multiple conditions
SELECT city, AVG(age) as avg_age
FROM users
GROUP BY city
HAVING AVG(age) > 25 AND COUNT(*) > 50;
 
-- WHERE vs HAVING
SELECT city, COUNT(*) as total
FROM users
WHERE age > 18       -- Filter rows sebelum grouping
GROUP BY city
HAVING COUNT(*) > 100;   -- Filter groups setelah grouping

-- Insert single row
INSERT INTO users (name, email, age)
VALUES ('Andi', 'andi@mail.com', 25);
 
-- Insert multiple rows
INSERT INTO users (name, email, age)
VALUES
 ('Budi', 'budi@mail.com', 23),
 ('Citra', 'citra@mail.com', 27),
 ('Doni', 'doni@mail.com', 30);
 
-- Insert semua kolom (ga recommended)
INSERT INTO users VALUES (1, 'Eko', 'eko@mail.com', 28, NOW());
 
-- Insert dengan default values
INSERT INTO users (name, email) VALUES ('Fani', 'fani@mail.com');
 
-- Insert from SELECT
INSERT INTO users_backup SELECT * FROM users WHERE age > 30;
 
-- Insert atau update jika exist (MySQL)
INSERT INTO users (id, name, email)
VALUES (1, 'Andi', 'andi@mail.com')
ON DUPLICATE KEY UPDATE name = 'Andi', email = 'andi@mail.com';
 
-- Insert atau skip jika exist (PostgreSQL)
INSERT INTO users (id, name, email)
VALUES (1, 'Andi', 'andi@mail.com')
ON CONFLICT (id) DO NOTHING;

-- Update single column
UPDATE users SET age = 26 WHERE id = 1;
 
-- Update multiple columns
UPDATE users SET age = 26, city = 'Jakarta' WHERE id = 1;
 
-- Update dengan calculation
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
 
-- Update semua rows (hati-hati!)
UPDATE users SET status = 'active';
 
-- Update dari table lain
UPDATE users u
SET city = (SELECT city FROM cities c WHERE c.id = u.city_id);
 
-- Update dengan JOIN (MySQL)
UPDATE users u
JOIN orders o ON u.id = o.user_id
SET u.total_orders = o.order_count;

-- Delete specific row
DELETE FROM users WHERE id = 5;
 
-- Delete dengan condition
DELETE FROM users WHERE age < 18;
 
-- Delete semua rows (hati-hati!)
DELETE FROM users;
 
-- Delete faster (truncate)
TRUNCATE TABLE users;
 
-- Delete dengan subquery
DELETE FROM users WHERE id IN (SELECT user_id FROM banned_users);
 
-- Delete dengan JOIN (MySQL)
DELETE u FROM users u
JOIN inactive_accounts i ON u.id = i.user_id;

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;
 
-- Sum
SELECT SUM(price) FROM products;
SELECT SUM(quantity * price) as total FROM orders;
 
-- Average
SELECT AVG(age) FROM users;
SELECT AVG(price) FROM products WHERE category = 'electronics';
 
-- Min & Max
SELECT MIN(age), MAX(age) FROM users;
SELECT MIN(price), MAX(price) FROM products;
 
-- Multiple aggregates
SELECT
 COUNT(*) as total_users,
 AVG(age) as avg_age,
 MIN(age) as youngest,
 MAX(age) as oldest
FROM users;
 
-- Group concat (gabungin values)
SELECT GROUP_CONCAT(name) FROM users;               -- MySQL
SELECT STRING_AGG(name, ', ') FROM users;             -- PostgreSQL

-- Concatenate
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;
SELECT first_name || ' ' || last_name as full_name FROM users;  -- PostgreSQL
 
-- Uppercase & Lowercase
SELECT UPPER(name) FROM users;
SELECT LOWER(email) FROM users;
 
-- Substring
SELECT SUBSTRING(name, 1, 3) FROM users;              -- First 3 chars
SELECT LEFT(name, 3) FROM users;                  -- Left 3 chars
SELECT RIGHT(name, 3) FROM users;                 -- Right 3 chars
 
-- Length
SELECT LENGTH(name) FROM users;
SELECT CHAR_LENGTH(name) FROM users;                -- Character length
 
-- Trim whitespace
SELECT TRIM(name) FROM users;
SELECT LTRIM(name) FROM users;                   -- Left trim
SELECT RTRIM(name) FROM users;                   -- Right trim
 
-- Replace
SELECT REPLACE(email, '@gmail.com', '@mail.com') FROM users;

-- Current date/time
SELECT NOW();           -- Current timestamp
SELECT CURDATE();         -- Current date (MySQL)
SELECT CURRENT_DATE;       -- Current date (standard)
SELECT CURRENT_TIME;       -- Current time
SELECT CURRENT_TIMESTAMP;     -- Current timestamp
 
-- Extract parts
SELECT YEAR(created_at) FROM users;
SELECT MONTH(created_at) FROM users;
SELECT DAY(created_at) FROM users;
SELECT HOUR(created_at) FROM users;
 
-- Date arithmetic
SELECT DATE_ADD(created_at, INTERVAL 7 DAY) FROM users;      -- MySQL
SELECT created_at + INTERVAL '7 days' FROM users;         -- PostgreSQL
 
SELECT DATE_SUB(created_at, INTERVAL 1 MONTH) FROM users;     -- MySQL
SELECT created_at - INTERVAL '1 month' FROM users;         -- PostgreSQL
 
-- Date difference
SELECT DATEDIFF(NOW(), created_at) as days_ago FROM users;     -- MySQL
SELECT NOW() - created_at as diff FROM users;           -- PostgreSQL
 
-- Format date
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM users;       -- MySQL
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM users;        -- PostgreSQL

-- CASE statement
SELECT
 name,
 age,
 CASE
  WHEN age < 18 THEN 'Minor'
  WHEN age >= 18 AND age < 60 THEN 'Adult'
  ELSE 'Senior'
 END as age_group
FROM users;
 
-- Simple CASE
SELECT
 name,
 CASE status
  WHEN 'active' THEN 'Active User'
  WHEN 'inactive' THEN 'Inactive User'
  ELSE 'Unknown'
 END as status_label
FROM users;
 
-- COALESCE (return first non-null)
SELECT COALESCE(phone, email, 'No contact') as contact FROM users;
 
-- NULLIF (return NULL if equal)
SELECT NULLIF(status, 'unknown') FROM users;
 
-- IFNULL / ISNULL
SELECT IFNULL(phone, 'N/A') FROM users;              -- MySQL
SELECT COALESCE(phone, 'N/A') FROM users;             -- Standard

-- INNER JOIN (only matching rows)
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;
 
-- LEFT JOIN (all from left, matching from right)
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
 
-- RIGHT JOIN (all from right, matching from left)
SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
 
-- FULL OUTER JOIN (all from both)
SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
 
-- CROSS JOIN (cartesian product)
SELECT users.name, products.name
FROM users
CROSS JOIN products;
 
-- Self JOIN
SELECT
 e1.name as employee,
 e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;
 
-- Multiple JOINs
SELECT u.name, p.title, c.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id
INNER JOIN comments c ON p.id = c.post_id;
 
-- JOIN dengan condition tambahan
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed';

-- Subquery di WHERE
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000000);
 
-- Subquery di SELECT
SELECT
 name,
 (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as total_orders
FROM users;
 
-- Subquery di FROM
SELECT avg_age FROM (
 SELECT city, AVG(age) as avg_age FROM users GROUP BY city
) as city_stats
WHERE avg_age > 25;
 
-- EXISTS
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
-- NOT EXISTS
SELECT name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
-- Correlated subquery
SELECT name,
 (SELECT MAX(total) FROM orders WHERE user_id = users.id) as max_order
FROM users;

-- UNION (combine results, remove duplicates)
SELECT name FROM users_2024
UNION
SELECT name FROM users_2025;
 
-- UNION ALL (keep duplicates)
SELECT name FROM users_2024
UNION ALL
SELECT name FROM users_2025;
 
-- INTERSECT (rows in both)
SELECT email FROM users_active
INTERSECT
SELECT email FROM users_premium;
 
-- EXCEPT (rows in first but not second)
SELECT email FROM users_all
EXCEPT
SELECT email FROM users_banned;

-- Create index
CREATE INDEX idx_email ON users(email);
 
-- Unique index
CREATE UNIQUE INDEX idx_email ON users(email);
 
-- Composite index
CREATE INDEX idx_name_city ON users(name, city);
 
-- Drop index
DROP INDEX idx_email ON users;       -- MySQL
DROP INDEX idx_email;            -- PostgreSQL
 
-- Show indexes
SHOW INDEX FROM users;            -- MySQL
\di                     -- PostgreSQL
 
-- Full text index (MySQL)
CREATE FULLTEXT INDEX idx_content ON posts(title, content);

-- Primary Key
CREATE TABLE users (
 id INT PRIMARY KEY
);
 
-- Foreign Key
CREATE TABLE orders (
 id INT PRIMARY KEY,
 user_id INT,
 FOREIGN KEY (user_id) REFERENCES users(id)
);
 
-- Foreign Key dengan action
CREATE TABLE orders (
 id INT PRIMARY KEY,
 user_id INT,
 FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);
 
-- NOT NULL
CREATE TABLE users (
 name VARCHAR(100) NOT NULL
);
 
-- UNIQUE
CREATE TABLE users (
 email VARCHAR(255) UNIQUE
);
 
-- CHECK constraint
CREATE TABLE users (
 age INT CHECK (age >= 18)
);
 
-- DEFAULT
CREATE TABLE users (
 status VARCHAR(20) DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Start transaction
START TRANSACTION;
BEGIN;               -- Alternative
 
-- Commit (save changes)
COMMIT;
 
-- Rollback (undo changes)
ROLLBACK;
 
-- Example
START TRANSACTION;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 
COMMIT;
 
-- Savepoint
START TRANSACTION;
UPDATE users SET status = 'active' WHERE id = 1;
SAVEPOINT sp1;
UPDATE users SET status = 'inactive' WHERE id = 2;
ROLLBACK TO sp1;          -- Undo last update only
COMMIT;

-- Create view
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';
 
-- Query view (like table)
SELECT * FROM active_users;
 
-- Create or replace
CREATE OR REPLACE VIEW active_users AS
SELECT id, name, email, city FROM users WHERE status = 'active';
 
-- Drop view
DROP VIEW active_users;
 
-- Materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW user_stats AS
SELECT city, COUNT(*) as total FROM users GROUP BY city;
 
-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_stats;

-- Basic CTE
WITH active_users AS (
 SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE age > 18;
 
-- Multiple CTEs
WITH
 active_users AS (
  SELECT * FROM users WHERE status = 'active'
 ),
 recent_orders AS (
  SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
 )
SELECT u.name, COUNT(o.id) as order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.name;
 
-- Recursive CTE
WITH RECURSIVE subordinates AS (
 SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
 UNION ALL
 SELECT e.id, e.name, e.manager_id
 FROM employees e
 INNER JOIN subordinates s ON s.id = e.manager_id
)
SELECT * FROM subordinates;

-- ROW_NUMBER
SELECT
 name,
 age,
 ROW_NUMBER() OVER (ORDER BY age DESC) as rank
FROM users;
 
-- RANK & DENSE_RANK
SELECT
 name,
 score,
 RANK() OVER (ORDER BY score DESC) as rank,
 DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM users;
 
-- PARTITION BY
SELECT
 city,
 name,
 age,
 ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) as rank_in_city
FROM users;
 
-- Running total
SELECT
 date,
 amount,
 SUM(amount) OVER (ORDER BY date) as running_total
FROM orders;
 
-- Moving average
SELECT
 date,
 amount,
 AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avg_7_days
FROM orders;
 
-- LAG & LEAD (prev/next row)
SELECT
 date,
 amount,
 LAG(amount, 1) OVER (ORDER BY date) as prev_day,
 LEAD(amount, 1) OVER (ORDER BY date) as next_day
FROM orders;

-- Use EXPLAIN untuk analyze query
EXPLAIN SELECT * FROM users WHERE email = 'test@mail.com';
 
-- Hindari SELECT *
SELECT id, name, email FROM users;       -- Good
SELECT * FROM users;               -- Bad
 
-- Use LIMIT
SELECT * FROM users LIMIT 100;
 
-- Use indexes untuk WHERE, JOIN, ORDER BY
CREATE INDEX idx_email ON users(email);
 
-- Avoid functions di WHERE (ga bisa pake index)
SELECT * FROM users WHERE YEAR(created_at) = 2024;       -- Bad
SELECT * FROM users WHERE created_at >= '2024-01-01'
 AND created_at < '2025-01-01';                -- Good
 
-- Use EXISTS instead of IN dengan subquery
SELECT * FROM users WHERE EXISTS (
 SELECT 1 FROM orders WHERE user_id = users.id
);
 
-- Batch inserts
INSERT INTO users (name, email) VALUES
 ('User 1', 'user1@mail.com'),
 ('User 2', 'user2@mail.com'),
 ('User 3', 'user3@mail.com');

-- Pagination
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;         -- Page 1
 
-- Search dengan ranking
SELECT * FROM products
WHERE name LIKE '%laptop%' OR description LIKE '%laptop%'
ORDER BY
 CASE
  WHEN name LIKE '%laptop%' THEN 1
  ELSE 2
 END;
 
-- Upsert (Insert or Update)
INSERT INTO users (id, name, email)
VALUES (1, 'Andi', 'andi@mail.com')
ON DUPLICATE KEY UPDATE name = 'Andi', email = 'andi@mail.com'; -- MySQL
 
-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 1;
SELECT * FROM users WHERE deleted_at IS NULL;
 
-- Random rows
SELECT * FROM users ORDER BY RAND() LIMIT 10;  -- MySQL
SELECT * FROM users ORDER BY RANDOM() LIMIT 10; -- PostgreSQL
 
-- Find duplicates
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
 
-- Delete duplicates (keep one)
DELETE u1 FROM users u1
INNER JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;

-- NEVER do this (SQL Injection risk!)
-- SELECT * FROM users WHERE email = '" + userInput + "'
 
-- Use prepared statements (application level)
-- Example di berbagai bahasa:
 
-- PHP PDO
-- $stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
-- $stmt->execute([$email]);
 
-- Node.js (PostgreSQL)
-- const result = await pool.query("SELECT * FROM users WHERE email = $1", [email]);
 
-- Python
-- cursor.execute("SELECT * FROM users WHERE email = %s", (email,))
 
-- Validate input di application level
-- Escape special characters
-- Use ORM yang udah handle ini (Prisma, TypeORM, Eloquent)
 
-- Grant minimal permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON database.* TO 'app_user'@'localhost';
 
-- Revoke permissions
REVOKE ALL PRIVILEGES ON database.* FROM 'app_user'@'localhost';