sql
CREATE TABLE users (
user_id INTEGER PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL
);Notes
Use explicit types and constraints to enforce data quality.
SQL DDL and DML
Create and change schema objects, then insert, update, delete, merge, and manage transactional data changes.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
CREATE, ALTER, and DROP
Build and change schema objects.
sql
ALTER TABLE users
ADD COLUMN last_login_at TIMESTAMP;Notes
ALTER TABLE changes schema without recreating the table.
Add a constraint
Attach a foreign key or check constraint after table creation.
sqlANYalter-tableconstraintforeign-key
sql
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);Notes
Constraints help maintain relational integrity.
sql
DROP TABLE old_logs;Notes
Be careful: dropping a table deletes its data. Some engines support IF EXISTS.
sql
TRUNCATE TABLE staging_events;Notes
TRUNCATE is often faster than DELETE for clearing a table.
Constraints and Indexes
Enforce integrity and speed up lookups.
sql
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL
);Notes
Primary keys uniquely identify each row.
sql
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);Notes
Unique constraints prevent duplicate values.
sql
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price >= 0);Notes
CHECK constraints enforce domain rules inside the database.
sql
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);Notes
Indexes speed up reads but add write overhead and storage cost.
sql
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);Notes
Column order matters in composite indexes.
INSERT, UPDATE, and DELETE
Modify table data.
sql
INSERT INTO users (user_id, email, created_at)
VALUES (1, 'a@example.com', CURRENT_TIMESTAMP);Notes
Specify column names explicitly for clarity and safety.
sql
INSERT INTO roles (role_id, role_name)
VALUES
(1, 'admin'),
(2, 'editor'),
(3, 'viewer');Notes
Multi-row INSERT is concise and often more efficient than repeated single-row inserts.
sql
INSERT INTO active_customers (customer_id, email)
SELECT customer_id, email
FROM customers
WHERE status = "active";Notes
Great for staging and snapshot tables.
sql
UPDATE users
SET status = 'inactive'
WHERE last_login_at < DATE '2024-01-01';Notes
Always confirm the WHERE clause before running UPDATE in production.
sql
DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;Notes
DELETE removes rows one by one from the table.
Merge or upsert data
Insert new rows and update existing ones in one logical operation.
sqlANYmergeupsertdml
sql
MERGE INTO inventory AS target
USING incoming_inventory AS src
ON target.sku = src.sku
WHEN MATCHED THEN UPDATE SET quantity = src.quantity
WHEN NOT MATCHED THEN INSERT (sku, quantity) VALUES (src.sku, src.quantity);Notes
MERGE support and exact syntax vary by dialect. Some engines use ON CONFLICT or ON DUPLICATE KEY UPDATE instead.
Transactions
Control units of work and isolation boundaries.
Begin, commit, and rollback
Wrap multiple changes in one transaction.
sqlANYtransactionsbegincommitrollback
sql
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;Notes
If something fails, use ROLLBACK instead of COMMIT.
sql
BEGIN;
SAVEPOINT before_optional_step;
UPDATE orders SET status = "archived" WHERE order_id = 10;
ROLLBACK TO SAVEPOINT before_optional_step;
COMMIT;Notes
Savepoints let you recover from part of a transaction without aborting the whole unit of work.
Set transaction isolation level
Control how concurrent transactions interact.
sqlANYtransactionsisolation
sql
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;Notes
Isolation level support can vary across engines, but this is a core transactional concept.
More in SQL
SQL Indexing and Performance
Query tuning patterns with EXPLAIN, indexing, keyset pagination, deduplication, and optimization-oriented SQL recipes.
SQL Window Functions and CTEs
Use CTEs, recursive queries, ranking, running totals, partitions, and lag/lead patterns for analytical SQL.
SQL Functions and Aggregation
Reference for string, date, numeric, conditional, null-handling, and aggregate SQL functions.
SQL Joins and Subqueries
Advanced relational query patterns for joins, subqueries, EXISTS, IN, set operations, and correlated queries.
SQL Cheat Sheet
Core SQL syntax and patterns for querying, filtering, sorting, joining, grouping, and modifying data.