Setting Up SQLite and Your First Query

The 24-chapter SQL for Beginners series now moves from ideas to your terminal. Chapter 2 installs SQLite, creates the four-table shop database that every remaining example runs against, loads its sample rows, and runs your first query — from an empty folder to a real result set in a single sitting.

Why SQLite

SQLite is a relational engine that lives entirely inside one library: no server to install, no daemon to configure, no accounts to create. You point it at a file — shop.db — and it reads and writes tables inside that file, creating the file if it does not exist yet.

• Zero configuration — the database is a file: copy it, delete it, start again. What that means for real traffic is the subject of chapter 21.
• Already installed — macOS and most Linux distributions ship the sqlite3 command, and PHP, Python, Node.js, browsers and every phone embed the library.
• Real, standard SQL — joins, subqueries, transactions and window functions all work, so nothing you learn here has to be unlearned on MySQL or PostgreSQL.
• One honest difference — it is embedded rather than client-server, foreign keys are off until you switch them on, and a few functions have other names. Each difference is flagged when you reach it.

Install it and say hello

Check what you already have, from a fresh terminal — a new install cannot update a shell that is already running:

sqlite3 --version

Expected output:

3.51.0 2025-06-12 13:14:41 ... (64-bit)

Your build will differ; anything from 3.8.3 upwards runs this series as written, and window functions in chapter 16 want 3.25 or newer. If the command is missing, grab the tools from sqlite.org/download on Windows, or install your distribution’s package — sudo apt install sqlite3 on Debian or Ubuntu.

Now open a folder for the series and start the shell. The database file is created in whatever directory you are standing in:

mkdir sql-for-beginners
cd sql-for-beginners
sqlite3 shop.db
sqlite>

Create the four tables

Paste this at the sqlite> prompt: four CREATE TABLE statements, one per table in the shop. Whitespace and capitalisation are style, not syntax — SQLite takes them exactly as written:

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    city TEXT,
    country TEXT,
    signup_date TEXT
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    status TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price INTEGER NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Expected output: nothing at all. SQLite stays silent when a statement succeeds, so the absence of an error is the confirmation. Read the definitions once: INTEGER PRIMARY KEY is the row’s identity and SQLite fills it in when you leave the id out, NOT NULL refuses empty values, and each FOREIGN KEY line declares which table a column must point at. Chapter 8 works through the type list and chapter 9 through those key lines in detail.

Load the sample rows

Empty tables prove nothing, so here is the data the rest of the series expects: six customers, six products, six orders and eight order lines, with the ids, prices and dates you will keep seeing quoted in later chapters.

INSERT INTO customers (id, first_name, last_name, email, city, country, signup_date) VALUES
    (1, 'Aarav', 'Sharma', 'aarav@example.com', 'Mumbai', 'India', '2025-11-02'),
    (2, 'Priya', 'Nair', 'priya@example.com', 'Delhi', 'India', '2025-11-15'),
    (3, 'Lena', 'Fischer', 'lena@example.com', 'Berlin', 'Germany', '2025-12-01'),
    (4, 'Marc', 'Tremblay', 'marc@example.com', 'Toronto', 'Canada', '2025-12-20'),
    (5, 'Wei', 'Ling', 'wei@example.com', 'Singapore', 'Singapore', '2026-01-12'),
    (6, 'Amara', 'Ochieng', 'amara@example.com', 'Nairobi', 'Kenya', '2026-02-14');

INSERT INTO products (id, name, category, price, stock) VALUES
    (1, 'The Silent Patient', 'Fiction', 499, 40),
    (2, 'The Very Hungry Caterpillar', 'Kids', 249, 60),
    (3, 'Clean Code', 'Tech', 899, 18),
    (4, 'Python Crash Course', 'Tech', 749, 22),
    (5, 'Matilda', 'Kids', 349, 30),
    (6, 'Robotics for Kids', 'Kids', 649, 15);

