SQL Cheat Sheet — Quick Reference for SQL Developers

SELECT Basics

▶

SELECT * FROM users;

Select all columns from table

SELECT name, email FROM users;

Select specific columns

SELECT DISTINCT city FROM users;

Select unique values

SELECT * FROM users WHERE age > 25;

Filter with WHERE clause

SELECT * FROM users WHERE name LIKE '%john%';

Pattern matching with LIKE

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

Range filter

SELECT * FROM users WHERE city IN ('NYC', 'LA');

Filter by list of values

SELECT * FROM users WHERE email IS NOT NULL;

Filter null values

SELECT * FROM users ORDER BY name ASC;

Sort results ascending

SELECT * FROM users LIMIT 10 OFFSET 20;

Pagination

SELECT name AS full_name FROM users;

Column alias

JOINs

▶

SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Inner join — matching rows only

SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Left join — all from left + matching right

SELECT * FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

Right join — all from right + matching left

SELECT * FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

Full outer join — all rows from both

SELECT * FROM users u
CROSS JOIN products p;

Cross join — cartesian product

SELECT * FROM employees e
JOIN employees m ON e.manager_id = m.id;

Self join

SELECT * FROM users u
JOIN orders o USING (user_id);

JOIN with USING (same column name)

Aggregation & Grouping

▶

SELECT COUNT(*) FROM users;

Count all rows

SELECT COUNT(DISTINCT city) FROM users;

Count unique values

SELECT SUM(amount) FROM orders;

Sum of column

SELECT AVG(salary) FROM employees;

Average of column

SELECT MIN(price), MAX(price) FROM products;

Min and max

SELECT city, COUNT(*) FROM users
GROUP BY city;

Group and count

SELECT city, COUNT(*) FROM users
GROUP BY city HAVING COUNT(*) > 5;

Filter groups with HAVING

SELECT dept, AVG(salary) avg_sal FROM emp
GROUP BY dept ORDER BY avg_sal DESC;

Group, aggregate, sort

INSERT, UPDATE, DELETE

▶

INSERT INTO users (name, email)
VALUES ('John', 'john@example.com');

Insert single row

INSERT INTO users (name, email) VALUES
('John', 'j@e.com'), ('Jane', 'ja@e.com');

Insert multiple rows

INSERT INTO archive SELECT * FROM users
WHERE created_at < '2024-01-01';

Insert from SELECT

UPDATE users SET name = 'Jane'
WHERE id = 1;

Update specific row

UPDATE users SET active = false
WHERE last_login < '2024-01-01';

Update multiple rows

DELETE FROM users WHERE id = 1;

Delete specific row

TRUNCATE TABLE temp_data;

Delete all rows (fast, no log)

INSERT INTO users (id, name) VALUES (1, 'John')
ON CONFLICT (id) DO UPDATE SET name = 'John';

Upsert (PostgreSQL)

Subqueries & CTEs

▶

SELECT * FROM users WHERE id IN
(SELECT user_id FROM orders);

Subquery in WHERE

SELECT *, (SELECT COUNT(*) FROM orders o
WHERE o.user_id = u.id) AS order_count
FROM users u;

Correlated subquery

SELECT * FROM users WHERE EXISTS
(SELECT 1 FROM orders WHERE user_id = users.id);

EXISTS subquery

WITH active AS (
  SELECT * FROM users WHERE active = true
)
SELECT * FROM active WHERE age > 25;

Common Table Expression (CTE)

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

Recursive CTE (tree traversal)

Window Functions

▶

SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

Row number ranking

SELECT dept, name, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
FROM employees;

Rank within partition (gaps)

SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC)
FROM employees;

Dense rank (no gaps)

SELECT date, amount,
SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;

Running total

SELECT date, amount,
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

Moving average

SELECT name, salary,
LAG(salary) OVER (ORDER BY hire_date) AS prev_salary,
LEAD(salary) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

LAG/LEAD — access adjacent rows

SELECT dept, name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Distribute rows into N buckets

Table Operations

▶

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Create table

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

Add column

ALTER TABLE users DROP COLUMN phone;

Drop column

ALTER TABLE users RENAME COLUMN name TO full_name;

Rename column

ALTER TABLE users ALTER COLUMN name TYPE TEXT;

Change column type

DROP TABLE IF EXISTS temp_data;

Drop table safely

CREATE TABLE orders_backup AS
SELECT * FROM orders;

Copy table structure and data

Indexes & Performance

▶

CREATE INDEX idx_email ON users(email);

Create index

CREATE UNIQUE INDEX idx_email ON users(email);

Create unique index

CREATE INDEX idx_comp ON orders(user_id, status);

Composite index

DROP INDEX idx_email;

Drop index

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';

Analyze query plan

CREATE INDEX idx_gin ON docs USING GIN(tags);

GIN index for arrays/JSONB (PostgreSQL)

Set Operations

▶

SELECT name FROM employees
UNION
SELECT name FROM contractors;

Combine results, remove duplicates

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

Combine results, keep duplicates

SELECT id FROM set_a
INTERSECT
SELECT id FROM set_b;

Common elements

SELECT id FROM set_a
EXCEPT
SELECT id FROM set_b;

In A but not in B

String & Date Functions

▶

SELECT CONCAT(first_name, ' ', last_name) FROM users;

Concatenate strings

SELECT UPPER(name), LOWER(email) FROM users;

Case conversion

SELECT LENGTH(name) FROM users;

String length

SELECT SUBSTRING(name FROM 1 FOR 3) FROM users;

Extract substring

SELECT TRIM(' hello ');

Remove whitespace

SELECT REPLACE(name, 'old', 'new') FROM users;

Replace substring

SELECT COALESCE(phone, email, 'N/A') FROM users;

First non-null value

SELECT NOW();

Current timestamp

SELECT EXTRACT(YEAR FROM created_at) FROM users;

Extract date part

SELECT DATE_TRUNC('month', created_at) FROM users;

Truncate to month (PostgreSQL)

SELECT AGE(NOW(), created_at) FROM users;

Time difference (PostgreSQL)

SELECT CASE
  WHEN age < 18 THEN 'minor'
  WHEN age < 65 THEN 'adult'
  ELSE 'senior'
END AS category FROM users;

CASE expression

Views & Transactions

▶

CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true;

Create view

CREATE OR REPLACE VIEW v AS SELECT ...;

Update view definition

DROP VIEW IF EXISTS active_users;

Drop view

CREATE MATERIALIZED VIEW mv AS
SELECT dept, AVG(salary) FROM emp GROUP BY dept;

Materialized view (cached)

REFRESH MATERIALIZED VIEW mv;

Refresh materialized view data

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

Transaction (all or nothing)

ROLLBACK;

Undo transaction

SAVEPOINT sp1;
-- ... operations ...
ROLLBACK TO sp1;

Partial rollback with savepoint

JSON (PostgreSQL)

▶

SELECT data->>'name' FROM users;

Extract JSON text value

SELECT data->'address'->>'city' FROM users;

Nested JSON access

SELECT * FROM users WHERE data @> '{"role":"admin"}';

JSONB containment check

SELECT jsonb_array_elements(data->'tags') FROM posts;

Expand JSON array

UPDATE users SET data = data || '{"verified":true}';

Merge JSONB data

🗂 SQL Cheat Sheet