Extend your SQLite skills across 17 guided labs covering richer queries, integrity rules, automation, specialized search, analytics, backup, tuning, and maintenance. Each lab works with a small local database so you can execute the relevant SQL or CLI commands and inspect the resulting schema, rows, query plan, or database state.
The course has broad intermediate-to-advanced topic coverage but retains a step-by-step learning format. You will move from constraints and indexes through joins, subqueries, transactions, triggers, views, FTS5, CTEs, window functions, and operational commands without having to design a single large application.
What You Will Learn
- Enforce integrity with foreign keys,
CHECKconstraints, composite keys, andON CONFLICTactions - Create and remove indexes, inspect
EXPLAIN QUERY PLAN, rebuild indexes, runANALYZE, and reclaim space withVACUUM - Combine and summarize data with inner and left joins, multi-table joins, aggregates,
GROUP BY,HAVING, and correlated subqueries - Control changes with
BEGIN,COMMIT,ROLLBACK, savepoints, and constraints that reject invalid balances - Automate audit records with triggers and build simple, joined, aggregate, and trigger-backed updatable views
- Create FTS5 virtual tables and use
MATCH, prefix, column, and boolean full-text searches - Store JSON text and expose a custom Python
json_extractSQL function for extraction, filtering, and updates - Export and restore SQL dumps, configure PRAGMAs, use temporary tables, write simple and recursive CTEs, and calculate rankings and running totals with window functions
Who This Course Is For
This course is for developers, analysts, and database learners who already know how to create SQLite databases and tables and write basic SELECT, INSERT, UPDATE, and DELETE statements. It is useful when you want guided exposure to a wide range of SQLite-specific features before applying them in a larger project.
Prerequisites: Basic SQLite CLI and SQL experience is recommended, including table creation, primary keys, simple filtering, and CRUD operations. Basic Python familiarity is helpful for the JSON lab but is not needed for most of the course.
Learning environment: All 17 Beginner-level guided labs run in an Ubuntu 22.04 desktop VM, primarily with the sqlite3 CLI. The course contains 84 task steps and 101 automated checks; the JSON lab additionally creates and runs a Python script with the standard sqlite3 and json modules.
Frequently Asked Questions
How advanced is the course in practice?
Its subject list reaches beyond basic SQL, including recursive CTEs, window functions, FTS5, triggers, and query plans. However, all 17 labs are labeled Beginner and provide step-by-step commands on small sample databases, so the course emphasizes guided feature practice rather than independent database engineering.
Does the JSON lab use SQLite’s native JSON functions?
No. It stores JSON as text and defines a Python function named json_extract, then registers that function on a Python SQLite connection. This is different from practicing SQLite’s built-in JSON operators or JSON functions.
What kind of backup is performed?
The backup lab uses the CLI .dump command to create a text SQL dump and restores it by feeding that SQL into a new database. It does not cover the online backup API, binary file-copy safety, scheduled backups, or point-in-time recovery.
Does this prepare me to tune a production database?
It introduces indexes, query plans, journal mode, foreign-key checks, cache size, integrity checks, ANALYZE, and VACUUM. It does not benchmark real workloads or cover concurrent writers, application connection management, failure testing, or production monitoring.