INSERT INTO orders (id, customer_id, order_date, status) VALUES
    (1, 1, '2026-01-12', 'paid'),
    (2, 1, '2026-01-20', 'shipped'),
    (3, 2, '2026-02-03', 'cancelled'),
    (4, 3, '2026-02-11', 'shipped'),
    (5, 5, '2026-02-25', 'paid'),
    (6, 6, '2026-03-02', 'paid');

INSERT INTO order_items (id, order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 1, 499),
    (2, 1, 2, 2, 249),
    (3, 2, 3, 1, 899),
    (4, 3, 4, 1, 749),
    (5, 4, 1, 2, 499),
    (6, 4, 5, 1, 349),
    (7, 5, 6, 1, 649),
    (8, 6, 3, 2, 899);

Again: no output, no error. The ids are written out by hand here so that later chapters can say “order 4” and mean it; in real code you normally let INTEGER PRIMARY KEY assign them. One multi-row INSERT per table is also the fastest way to load a batch, and the trailing semicolon ends each statement.

Look around the shell

Lines starting with a dot belong to the shell, not to SQL. Two of them confirm the work you just did:

sqlite> .tables
customers    order_items  orders       products
sqlite> .schema orders
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TEXT NOT NULL,
    status TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Expected output: the four table names in columns, then the original CREATE TABLE orders statement printed back at you — SQLite stores your exact wording, so .schema is the quickest way to spot a typo in a column definition you cannot remember. .help lists every meta-command and .quit (or Ctrl+D) closes the shell; the file keeps everything you did.

Your first query

SELECT * means “every column” and FROM names the table. Read the whole customers table:

SELECT * FROM customers;

Expected output:

1|Aarav|Sharma|aarav@example.com|Mumbai|India|2025-11-02
2|Priya|Nair|priya@example.com|Delhi|India|2025-11-15
3|Lena|Fischer|lena@example.com|Berlin|Germany|2025-12-01
4|Marc|Tremblay|marc@example.com|Toronto|Canada|2025-12-20
5|Wei|Ling|wei@example.com|Singapore|Singapore|2026-01-12
6|Amara|Ochieng|amara@example.com|Nairobi|Kenya|2026-02-14

Readable enough, but pipes instead of headers. Two more meta-commands fix that, and a narrower query shows the grid properly:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT first_name, city FROM customers;
first_name  city
----------  ---------
Aarav       Mumbai
Priya       Delhi
Lena        Berlin
Marc        Toronto
Wei         Singapore
Amara       Nairobi

Those commands — .tables, .headers on and .mode column — are the ones you will type every single session. .mode list returns to pipes, and .read setup.sql runs a script file from disk (sqlite3 shop.db < setup.sql does the same from outside the shell), which is how you will rebuild this database in ten seconds whenever you break it.

Common mistakes

• Forgetting which folder you are in — sqlite3 shop.db creates the file wherever your terminal stands, so re-running from another directory silently starts a fresh, empty database.
• Re-running the CREATE statements — SQLite answers table customers already exists; delete the file, or write CREATE TABLE IF NOT EXISTS when you want a setup script to be re-runnable.
• Splitting a statement without its semicolon — the shell waits at a ...> continuation prompt and looks as if it has hung; type ; and Enter, or press Ctrl+C to back out.
• Mismatched quotes — SQL strings take single quotes ('India'); a curly quote copied from a word processor or a double quote where a single one belongs breaks the whole statement.
• Typing the sample rows differently — one changed id, price or status makes every expected output in the rest of this series disagree with yours; compare your SELECT * against the blocks above.

Practice checklist

1. Run sqlite3 --version and write your build number into your series notes.
2. Create shop.db, paste the four CREATE TABLE statements, and confirm with .tables.
3. Load all four INSERT blocks, then check that SELECT * FROM products; returns exactly six rows.
4. Turn on .headers on and .mode column, re-run SELECT * FROM customers;, and describe the difference in one sentence.
5. Quit with .quit, start the shell again and prove the data survived — then open other.db and explain why it is empty.

What's next?

The database exists and answers queries; next comes the statement you will type more often than all the others combined: The SELECT Statement.