Explore/Computer science & programming/Databases and SQL

How do I store related data and query it correctly?

Build a SQLite reading log with keys, joins, constraints and rollback checks.

Computer science & programmingTopic · Selected readingBeginner friendly

Background and goal

No SQL background. Use SQLite’s shell without programming, or use Python after Python foundations. You need access to a terminal and a scratch database file.

A reproducible reading-log database with explained queries and a verified rollback.

Useful preparation

Start here if the background is new. Equivalent experience is enough.

Python foundations
If you choose the Python workflow, use functions and file handling to build the SQLite reading log.
Computer science & programmingBeginner

Your pathway

5 steps · 16 hours

01
Open a scratch database
2h · week 1
SQLite: SQLite In 5 Minutes Or Less
SQLite developers · reference
Creating a database and basic SQL
Check reading access
Open the reading
SQLite: Command Line Shell For SQLite
SQLite developers · reference
Getting started and dot commands
Check reading access
Open the reading
Python reference: sqlite3
Python Software Foundation · reference
Tutorial and SQL placeholders
Check reading access
Open the reading
Reading notes and task
SQLite: SQLite In 5 Minutes Or Less. Run the opening create, insert and select examples in a disposable database.
SQLite: Command Line Shell For SQLite. Open a scratch database; use .tables and .schema to inspect it.
Python reference: sqlite3. Use the tutorial for Python setup; bind user input with placeholders instead of building SQL strings.
Try thisUse SQLite’s command-line shell or Python’s sqlite3 tutorial to open a disposable reading-log database. Create one small table, insert an invented book and query it. Keep all exercises in this scratch database; no existing personal records are needed.
Your outputA scratch database and the SQL needed to recreate its first table.
02
Model books and reading entries
4h · week 1
SQLite: CREATE TABLE
SQLite developers · reference
Column definitions and constraints
Check reading access
Open the reading
SQLite: INSERT
SQLite developers · reference
Single-row and multi-row inserts
Check reading access
Open the reading
SQLite: Datatypes In SQLite
SQLite developers · reference
Storage classes and type affinity
Check reading access
Open the reading
SQLite: SQLite Foreign Key Support
SQLite developers · reference
Introduction and enabling foreign keys
Check reading access
Open the reading
Reading notes and task
SQLite: CREATE TABLE. Define a stable key and a NOT NULL field; test an invalid row.
SQLite: INSERT. Insert a few invented books and explicitly name the columns.
SQLite: Datatypes In SQLite. Compare declared column types with SQLite’s stored values.
SQLite: SQLite Foreign Key Support. Enable PRAGMA foreign_keys=ON for each connection before a transaction; test an orphan row.
Try thisDefine books(id INTEGER PRIMARY KEY, title TEXT NOT NULL) and readings(id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES books(id), minutes INTEGER NOT NULL CHECK(minutes >= 0)). Enable PRAGMA foreign_keys=ON on each connection before starting a transaction. Insert three invented books and reading entries; deliberately test an orphan book_id and a negative duration.
Your outputA reproducible schema, seed rows and two rejected invalid writes.
Check your understanding
Two invented books both have the title “Notes”. Why is the title a poor primary key? Propose a key and explain how a reading entry should refer to its book.
Hint
A display name and a stable row identity serve different purposes.
Compare with a worked answer
Use a distinct book id as the primary key, allowing titles to repeat. A reading entry stores book_id as a foreign key to books(id). In SQLite, enable foreign-key enforcement for each connection before a transaction, then test that an entry for a missing book is rejected. A title may also change, so it should not be the only identifier used by related records.
What to look for
Allow duplicate titles while keeping unique row identities.
Connect readings.book_id to books.id.
Enable and test SQLite foreign-key enforcement.
Common mistake: Assuming a familiar display name is necessarily unique or immutable.
Start this pathway to keep your answers in My learning.
03
Query without losing empty cases
4h · week 2
SQLite: SELECT
SQLite developers · reference
Simple SELECT, FROM/JOIN, WHERE, ORDER BY and GROUP BY
Check reading access
Open the reading
SQLite: Built-in Aggregate Functions
SQLite developers · reference
count(), sum() and avg()
Check reading access
Open the reading
SQLite: SQL Language Expressions
SQLite developers · reference
Comparisons, NULL and bound parameters
Check reading access
Open the reading
Reading notes and task
SQLite: SELECT. Predict a query result before running it; follow one joined row.
SQLite: Built-in Aggregate Functions. Compare COUNT(*) and COUNT(column) on a small table with a missing value.
SQLite: SQL Language Expressions. Use IS NULL for missing values and distinguish a value from SQL syntax.
Try thisWrite a filtered query with ORDER BY, then count reading entries per book. Use a LEFT JOIN so unread books remain visible and COUNT(readings.id) so their count is zero. Bind a title parameter with placeholders if using Python. Test a book with two entries, a book with one and a book with none.
Your outputThree explained queries with expected results for the empty case.
Check your understanding
Invented books have ids 1, 2 and 3. Reading entries have ids 10 and 11 for book 1, and id 12 for book 2; book 3 has none. Write a query that returns every book id and its reading-entry count. What counts should it return?
Hint
Start from books, preserve unmatched rows and count a reading-entry column.
Compare with a worked answer
SELECT books.id, COUNT(readings.id) AS entry_count FROM books LEFT JOIN readings ON readings.book_id = books.id GROUP BY books.id ORDER BY books.id; The result is (1, 2), (2, 1), (3, 0). LEFT JOIN keeps the unmatched book, and COUNT(readings.id) ignores the NULL placeholder for it. COUNT(*) would count that preserved row and incorrectly give the unread book a count of one.
What to look for
Use a LEFT JOIN from books to readings.
Group by the book id and count readings.id.
Return counts 2, 1 and 0, with an explanation of the empty case.
Common mistake: Using an inner join that hides unread books or COUNT(*) that counts their preserved row.
Start this pathway to keep your answers in My learning.
04
Check a failure and a backup
4h · week 3
SQLite: UPDATE
SQLite developers · reference
Overview and WHERE
Check reading access
Open the reading
SQLite: DELETE
SQLite developers · reference
Overview and WHERE
Check reading access
Open the reading
SQLite: SQLite Online Backup API
SQLite developers · reference
Overview
Check reading access
Open the reading
Reading notes and task
SQLite: UPDATE. Update a copied table and check which rows match before changing them.
SQLite: DELETE. Preview the selected rows before trying a delete in your scratch database.
SQLite: SQLite Online Backup API. Distinguish a consistent backup from copying a database file during active writes.
Try thisBefore trying an update or delete in the scratch database, select the rows its WHERE clause matches. Use Python’s connection backup method or SQLite’s .backup command to make a consistent copy, then open the copy and check its rows. Explain why a copied file during active writes may not be the backup you intended.
Your outputA mutation checklist and a restored scratch-database copy.
05
Ship a reproducible reading log
2h · week 4
SQLite: Transaction
SQLite developers · reference
BEGIN, COMMIT and ROLLBACK
Check reading access
Open the reading
Python reference: sqlite3
Python Software Foundation · reference
Tutorial and SQL placeholders
Check reading access
Open the reading
Reading notes and task
SQLite: Transaction. Group two related writes and verify that rolling back removes both.
Python reference: sqlite3. Use the tutorial for Python setup; bind user input with placeholders instead of building SQL strings.
Try thisPackage the schema, seed data and queries as SQL files or a small Python script. In an explicit transaction, insert a book and a reading entry, then roll back and verify that neither remains. Repeat with COMMIT. Document foreign-key setup and parameter binding, plus instructions that another learner can run on a fresh scratch database.
Your outputA reproducible SQLite reading log with query checks and a rollback demonstration.
Check your understanding
In an empty scratch database with the two tables already created, execute BEGIN; insert book id 4; insert a reading for book 4; then execute ROLLBACK. What should remain? How would the result differ with COMMIT?
Hint
Both inserts occur inside the same explicit transaction; the tables were created before it.
Compare with a worked answer
After ROLLBACK, both tables still exist but neither inserted row remains. The two writes were part of one transaction. Repeating valid inserts in a fresh transaction and using COMMIT preserves both rows. Foreign-key enforcement must already be enabled before BEGIN. Check the state with SELECT rather than assuming an error or rollback had the desired effect.
What to look for
Remove both inserted rows on rollback while retaining the pre-existing tables.
Preserve both valid rows on commit.
Verify the resulting state and enable foreign keys before BEGIN.
Common mistake: Letting two related writes commit independently and expecting a later rollback to undo both.
Start this pathway to keep your answers in My learning.

