Database Cheatsheets.
SQL, JDBC, PHP & Node.js

CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    role VARCHAR(50) DEFAULT 'member',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_users_role (role)
);

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(30) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE CASCADE
);

-- Add a new column with default value
ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL AFTER email;

-- Modify column data type or nullability
ALTER TABLE users MODIFY COLUMN name VARCHAR(150) NOT NULL;

-- Add a composite index
ALTER TABLE orders ADD INDEX idx_orders_user_status (user_id, status);

-- Drop a column safely
ALTER TABLE users DROP COLUMN phone;

-- Insert single & multiple rows
INSERT INTO users (name, email, role) VALUES
('Alice Johnson', 'alice@example.com', 'admin'),
('Bob Smith', 'bob@example.com', 'member');

-- Select with conditions, sorting, and pagination
SELECT id, name, email, role
FROM users
WHERE is_active = TRUE AND role != 'banned'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Update rows matching a predicate
UPDATE users
SET role = 'manager', is_active = TRUE
WHERE email = 'bob@example.com';

-- Delete specific records
DELETE FROM orders
WHERE status = 'cancelled' AND created_at < NOW() - INTERVAL 30 DAY;

-- MySQL / MariaDB
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (101, 1, NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = VALUES(last_login);

-- PostgreSQL / SQLite (3.24+)
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (101, 1, CURRENT_TIMESTAMP)
ON CONFLICT (user_id) DO UPDATE SET
    login_count = user_stats.login_count + 1,
    last_login = EXCLUDED.last_login;

-- 1. INNER JOIN (only records present in both tables)
SELECT u.name, o.id AS order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 2. LEFT JOIN (all left rows, NULL if no match on right)
SELECT u.name, COUNT(o.id) AS total_orders, COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 3. Self Join (e.g., employee and manager hierarchy)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 4. Cross Join (cartesian product of all combinations)
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- Grouping with aggregate filters (HAVING filter runs AFTER group aggregation)
SELECT
    role,
    COUNT(*) AS user_count,
    AVG(TIMESTAMPDIFF(YEAR, birth_date, CURDATE())) AS avg_age,
    MAX(created_at) AS latest_signup
FROM users
WHERE is_active = TRUE
GROUP BY role
HAVING COUNT(*) >= 5
ORDER BY user_count DESC;

-- Rank top 3 highest spending orders per customer
WITH ranked_orders AS (
    SELECT
        id,
        user_id,
        total_amount,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY total_amount DESC) as rank_num,
        LAG(total_amount, 1) OVER(PARTITION BY user_id ORDER BY created_at) as prev_order_amount
    FROM orders
)
SELECT * FROM ranked_orders WHERE rank_num <= 3;

-- MySQL / MariaDB / PostgreSQL
START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 10;
UPDATE accounts SET balance = balance + 500 WHERE id = 20;

-- Verify state before committing
INSERT INTO transfer_logs (from_account, to_account, amount) VALUES (10, 20, 500);

-- If everything succeeded:
COMMIT;

-- In case of any validation or constraint error:
-- ROLLBACK;