SQLite3 — Complete Guide

What is SQLite3?

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike MySQL or PostgreSQL, it stores the entire database in a single .db file on disk — no daemon, no setup, just a file you can open and query.

Why SQLite? It's built into Python, PHP, Node.js, Android, iOS, and countless other platforms. It's the most widely deployed database in the world.

Select your OS:

Installing SQLite3 on Windows Windows

Download the precompiled binaries

Visit sqlite.org/download.html and download the Precompiled Binaries for Windows bundle — grab sqlite-tools-win-x64-*.zip.

Extract the ZIP file

Right-click the ZIP and select Extract All. You'll find three executables: sqlite3.exe, sqldiff.exe, and sqlite3_analyzer.exe.

Add to your PATH

Move the folder (e.g. C:\sqlite) and add it to your System PATH so you can call sqlite3 from any terminal. Search for Environment Variables → Edit System Environment Variables → Path → New.

Verify the installation

Open a new Command Prompt or PowerShell and run:

sqlite3 --version

You should see something like 3.46.0 2024-05-23...

Alternative: If you use Chocolatey, run choco install sqlite in an elevated terminal for a one-step install.

Installing SQLite3 on macOS macOS

Good news: SQLite3 comes pre-installed on macOS! You may not need to do anything at all.

Check if it's already installed

Open Terminal and run:

sqlite3 --version

If you see a version number, you're done! If not, continue below.

Install via Homebrew (recommended)

If you have Homebrew installed (the standard macOS package manager), run:

brew install sqlite3

Update your shell PATH (if needed)

Homebrew installs to a custom prefix. To use the Homebrew version instead of the system one, add it to your path in ~/.zshrc or ~/.bash_profile:

# For Apple Silicon (M1/M2/M3):
export PATH="/opt/homebrew/opt/sqlite/bin:$PATH"

# For Intel Mac:
export PATH="/usr/local/opt/sqlite/bin:$PATH"

Verify

sqlite3 --version

Installing SQLite3 on Linux Linux

Debian / Ubuntu / Mint

SQLite3 is available in the default apt repositories:

sudo apt update
sudo apt install sqlite3 libsqlite3-dev

Fedora / RHEL / CentOS

sudo dnf install sqlite sqlite-devel

On older CentOS/RHEL with yum:

sudo yum install sqlite sqlite-devel

Arch Linux

sudo pacman -S sqlite

Verify

sqlite3 --version

You should see the installed version number printed to stdout.

Building from source? Download the amalgamation source from sqlite.org, then run ./configure && make && sudo make install.

Step 1 — Opening the SQLite Shell

Open a terminal and type sqlite3 followed by a filename. If the file doesn't exist, SQLite creates it automatically. You'll land at the sqlite> prompt.

# Create (or open) a database file
sqlite3 mydb.db

# Open an in-memory database — gone when you exit
sqlite3

# Check the version without entering the shell
sqlite3 --version

Terminal Output

$ sqlite3 mydb.db
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite> █

-- opening in-memory database:
$ sqlite3
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> █

-- checking version only:
$ sqlite3 --version
3.46.0 2024-05-23 13:25:27 bda12674b4179f3...

Tip: Every SQLite database is just a file. You can copy, move, or email it like any other file.

Step 2 — Essential Dot-Commands

Dot-commands start with a . and are shell meta-commands — they are NOT SQL. They control how the shell behaves and displays output.

.help              -- list all dot-commands
.tables            -- list tables in the database
.schema            -- show CREATE statements for all tables
.schema users      -- show schema for one specific table
.headers on        -- show column names above results
.mode table        -- format results as an ASCII table (most readable)
.mode column       -- fixed-width column format
.mode csv          -- comma-separated output
.databases         -- show attached databases
.quit              -- exit the shell (also Ctrl+D)

Terminal Output

sqlite> .tables
orders        products      users

sqlite> .schema users
CREATE TABLE users (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT    NOT NULL,
  email      TEXT    UNIQUE,
  age        INTEGER,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

sqlite> .databases
main: /home/gordon/mydb.db r/w

sqlite> .headers on
sqlite> .mode table
(no output — settings applied silently)

First thing to do every time you open the shell: run .headers on and .mode table — it makes every query output much easier to read.

Step 3 — Create Your First Table

A table is where your data lives. Each column has a name and a data type. SQLite has five storage types: INTEGER, TEXT, REAL, BLOB, and NULL.

-- The simplest possible table
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  name  TEXT,
  age   INTEGER
);

