programming · Level 3
SQL: model and query your first database
Build a small SQLite database with tools that come with Python, question it, change it safely and check SQL written by others, including AI assistants.
Start with the essentials
The short answer
SQL is the standard language for defining, filling and questioning relational databases, where data lives in tables linked by keys. SQLite keeps a whole database in one file, needs no server and comes with Python. Start with strict tables and constraints, switch foreign keys on, preview every change with a SELECT on a copy, and pass values to SQL with placeholders.
What you will learn
- You will be able to design related tables with primary keys, foreign keys and constraints, and say why each exists.
- You will be able to write queries that filter, sort, group, join and nest, and handle NULL on purpose.
- You will be able to change data safely: on a copy, after a preview, inside a transaction you can roll back.
- You will be able to read a query plan and add an index where it helps.
- You will be able to use SQL from Python with placeholders, and explain SQL injection.
- You will be able to review SQL an AI assistant writes against the real schema before you run it.
Who it is for
Adults who have never used a database and want to store and question data reliably, for a small business, a club, a project or their own programs. You should be happy to type commands into a terminal. Some Python helps in chapter 5 but is not required.
Before you start
- No database experience is needed. You need a computer with Python 3.12 or later and the confidence to run commands in a terminal. The Python: from first script to a useful automation workbook covers installing Python and is useful background for chapter 5.
Keep learning
The complete workbook
This workbook takes someone who has never used a database to a small, well-designed SQLite database for an invented plant nursery. You will define tables, keys and constraints, query with filters, groups, joins and subqueries, change data safely on copies and inside transactions, add an index and use SQL from Python without injection. It ends by reviewing SQL an AI assistant wrote.
- 01Your first table, and why types matterIn the workbook · 1 exercise
Open SQLite from Python, make a table and see what flexible typing does to numbers.
- 02Model the nursery: keys and constraintsIn the workbook · 1 exercise
Design four related tables for an invented plant nursery, and let the database reject bad data.
- 03Ask questions: filters, NULL, groups and joinsIn the workbook · 1 exercise
Most SQL reads data. Learn the shape of a query, the trap NULL sets and the join that quietly multiplies.
- 04Change data safely: copies, transactions and indexesIn the workbook · 1 exercise
UPDATEandDELETEchange a thousand rows as easily as one. Copy first, preview, and keep an undo button. - 05SQL from Python: placeholders, backups and exportsIn the workbook · 1 exercise
Programs send SQL too. Pass values safely, and keep copies you can restore.
- 06Review SQL an AI assistant writesIn the workbook · 1 exercise
An assistant drafts SQL in seconds. Check it against the real schema before it touches anything.
Also inside: a 10-point checklist, a glossary of 12 terms and 10 questions and answers to test yourself. 6 hands-on exercises, each with a worked answer at the back where the workbook gives one.
No login, no card, no account. Before the download we ask you to follow Mickai (two quick links). Free to download and use for personal learning, study groups and inside your own team. Please do not resell the workbooks or republish them as your own. Link people to trust-agent.ai instead.
Test yourself
Questions and answers
Do I need to install a database server to learn SQL?
No. SQLite runs inside the program that uses it and keeps each database in one ordinary file, with no server or account. Python includes it, and Python 3.12 or later includes a small shell you start with python -m sqlite3. This workbook was tested with Python 3.12.10 and SQLite 3.49.1.
Will what I learn here work in other databases?
Mostly. SELECT, WHERE, GROUP BY, joins, INSERT, UPDATE and DELETE carry over, and SQL is an ISO standard, but no two engines behave exactly alike. SQLite's flexible typing and its foreign keys being off by default are its own. Check syntax such as LIMIT against your engine's documentation before you rely on it.
Why are foreign keys off by default in SQLite?
For backwards compatibility. SQLite parsed foreign keys long before it enforced them, and when enforcement arrived in version 3.6.19, switching it on by default would have broken existing databases whose declarations were wrong. Switch it on for every connection with PRAGMA foreign_keys = ON;. The documentation advises careful developers not to assume either setting.
What is the difference between WHERE and HAVING?
WHERE filters rows before they are grouped. HAVING filters the groups afterwards, so it can test aggregate results such as count(*) > 1. A query can use both: WHERE to keep only this month's orders, then HAVING to keep only customers with more than one of them.
Why does my query miss rows where a column is empty?
If the value is NULL, a comparison such as town <> 'Leeds' gives unknown, and WHERE keeps only rows where the condition is true. Add OR town IS NULL, or decide deliberately to leave them out. An empty text value, '', is not NULL in SQLite: it is a real value and compares normally.
How do I undo an UPDATE or DELETE?
Inside a transaction, ROLLBACK undoes everything since BEGIN. Outside one, the shell saves each statement at once and there is no undo. That is why you preview with SELECT, practise on a copy and keep backups made with VACUUM INTO.
When should I add an index?
When a real query on a growing table searches or joins on a column, including foreign key columns, which SQLite does not index for you. Check with EXPLAIN QUERY PLAN: SCAN means the whole table is read, SEARCH means only some rows are visited. An index takes space and has to be kept up to date, so do not add one to every column.
What is SQL injection and how do I prevent it?
It happens when input is pasted into the text of a query, so a crafted value changes what the query does, for example to return every row. Pass values with placeholders such as ?, so they are only ever treated as data. OWASP lists parameterised queries as a primary defence. Placeholders cannot stand in for table or column names: take those from your own code.
Can I let an AI assistant write my SQL?
It can draft SQL quickly, but check everything: that each column exists, that joins use the right keys, that every UPDATE and DELETE has a WHERE, that values are quoted and typed correctly, and that the syntax suits your database. Give it the schema and invented sample rows, never real personal data, and test on a copy.
How should I back up an SQLite database?
Use VACUUM INTO 'backup.db'; in the shell, or Connection.backup() from Python: both work while the database is in use. Copying the file is safe only when no transaction is in progress. For a copy you can read, Python's iterdump() gives the whole database as SQL text. Keep backups somewhere separate, and practise restoring one.
When you have finished
Get your certificate of completion
Type your name and download a certificate for this workbook as a PDF, ready to print or to add to LinkedIn. It is made on your own device, so your name is never sent to us. It is a self-declared certificate, not an accredited qualification.
Learn the language
Key terms
- Relational database
- A database that stores data in tables of rows and columns, linked to each other by keys.
- SQL
- The language for defining, changing and querying relational databases, standardised as ISO/IEC 9075, with differences between engines.
- Table
- A named set of rows that share the same columns. Each row is one thing; each column is one fact about it.
- Primary key
- The column or columns that identify each row. No two rows may share a primary key.
- Foreign key
- A column whose value must match the primary key of a row in another table. SQLite enforces this only when foreign keys are switched on.
- Constraint
- A rule the database checks on every change, such as NOT NULL, UNIQUE, CHECK or a foreign key.
6 of the workbook's 12 terms. The complete glossary is in the workbook.
Follow the evidence
Sources and checks
Facts last checked: .
Examples in this workbook were run on: Python 3.12.10 with its built-in sqlite3 module and SQLite 3.49.1, on Windows 11 (2026-09-26).
These workbooks use AI assistance. See how the workbooks are made.
- About SQLiteSQLite
- Datatypes in SQLiteSQLite
- STRICT tablesSQLite
- SQLite foreign key supportSQLite
- Pragma statements supported by SQLiteSQLite
- CREATE TABLESQLite
- SELECTSQLite
- SQL language expressionsSQLite
- Built-in aggregate functionsSQLite
- Built-in scalar SQL functionsSQLite
- UPDATESQLite
- DELETESQLite
- TransactionSQLite
- VACUUMSQLite
- EXPLAIN QUERY PLANSQLite
- Query planningSQLite
- How to corrupt an SQLite database fileSQLite
- Quirks, caveats and gotchas in SQLiteSQLite
- Appropriate uses for SQLiteSQLite
- sqlite3: DB-API 2.0 interface for SQLite databasesPython Software Foundation
- SQL InjectionOWASP Foundation
- SQL Injection Prevention Cheat SheetOWASP Cheat Sheet Series
- What is personal data?Information Commissioner's Office
- ISO/IEC 9075-1:2023 Database languages SQL, Part 1: FrameworkInternational Organization for Standardization
- SELECT (compatibility: LIMIT and OFFSET)PostgreSQL Global Development Group
Created by Mickarle Wagstaff-Irons - Micky Irons with the Mickai team. Published by Mickai LTD. Last updated 26 September 2026.
NextKeep going
Where to go next
Recommended for you
Python: from first script to a useful automation
A complete beginner's route from installing Python to a tested, dry-run-first tool that sorts and reports on a folder, using only what comes with Python.
Recommended for you
Build your first app with an AI coding assistant
Build a small bill-splitting web page with any AI coding assistant while staying in control: brief it, build in steps, review the code, test edge cases and keep safe copies.
Recommended for you
RAG and knowledge bases, step by step
Build a knowledge base a model can answer from: collect, clean, chunk, embed, retrieve, cite and evaluate, with security basics and a hands-on exercise using five short documents.