SQLite Tutorial for Beginners: A Reading-List Database

SQLite runs in the process that uses it. A database file contains the stored data and schema, while an in-memory connection can provide a disposable database for exercises. There is no server-style CREATE DATABASE or USE statement to copy from a MySQL lesson. Opening or attaching a database is handled through the SQLite interface and supported connection features.

The sqlite3 command-line shell has dot commands for tasks such as output mode and importing files. Those commands are shell instructions, not ordinary SQL to send through every driver. Keep the distinction clear when moving a script from the shell into application code. A file’s access permissions and deployment location matter because SQLite does not provide the same server account-and-role model as PostgreSQL or SQL Server.

Ordinary SQLite columns use type affinity rather than the rigid typing assumptions of many server schemas. A DECIMAL declaration does not provide fixed-precision decimal arithmetic by itself. SQLite values have storage classes, and conversions follow documented affinity rules. This is useful flexibility, but an import pipeline must not mistake accepted malformed values for validated domain data.

STRICT tables impose stronger type rules using a limited set of permitted type names such as INTEGER, REAL and TEXT. The reading-list table uses INTEGER counts and TEXT labels. STRICT does not replace NOT NULL, CHECK or business validation. A positive page-count rule still needs a constraint, and a title that is present but meaningless needs a separate application or domain policy.

INTEGER PRIMARY KEY aliases the rowid in the relevant ordinary form and can generate an id when omitted. AUTOINCREMENT adds a specific nonreuse policy and is usually not needed for basic identity. It does not promise gap-free numbering. The example supplies explicit ids only to make its expected rows easy to compare.

pages_read can be NULL here because a book may be on the list before reading progress has been recorded. Zero pages_read would mean a known starting measurement, while NULL means no measurement was saved. Arithmetic total_pages-pages_read is unknown for that record. A report can intentionally choose a fallback, but should state whether unrecorded progress is being assumed to equal zero.

The first query keeps unfinished books whose progress is known. Its WHERE pages_read<total_pages naturally excludes an unknown progress comparison. ORDER BY remaining_pages DESC,id makes the priority stable. The alias describes a numeric calculation rather than modifying the saved total or progress.

A complete reading-list display would need a separate treatment for unrecorded progress, such as an explicit Unknown group. Do not silently leave those books out of an interface that promises to show every unfinished or unstarted title. Define whether a finished state is stored directly or derived from progress, and avoid allowing those two models to contradict one another.

BEGIN, COMMIT and ROLLBACK group changes on a connection. SQLite has a single writer at a time in its ordinary concurrency model, even though reading concurrency and journaling modes provide useful options. WAL mode changes operational behaviour but is not a substitute for understanding application transaction length and contention. Keep write transactions as short as the business operation allows.

Foreign-key enforcement must normally be enabled per connection with PRAGMA foreign_keys=ON. A REFERENCES declaration is not enough to assume every connection enforces the relationship. Change the setting outside an active transaction and verify it. The reading-list fixture has one table, but an author-and-books extension should include actual foreign-key checks rather than merely similarly named columns.

SQLite supports modern CTEs and window functions, but feature availability depends on the library version shipped with the application. RIGHT and FULL joins arrived in version 3.39. Quantified ANY and ALL subquery comparison syntax is not supported. Date functions operate on supported textual or numeric representations rather than a native date storage class.

Backups should use a supported consistent method, such as SQLite’s backup API, rather than blindly copying an actively changing database file and ignoring its journal state. EXPLAIN QUERY PLAN can inspect access strategy, but its display format is not a stable application parsing contract. For a larger reading library, index only the filters and relationships that measurements justify.

The four books include one finished title, two unfinished titles with known progress and one title whose progress has not been recorded. The integer counts make the remaining-page calculation exact for this domain.

CREATE TABLE reading_books (
 id INTEGER PRIMARY KEY,title TEXT NOT NULL,
 total_pages INTEGER NOT NULL CHECK(total_pages>0),
 pages_read INTEGER CHECK(pages_read>=0 AND pages_read<=total_pages)
) STRICT;
INSERT INTO reading_books (id,title,total_pages,pages_read) VALUES
 (1,'River',200,50),(2,'Cloud',120,120),
 (3,'Stone',300,NULL),(4,'Field',80,20);

Return titles with unrecorded progress and their total page count. Use the dedicated missing-value predicate.

SELECT title,total_pages FROM reading_books
WHERE pages_read IS NULL ORDER BY id;

title | total_pages
Stone | 300

Stone is the only record whose pages_read value is NULL. Its total page count is known, so the report has enough context to request the missing measurement. The query does not assume the book is unstarted and does not change its saved progress. If the product deliberately treats new books as zero progress, store or document that policy consistently rather than deriving it accidentally from a generic fallback.

Read the SQLite quickstart, STRICT-table specification, datatype guide and foreign-key guide for the corresponding engine rules.