-- Safer version — won't error if table already exists
CREATE TABLE IF NOT EXISTS users (
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  TEXT    NOT NULL,
  email TEXT    UNIQUE,
  age   INTEGER,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Terminal Output

sqlite> CREATE TABLE users (
   ...>   id    INTEGER PRIMARY KEY,
   ...>   name  TEXT,
   ...>   age   INTEGER
   ...> );
(no output = success)

sqlite> .tables
users

sqlite> .schema users
CREATE TABLE users (
  id    INTEGER PRIMARY KEY,
  name  TEXT,
  age   INTEGER
);

-- If you try to create it again without IF NOT EXISTS:
sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY);
Parse error: table users already exists

PRIMARY KEY uniquely identifies each row. AUTOINCREMENT makes SQLite assign the next number automatically. NOT NULL requires a value. UNIQUE prevents duplicates.

Step 4 — Insert Data

-- Insert one row (specify columns explicitly)
INSERT INTO users (name, email, age)
VALUES ('Gordon', 'gordon@example.com', 30);

-- Insert multiple rows at once
INSERT INTO users (name, age)
VALUES
  ('Alice', 25),
  ('Bob', 34),
  ('Carol', 29);

Terminal Output

sqlite> INSERT INTO users (name, email, age)
   ...>   VALUES ('Gordon', 'gordon@example.com', 30);
(no output = 1 row inserted successfully)

sqlite> INSERT INTO users (name, age)
   ...>   VALUES ('Alice', 25), ('Bob', 34), ('Carol', 29);
(no output = 3 rows inserted successfully)

-- If you break a constraint, you get an error:
sqlite> INSERT INTO users (name, email, age) VALUES ('Eve', 'gordon@example.com', 22);
Runtime error: UNIQUE constraint failed: users.email

Best practice: Always name your columns explicitly. It makes your SQL resilient to future table changes.

Step 5 — Read Data with SELECT

-- Get everything
SELECT * FROM users;

-- Get specific columns only
SELECT name, age FROM users;

-- Filter rows with WHERE
SELECT * FROM users WHERE age > 25;

-- Sort and limit
SELECT * FROM users ORDER BY age ASC;
SELECT * FROM users LIMIT 2;

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(*) FROM users WHERE age > 25;

Terminal Output

sqlite> SELECT * FROM users;
+----+--------+--------------------+-----+---------------------+
| id | name   | email              | age | created_at          |
+----+--------+--------------------+-----+---------------------+
| 1  | Gordon | gordon@example.com | 30  | 2024-05-02 14:22:01 |
| 2  | Alice  |                    | 25  | 2024-05-02 14:22:01 |
| 3  | Bob    |                    | 34  | 2024-05-02 14:22:01 |
| 4  | Carol  |                    | 29  | 2024-05-02 14:22:01 |
+----+--------+--------------------+-----+---------------------+

sqlite> SELECT name, age FROM users;
+--------+-----+
| name   | age |
+--------+-----+
| Gordon | 30  |
| Alice  | 25  |
| Bob    | 34  |
| Carol  | 29  |
+--------+-----+

sqlite> SELECT * FROM users WHERE age > 25;
+----+--------+--------------------+-----+---------------------+
| id | name   | email              | age | created_at          |
+----+--------+--------------------+-----+---------------------+
| 1  | Gordon | gordon@example.com | 30  | 2024-05-02 14:22:01 |
| 3  | Bob    |                    | 34  | 2024-05-02 14:22:01 |
| 4  | Carol  |                    | 29  | 2024-05-02 14:22:01 |
+----+--------+--------------------+-----+---------------------+

sqlite> SELECT * FROM users ORDER BY age ASC;
+----+--------+-------+-----+---------------------+
| id | name   | email | age | created_at          |
+----+--------+-------+-----+---------------------+
| 2  | Alice  |       | 25  | 2024-05-02 14:22:01 |
| 4  | Carol  |       | 29  | 2024-05-02 14:22:01 |
| 1  | Gordon |       | 30  | 2024-05-02 14:22:01 |
| 3  | Bob    |       | 34  | 2024-05-02 14:22:01 |
+----+--------+-------+-----+---------------------+

sqlite> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
| 4        |
+----------+

sqlite> SELECT COUNT(*) FROM users WHERE age > 25;
+----------+
| COUNT(*) |
+----------+
| 3        |
+----------+

Step 6 — Update & Delete Rows

UPDATE — change existing data

-- Change one row by id
UPDATE users
SET age = 31
WHERE id = 1;

