SQLite Tutorial - An Easy Way to Master SQLite Fast

SQLite Tutorial Overview

• What is SQLite: An open-source, zero-configuration, self-contained, and transaction-based relational database engine designed for embedding in desktop, web, and mobile applications.
• Getting Started: The tutorial covers installation, setting up a sample database, and using sqlite3 command-line tools.
• Data Filtering: Covers standard SQL filtering techniques including DISTINCT, WHERE, AND/OR, LIMIT, BETWEEN, IN, LIKE, GLOB, and IS NULL.
• Table Joins: Explains INNER, LEFT, RIGHT, CROSS, SELF, and FULL OUTER joins.
• Set Operators: Details the use of UNION (and UNION ALL), EXCEPT, and INTERSECT.
• Subqueries & CTEs: Covers correlated subqueries, the EXISTS operator, and Common Table Expressions for query readability.
• Data Modification: Instructions for INSERT, UPDATE, DELETE, REPLACE, UPSERT, and the RETURNING clause.
• Data Definition (DDL):
  • Explains SQLite’s dynamic type system (storage classes, manifest typing, type affinity).
  • Covers creating, altering, renaming, and dropping tables.
  • Includes database optimization via the VACUUM command.
• Constraints: Defines PRIMARY KEY, NOT NULL, UNIQUE, CHECK, and AUTOINCREMENT (with advice on when to avoid the latter).
• Database Management Tools: Guides on listing tables, describing table structures, backing up/restoring via .dump, and importing/exporting CSV data.