Lesson 05 · tested on Debian 13
SQLite in the terminal: query a CSV file in 5 minutes (and the text-number trap)
Turn a CSV file into a database with one sqlite3 command, then answer real questions with SQL: totals per region, sales in a date range, monthly sums, and an exported report. Along the way, see why imported numbers sort wrong (they arrive as text) and how to fix it with a typed table.
Video coming soon — @ShellProven
Commands
Exactly as run in the video. Full folder with setup script and log: terminal-tutorials/sqlite-csv.
# The CSV file
$ cat sales.csv
date,region,product,amount
2026-01-14,north,keyboard,49.90
2026-01-20,south,monitor,189.00
2026-02-03,north,mouse,9.50
2026-02-11,west,monitor,179.00
2026-02-27,south,keyboard,52.00
2026-03-05,west,mouse,12.00
2026-03-09,north,monitor,199.00
2026-03-18,south,mouse,8.75
2026-03-30,west,keyboard,47.50
# Import with one command
$ sqlite3 shop.db ".import --csv sales.csv sales"
$ sqlite3 shop.db ".schema sales"
CREATE TABLE IF NOT EXISTS "sales"(
"date" TEXT, "region" TEXT, "product" TEXT, "amount" TEXT);
# The trap: numbers stored as text
$ sqlite3 shop.db "SELECT product, amount FROM sales ORDER BY amount DESC LIMIT 3"
mouse|9.50
mouse|8.75
keyboard|52.00
$ sqlite3 shop.db "SELECT product, amount FROM sales ORDER BY CAST(amount AS REAL) DESC LIMIT 3"
monitor|199.00
monitor|189.00
monitor|179.00
# The real fix: a typed table
$ rm shop.db && sqlite3 shop.db "CREATE TABLE sales(date TEXT, region TEXT, product TEXT, amount REAL)"
$ sqlite3 shop.db ".import --csv --skip 1 sales.csv sales"
$ sqlite3 shop.db "SELECT typeof(amount), COUNT(*) FROM sales GROUP BY 1"
real|9
# Answering questions with GROUP BY
$ sqlite3 -header -column shop.db "SELECT region, COUNT(*) AS orders, ROUND(SUM(amount), 2) AS total FROM sales GROUP BY region ORDER BY total DESC"
region orders total
------ ------ ------
north 3 258.4
south 3 249.75
west 3 238.5
# Dates and months
$ sqlite3 -header -column shop.db "SELECT date, product, amount FROM sales WHERE date >= '2026-03-01' ORDER BY date"
date product amount
---------- -------- ------
2026-03-05 mouse 12.0
2026-03-09 monitor 199.0
2026-03-18 mouse 8.75
2026-03-30 keyboard 47.5
$ sqlite3 -header -column shop.db "SELECT substr(date, 1, 7) AS month, ROUND(SUM(amount), 2) AS total FROM sales GROUP BY month"
month total
------- ------
2026-01 238.9
2026-02 240.5
2026-03 267.25
# Export the result back to CSV
$ sqlite3 -header -csv shop.db "SELECT product, ROUND(AVG(amount), 2) AS avg_price FROM sales GROUP BY product" > report.csv
$ cat report.csv
product,avg_price
keyboard,49.8
monitor,189.0
mouse,10.08
Quick check
Same two questions as in the video. Checked here, in your browser — nothing is sent anywhere.
Transparency. Script and narration made with AI, human-reviewed. The robot and the dog are AI-generated cartoon characters. The commands and their output are real runs in a container.