Chapter 1 · Databases and SQL
Setup: Your Practice Database
- Page 3 of 22
- 18 min read
Every example in this tutorial runs on one small file, shop.db: the BitByte Shop database. On this page you build it from a script, learn three ways to look inside it, and get a tiny Python helper that prints query results as neat tables and puts the data back to its starting state whenever you break something. Breaking things is encouraged — a reset takes less than a second.
You need nothing new: SQLite comes with Python. If you would also like a real MySQL or PostgreSQL server (optional), the second half of the page shows how to start one with a single Docker command.
What you will learn
- How to check that SQLite is ready, and which version you have
- How to create
shop.dbfromshop.sqlin Python, step by step - How to use
sqlhelp.py:show()for results,reset()for a fresh start - How to work with DB Browser for SQLite or the
sqlite3shell instead of Python - How to run the same database on MySQL or PostgreSQL, and which clients to use
SQLite is already installed
SQLite is not a server you install and start: it is a library, and Python ships with it as the sqlite3 module. Check it:
import sqlite3
print("SQLite version:", sqlite3.sqlite_version)SQLite version: 3.45.1Your number may differ — it depends on how your Python was built. Python 3.12 or newer usually brings SQLite 3.40 or later. A few features appeared in particular versions, and the pages that use them say so:
| Feature | Needs SQLite | Where |
|---|---|---|
Window functions (ROW_NUMBER() OVER …) | 3.25+ | Advanced tutorial |
The CONCAT() function | 3.44+ | SELECT: Columns, Expressions, Aliases and DISTINCT |
RETURNING on INSERT/UPDATE/DELETE | 3.35+ | INSERT, UPDATE, DELETE and Upserts, Safely |
RIGHT JOIN and FULL OUTER JOIN | 3.39+ | LEFT, RIGHT, FULL, CROSS and Self Joins |
Step 1: a project folder and two files
Make a folder for the tutorial and work inside it (in the virtual environment you set up in Python for AI, if you like):
mkdir sql-for-ai
cd sql-for-aiSave the two files below into that folder with exactly these names. The first, shop.sql, is the whole database as SQL: it deletes the tables if they exist, creates seven tables with their keys and rules, and fills them with rows. It runs unchanged on SQLite, MySQL 8 and PostgreSQL. Skim it now — the tables are the ones from Tables, Keys and Relationships, and you will learn every statement in it during this tutorial.
shop.sql
-- BitByte Shop: the practice database for the SQL & Data Handling tutorials.
-- Runs unchanged on SQLite, MySQL 8 and PostgreSQL. Foreign keys are written as table
-- constraints because MySQL silently ignores a REFERENCES written on the column itself.
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
city VARCHAR(50),
joined_on DATE NOT NULL
);
CREATE TABLE categories (
category_id INTEGER PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category_id INTEGER,
price INTEGER NOT NULL CHECK (price > 0),
stock INTEGER CHECK (stock >= 0),
FOREIGN KEY (category_id) REFERENCES categories (category_id)
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL
CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled')),
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0),
unit_price INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders (order_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
);
CREATE TABLE payments (
payment_id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
amount INTEGER NOT NULL CHECK (amount > 0),
method VARCHAR(20) NOT NULL,
paid_on DATE NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders (order_id)
);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
manager_id INTEGER,
salary INTEGER NOT NULL,
hired_on DATE NOT NULL,
FOREIGN KEY (manager_id) REFERENCES employees (employee_id)
);
INSERT INTO customers (customer_id, name, email, city, joined_on) VALUES
(1, 'Nadia Rahman', 'nadia@example.com', 'Dhaka', '2025-11-03'),
(2, 'Tanvir Ahmed', 'tanvir@example.com', 'Chattogram', '2025-12-14'),
(3, 'Farhana Akter', 'farhana@example.com', 'Dhaka', '2026-01-09'),
(4, 'Rafiq Islam', 'rafiq@example.com', 'Sylhet', '2026-01-22'),
(5, 'Sadia Chowdhury', 'sadia@example.com', NULL, '2026-02-05'),
(6, 'Imran Hossain', 'imran@example.com', 'Khulna', '2026-02-18'),
(7, 'Mitu Das', 'mitu@example.com', 'Dhaka', '2026-03-30'),
(8, 'Karim Uddin', 'karim@example.com', 'Rajshahi', '2026-04-11');
INSERT INTO categories (category_id, name) VALUES
(1, 'Books'),
(2, 'Electronics'),
(3, 'Courses'),
(4, 'Accessories'),
(5, 'Furniture');
INSERT INTO products (product_id, name, category_id, price, stock) VALUES
(1, 'Python Crash Course', 1, 1200, 40),
(2, 'Hands-On Machine Learning', 1, 2500, 15),
(3, 'Mechanical Keyboard', 2, 4500, 12),
(4, 'Wireless Mouse', 2, 900, 60),
(5, '27-inch Monitor', 2, 18500, 5),
(6, 'SQL Masterclass', 3, 3000, NULL),
(7, 'AI Engineering Bootcamp', 3, 15000, NULL),
(8, 'Laptop Stand', 4, 1500, 25),
(9, 'USB-C Hub', 4, 2200, 0),
(10, 'Noise-Cancelling Headphones', 2, 7500, 8),
(11, 'Gift Card', NULL, 1000, NULL);
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
(1, 1, '2026-01-12', 'delivered'),
(2, 2, '2026-01-20', 'delivered'),
(3, 1, '2026-02-03', 'delivered'),
(4, 3, '2026-02-14', 'delivered'),
(5, 4, '2026-02-25', 'cancelled'),
(6, 2, '2026-03-08', 'delivered'),
(7, 5, '2026-03-15', 'delivered'),
(8, 3, '2026-03-28', 'delivered'),
(9, 6, '2026-04-02', 'delivered'),
(10, 1, '2026-04-19', 'delivered'),
(11, 7, '2026-05-06', 'delivered'),
(12, 2, '2026-05-21', 'shipped'),
(13, 6, '2026-06-02', 'pending'),
(14, 3, '2026-06-09', 'delivered');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 1200),
(1, 4, 1, 900),
(2, 3, 1, 4500),
(3, 6, 1, 3000),
(4, 2, 1, 2500),
(4, 8, 1, 1500),
(5, 5, 1, 18500),
(6, 7, 1, 15000),
(7, 4, 2, 900),
(7, 9, 1, 2200),
(8, 1, 2, 1200),
(9, 3, 1, 4050),
(9, 4, 1, 900),
(10, 5, 1, 18500),
(11, 6, 1, 3000),
(11, 2, 1, 2500),
(12, 8, 2, 1500),
(13, 7, 1, 15000),
(14, 9, 1, 2200),
(14, 4, 1, 900);
INSERT INTO payments (payment_id, order_id, amount, method, paid_on) VALUES
(1, 1, 2100, 'bkash', '2026-01-12'),
(2, 2, 4500, 'card', '2026-01-20'),
(3, 3, 3000, 'bkash', '2026-02-03'),
(4, 4, 4000, 'cash', '2026-02-16'),
(5, 6, 5000, 'bkash', '2026-03-08'),
(6, 6, 10000, 'card', '2026-03-10'),
(7, 7, 4000, 'card', '2026-03-15'),
(8, 8, 2400, 'bkash', '2026-03-28'),
(9, 9, 4950, 'card', '2026-04-02'),
(10, 10, 18500, 'card', '2026-04-19'),
(11, 11, 5500, 'bkash', '2026-05-06'),
(12, 12, 3000, 'card', '2026-05-21'),
(13, 14, 3100, 'cash', '2026-06-11');
INSERT INTO employees (employee_id, name, department, manager_id, salary, hired_on) VALUES
(1, 'Ayesha Siddiqua', 'Management', NULL, 250000, '2022-01-10'),
(2, 'Hasan Mahmud', 'Engineering', 1, 180000, '2022-03-01'),
(3, 'Nusrat Jahan', 'Engineering', 2, 120000, '2023-06-15'),
(4, 'Zahid Hasan', 'Engineering', 2, 120000, '2024-02-01'),
(5, 'Lamia Karim', 'Data', 1, 160000, '2022-08-20'),
(6, 'Shuvo Roy', 'Data', 5, 95000, '2025-01-05'),
(7, 'Priya Sen', 'Data', 5, 110000, '2024-09-12');Notice the order: the tables are dropped children-first (payments before orders, orders before customers) and created parents-first. A server database will not drop a table while another table's foreign key still points at it, and a foreign key cannot point at a table that does not exist yet.
The second file, sqlhelp.py, is a small helper used throughout the tutorial:
sqlhelp.py
"""Helpers for the BitByte SQL tutorials: a connection to shop.db, show() and reset()."""
import sqlite3
con = sqlite3.connect("shop.db")
con.execute("PRAGMA foreign_keys = ON") # SQLite checks foreign keys only when asked
def show(sql, params=()):
"""Run one SELECT and print its rows as a table."""
cur = con.execute(sql, params)
cols = [d[0] for d in cur.description]
rows = cur.fetchall()
text = [["NULL" if v is None else str(v) for v in row] for row in rows]
widths = [max([len(c)] + [len(r[i]) for r in text]) for i, c in enumerate(cols)]
def line():
return "+" + "+".join("-" * (w + 2) for w in widths) + "+"
def fmt(cells, values=None):
out = []
for i, (cell, w) in enumerate(zip(cells, widths)):
number = values is not None and isinstance(values[i], (int, float))
out.append(cell.rjust(w) if number else cell.ljust(w))
return "| " + " | ".join(out) + " |"
print(line(), fmt(cols), line(), sep="\n")
for cells, values in zip(text, rows):
print(fmt(cells, values))
print(line())
def reset():
"""Rebuild shop.db from shop.sql: every table back to its starting rows."""
with open("shop.sql", encoding="utf-8") as f:
script = f.read()
con.commit()
con.execute("PRAGMA foreign_keys = OFF") # so tables you made that point at the shop do not block it
try:
con.executescript(script)
finally:
con.execute("PRAGMA foreign_keys = ON")It gives you three things:
con— an open connection toshop.db, with foreign-key checking switched on.show(sql, params=())— runs oneSELECTand prints the rows as a table, withNULLfor missing values and numbers right-aligned. Every result table in this tutorial looks like its output.reset()— runsshop.sqlagain, so every table is back to its starting rows.
Step 2: create shop.db from Python
Here is what building the database involves, written out step by step. Put it in a file called build_db.py in the same folder and run python build_db.py (or run it in a notebook cell):
import sqlite3
# 1. Connect. If shop.db does not exist, SQLite creates an empty file.
con = sqlite3.connect("shop.db")
# 2. Ask SQLite to enforce foreign keys on this connection.
con.execute("PRAGMA foreign_keys = ON")
# 3. Read shop.sql and run every statement in it.
with open("shop.sql", encoding="utf-8") as f:
con.executescript(f.read())
con.commit()
# 4. Check: how many rows did each table get?
tables = ["customers", "categories", "products", "orders",
"order_items", "payments", "employees"]
for table in tables:
count = con.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
print(f"{table:12} {count:3} rows")
con.close()customers 8 rows
categories 5 rows
products 11 rows
orders 14 rows
order_items 20 rows
payments 13 rows
employees 7 rowsIf you see those seven counts, the database is ready. Two details worth knowing:
execute()runs one statement;executescript()runs a whole script of statements separated by semicolons. That is why step 3 uses it.- The table names in step 4 are fixed strings written by us, so putting them into the SQL with an f-string is safe here. Values that come from users are a different story — for those you use
?placeholders, shown below.
reset() in sqlhelp.py does the job of steps 1–3 on the connection the helper already opened, so from now on you can simply call it.
Step 3: your first query
With shop.db, shop.sql and sqlhelp.py in the same folder, import the helper and ask a question — the three most expensive products:
from sqlhelp import con, show
show("SELECT product_id, name, price FROM products ORDER BY price DESC LIMIT 3")+------------+-----------------------------+-------+
| product_id | name | price |
+------------+-----------------------------+-------+
| 5 | 27-inch Monitor | 18500 |
| 7 | AI Engineering Bootcamp | 15000 |
| 10 | Noise-Cancelling Headphones | 7500 |
+------------+-----------------------------+-------+When part of the query comes from a variable, pass it separately with a ? placeholder instead of pasting it into the string. The database then treats it strictly as a value, which also protects you from SQL injection (covered in the advanced tutorial):
city = "Dhaka"
show("SELECT customer_id, name, city FROM customers WHERE city = ? ORDER BY customer_id", (city,))+-------------+---------------+-------+
| customer_id | name | city |
+-------------+---------------+-------+
| 1 | Nadia Rahman | Dhaka |
| 3 | Farhana Akter | Dhaka |
| 7 | Mitu Das | Dhaka |
+-------------+---------------+-------+show() is only for looking. In real code you want the rows as Python values, or as a pandas DataFrame ready for analysis or a model:
import pandas as pd
rows = con.execute("SELECT name, price FROM products ORDER BY price DESC LIMIT 2").fetchall()
print(rows)
df = pd.read_sql("SELECT product_id, name, price, stock FROM products ORDER BY product_id LIMIT 4", con)
print(df)[('27-inch Monitor', 18500), ('AI Engineering Bootcamp', 15000)]
product_id name price stock
0 1 Python Crash Course 1200 40
1 2 Hands-On Machine Learning 2500 15
2 3 Mechanical Keyboard 4500 12
3 4 Wireless Mouse 900 60Rows come back as a list of tuples; pd.read_sql turns a query into a DataFrame in one line, and SQL's NULL would become NaN or None there. You will use this a lot in the advanced tutorial.
Step 4: reset whenever you like
Pages that change data (INSERT, UPDATE, DELETE, DROP) assume you start from the original rows. Here we delete every payment on purpose, then bring them back:
from sqlhelp import con, show, reset
con.execute("DELETE FROM payments")
con.commit()
show("SELECT COUNT(*) AS payments FROM payments")
reset()
show("SELECT COUNT(*) AS payments FROM payments")+----------+
| payments |
+----------+
| 0 |
+----------+
+----------+
| payments |
+----------+
| 13 |
+----------+Make it a habit: start each page with reset(), so your results match the ones printed on the page. (Deleting shop.db and running build_db.py again does the same.)
Without Python: DB Browser for SQLite
DB Browser for SQLite is a free graphical tool for Windows, macOS and Linux — the easiest way to see a database. After installing it:
- Open Database and choose
shop.db. (Noshop.dbyet? Use File → Import → Database from SQL file…, pickshop.sql, and save the new database asshop.db.) - Database Structure lists the tables and their columns.
- Browse Data shows a table's rows like a spreadsheet.
- Execute SQL is where you type queries; press Ctrl+Return (or F5) to run, and the result appears below.
DB Browser keeps your changes in an open transaction until you click Write Changes (or Revert Changes). While changes are pending, any other program that tries to write — including your Python script — gets
database is locked. Write or revert before switching back to Python.
Without Python: the sqlite3 shell
The sqlite3 command-line shell is a single small program. macOS includes it; on Linux install it with your package manager (sudo apt install sqlite3); on Windows download the "sqlite-tools" zip from sqlite.org/download.html and unzip sqlite3.exe into your project folder. Then:
sqlite3 shop.dbInside the shell, lines starting with a dot are shell commands; everything else is SQL and must end with ;:
sqlite> .read shop.sql
sqlite> .tables
sqlite> .headers on
sqlite> .mode box
sqlite> PRAGMA foreign_keys = ON;
sqlite> SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
sqlite> .quit.read runs a script (here it rebuilds the database, like reset()), .tables lists the tables, and .headers on with .mode box prints results as bordered tables. Like Python, the shell leaves foreign keys off until you switch them on. You can also run one query straight from the terminal: sqlite3 shop.db "SELECT COUNT(*) FROM orders;".
Optional: MySQL or PostgreSQL
SQLite is all you need for this tutorial. But production systems usually run a server database, and the pages show MySQL and PostgreSQL wherever they differ. If you want to run those blocks yourself, you have two routes:
- Install natively: MySQL Community Server from dev.mysql.com/downloads; PostgreSQL from postgresql.org/download (the EDB installer on Windows, Postgres.app or Homebrew on macOS, your package manager on Linux).
- Use Docker (simpler, and easy to throw away): one command per server.
# MySQL 8 on port 3306, with an empty database called shop
docker run --name shop-mysql -e MYSQL_ROOT_PASSWORD=course -e MYSQL_DATABASE=shop \
-p 3306:3306 -d mysql:8.4
# PostgreSQL with the pgvector extension on port 5432, database shop
docker run --name shop-postgres -e POSTGRES_PASSWORD=course -e POSTGRES_DB=shop \
-p 5432:5432 -d pgvector/pgvector:pg17
# Wait a few seconds for them to start, then load the same shop.sql into each
docker exec -i shop-mysql mysql -uroot -pcourse shop < shop.sql
docker exec -i shop-postgres psql -U postgres -d shop < shop.sql
# Open an interactive prompt (type exit or \q to leave)
docker exec -it shop-mysql mysql -uroot -pcourse shop
docker exec -it shop-postgres psql -U postgres -d shopThe first time, PostgreSQL prints a NOTICE: table "payments" does not exist, skipping for each DROP TABLE IF EXISTS — harmless; MySQL warns that a password on the command line is insecure — fine for a practice container, never for a real server. To reset a server database, just load shop.sql again. Check that the data arrived by running the same query on both:
-- MySQL
SELECT COUNT(*) AS orders, MAX(order_date) AS last_order FROM orders;+--------+------------+
| orders | last_order |
+--------+------------+
| 14 | 2026-06-09 |
+--------+------------+-- PostgreSQL
SELECT COUNT(*) AS orders, MAX(order_date) AS last_order FROM orders;+--------+------------+
| orders | last_order |
+--------+------------+
| 14 | 2026-06-09 |
+--------+------------+A graphical client makes servers much more pleasant:
| Client | Works with | Notes |
|---|---|---|
| DBeaver (Community) | SQLite, MySQL, PostgreSQL, SQL Server and many more | Free; one tool for everything in this course |
| MySQL Workbench | MySQL | Free, from Oracle; also draws schema diagrams |
| pgAdmin | PostgreSQL | Free, the official PostgreSQL GUI; runs in a browser tab |
| DB Browser for SQLite | SQLite | Free and very simple |
| VS Code extensions (e.g. SQLTools) | Most databases | Handy if you already live in VS Code |
Connection settings for the Docker containers above: host localhost, port 3306 (MySQL) or 5432 (PostgreSQL), user root / postgres, password course, database shop. Connecting to them from Python (PyMySQL, psycopg) is covered in the advanced tutorial.
Common mistakes
- Running from a different folder.
sqlite3.connect("shop.db")looks in the current folder and silently creates an empty database if the file is not there. Your first query then fails with "no such table":import os import sqlite3 os.makedirs("elsewhere", exist_ok=True) empty = sqlite3.connect("elsewhere/shop.db") # wrong folder: a new, empty file try: empty.execute("SELECT * FROM products") except sqlite3.OperationalError as error: print("Error:", error) print("size of the new file:", os.path.getsize("elsewhere/shop.db"), "bytes") empty.close()Fix: run your script from the project folder (check withError: no such table: products size of the new file: 0 bytesprint(os.getcwd())), or build the path from the script's own location:Path(__file__).parent / "shop.db". - Using
execute()for a whole script. It runs one statement only:import sqlite3 con2 = sqlite3.connect("shop.db") try: con2.execute("DELETE FROM payments; DELETE FROM orders;") except sqlite3.ProgrammingError as error: print("Error:", error) con2.close()Fix:Error: You can only execute one statement at a time.executescript()for scripts, as in step 2. - Forgetting
commit(). Python'ssqlite3opens a transaction before anINSERT,UPDATEorDELETE. Close the connection without committing and the change is thrown away:import sqlite3 w = sqlite3.connect("shop.db") w.execute("INSERT INTO categories (category_id, name) VALUES (6, 'Toys')") w.close() # no commit: the insert is discarded r = sqlite3.connect("shop.db") print("categories:", r.execute("SELECT COUNT(*) FROM categories").fetchone()[0]) r.close()Fix: callcategories: 5con.commit()after changes you want to keep (or usewith con:, which commits for you). - Leaving a change uncommitted in another program. If DB Browser (or another script) holds an uncommitted change, writers elsewhere wait and then fail:
import sqlite3 a = sqlite3.connect("shop.db") a.execute("UPDATE products SET stock = stock WHERE product_id = 1") # transaction left open b = sqlite3.connect("shop.db", timeout=0) # do not wait try: b.execute("UPDATE products SET stock = stock WHERE product_id = 2") except sqlite3.OperationalError as error: print("Error:", error) a.rollback() a.close() b.close()Fix: commit or roll back promptly, and click Write Changes or Revert Changes in DB Browser.Error: database is locked
Try it yourself
- Easy: Ask SQLite for its version with a query instead of Python (
sqlite_version()), and count the rows inemployeeswithshow(). - Medium: Use
show()with a?placeholder to list theproduct_id,nameandpriceof every product in category 2 (Electronics), sorted byproduct_id. - Hard: Write a function
table_sizes()that reads the table names from SQLite's own catalogue,sqlite_master, and prints each table's row count. Delete the rows oforder_items, run it, thenreset()and run it again.
Answers
1. sqlite_version() is a SQL function; the count is an ordinary query:
from sqlhelp import show
show("SELECT sqlite_version() AS version")
show("SELECT COUNT(*) AS employees FROM employees")2. The value goes in the tuple, not in the string:
show("SELECT product_id, name, price FROM products WHERE category_id = ? ORDER BY product_id", (2,))3. Every table SQLite knows about is a row in sqlite_master:
from sqlhelp import con, reset
def table_sizes():
names = [row[0] for row in con.execute(
"SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name")]
for name in names:
count = con.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]
print(f"{name:12} {count}")
con.execute("DELETE FROM order_items")
con.commit()
table_sizes()
reset()
print("--- after reset")
table_sizes()categories 5
customers 8
employees 7
order_items 0
orders 14
payments 13
products 11
--- after reset
categories 5
customers 8
employees 7
order_items 20
orders 14
payments 13
products 11Summary
- SQLite comes with Python (
import sqlite3);sqlite3.sqlite_versiontells you which features you have. shop.sqlbuilds the whole shop;executescript()runs it, andreset()insqlhelp.pydoes that for you any time.show()prints a query's result as a table; use?placeholders for values, andfetchall()orpd.read_sqlwhen you need the data in Python.- DB Browser for SQLite and the
sqlite3shell work on the same file; commit or write your changes so other programs are not locked out. - MySQL and PostgreSQL are one
docker runaway, load the sameshop.sql, and are easiest to use through DBeaver, MySQL Workbench or pgAdmin.
Next: How SQL Works: Statements, Command Types and Query Order looks at the shape of the language itself — statements, keywords, quotes, comments, the families of commands, and the order in which a database really evaluates a query.