SQL Study Guide Cheat Sheet
Core syntax & mental models
Focus: ANSI SQL with notes for common dialects (PostgreSQL, Oracle, MySQL).
Golden rule: SELECT FROM WHERE GROUP BY HAVING WINDOW ORDER BY.
Practice everyday: small queries beat long theory.
Generated: 2025-08-30 09:00 Page 1/12
SELECT & FROM
Basics
SELECT column, expression, function; aliases via AS.
FROM tables, views, subqueries, common table expressions (WITH ... AS ...).
Tip: explicitly list columns; avoid SELECT * in production queries.
Generated: 2025-08-30 09:00 Page 1/12
WHERE & Operators
Filtering
Comparison: =, <>, <, >, <=, >=; logical: AND/OR/NOT; NULL with IS (NOT) NULL.
Predicates: BETWEEN, IN(...), LIKE (use % and _), EXISTS with subqueries.
Beware NULL semantics: 3-valued logic; COALESCE for defaults.
Generated: 2025-08-30 09:00 Page 1/12
JOINs
Inner, outer, cross
INNER JOIN: match on keys; LEFT/RIGHT JOIN: preserve rows.
FULL OUTER JOIN: keep all with NULLs for missing sides.
ON vs USING; NATURAL JOIN pitfalls; prefer explicit keys.
Generated: 2025-08-30 09:00 Page 1/12
Aggregation
GROUP BY & HAVING
Aggregates: COUNT, SUM, AVG, MIN, MAX; DISTINCT vs ALL.
GROUP BY columns must appear in SELECT unless inside aggregates.
HAVING filters after aggregation; WHERE filters before.
Generated: 2025-08-30 09:00 Page 1/12
Window Functions
Analytics powerhouse
OVER(PARTITION BY ... ORDER BY ...) with functions: ROW_NUMBER, RANK, DENSE_RANK.
Moving calcs: LAG/LEAD, running totals with SUM() OVER(ORDER BY ...).
Frames: ROWS/RANGE; default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Generated: 2025-08-30 09:00 Page 1/12
Subqueries & CTEs
Decomposition & reuse
Scalar, row, table subqueries; correlated vs uncorrelated.
WITH CTE AS (...) improves readability; recursive CTEs for hierarchies.
Materialization hints vary by engine; measure before relying on them.
Generated: 2025-08-30 09:00 Page 1/12
Set Operations
UNION/INTERSECT/EXCEPT
UNION removes duplicates; UNION ALL keeps them.
INTERSECT and EXCEPT for overlaps and differences (name varies: MINUS in Oracle).
Operands must be union-compatible: same number and types of columns.
Generated: 2025-08-30 09:00 Page 1/12
DDL Essentials
Schemas & tables
CREATE/ALTER/DROP TABLE; constraints: PRIMARY KEY, UNIQUE, CHECK, FOREIGN KEY.
Data types: choose smallest sufficient; avoid misuse of strings for numerics/dates.
Indexes: create where selective and used in joins/filters.
Generated: 2025-08-30 09:00 Page 1/12
DML & Transactions
INSERT/UPDATE/DELETE & ACID
INSERT INTO ... VALUES/SELECT; UPSERT patterns differ by engine (MERGE, ON CONFLICT).
UPDATE with join; DELETE with caution use transactions and WHERE.
ACID: atomicity, consistency, isolation, durability; isolation levels.
Generated: 2025-08-30 09:00 Page 1/12
Indexes & Explain
Performance basics
B-Tree vs Hash vs GiST/GIN (full-text & geo).
Use EXPLAIN/EXPLAIN ANALYZE to inspect plans; look for seq scans vs index scans.
Anti-patterns: functions on indexed columns, leading wildcards in LIKE.
Generated: 2025-08-30 09:00 Page 1/12
Normalization & Anti-Patterns
Design sanity
1NF, 2NF, 3NF, BCNF decompose to remove anomalies.
Denormalize for analytics cautiously; document ETL lineage.
Anti-patterns: EAV tables, overuse of NULLs, hidden temporal logic.
Generated: 2025-08-30 09:00 Page 1/12
