SQLite / Intermediate: query structure and transactional design

SQLite β€” Intermediate: query structure and transactional design

Use staged queries, savepoints, indexes, plans, windows, and JSON with explicit scope and compatibility.

6 lessons Β· No account required

What you will learn

  • Build correct analytical and transactional SQL.
  • Justify access paths without unsupported performance promises.

  1. Subqueries, CTEs, and views

    Express existence tests and staged transformations while distinguishing statement-scoped query names, persistent views, and actual stored intermediate data.

    Start learning

  2. Transactions, savepoints, and error outcomes

    Combine related SQLite changes into one outcome and handle statement failure, transaction state, savepoint rollback, and connection policy explicitly.

    Start learning

  3. Indexes, expression indexes, and partial indexes

    Design SQLite indexes around concrete predicates, output needs, and write costs while using expression and partial-index rules accurately.

    Start learning

  4. Query plans, statistics, and measured evidence

    Interpret SQLite EXPLAIN QUERY PLAN without treating its text as a stable API, and distinguish useful access paths from unsupported performance claims.

    Start learning

  5. Window functions, ranking, and frames

    Calculate per-group rankings and running measures while preserving detail rows and making tie rules, frames, and final presentation explicit.

    Start learning

  6. JSON functions, scalar types, and JSONB

    Use SQLite JSON data deliberately while distinguishing SQL NULL, JSON null, text values, arrays, and the opaque version-gated JSONB representation.

    Start learning
Back to course