Compare perspectives

Editorial perspectives based on selected works, rather than author-endorsed reading lists.

Guided route

Editorial synthesis

Follow the selected readings and build the final output.

This introductory route covers selected foundations, not the entire field.

Data modelling

Editorial synthesis

Focus on identities, relationships and rejected invalid states.

Constraints cannot decide whether the information entered is factually true.

Queries and execution

Editorial synthesis

Focus on row counts, missing values and a query’s plan.

An efficient query can still answer the wrong question.

Key ideas

View connections

Relational data Primary key SQL joins Database transactions

All 20 resources

Background, different viewpoints, and further reading.

SQLite: SQLite In 5 Minutes Or Less
SQLite developersreferencebackground
Run the opening create, insert and select examples in a disposable database.
Creating a database and basic SQL
SQLite: Command Line Shell For SQLite
SQLite developersreferencebackground
Open a scratch database; use .tables and .schema to inspect it.
Getting started and dot commands
SQLite: Datatypes In SQLite
SQLite developersreferencebackground
Compare declared column types with SQLite’s stored values.
Storage classes and type affinity
SQLite: CREATE TABLE
SQLite developersreferencecore
Define a stable key and a NOT NULL field; test an invalid row.
Column definitions and constraints
SQLite: INSERT
SQLite developersreferencecore
Insert a few invented books and explicitly name the columns.
Single-row and multi-row inserts
SQLite: SELECT
SQLite developersreferencecore
Predict a query result before running it; follow one joined row.
Simple SELECT, FROM/JOIN, WHERE, ORDER BY and GROUP BY
SQLite: SQL Language Expressions
SQLite developersreferencecommentary
Use IS NULL for missing values and distinguish a value from SQL syntax.
Comparisons, NULL and bound parameters
SQLite: Built-in Aggregate Functions
SQLite developersreferencecore
Compare COUNT(*) and COUNT(column) on a small table with a missing value.
count(), sum() and avg()
SQLite: UPDATE
SQLite developersreferencecommentary
Update a copied table and check which rows match before changing them.
Overview and WHERE
SQLite: DELETE
SQLite developersreferencecriticism
Preview the selected rows before trying a delete in your scratch database.
Overview and WHERE
SQLite: SQLite Foreign Key Support
SQLite developersreferencecore
Enable PRAGMA foreign_keys=ON for each connection before a transaction; test an orphan row.
Introduction and enabling foreign keys
SQLite: Transaction
SQLite developersreferencecore
Group two related writes and verify that rolling back removes both.
BEGIN, COMMIT and ROLLBACK
SQLite: Atomic Commit In SQLite
SQLite developersreferenceextension
Read why a transaction must handle interrupted writes; do not treat this as a recovery guarantee for every setup.
Overview and single-file commit
SQLite: The SQLite Query Optimizer Overview
SQLite developersreferenceextension
Compare a lookup with and without a suitable index after the query is correct.
Index term usage
SQLite: EXPLAIN QUERY PLAN
SQLite developersreferenceextension
Inspect a query plan; note that its text format can change between releases.
Table and index scans
SQLite: CREATE INDEX
SQLite developersreferenceextension
Create an index for one real lookup and record its cost as well as its benefit.
Overview
SQLite: ALTER TABLE
SQLite developersreferenceextension
Try a schema change on a copy and keep a reproducible migration note.
Adding columns
SQLite: The WITH Clause
SQLite developersreferenceextension
Name an intermediate query result without introducing recursive SQL yet.
Ordinary common table expressions
SQLite: SQLite Online Backup API
SQLite developersreferencecriticism
Distinguish a consistent backup from copying a database file during active writes.
Overview
Python reference: sqlite3
Python Software Foundationreferencecore
Use the tutorial for Python setup; bind user input with placeholders instead of building SQL strings.
Tutorial and SQL placeholders

Continue learning

Building web applications
Compare browser storage with a relational database and explain when the distinction matters.
Computer science & programmingBeginner

Finding the next step…

How do I store related data and query it correctly?

Useful preparation

Your pathway

Open a scratch database

Model books and reading entries

Query without losing empty cases

Check a failure and a backup

Ship a reproducible reading log

Guided route

Data modelling

Queries and execution

Key ideas

Continue learning

How do I store related data and query it correctly?

Useful preparation

Your pathway

Open a scratch database

Model books and reading entries

Query without losing empty cases

Check a failure and a backup

Ship a reproducible reading log

Guided route

Data modelling

Queries and execution

Key ideas

Continue learning