SQLite in Practice: Track Your Spending
Put your SQL skills to work with SQLite, the database in a single file: import bank statements, clean them up, answer real money questions and use it all from Python.
A whole database in one file
SQLite is the most widely used database in the world. It's inside every smartphone, every major web browser and thousands of apps — and unlike most databases, there's no server to install or run. The whole database is one ordinary file you can copy, email or back up like a document.
In this course you'll use it for something useful: turning the CSV files your bank lets you download into a personal budget database. You'll follow Alex, who works casual shifts at a café, through three months of spending.
What you'll learn
- What makes SQLite different from server databases like PostgreSQL — and when to choose it
- The sqlite3 command-line tool and the free DB Browser for SQLite
- Import CSV files, clean up messy dates and amounts, and remove duplicates from overlapping downloads
- SQLite's own rules: flexible types and STRICT tables, dates as text, and the foreign-keys setting that catches people out
- Categorise spending, compare it with a budget and build monthly reports
- Use SQLite from Python safely, back it up, and export to a spreadsheet
Before you start
You should know basic SQL: SELECT, WHERE, GROUP BY and joins. PostgreSQL Foundations teaches all of it — almost everything there works in SQLite too. Module 5 uses a little Python; Python Foundations covers what you need.
How it works
- 5 modules, 15 lessons, about 5½ hours including practice and quizzes
- Uses the current version, SQLite 3.54 — older versions (like the one built into a Mac) work too, with a small display difference we'll show you
- Every command in the course was run in a real terminal, and what you see is exactly what appeared on screen
- Practice questions in every lesson, with answers in the starter files
- A quiz after each module, a cheat sheet and a certificate
The bank files are made up, but they look just like the real thing — including the annoying parts.
Free forever — like every self-paced course on Avro Academy: all lessons, quizzes, downloads and your certificate. Our promise
Course outline
Module 1: Meet SQLite
What makes SQLite different, installing sqlite3 and DB Browser, and your first database file.
- What SQLite isFree preview10 min
- Install sqlite3 and DB Browser15 min
- Your first database file20 min
- Module 1 quiz5 min
Module 2: Getting data in
Importing CSV files, cleaning them into a proper table, and removing duplicates from overlapping downloads.
- Import a CSV file20 min
- Clean it into a proper table25 min
- Duplicates from overlapping files20 min
- Module 2 quiz5 min
Module 3: How SQLite is different
Flexible types and STRICT tables, dates as text, and keys and the foreign keys switch.
- SQLite's flexible types20 min
- Dates and times20 min
- Keys and the foreign keys switch20 min
- Module 3 quiz5 min
Module 4: Answering money questions
Categorising with rules, monthly reports against a budget, running balances, views and exporting.
- Categorise the spending25 min
- Monthly reports and the budget25 min
- Views and sharing results20 min
- Module 4 quiz5 min
Module 5: Python and beyond
Using SQLite from Python safely, backups, sharing, and choosing between SQLite and PostgreSQL.
- SQLite from Python25 min
- Adding data safely from Python20 min
- Backups, sharing and choosing a database25 min
- Module 5 and final quiz5 min