SQL Advanced Recap — Constraints, Design, and Tuning at a Glance
A recap of the SQL Advanced series. Cross-reference advanced UPDATE/DELETE and UPSERT, constraints and foreign keys, type affinity and STRICT, normalization and views, and index design and tuning starting from EXPLAIN QUERY PLAN — with diagrams and an article index.
The big picture of what you learned in SQL Advanced
This page pulls the SQL Advanced series together on a single page, viewed through three lenses: constraints and design that keep data safe, a readable, correct schema, and indexes and tuning for fast queries.
After picking up the grammar in the Basics and aggregation, joins, and window functions in the Intermediate series, Advanced was about keeping data correct and making it run fast — design and tuning.
Use the links to any article to jump back for review anytime.
Keeping data correct — advanced writes, constraints, foreign keys, types, design
We covered advanced UPDATE / DELETE and UPSERT with subqueries and joins, column constraints and foreign keys, type affinity and STRICT, and on to normalization, views, and design practice.
The diagram below groups the advanced DML and constraints/types together, and the design/views together.
| Category | Article | What you learn |
|---|---|---|
| Advanced writes | UPDATE / DELETE with Subqueries and JOINs | Correlated updates, UPDATE … FROM, DELETE with subquery conditions |
| Advanced writes | UPSERT (ON CONFLICT) and Bulk INSERT Applications | INSERT … ON CONFLICT DO UPDATE, excluded, bulk INSERT |
| Constraints | Column Constraints — NOT NULL / UNIQUE / CHECK / PRIMARY KEY | NOT NULL / UNIQUE / CHECK / PRIMARY KEY and violations |
| Foreign keys | Foreign Key Constraints and Referential Actions | REFERENCES, ON DELETE / UPDATE, foreign_keys=ON |
| Constraint changes | Adding/Changing Constraints and Table Redefinition | Retrofitting constraints, the table-rebuild procedure |
| Types | Strings, Numbers, Booleans and Implicit Type Conversion | Type affinity, implicit conversion, vs. strict types |
| Types | Date/Time Types and STRICT Tables | Storing and comparing date/time, STRICT tables |
| Design | Table Design and Normalization | 1NF to 3NF, removing repeating columns, 3-tier schema |
| Design | Views (VIEW) | CREATE VIEW, using views, updatable views |
Making queries fast — execution plans, indexes, tuning
Read plans with EXPLAIN QUERY PLAN, understand statistics, the optimizer, and join algorithms, get a handle on index design, index-only scans, indexing for sorts and groups, and the patterns where indexes don't kick in, then finish with query rewrites.
The table below organizes each article.
| Category | Article | What you learn |
|---|---|---|
| Execution plan | Reading Execution Plans | Vocabulary: SCAN / SEARCH / USE TEMP B-TREE and more |
| Statistics | Statistics and the Optimizer | ANALYZE, sqlite_stat1, selectivity and plan changes |
| Joins | Join Algorithms and Join Order | Nested loop, join order, automatic indexes |
| Indexes | Indexes — Composite, Partial, Expression, UNIQUE | Composite, partial, expression, UNIQUE indexes with EXPLAIN |
| Indexes | Index Design | Composite column order, leftmost prefix, conditions that use the index |
| Indexes | Index-Only Scan | Index-only scan, USING COVERING INDEX |
| Indexes | Indexing ORDER BY / GROUP BY | Avoiding USE TEMP B-TREE, indexing sorts and groups |
| Indexes | When Indexes Don't Kick In | Functions on columns, implicit casts, LIKE, OR, and expression/partial index fixes |
| Rewrites | Query Rewrites ① IN / EXISTS and decorrelation | IN vs EXISTS, removing correlation, semi-join / anti-join |
| Rewrites | Query Rewrites ② removing repeated work, worked examples | Eliminating repeated work, subquery optimization with examples |
Nice work!
By now you've pulled together advanced UPDATE / DELETE and UPSERT, the design side with constraints, foreign keys, types, normalization, and views, and the tuning side with index design and more, starting from EXPLAIN QUERY PLAN.
This also marks the finish of all three tiers of the SQL course — Basics, Intermediate, and Advanced.
Great job making it all the way through.
Head back to any article that caught your eye and keep experimenting with your own data.