SQL Mastery: The Essential Cheat Sheet for Data Professionals

Whether you're a backend developer, a data analyst, or someone who just needs to pull data from a database without breaking things, SQL is one of those skills that pays dividends every single day. The problem is that the syntax varies across PostgreSQL, MySQL, and SQL Server just enough to trip you up constantly. This cheat sheet covers all three, so you can stop second-guessing your queries.

Data Types

Key types across PostgreSQL, MySQL, and SQL Server:

• Integer: INT, BIGINT, SMALLINT (all)
• Decimal: NUMERIC(p,s) / DECIMAL(p,s) (all)
• String (variable): VARCHAR(n) (all)
• Large text: TEXT (PG/MySQL), VARCHAR(MAX) (SQL Server)
• Boolean: BOOLEAN (PG), TINYINT(1) (MySQL), BIT (SQL Server)
• Datetime: TIMESTAMP (PG), DATETIME (MySQL/SQL Server)
• UUID: UUID (PG), CHAR(36) (MySQL), UNIQUEIDENTIFIER (SQL Server)
• JSON: JSON/JSONB (PG), JSON (MySQL), NVARCHAR(MAX) (SQL Server)

DDL -- Define Structure

Create Table

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,        -- auto-increment (PostgreSQL)
    username    VARCHAR(50)  NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL,
    age         INT          CHECK (age >= 0),
    role        VARCHAR(20)  DEFAULT 'user',
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
);

Alter Table

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN username TO user_name;   -- PG
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;          -- PG
ALTER TABLE users MODIFY COLUMN age BIGINT;              -- MySQL

Drop / Truncate

DROP TABLE IF EXISTS users;
TRUNCATE TABLE users;               -- Delete all rows, keep structure (fast)
TRUNCATE TABLE users RESTART IDENTITY;  -- Reset auto-increment (PG)

DML -- Manipulate Data

INSERT

-- Single row
INSERT INTO users (username, email, age)
VALUES ('alice', '[e-mail]', 30);

-- Multiple rows
INSERT INTO users (username, email, age)
VALUES
    ('bob',   '[e-mail]',   25),
    ('carol', '[e-mail]', 28);

-- Upsert (PostgreSQL)
INSERT INTO users (id, username, email)
VALUES (1, 'alice', '[e-mail]')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email;

UPDATE

UPDATE users SET email = '[e-mail]' WHERE id = 1;
UPDATE products SET price = price * 1.10 WHERE category = 'electronics';

-- Update from another table (PostgreSQL)
UPDATE users u SET email = c.email
FROM contacts c WHERE u.id = c.user_id;

DELETE

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE age < 18;
-- PostgreSQL: DELETE FROM users WHERE id = 1 RETURNING *;

Querying Data

SELECT id, username, email FROM users;
SELECT DISTINCT country FROM users;
SELECT username AS name, email AS contact FROM users;

-- ORDER BY
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY age DESC, username ASC;

-- LIMIT / OFFSET (Pagination)
SELECT * FROM users LIMIT 10;               -- PG/MySQL
SELECT * FROM users LIMIT 10 OFFSET 20;     -- Rows 21-30
SELECT TOP 10 * FROM users;                 -- SQL Server

Filtering & Conditions

WHERE age BETWEEN 18 AND 65
WHERE name IN ('Alice', 'Bob')
WHERE email IS NULL
WHERE username LIKE 'a%'           -- Starts with 'a'
WHERE username LIKE '%smith'       -- Ends with 'smith'
WHERE username ILIKE '%JOHN%'      -- Case-insensitive (PostgreSQL)
WHERE age > 18 AND role = 'admin'
WHERE age > 60 OR role = 'vip'

Joins

-- INNER JOIN -- only matching rows
SELECT u.username, o.total
FROM users u INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN -- all left rows + matching right (NULL if no match)
SELECT u.username, o.total
FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- Anti-Join: Users with NO orders
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

-- SELF JOIN
SELECT a.username AS employee, b.username AS manager
FROM users a JOIN users b ON a.manager_id = b.id;

-- Multi-Table Join
SELECT u.username, o.id AS order_id, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

Aggregates & Grouping

SELECT COUNT(*) FROM orders;
SELECT COUNT(DISTINCT country) FROM users;
SELECT SUM(total), AVG(total), MIN(total), MAX(total) FROM orders;

-- GROUP BY
SELECT user_id, COUNT(*) AS order_count
FROM orders GROUP BY user_id;

-- HAVING (filter on aggregates)
SELECT user_id, COUNT(*) AS cnt
FROM orders GROUP BY user_id
HAVING COUNT(*) > 5;

Subqueries & CTEs

-- Subquery in WHERE
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- EXISTS (often faster than IN)
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- CTE (Common Table Expression)
WITH active_users AS (
    SELECT * FROM users WHERE last_login > NOW() - INTERVAL '30 days'
)
SELECT * FROM active_users WHERE role = 'admin';

-- Recursive CTE (org chart / tree)
WITH RECURSIVE subordinates AS (
    SELECT id, username, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.username, e.manager_id
    FROM employees e JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

Window Functions

-- ROW_NUMBER
SELECT username, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

-- RANK / DENSE_RANK
SELECT username, salary,
    RANK() OVER (ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

-- LAG / LEAD
SELECT username, salary, month,
    LAG(salary) OVER (PARTITION BY username ORDER BY month) AS prev_salary
FROM salary_history;

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

-- Top N per group
SELECT * FROM (
    SELECT username, dept, salary,
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employees
) ranked WHERE rn <= 3;

Indexes

CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_username ON users (username);
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

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

EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[e-mail]';

Index Guidelines

• Primary keys are auto-indexed
• Always index foreign keys
• Index WHERE clause and ORDER BY columns
• Index JOIN columns on both sides
• Skip indexes on low-cardinality columns (boolean, status)
• Avoid heavy indexing on frequently updated columns

Transactions

BEGIN;
    UPDATE accounts SET balance = balance - 500 WHERE id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

-- Savepoints
BEGIN;
    INSERT INTO orders (user_id, total) VALUES (1, 100);
    SAVEPOINT sp1;
    INSERT INTO order_items VALUES (1, 5, 3);
    ROLLBACK TO SAVEPOINT sp1;
    INSERT INTO order_items VALUES (1, 5, 2);
COMMIT;

Views

CREATE VIEW active_user_orders AS
SELECT u.username, o.id, o.total
FROM users u JOIN orders o ON u.id = o.user_id
WHERE u.active = true;

-- Materialized view (PostgreSQL)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT category, SUM(total) AS revenue FROM orders GROUP BY category;

REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;

Performance Tips

• Use EXPLAIN ANALYZE to understand query plans
• Avoid SELECT * in production -- specify columns
• Use CTEs over correlated subqueries for readability and performance
• Index your JOIN and WHERE columns
• Use EXISTS instead of IN for large subqueries
• Use LIMIT for pagination, but consider keyset pagination for large offsets
• Use parameterized queries -- never concatenate user input into SQL

If you found this useful, share it with a colleague who needs it. Subscribe for more developer resources every week.

---

Want the full resource?

SQL Cheat Sheet — $6.99 on Gumroad

Get the complete, downloadable version. Perfect for bookmarking, printing, or sharing with your team.

Get it now on Gumroad →

---

If you found this useful, drop a ❤️ and follow for more developer resources every week.