-- Change multiple columns
UPDATE users
SET age = 31,
    email = 'new@x.com'
WHERE id = 1;

DELETE — remove rows

-- Delete one specific row
DELETE FROM users
WHERE id = 2;

-- Delete all rows (keeps table)
DELETE FROM users;

-- Remove the table entirely
DROP TABLE IF EXISTS users;

Terminal Output

sqlite> UPDATE users SET age = 31 WHERE id = 1;
(no output = 1 row updated)

-- Verify the change:
sqlite> SELECT id, name, age FROM users WHERE id = 1;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1  | Gordon | 31  |
+----+--------+-----+

sqlite> DELETE FROM users WHERE id = 2;
(no output = 1 row deleted)

-- Verify Alice is gone:
sqlite> SELECT id, name FROM users;
+----+--------+
| id | name   |
+----+--------+
| 1  | Gordon |
| 3  | Bob    |
| 4  | Carol  |
+----+--------+

-- Dangerous: UPDATE without WHERE — hits ALL rows!
sqlite> UPDATE users SET age = 99;
(no output — but now EVERY user has age = 99!)

sqlite> DROP TABLE IF EXISTS users;
(no output = table dropped successfully)

sqlite> .tables
(empty — no tables remain)

Rule #1: Always write your WHERE clause first and verify it with a SELECT before running UPDATE or DELETE. A missing WHERE updates or deletes every row.

Advanced WHERE Filtering

-- AND / OR logic
SELECT * FROM users WHERE age > 25 AND age < 40;
SELECT * FROM users WHERE name = 'Alice' OR name = 'Bob';

-- BETWEEN (inclusive on both ends)
SELECT * FROM users WHERE age BETWEEN 25 AND 40;

-- IN — match any value in a list
SELECT * FROM users WHERE name IN ('Alice', 'Bob', 'Carol');

-- NOT IN — exclude a list
SELECT * FROM users WHERE name NOT IN ('Alice', 'Bob');

-- LIKE — pattern matching (% = any chars, _ = one char)
SELECT * FROM users WHERE name LIKE 'G%';      -- starts with G
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name LIKE '_ob';     -- Bob, Rob, etc.

-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

Terminal Output

sqlite> SELECT * FROM users WHERE age > 25 AND age < 40;
+----+--------+--------------------+-----+
| id | name   | email              | age |
+----+--------+--------------------+-----+
| 1  | Gordon | gordon@example.com | 30  |
| 3  | Bob    |                    | 34  |
| 4  | Carol  | carol@example.com  | 29  |
+----+--------+--------------------+-----+

sqlite> SELECT * FROM users WHERE name IN ('Alice', 'Bob', 'Carol');
+----+-------+-----------------+-----+
| id | name  | email           | age |
+----+-------+-----------------+-----+
| 2  | Alice | alice@gmail.com | 25  |
| 3  | Bob   |                 | 34  |
| 4  | Carol | carol@example.com | 29  |
+----+-------+-----------------+-----+

sqlite> SELECT * FROM users WHERE name LIKE 'G%';
+----+--------+--------------------+-----+
| id | name   | email              | age |
+----+--------+--------------------+-----+
| 1  | Gordon | gordon@example.com | 30  |
+----+--------+--------------------+-----+

sqlite> SELECT * FROM users WHERE email IS NULL;
+----+------+-------+-----+
| id | name | email | age |
+----+------+-------+-----+
| 3  | Bob  |       | 34  |
+----+------+-------+-----+

sqlite> SELECT * FROM users WHERE email IS NOT NULL;
+----+--------+--------------------+-----+
| id | name   | email              | age |
+----+--------+--------------------+-----+
| 1  | Gordon | gordon@example.com | 30  |
| 2  | Alice  | alice@gmail.com    | 25  |
| 4  | Carol  | carol@example.com  | 29  |
+----+--------+--------------------+-----+

Aggregate Functions & GROUP BY

Aggregate functions summarize multiple rows into a single result. Combine them with GROUP BY to compute per-group statistics.

-- Built-in aggregate functions
SELECT COUNT(*) FROM users;             -- total rows
SELECT COUNT(email) FROM users;         -- rows where email is not NULL
SELECT SUM(age) FROM users;             -- sum of all ages
SELECT AVG(age) FROM users;             -- average age
SELECT MIN(age), MAX(age) FROM users;  -- min and max

-- GROUP BY — aggregate per category
-- Suppose we have an orders table with a status column
SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status;

