SQL Cheatsheet

Quick reference for SQL syntax covering DDL, DML, SELECT queries, JOINs, aggregate functions, subqueries, window functions, and indexing

69 commands

CREATE TABLE

Create a table

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255));

DROP TABLE

Drop a table

DROP TABLE IF EXISTS users;

ALTER TABLE ADD

Add a column to a table

ALTER TABLE users ADD COLUMN age INT;

ALTER TABLE DROP

Drop a column from a table

ALTER TABLE users DROP COLUMN age;

ALTER TABLE MODIFY

Modify column type

ALTER TABLE users MODIFY COLUMN name VARCHAR(200);

ALTER TABLE RENAME

Rename a table

ALTER TABLE users RENAME TO members;

TRUNCATE TABLE

Fast delete all rows from table

TRUNCATE TABLE logs;

CREATE DATABASE

Create a database

CREATE DATABASE myapp;

DROP DATABASE

Drop a database

DROP DATABASE IF EXISTS myapp;

CREATE VIEW

Create a view

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

INSERT INTO

Insert data

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

INSERT INTO ... SELECT

Insert from SELECT result

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

UPDATE

Update data

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

DELETE

Delete data

DELETE FROM users WHERE status = 'inactive';

UPSERT (MySQL)

Insert or update on duplicate (MySQL)

INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name = 'Alice';

UPSERT (PostgreSQL)

Insert or update on conflict (PostgreSQL)

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

MERGE

Merge (upsert operation)

MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...;

SELECT

Retrieve data

SELECT name, email FROM users;

SELECT DISTINCT

Select distinct values

SELECT DISTINCT country FROM users;

WHERE

Filter with conditions

SELECT * FROM users WHERE age >= 18;

AND / OR

Combine multiple conditions

SELECT * FROM users WHERE age >= 18 AND status = 'active';

IN

Check if value is in a list

SELECT * FROM users WHERE country IN ('US', 'JP', 'UK');

BETWEEN

Get values within a range

SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

LIKE

Pattern matching

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

IS NULL / IS NOT NULL

NULL check

SELECT * FROM users WHERE email IS NOT NULL;

ORDER BY

Sort results

SELECT * FROM users ORDER BY created_at DESC;

LIMIT / OFFSET

Limit number of rows

SELECT * FROM users LIMIT 10 OFFSET 20;

CASE WHEN

Conditional expression

SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS category FROM users;

COALESCE

Return first non-null value

SELECT COALESCE(nickname, name, 'Unknown') FROM users;

INNER JOIN

Inner join (matching rows only)

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

LEFT JOIN

Left outer join (keep all left rows)

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

RIGHT JOIN

Right outer join (keep all right rows)

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

FULL OUTER JOIN

Full outer join (keep all rows)

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

CROSS JOIN

Cross join (all combinations)

SELECT * FROM colors CROSS JOIN sizes;

SELF JOIN

Self join (join table to itself)

SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

NATURAL JOIN

Natural join (auto-join on same columns)

SELECT * FROM orders NATURAL JOIN customers;

COUNT

Count rows

SELECT COUNT(*) FROM users;

SUM

Calculate sum

SELECT SUM(amount) FROM orders;

AVG

Calculate average

SELECT AVG(price) FROM products;

MAX

Get maximum value

SELECT MAX(salary) FROM employees;

MIN

Get minimum value

SELECT MIN(price) FROM products;

GROUP BY

Group and aggregate

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

HAVING

Filter after grouping

SELECT country, COUNT(*) AS cnt FROM users GROUP BY country HAVING cnt > 10;

GROUP_CONCAT (MySQL)

Concatenate values in group

SELECT department, GROUP_CONCAT(name) FROM employees GROUP BY department;

STRING_AGG (PostgreSQL)

Concatenate values in group

SELECT department, STRING_AGG(name, ', ') FROM employees GROUP BY department;

WHERE ... IN (SELECT)

Filter by subquery result

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

EXISTS

Check if subquery returns rows

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

スカラーサブクエリ

Scalar subquery returning single value

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

WITH (CTE)

Define Common Table Expression

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

WITH RECURSIVE

Define recursive CTE

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

UNION

Combine results (remove duplicates)

SELECT name FROM users UNION SELECT name FROM admins;

UNION ALL

Combine results (keep duplicates)

SELECT name FROM users UNION ALL SELECT name FROM admins;

ROW_NUMBER()

Assign row numbers

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

RANK()

Assign rank (gaps on ties)

SELECT name, RANK() OVER (ORDER BY score DESC) AS rank FROM students;

DENSE_RANK()

Assign rank (no gaps on ties)

SELECT name, DENSE_RANK() OVER (ORDER BY score DESC) AS rank FROM students;

LAG()

Get value from previous row

SELECT date, amount, LAG(amount) OVER (ORDER BY date) AS prev_amount FROM sales;

LEAD()

Get value from next row

SELECT date, amount, LEAD(amount) OVER (ORDER BY date) AS next_amount FROM sales;

SUM() OVER

Calculate running total

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

PARTITION BY

Apply window function within partition

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

NTILE()

Divide rows into N equal groups

SELECT name, NTILE(4) OVER (ORDER BY score DESC) AS quartile FROM students;

CREATE INDEX

Create an index

CREATE INDEX idx_users_email ON users (email);

CREATE UNIQUE INDEX

Create a unique index

CREATE UNIQUE INDEX idx_users_email ON users (email);

DROP INDEX

Drop an index

DROP INDEX idx_users_email;

PRIMARY KEY

Set primary key constraint

ALTER TABLE users ADD PRIMARY KEY (id);

FOREIGN KEY

Set foreign key constraint

ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);

UNIQUE

Set unique constraint

ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

CHECK

Set check constraint

ALTER TABLE users ADD CONSTRAINT chk_age CHECK (age >= 0);

NOT NULL

Set NOT NULL constraint

ALTER TABLE users MODIFY COLUMN name VARCHAR(100) NOT NULL;

DEFAULT

Set default value

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';