Foundations
SQL Simplified
SELECT, JOINs, GROUP BY and window functions explained one small table at a time.
- Handwritten-style
- Diagrams and tables
- Worked examples
Who this notebook is for
Beginners learning SQL for the first time, and working analysts who want the gaps filled before a data engineering interview.
What you will be able to do
- Write SELECT, WHERE, GROUP BY and JOIN queries and predict their output
- Handle NULLs correctly instead of getting silently wrong counts
- Use CTEs, subqueries and window functions for ranking and running totals
- Read a query plan and know why an index or a rewrite makes a query faster
What's inside
Pick a chapter and the notebook opens right there.
- Relational FoundationsRDBMS Engine Architecture, Primary/Foreign Keys & ConstraintsStart here
- DDL, DML & ACIDCREATE/ALTER/DROP, TRUNCATE vs DELETE, Transactions & IsolationStart here
- Retrieval & NULL LogicLogical Query Order, 3-Valued Logic (3VL), NULL Pitfalls & Truth TablesStart here
- Patterns, Sort & PaginationLIKE, REGEXP, ORDER BY NULLS FIRST/LAST, Keyset CursorsStart here
- Grouping & AggregationsCOUNT(*) vs COUNT(col), GROUP BY Pipeline & Single- Value RuleStart here
- Advanced GroupingHAVING vs WHERE boundary, GROUPING SETS, ROLLUP & CUBEStart here
- Core JoinsINNER, LEFT, RIGHT, FULL OUTER, CROSS joins & Multi-table GraphsStart here
- Complex Joins & EnginesSELF Joins, Non-Equi Joins, Nested Loop, Hash & Merge JoinsStart here
- SubqueriesScalar, Column, Correlated Subqueries, EXISTS vs IN & Derived TablesStart here
- CTEs & RecursionWITH Common Table Expressions, Recursive Trees & Date SequencesStart here
- Window Functions (Ranking)OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, NTILEStart here
- Window Navigation & FramesLEAD, LAG, FIRST_VALUE, ROWS BETWEEN vs RANGE BETWEENStart here
- Built-in FunctionsText parsing (SUBSTRING, TRIM), Date arithmetic & Math roundingsStart here
- Conditionals & UPSERTCASE expressions, Pivoting, COALESCE, NULLIF & ON CONFLICTStart here
- Indexing StructuresB-Trees, Clustered vs Secondary Indexes, Composite Leftmost RuleStart here
- Query TuningReading EXPLAIN plans, Cost models, Sargability & Optimizer statisticsStart here
- Database Design1NF, 2NF, 3NF, BCNF Normalization, ER Modeling & Foreign Key cascadesStart here
- FAANG Interview Part INth Highest Salary, Consecutives, Retention & Self-joinsStart here
- FAANG Interview Part IIGaps & Islands, Medians, Churn, Trees & Market BasketStart here
- Modern Analytical SQL & VectorsQUALIFY, PIVOT/UNPIVOT, HyperLogLog, pgvector & 12 CommandmentsStart here
- Gap-Fill SupplementLATERAL/CROSS APPLY, row-level security & SQL injection, CTE materialization/hints/TABLESAMPLE, write skew & shardingStart here
Practice this
Put it to work on LakeBench.
Read the guide first
Short explainers that pair with this notebook.
- SQL for beginners: write your first queries with SELECT, WHERE and ORDER BY
- SQL joins explained for beginners: INNER, LEFT, RIGHT and FULL with real output
- SQL GROUP BY and HAVING explained for beginners, with real output
- SQL NULL explained for beginners: IS NULL, COALESCE and the traps
- SQL subquery vs CTE for beginners: what they are and when to use each
- UNION vs UNION ALL in SQL: what is the difference?
- DELETE vs TRUNCATE vs DROP in SQL: the differences, with examples
- SQL interview questions for data engineers: joins, NULLs, pivots and hierarchies
- SQL window function interview questions, with worked answers
- Primary key vs foreign key explained for beginners, with SQL examples
Common questions
- Which SQL dialect do the notes use?
- They teach standard SQL ideas that carry across databases. Examples use small tables so you can follow each result by eye.
- Does it cover interview questions?
- Yes. The last chapters are interview-style problems, and the SQL Scenarios notebook goes much deeper on patterns such as gaps and islands and sessionization.
- Can I read a sample before I commit?
- Yes. The contents pages and the first pages of every notebook can be read online, so you can judge the style before going further.