-- HAVING — filter groups (like WHERE but for aggregates)
SELECT status, COUNT(*) AS total
FROM orders
GROUP BY status
HAVING total > 5;

-- Column aliases with AS
SELECT name, age AS years_old FROM users;
SELECT ROUND(AVG(age), 1) AS avg_age FROM users;

Terminal Output

sqlite> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
| 4        |
+----------+

sqlite> SELECT COUNT(email) FROM users;
+--------------+
| COUNT(email) |
+--------------+
| 3            |
+--------------+

sqlite> SELECT AVG(age) FROM users;
+----------+
| AVG(age) |
+----------+
| 29.5     |
+----------+

sqlite> SELECT MIN(age), MAX(age) FROM users;
+----------+----------+
| MIN(age) | MAX(age) |
+----------+----------+
| 25       | 34       |
+----------+----------+

-- With an orders table (status, total columns):
sqlite> SELECT status, COUNT(*) AS total FROM orders GROUP BY status;
+-----------+-------+
| status    | total |
+-----------+-------+
| delivered | 12    |
| pending   | 3     |
| shipped   | 7     |
+-----------+-------+

sqlite> SELECT ROUND(AVG(age), 1) AS avg_age FROM users;
+---------+
| avg_age |
+---------+
| 29.5    |
+---------+

Joining Tables

JOINs combine rows from two or more tables based on a related column. Set up two tables first:

-- Setup: two related tables
CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT
);
CREATE TABLE employees (
  id    INTEGER PRIMARY KEY,
  name  TEXT,
  dept_id INTEGER REFERENCES departments(id)
);

-- INNER JOIN — only rows that match in BOTH tables
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN — all employees, even those with no department
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- Self-join — compare rows within the same table
SELECT a.name, b.name AS colleague
FROM employees a
JOIN employees b ON a.dept_id = b.dept_id
WHERE a.id != b.id;

Terminal Output

-- After inserting sample rows into departments and employees:
sqlite> SELECT e.name, d.name AS department FROM employees e INNER JOIN departments d ON e.dept_id = d.id;
+-------+-------------+
| name  | department  |
+-------+-------------+
| Alice | Engineering |
| Bob   | Marketing   |
| Carol | Engineering |
+-------+-------------+

-- LEFT JOIN includes Dave, who has no department (NULL):
sqlite> SELECT e.name, d.name AS department FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;
+-------+-------------+
| name  | department  |
+-------+-------------+
| Alice | Engineering |
| Bob   | Marketing   |
| Carol | Engineering |
| Dave  |             |
+-------+-------------+

-- Self-join: list each employee with their colleagues in the same dept:
sqlite> SELECT a.name, b.name AS colleague FROM employees a JOIN employees b ON a.dept_id = b.dept_id WHERE a.id != b.id;
+-------+-----------+
| name  | colleague |
+-------+-----------+
| Alice | Carol     |
| Carol | Alice     |
+-------+-----------+

INNER JOIN = only matched rows. LEFT JOIN = all left-table rows + matched right-table rows (NULLs where no match). SQLite does not support RIGHT JOIN or FULL OUTER JOIN directly.

Altering Tables

-- Add a new column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Rename the table
ALTER TABLE users RENAME TO customers;

-- Rename a column (SQLite 3.25.0+)
ALTER TABLE users RENAME COLUMN age TO birth_year;

-- SQLite can't DROP columns directly.
-- Workaround: recreate the table without the column
CREATE TABLE users_new AS
  SELECT id, name, email FROM users;  -- omit 'age'
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

Terminal Output

sqlite> ALTER TABLE users ADD COLUMN phone TEXT;
-- no output; column silently added
sqlite> .schema users
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, email TEXT, age INTEGER, phone TEXT);

sqlite> ALTER TABLE users RENAME TO customers;
sqlite> ALTER TABLE customers RENAME COLUMN age TO birth_year;
-- both return no output on success

-- Drop-column workaround (recreate without 'age'):
sqlite> CREATE TABLE users_new AS SELECT id, name, email FROM users;
sqlite> DROP TABLE users;
sqlite> ALTER TABLE users_new RENAME TO users;
sqlite> .schema users
CREATE TABLE users(id INTEGER, name TEXT, email TEXT);

SQLite limitation: ALTER TABLE is much more restricted than MySQL or PostgreSQL. You cannot drop columns, change column types, or add constraints to existing columns directly.

Useful Built-in Functions

Text functions

-- String operations
LENGTH('hello')         -- 5
UPPER('hello')         -- HELLO
LOWER('HELLO')         -- hello
TRIM('  hi  ')         -- hi
SUBSTR('hello',2,3)    -- ell
REPLACE(name,'o','0') -- swap chars
INSTR(name, 'or')     -- find position

-- Combine columns
SELECT first || ' ' || last
  AS full_name FROM people;

Numeric & date functions

-- Math
ABS(-5)             -- 5
ROUND(3.14159, 2)  -- 3.14
MAX(3, 7, 2)       -- 7
MIN(3, 7, 2)       -- 2
RANDOM()           -- random integer

-- Dates
DATE('now')         -- 2025-05-02
DATETIME('now')    -- 2025-05-02 14:30:00
STRFTIME('%Y', 'now') -- 2025

-- Null handling
COALESCE(NULL, 'default') -- default
IFNULL(col, 0)     -- 0 if null

Terminal Output

sqlite> SELECT LENGTH('hello'), UPPER('hello'), TRIM('  hi  ');
+----------------+----------------+-------------------+
| LENGTH('hello')| UPPER('hello') | TRIM('  hi  ')    |
+----------------+----------------+-------------------+
| 5              | HELLO          | hi                |
+----------------+----------------+-------------------+

sqlite> SELECT SUBSTR('hello', 2, 3), REPLACE('Gordon', 'o', '0');
+-----------------------+-----------------------------+
| SUBSTR('hello', 2, 3) | REPLACE('Gordon', 'o', '0') |
+-----------------------+-----------------------------+
| ell                   | G0rd0n                      |
+-----------------------+-----------------------------+

sqlite> SELECT ABS(-5), ROUND(3.14159, 2), DATE('now'), sqlite_version();
+---------+-------------------+------------+------------------+
| ABS(-5) | ROUND(3.14159, 2) | DATE('now')| sqlite_version() |
+---------+-------------------+------------+------------------+
| 5       | 3.14              | 2026-05-02 | 3.45.1           |
+---------+-------------------+------------+------------------+

sqlite> SELECT COALESCE(NULL, 'default'), IFNULL(NULL, 0);
+---------------------------+-----------------+
| COALESCE(NULL, 'default') | IFNULL(NULL, 0) |
+---------------------------+-----------------+
| default                   | 0               |
+---------------------------+-----------------+

Indexes — Speed Up Queries

An index lets SQLite find rows without scanning every row. Create them on columns you frequently filter or sort on.

-- Create a basic index
CREATE INDEX idx_users_name ON users (name);

-- Unique index (also enforces constraint)
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Composite index (covers multi-column queries)
CREATE INDEX idx_orders_user_date
  ON orders (user_id, created_at);

-- View all indexes on a table
PRAGMA index_list(users);

-- Drop an index
DROP INDEX IF EXISTS idx_users_name;

-- EXPLAIN QUERY PLAN — see if your index is being used
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'g@x.com';

Terminal Output

sqlite> CREATE INDEX idx_users_name ON users (name);
sqlite> CREATE UNIQUE INDEX idx_users_email ON users (email);
-- no output on success

sqlite> PRAGMA index_list(users);
+-----+------------------+--------+--------+---------+
| seq | name             | unique | origin | partial |
+-----+------------------+--------+--------+---------+
| 0   | idx_users_email  | 1      | c      | 0       |
| 1   | idx_users_name   | 0      | c      | 0       |
+-----+------------------+--------+--------+---------+

sqlite> EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'g@x.com';
+----+----+----+--------------------------------------------------------------+
| id | parent | notused | detail                                               |
+----+----+----+--------------------------------------------------------------+
| 2  | 0      | 0       | SEARCH users USING INDEX idx_users_email (email=?)   |
+----+----+----+--------------------------------------------------------------+

-- Without the index, you'd see SCAN instead of SEARCH:
-- detail: SCAN users

Trade-off: Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE) because the index must be updated too. Only index columns you actually query on.

Views — Saved Queries

A view is a named query stored in the database. It looks and acts like a table but doesn't store data itself — it re-runs the query each time you access it.

-- Create a view
CREATE VIEW active_users AS
  SELECT id, name, email
  FROM users
  WHERE status = 'active';

-- Use a view like a table
SELECT * FROM active_users;
SELECT * FROM active_users WHERE name LIKE 'A%';

-- List all views
SELECT name FROM sqlite_master WHERE type = 'view';

-- Drop a view
DROP VIEW IF EXISTS active_users;

Terminal Output

sqlite> CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE status = 'active';
-- no output on success

sqlite> SELECT * FROM active_users;
+----+--------+--------------------+
| id | name   | email              |
+----+--------+--------------------+
| 1  | Gordon | gordon@example.com |
| 2  | Alice  | alice@gmail.com    |
+----+--------+--------------------+

sqlite> SELECT * FROM active_users WHERE name LIKE 'A%';
+----+-------+-----------------+
| id | name  | email           |
+----+-------+-----------------+
| 2  | Alice | alice@gmail.com |
+----+-------+-----------------+

sqlite> SELECT name FROM sqlite_master WHERE type = 'view';
+--------------+
| name         |
+--------------+
| active_users |
+--------------+

Subqueries

A subquery is a SELECT nested inside another SQL statement. It can appear in WHERE, FROM, or SELECT clauses.

-- WHERE subquery — find users older than average
SELECT name, age FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- EXISTS subquery — find users who have placed orders
SELECT name FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
);

-- IN subquery
SELECT * FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE total > 100
);

-- FROM subquery (derived table)
SELECT dept, avg_age FROM (
  SELECT dept_id AS dept, AVG(age) AS avg_age
  FROM employees
  GROUP BY dept_id
) WHERE avg_age > 30;

Terminal Output

-- AVG(age) of users = 29.5; rows with age > 29.5:
sqlite> SELECT name, age FROM users WHERE age > (SELECT AVG(age) FROM users);
+--------+-----+
| name   | age |
+--------+-----+
| Gordon | 30  |
| Bob    | 34  |
+--------+-----+

-- EXISTS subquery — users who have at least one order:
sqlite> SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
+--------+
| name   |
+--------+
| Gordon |
| Alice  |
+--------+

-- FROM subquery — departments with avg employee age > 30:
sqlite> SELECT dept, avg_age FROM (SELECT dept_id AS dept, AVG(age) AS avg_age FROM employees GROUP BY dept_id) WHERE avg_age > 30;
+------+---------+
| dept | avg_age |
+------+---------+
| 2    | 32.5    |
+------+---------+

Transactions

A transaction groups multiple statements into a single all-or-nothing operation. If anything fails, you roll back and nothing is saved.

-- Wrap multiple operations in a transaction
BEGIN TRANSACTION;

INSERT INTO orders (user_id, total)
  VALUES (1, 99.99);

UPDATE inventory
  SET stock = stock - 1
  WHERE product_id = 42;

-- If everything is OK, save it
COMMIT;

-- Or undo everything if something went wrong
ROLLBACK;

-- SAVEPOINT — partial rollback points
SAVEPOINT before_update;
UPDATE users SET age = 99 WHERE id = 1;
ROLLBACK TO before_update;  -- undoes only since savepoint
RELEASE before_update;

Terminal Output

sqlite> BEGIN TRANSACTION;
sqlite> INSERT INTO orders (user_id, total) VALUES (1, 99.99);
sqlite> UPDATE inventory SET stock = stock - 1 WHERE product_id = 42;
sqlite> COMMIT;
-- all changes saved atomically; no output on success

-- ROLLBACK example — undo if something goes wrong:
sqlite> BEGIN TRANSACTION;
sqlite> DELETE FROM orders WHERE id = 5;
sqlite> ROLLBACK;
-- DELETE is undone; row 5 still exists

-- SAVEPOINT — partial rollback:
sqlite> SAVEPOINT before_update;
sqlite> UPDATE users SET age = 99 WHERE id = 1;
sqlite> SELECT age FROM users WHERE id = 1;
+-----+
| age |
+-----+
| 99  |
+-----+
sqlite> ROLLBACK TO before_update;
sqlite> SELECT age FROM users WHERE id = 1;
+-----+
| age |
+-----+
| 30  |
+-----+

Why use transactions? Performance — wrapping 1000 INSERTs in one transaction can be 10–100x faster than auto-committing each one. Data integrity — either all changes happen, or none do.

CTEs — Common Table Expressions

A CTE (WITH clause) creates a temporary named result set you can reference in the same query. They make complex queries readable and can be recursive.

-- Basic CTE — name a subquery for readability
WITH high_spenders AS (
  SELECT user_id, SUM(total) AS lifetime
  FROM orders
  GROUP BY user_id
  HAVING lifetime > 500
)
SELECT u.name, hs.lifetime
FROM users u
JOIN high_spenders hs ON u.id = hs.user_id
ORDER BY hs.lifetime DESC;

-- Chained CTEs (reference each other)
WITH
  totals AS (
    SELECT user_id, SUM(total) AS spend FROM orders GROUP BY user_id
  ),
  ranked AS (
    SELECT *, RANK() OVER (ORDER BY spend DESC) AS rnk
    FROM totals
  )
SELECT * FROM ranked WHERE rnk <= 10;

-- Recursive CTE — walk a tree/hierarchy
WITH RECURSIVE org_tree(id, name, depth) AS (
  SELECT id, name, 0 FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth;

Terminal Output

-- high_spenders CTE — users with lifetime spend > $500:
sqlite> WITH high_spenders AS (
   ...>   SELECT user_id, SUM(total) AS lifetime FROM orders GROUP BY user_id HAVING lifetime > 500
   ...> )
   ...> SELECT u.name, hs.lifetime FROM users u JOIN high_spenders hs ON u.id = hs.user_id ORDER BY hs.lifetime DESC;
+--------+----------+
| name   | lifetime |
+--------+----------+
| Gordon | 823.50   |
| Alice  | 612.00   |
+--------+----------+

-- Chained CTE with RANK():
sqlite> WITH totals AS (SELECT user_id, SUM(total) AS spend FROM orders GROUP BY user_id),
   ...>      ranked AS (SELECT *, RANK() OVER (ORDER BY spend DESC) AS rnk FROM totals)
   ...> SELECT * FROM ranked WHERE rnk <= 3;
+---------+--------+-----+
| user_id | spend  | rnk |
+---------+--------+-----+
| 1       | 823.50 | 1   |
| 2       | 612.00 | 2   |
| 3       | 210.00 | 3   |
+---------+--------+-----+

-- Recursive CTE — org hierarchy:
sqlite> WITH RECURSIVE org_tree(id, name, depth) AS (
   ...>   SELECT id, name, 0 FROM employees WHERE manager_id IS NULL
   ...>   UNION ALL
   ...>   SELECT e.id, e.name, t.depth + 1 FROM employees e JOIN org_tree t ON e.manager_id = t.id
   ...> ) SELECT * FROM org_tree ORDER BY depth;
+----+-------+-------+
| id | name  | depth |
+----+-------+-------+
| 1  | CEO   | 0     |
| 2  | Alice | 1     |
| 3  | Bob   | 1     |
| 4  | Carol | 2     |
+----+-------+-------+

Triggers

A trigger is SQL that runs automatically when a specific event (INSERT, UPDATE, DELETE) happens on a table.

-- Auto-set updated_at timestamp on row change
CREATE TRIGGER set_updated_at
AFTER UPDATE ON users
FOR EACH ROW BEGIN
  UPDATE users
  SET updated_at = DATETIME('now')
  WHERE id = NEW.id;
END;

-- Audit log — record who deleted what
CREATE TRIGGER log_deletes
BEFORE DELETE ON orders
FOR EACH ROW BEGIN
  INSERT INTO audit_log (action, record_id, happened_at)
  VALUES ('DELETE', OLD.id, DATETIME('now'));
END;

-- List all triggers
SELECT name FROM sqlite_master WHERE type = 'trigger';

-- Drop a trigger
DROP TRIGGER IF EXISTS set_updated_at;

Terminal Output

sqlite> CREATE TRIGGER set_updated_at AFTER UPDATE ON users FOR EACH ROW BEGIN UPDATE users SET updated_at = DATETIME('now') WHERE id = NEW.id; END;
-- no output on success

sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
+----------------+
| name           |
+----------------+
| set_updated_at |
| log_deletes    |
+----------------+

-- After updating a user, the trigger fires automatically:
sqlite> UPDATE users SET age = 31 WHERE id = 1;
sqlite> SELECT id, name, updated_at FROM users WHERE id = 1;
+----+--------+---------------------+
| id | name   | updated_at          |
+----+--------+---------------------+
| 1  | Gordon | 2026-05-02 14:22:11 |
+----+--------+---------------------+

-- DROP TRIGGER removes it cleanly:
sqlite> DROP TRIGGER IF EXISTS set_updated_at;
sqlite> SELECT name FROM sqlite_master WHERE type = 'trigger';
+-------------+
| name        |
+-------------+
| log_deletes |
+-------------+

Inside a trigger, NEW.column refers to the incoming value and OLD.column refers to the previous value (for UPDATE and DELETE).

PRAGMA — Database Settings & Info

PRAGMAs are special commands that control SQLite's internal settings and expose metadata.

-- Enforce foreign key constraints (OFF by default!)
PRAGMA foreign_keys = ON;

-- Check integrity of the database file
PRAGMA integrity_check;

-- See column info for a table
PRAGMA table_info(users);

-- See all indexes on a table
PRAGMA index_list(users);

-- Enable WAL mode (better write performance)
PRAGMA journal_mode = WAL;

-- Tune the cache size (in pages; negative = KB)
PRAGMA cache_size = -64000;  -- 64MB cache

-- Reclaim unused space after deletes
VACUUM;

-- See SQLite version info
SELECT sqlite_version();

Terminal Output

sqlite> PRAGMA foreign_keys = ON;
-- no output; now FK constraints are enforced

sqlite> PRAGMA integrity_check;
+-----------------+
| integrity_check |
+-----------------+
| ok              |
+-----------------+

sqlite> PRAGMA table_info(users);
+-----+-------+---------+---------+------------+----+
| cid | name  | type    | notnull | dflt_value | pk |
+-----+-------+---------+---------+------------+----+
| 0   | id    | INTEGER | 0       |            | 1  |
| 1   | name  | TEXT    | 0       |            | 0  |
| 2   | email | TEXT    | 0       |            | 0  |
| 3   | age   | INTEGER | 0       |            | 0  |
+-----+-------+---------+---------+------------+----+

sqlite> PRAGMA journal_mode = WAL;
+--------------+
| journal_mode |
+--------------+
| wal          |
+--------------+

sqlite> SELECT sqlite_version();
+------------------+
| sqlite_version() |
+------------------+
| 3.45.1           |
+------------------+

Important: PRAGMA foreign_keys = ON must be run every time you open a connection — it resets to OFF by default. Foreign key constraints are silently ignored if this is off.

UPSERT, Window Functions & JSON

UPSERT (INSERT OR REPLACE)

-- Insert or update if the key already exists (SQLite 3.24+)
INSERT INTO users (id, name, age)
VALUES (1, 'Gordon', 31)
ON CONFLICT(id) DO UPDATE SET
  name = excluded.name,
  age  = excluded.age;

-- Older approach — replace entire row
INSERT OR REPLACE INTO users (id, name, age)
VALUES (1, 'Gordon', 31);

Window Functions (SQLite 3.25+)

-- ROW_NUMBER — rank each row within a partition
SELECT name, age,
  ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;

-- Running total
SELECT name, total,
  SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- LAG / LEAD — compare to previous/next row
SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_revenue
FROM daily_sales;

JSON Support (SQLite 3.38+)

-- Store JSON in a TEXT column, query with json_extract
INSERT INTO products (name, attrs)
VALUES ('Widget', '{"color":"blue","weight":1.5}');

-- Extract a field from JSON
SELECT name, json_extract(attrs, '$.color') AS color
FROM products;

-- Filter by JSON field value
SELECT * FROM products
WHERE json_extract(attrs, '$.color') = 'blue';

Terminal Output

-- UPSERT: id=1 already exists; age is updated to 31
sqlite> INSERT INTO users (id, name, age) VALUES (1, 'Gordon', 31)
   ...> ON CONFLICT(id) DO UPDATE SET name = excluded.name, age = excluded.age;
sqlite> SELECT id, name, age FROM users WHERE id = 1;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 1  | Gordon | 31  |
+----+--------+-----+

-- Window function: rank all users by age descending
sqlite> SELECT name, age, ROW_NUMBER() OVER (ORDER BY age DESC) AS rank FROM users;
+--------+-----+------+
| name   | age | rank |
+--------+-----+------+
| Bob    | 34  | 1    |
| Gordon | 31  | 2    |
| Carol  | 29  | 3    |
| Alice  | 25  | 4    |
+--------+-----+------+

-- Running order total, earliest to latest
sqlite> SELECT name, total, SUM(total) OVER (ORDER BY created_at) AS running_total FROM orders;
+--------+--------+---------------+
| name   | total  | running_total |
+--------+--------+---------------+
| Order1 | 49.99  | 49.99         |
| Order2 | 99.99  | 149.98        |
| Order3 | 199.00 | 348.98        |
+--------+--------+---------------+

-- JSON: extract color field from stored JSON
sqlite> SELECT name, json_extract(attrs, '$.color') AS color FROM products;
+--------+-------+
| name   | color |
+--------+-------+
| Widget | blue  |
+--------+-------+

SQLite3 Knowledge Quiz

10 questions — click an option to answer

0 / 10 answered

Score: 0 / 0

0/10

Correct

Incorrect

Score