SQL Reference for Feather

How to use this tutorial

This is Feather's built-in SQL reference — designed for anyone using Feather to work with SQLite databases. It covers the full range of SQLite SQL: from basic queries to advanced schema design, without the overhead of setting up a server. SQLite is an open-source, zero-configuration, self-contained, transactional relational database engine, and Feather is your native macOS interface for it.

Every concept is explained first, then demonstrated with runnable SQL you can paste directly into the sqlite3 shell. We use the Dary's Video Rental sample database — a fictional mom-and-pop video store in Vermont — so that joins and aggregates have a realistic shape.

The natural learning path:

1. Install SQLite and open the sample database.
2. Learn to read data: SELECT, filtering, sorting, paginating.
3. Combine data: joins, unions, subqueries, CTEs.
4. Modify data: INSERT, UPDATE, DELETE, transactions.
5. Design schemas: data types, constraints, indexes, views, triggers.

What is SQLite

SQLite is a software library that provides a relational database management system. The "lite" in SQLite refers to its lightweight nature in setup, administration, and required resources. It is the most widely deployed SQL database engine in the world, shipping inside phones, browsers, desktop apps, game consoles, cars, and embedded devices.

Serverless architecture

Traditional relational databases (MySQL, PostgreSQL, Oracle) run as a separate server process. Client applications connect over TCP/IP and send SQL over the wire. SQLite works differently: it has no server. The database lives in a single file on disk, and the application reads and writes that file directly using the SQLite library.

Self-contained and zero-configuration

SQLite is written in ANSI C as a single source file. You can drop sqlite3.c and sqlite3.h into any C project and compile them in. There are no config files, no daemons to start, no users or permissions to set up. When you need a database, you open a file.

Transactional and ACID

All changes in SQLite happen inside transactions that are Atomic, Consistent, Isolated, and Durable — even in the face of crashes or power failures. Multiple processes can read the same database simultaneously, and writes are serialized safely.

Dynamic typing

Unlike most databases where a column has a rigid type, SQLite uses manifest typing: the type is a property of the value, not of the column. A column declared INTEGER will accept text or BLOBs without complaint. This is powerful but also a footgun — we cover it in detail in the Data Types chapter.

When to reach for SQLite

• Embedded apps — mobile apps, desktop software, IoT.
• Local storage — settings, caches, logs, offline queues.
• Application file format — instead of a custom binary or XML format, use a SQLite file.
• Small-to-medium websites — low-traffic sites serve fine from SQLite.
• Testing & prototyping — in-memory databases spin up in microseconds.

When not to use it: high-concurrency write-heavy workloads, or when you need multiple machines sharing one database over a network. For those, use a client/server database.

Download & install SQLite

The SQLite command-line program sqlite3 is the fastest way to interact with a database. Get it from the official download page: sqlite.org/download.html.

Windows

Download the precompiled binaries for Windows — the "sqlite-tools" ZIP file. Extract it to a directory such as C:\sqlite and add that directory to your PATH. You'll see sqlite3.exe, sqldiff.exe, and related tools.

macOS

macOS ships with SQLite. To get the latest version:

brew install sqlite

Linux

# Debian / Ubuntu
sudo apt install sqlite3

# Fedora / RHEL
sudo dnf install sqlite

Starting the shell

$ sqlite3
SQLite version 3.45.0  2024-01-15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

With no arguments, the shell opens a temporary in-memory database — great for experimenting. To work on a file, pass its path:

$ sqlite3 mydata.db

The .help command inside the shell lists all dot commands; .quit exits. We cover the shell in depth in the sqlite3 Commands chapter.

Feather as your GUI

Feather is a native macOS SQLite browser — you're already using it. Open any .db or .sqlite file via the Welcome screen, browse and edit table data, inspect and modify schema with the Structure tab, run arbitrary SQL in the Query tab, and import or export data as SQL or CSV.

The Dary's Video Rental sample database

Every example in this tutorial uses daryvideorental.db, a sample database for a fictional mom-and-pop video rental store located in Dary, Vermont. The data spans 1995 through 2003 — right at the peak of the rent-a-VHS era. It's small enough to understand at a glance but has enough structure to illustrate every kind of query you'll write in practice.

What's in it

The database has six tables:

The shape of the data

• A movie can have many physical copies in inventory, each identified by a barcode like DV000047.
• A rental points at a specific copy_id in inventory (not at the movie directly) — so you always know which VHS tape went out the door.
• return_date is NULL when a copy is still checked out. About 200 copies are currently rented.
• Membership tiers come with discounts: Standard pays $3.49, Gold $2.99, Platinum $2.50.
• Every customer lives in Dary, VT — this is a small town.

Opening it in Feather

daryvideorental.db is bundled with Feather. Open it via Help → Open Sample Database from the menu bar, or use File → Open and navigate to Feather.app/Contents/Resources/daryvideorental.db. Once open, paste any query from this reference into Feather's Query tab and run it with ⌘Return.

You're now looking at a full decade of rental records across a thousand-movie catalog — enough real-looking data to practice against every query in this reference.

Essential sqlite3 shell commands

The sqlite3 shell has two kinds of input: normal SQL statements (terminated with ;) and dot commands that configure the shell itself (no semicolon, no spaces before the dot).

Most useful dot commands

Making the shell readable

The default output is dense. A one-time setup that makes everything nicer:

sqlite> .mode column
sqlite> .headers on
sqlite> .nullvalue NULL
sqlite> SELECT movie_id, title FROM movies LIMIT 3;
movie_id  title
--------  ------------
1         Jaws
2         Star Wars
4         Taxi Driver

You can save these preferences to ~/.sqliterc so every new session starts configured:

-- ~/.sqliterc
.mode column
.headers on
.nullvalue NULL

Multi-line statements

SQL statements can span many lines; the shell waits for a semicolon before executing. If you started a statement by mistake, type ; on an empty line to finish it — SQLite will return a parse error and return you to the prompt.

Attaching multiple databases

ATTACH DATABASE 'archive.db' AS archive;

-- Now you can query across both.
SELECT *
FROM main.customers c
LEFT JOIN archive.old_rentals o USING (customer_id);

SELECT: querying data

The SELECT statement is the heart of SQL. It retrieves rows from one or more tables and returns them as a result set. The simplest form:

SELECT column_1, column_2
FROM table_name;

Or, to retrieve every column:

SELECT * FROM table_name;

Examples against Dary's

-- All movies: risky; 1,000+ rows
SELECT * FROM movies;

-- Just what you need
SELECT movie_id, title, release_year FROM movies;

-- Expressions and aliases are allowed too
SELECT
    title,
    runtime_minutes / 60.0           AS runtime_hours,
    (2024 - release_year)           AS years_old
FROM movies;

Concatenation and calculation

SQLite uses || as the string concatenation operator (not +, which is arithmetic):

SELECT first_name || ' ' || last_name AS full_name
FROM customers;

The SELECT without FROM

You don't need a table to use SELECT. It works as a calculator:

SELECT 1 + 1;             -- 2
SELECT 'hello' || ' world';  -- hello world
SELECT sqlite_version();   -- the installed version

ORDER BY: sorting results

SQL result sets have no guaranteed order unless you ask for one. The ORDER BY clause sorts rows by one or more columns, ascending (ASC, the default) or descending (DESC).

SELECT title, director, runtime_minutes
FROM movies
ORDER BY title ASC;

SELECT title, director, runtime_minutes
FROM movies
ORDER BY runtime_minutes DESC;

Sorting by multiple columns

Sort by album first, then by track order within the album:

SELECT title, genre_id, release_year
FROM movies
ORDER BY genre_id ASC, release_year DESC;

Sort position

You can reference columns by their position in the SELECT list:

SELECT title, runtime_minutes
FROM movies
ORDER BY 2 DESC;  -- sort by runtime_minutes

This is tempting but fragile — adding a column to the select list silently changes the sort. Prefer column names.

NULL ordering

In SQLite, NULLs sort as lower than any non-null value. With ASC, NULLs come first; with DESC, they come last. To override, use NULLS FIRST or NULLS LAST:

SELECT title, director
FROM movies
ORDER BY director ASC NULLS LAST;

SELECT DISTINCT: removing duplicates

DISTINCT removes duplicate rows from a result set. It applies to the entire row — that is, to the full combination of columns in the SELECT list.

-- Unique directors in the catalog
SELECT DISTINCT director
FROM movies
ORDER BY director;

-- Unique (year, MPAA rating) pairs
SELECT DISTINCT release_year, mpaa_rating
FROM movies
ORDER BY release_year, mpaa_rating;

DISTINCT and NULL

SQLite treats NULLs as equal for the purpose of DISTINCT. That is, multiple NULLs collapse to a single row in the result — even though NULL = NULL is false in regular comparisons. It's a pragmatic choice, not a logically consistent one.

WHERE: filtering rows

The WHERE clause filters rows to only those matching a condition. It appears between FROM and ORDER BY:

SELECT columns
FROM table
WHERE condition
ORDER BY column;

Comparison operators

Examples

-- Movies longer than 2 hours
SELECT title, runtime_minutes
FROM movies
WHERE runtime_minutes > 120;

-- Gold-tier customers
SELECT first_name, last_name, membership_date
FROM customers
WHERE membership_tier = 'Gold';

-- Movies directed by someone whose name starts with "Steven"
SELECT title, director
FROM movies
WHERE director LIKE 'Steven%';

AND / OR: combining conditions

AND and OR combine multiple conditions. AND requires both conditions to be true; OR accepts either.

-- PG-rated comedies
SELECT title
FROM movies
WHERE mpaa_rating = 'PG'
  AND genre_id = 4;  -- 4 = Comedy

-- Gold or Platinum customers
SELECT first_name, last_name, membership_tier
FROM customers
WHERE membership_tier = 'Gold'
   OR membership_tier = 'Platinum';

Precedence and parentheses

AND binds tighter than OR. When mixing them, use parentheses to make intent explicit and correct:

-- "Comedy movies from either 1999 or 2000"
SELECT title
FROM movies
WHERE genre_id = 4
  AND (release_year = 1999 OR release_year = 2000);

-- Very different from this:
SELECT title
FROM movies
WHERE genre_id = 4
  AND release_year = 1999
   OR release_year = 2000;  -- = (genre=4 AND year=1999) OR year=2000

LIMIT: constraining result size

LIMIT caps the number of rows returned. It's essential for pagination and for keeping interactive queries responsive.

-- First 10 movies
SELECT movie_id, title
FROM movies
LIMIT 10;

-- 5 longest movies
SELECT title, runtime_minutes
FROM movies
ORDER BY runtime_minutes DESC
LIMIT 5;

OFFSET for pagination

Combine LIMIT with OFFSET to skip ahead in the result — typically to render "page 2", "page 3", etc.

-- Rows 11-20
SELECT movie_id, title
FROM movies
ORDER BY movie_id
LIMIT 10 OFFSET 10;

-- Shorthand (offset-first)
SELECT movie_id, title
FROM movies
ORDER BY movie_id
LIMIT 10, 10;  -- OFFSET 10, LIMIT 10

BETWEEN: range tests

BETWEEN low AND high checks whether a value falls inside a range, inclusive on both ends. It is sugar for value >= low AND value <= high.

-- Movies released in the 1980s
SELECT title, release_year
FROM movies
WHERE release_year BETWEEN 1980 AND 1989
ORDER BY release_year;

-- Outside that range
SELECT title, release_year
FROM movies
WHERE release_year NOT BETWEEN 1980 AND 1989;

Works with dates and strings too

-- Rentals in Q1 2003 (TEXT dates in ISO format sort correctly)
SELECT *
FROM rentals
WHERE rental_date BETWEEN '2003-01-01' AND '2003-03-31';

IN: membership tests

IN tests whether a value equals any value in a list. It's equivalent to a chain of OR comparisons but much more readable.

-- Chained ORs (ugly)
SELECT *
FROM movies
WHERE mpaa_rating = 'G'
   OR mpaa_rating = 'PG'
   OR mpaa_rating = 'PG-13';

-- Same thing with IN — "family-friendly"
SELECT *
FROM movies
WHERE mpaa_rating IN ('G', 'PG', 'PG-13');

IN with a subquery

The right side can be a subquery that returns a single column of values:

-- All movies in the Horror genre
SELECT title
FROM movies
WHERE genre_id IN (
    SELECT genre_id
    FROM genres
    WHERE name = 'Horror'
);

NOT IN and NULLs

LIKE: simple pattern matching

LIKE matches strings against a pattern using two wildcards:

-- Movies whose title starts with "The"
SELECT title FROM movies WHERE title LIKE 'The %';

-- Ends with "Movie"
SELECT title FROM movies WHERE title LIKE '%Movie';

-- Contains "Star"
SELECT title FROM movies WHERE title LIKE '%Star%';

-- Title is exactly 4 characters
SELECT title FROM movies WHERE title LIKE '____';

-- Starts with "S", then any single char, then "ng"
SELECT title FROM movies WHERE title LIKE 'S_ng%';

Case sensitivity

By default in SQLite, LIKE is case-insensitive for ASCII letters only. Non-ASCII letters (ü, é, etc.) are compared case-sensitively. You can change this with PRAGMA case_sensitive_like = 1;.

Escaping literal % and _

Use ESCAPE to specify an escape character:

-- Find notes containing a literal "10%"
SELECT note
FROM promo_notes
WHERE note LIKE '%10\%%' ESCAPE '\';

IS NULL: testing for missing values

NULL represents an unknown or missing value. It is not equal to anything — not even to another NULL. This means:

SELECT 1 = NULL;        -- NULL (not true, not false)
SELECT NULL = NULL;     -- NULL
SELECT NULL <> NULL;    -- NULL

To test for NULL, use IS NULL and IS NOT NULL:

-- Rentals currently checked out (not yet returned)
SELECT rental_id, customer_id, rental_date
FROM rentals
WHERE return_date IS NULL;

-- Rentals that have been returned
SELECT rental_id, return_date
FROM rentals
WHERE return_date IS NOT NULL;

IS vs =

SQLite's IS operator is a NULL-safe equality check: x IS y is true when x = y, and also true when both are NULL. This is a SQLite extension beyond standard SQL.

GLOB: Unix-style pattern matching

GLOB is like LIKE but uses Unix-shell wildcards and is case-sensitive.

-- Movies starting with "The " (case-sensitive)
SELECT title FROM movies WHERE title GLOB 'The *';

-- Titles starting with a digit
SELECT title FROM movies WHERE title GLOB '[0-9]*';

-- Titles NOT starting with a letter
SELECT title FROM movies WHERE title GLOB '[^a-zA-Z]*';

Joins: combining tables

Real data is spread across tables for a reason: normalization reduces duplication and makes updates sane. When you want to see it back together, you join tables. SQLite supports five kinds of joins:

• INNER JOIN — rows that match in both tables.
• LEFT JOIN — every row from the left, matched if possible.
• RIGHT JOIN — every row from the right, matched if possible.
• FULL OUTER JOIN — every row from both sides.
• CROSS JOIN — every combination (Cartesian product).

A SELF JOIN is just a table joined to itself — not a new kind of join, but a useful technique.

The building block

Every join is a FROM with multiple tables, an ON condition that describes how rows match up, and a join type that says what to do with rows that don't match.

SELECT columns
FROM left_table
<TYPE> JOIN right_table
  ON left_table.key = right_table.key;

The inventory table has a movie_id column that points into movies. To see each physical copy with its movie title, you join those two:

SELECT i.barcode, i.condition, m.title
FROM inventory i
INNER JOIN movies m ON m.movie_id = i.movie_id;

(Table aliases like i and m make long join queries readable.)

USING shorthand

If the join column has the same name in both tables, USING is tidier than ON:

SELECT i.barcode, m.title
FROM inventory i
INNER JOIN movies m USING (movie_id);

INNER JOIN

An INNER JOIN returns only rows where the ON condition is satisfied on both sides. If an inventory copy's movie_id didn't match any row in movies, that copy simply wouldn't appear in the result.

-- Each rental with the movie title and the genre name
SELECT
    r.rental_id,
    m.title,
    g.name AS genre,
    r.rental_date
FROM rentals r
INNER JOIN inventory i ON i.copy_id = r.copy_id
INNER JOIN movies m    ON m.movie_id = i.movie_id
INNER JOIN genres g    ON g.genre_id = m.genre_id
ORDER BY genre, title;

You can chain as many joins as you need. The query planner figures out an efficient order — indexes on the join columns are what make this fast.

LEFT JOIN

A LEFT JOIN returns every row from the left-hand table. If there's a match on the right, you see it; if not, the right-hand columns come back as NULL.

-- Every movie, with the barcodes of its copies (if any)
SELECT
    m.title,
    i.barcode
FROM movies m
LEFT JOIN inventory i ON i.movie_id = m.movie_id
ORDER BY m.title;

Finding orphans

A favorite pattern: LEFT JOIN, then keep only the rows where the right side came back NULL — these are rows on the left that have no match on the right.

-- Movies the store has never stocked a copy of
SELECT m.title
FROM movies m
LEFT JOIN inventory i ON i.movie_id = m.movie_id
WHERE i.copy_id IS NULL;

RIGHT JOIN

A RIGHT JOIN is the mirror of LEFT JOIN — it returns every row from the right-hand table, matched from the left where possible. Support for RIGHT JOIN was added in SQLite 3.39 (2022).

SELECT
    m.title,
    i.barcode
FROM inventory i
RIGHT JOIN movies m ON m.movie_id = i.movie_id;

In practice, most people always write LEFT JOINs — just swap the table order if you were going to reach for RIGHT JOIN. It's a stylistic convention that keeps queries more consistent.

CROSS JOIN

A CROSS JOIN produces the Cartesian product: every row on the left paired with every row on the right, with no matching condition. If the left table has 10 rows and the right has 20, you get 200 rows.

-- Every genre × MPAA-rating combination (for a coverage report)
SELECT g.name AS genre, r.rating
FROM genres g
CROSS JOIN (VALUES ('G'), ('PG'), ('PG-13'), ('R')) AS r(rating);

Use cases are narrow: generating combinations, building lookup tables, or padding out reports. Cross-joining two large tables by accident is a common bug — always double-check your ON clauses.

SELF JOIN

A self join is a table joined to itself. It's the standard way to work with hierarchical data — employees and managers, categories and subcategories — where a row references another row in the same table.

Dary's employees table has a reports_to column pointing at an employee_id in the same table. Margaret is the manager (reports to nobody); David reports to Margaret; the three clerks report to David.

-- Each employee alongside their manager
SELECT
    e.first_name || ' ' || e.last_name AS employee,
    m.first_name || ' ' || m.last_name AS manager
FROM employees e
LEFT JOIN employees m ON m.employee_id = e.reports_to;

We use LEFT JOIN instead of INNER JOIN so Margaret (who reports to nobody) still shows up. Aliases e and m are essential — SQL needs a way to tell the two "copies" of the table apart.

FULL OUTER JOIN

A FULL OUTER JOIN returns every row from both tables — matched together where possible, and with NULLs on the missing side otherwise. Support was added in SQLite 3.39.

SELECT
    m.title,
    i.barcode
FROM movies m
FULL OUTER JOIN inventory i ON i.movie_id = m.movie_id;

Before 3.39, the idiom was to simulate it by UNION-ing a LEFT JOIN with a RIGHT JOIN:

SELECT m.title, i.barcode
FROM movies m LEFT JOIN inventory i ON i.movie_id = m.movie_id
UNION
SELECT m.title, i.barcode
FROM movies m RIGHT JOIN inventory i ON i.movie_id = m.movie_id;

GROUP BY: summarizing data

GROUP BY collapses rows that share values in specified columns into a single output row per group. You then apply aggregate functions (COUNT, SUM, AVG, etc.) to summarize each group.

-- Count of copies per movie
SELECT
    movie_id,
    COUNT(*) AS copy_count
FROM inventory
GROUP BY movie_id
ORDER BY copy_count DESC;

-- Total revenue per membership tier
SELECT
    c.membership_tier,
    ROUND(SUM(r.rental_fee + r.late_fee), 2) AS revenue
FROM rentals r
JOIN customers c USING (customer_id)
GROUP BY c.membership_tier
ORDER BY revenue DESC;

Grouping by multiple columns

-- Average runtime per genre per MPAA rating
SELECT
    genre_id,
    mpaa_rating,
    ROUND(AVG(runtime_minutes), 1) AS avg_runtime
FROM movies
GROUP BY genre_id, mpaa_rating;

The cardinal GROUP BY rule

Every column in the SELECT list must either appear in the GROUP BY clause, or be inside an aggregate function. Anything else is ambiguous: the database doesn't know which row's value to show.

(SQLite will sometimes let you break this rule and pick an arbitrary value, but don't rely on that — it's a source of subtle bugs.)

HAVING: filtering groups

WHERE filters rows before grouping; HAVING filters groups after. If you need to reference an aggregate in your filter, you need HAVING.

-- Movies we have more than one copy of
SELECT
    movie_id,
    COUNT(*) AS copy_count
FROM inventory
GROUP BY movie_id
HAVING COUNT(*) > 1
ORDER BY copy_count DESC;

-- Customers who've rented more than 5 times
SELECT customer_id, COUNT(*) AS rental_count
FROM rentals
GROUP BY customer_id
HAVING COUNT(*) > 5;

WHERE vs HAVING, side by side

Both clauses can filter, but the order matters: WHERE → GROUP BY → HAVING → ORDER BY.

-- Revenue per genre (returned rentals only), top genres only
SELECT
    g.name,
    ROUND(SUM(r.rental_fee), 2) AS revenue
FROM rentals r
JOIN inventory i USING (copy_id)
JOIN movies m    USING (movie_id)
JOIN genres g    USING (genre_id)
WHERE r.return_date IS NOT NULL   -- filter rows first
GROUP BY g.name
HAVING SUM(r.rental_fee) > 100    -- then filter groups
ORDER BY revenue DESC;

UNION: stacking result sets

UNION combines the rows from two SELECT statements into a single result set. The two queries must return the same number of columns with compatible types. Column names come from the first query.

SELECT first_name, last_name, 'Employee' AS kind FROM employees
UNION
SELECT first_name, last_name, 'Customer' FROM customers;

UNION vs UNION ALL

UNION removes duplicates from the combined result. That costs extra work. If you know duplicates are impossible — or you don't care — use UNION ALL, which just concatenates:

SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers;

EXCEPT: set difference

EXCEPT returns rows from the left query that don't appear in the right query — a set difference.

-- Movies we have no copies of in inventory
SELECT movie_id FROM movies
EXCEPT
SELECT movie_id FROM inventory;

As with UNION, both queries must have the same number of columns with matching types. Duplicates are removed automatically.

INTERSECT: set intersection

INTERSECT returns rows present in both queries.

-- Genres that appear both in our catalog AND in at least one rental
SELECT DISTINCT genre_id FROM movies
INTERSECT
SELECT DISTINCT m.genre_id
FROM rentals r
JOIN inventory i USING (copy_id)
JOIN movies m    USING (movie_id);

Subqueries

A subquery is a SELECT nested inside another SQL statement. It computes something — a value, a row, or a whole table — that the outer query uses.

Scalar subquery (returns one value)

-- Movies longer than the average
SELECT title, runtime_minutes
FROM movies
WHERE runtime_minutes > (SELECT AVG(runtime_minutes) FROM movies);

Subquery in the FROM clause (returns a table)

-- Top 5 genres by catalog size, then total their runtime
SELECT g.name, SUM(m.runtime_minutes) AS total_minutes
FROM movies m
JOIN genres g USING (genre_id)
WHERE m.genre_id IN (
    SELECT genre_id
    FROM movies
    GROUP BY genre_id
    ORDER BY COUNT(*) DESC
    LIMIT 5
)
GROUP BY g.name;

Correlated subquery

A correlated subquery references columns from the outer query, and is re-evaluated for every outer row. They're powerful but can be slow — a join or window function is often faster.

-- Each movie with its most recent rental date
SELECT
    m.title,
    (SELECT MAX(r.rental_date)
       FROM rentals r
       JOIN inventory i ON i.copy_id = r.copy_id
       WHERE i.movie_id = m.movie_id) AS last_rented
FROM movies m;

EXISTS: testing for row presence

EXISTS takes a subquery and returns true if it produces any rows. It's short-circuiting — SQLite stops as soon as the first match is found — so it's usually more efficient than COUNT(*) > 0.

-- Customers who have rented at least once
SELECT customer_id, first_name, last_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM rentals r
    WHERE r.customer_id = c.customer_id
);

-- The inverse: members who've never rented anything
SELECT customer_id, first_name, last_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM rentals r
    WHERE r.customer_id = c.customer_id
);

Common Table Expressions (WITH)

A Common Table Expression (CTE) is a named, one-off result set that you define at the top of a query and reference by name below. CTEs make complex queries dramatically more readable than deeply nested subqueries.

-- Top-earning genres, using a CTE to name an intermediate step
WITH genre_revenue AS (
    SELECT
        g.name            AS genre,
        SUM(r.rental_fee) AS revenue
    FROM rentals r
    JOIN inventory i USING (copy_id)
    JOIN movies m    USING (movie_id)
    JOIN genres g    USING (genre_id)
    GROUP BY g.name
)
SELECT *
FROM genre_revenue
ORDER BY revenue DESC
LIMIT 5;

Multiple CTEs

WITH
    copies_per_movie AS (
        SELECT movie_id, COUNT(*) AS copy_count
        FROM inventory
        GROUP BY movie_id
    ),
    copies_per_genre AS (
        SELECT m.genre_id, SUM(c.copy_count) AS genre_copies
        FROM movies m
        JOIN copies_per_movie c USING (movie_id)
        GROUP BY m.genre_id
    )
SELECT g.name, cg.genre_copies
FROM genres g
JOIN copies_per_genre cg USING (genre_id)
ORDER BY cg.genre_copies DESC
LIMIT 10;

Recursive CTEs

A recursive CTE refers to itself and is the standard way to walk trees and graphs in SQL — org charts, category hierarchies, file systems. The form is: an anchor query, UNION ALL, then a recursive query that references the CTE by name.

-- Numbers 1 through 10
WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

-- The store's org chart, walking down from Margaret to the clerks
WITH RECURSIVE chain(employee_id, first_name, last_name, reports_to, depth) AS (
    SELECT employee_id, first_name, last_name, reports_to, 0
    FROM employees
    WHERE reports_to IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, e.last_name, e.reports_to, c.depth + 1
    FROM employees e
    JOIN chain c ON e.reports_to = c.employee_id
)
SELECT depth, first_name, last_name FROM chain ORDER BY depth;

CASE: conditional expressions

CASE is SQL's if/else. It returns a value based on conditions and is usable anywhere an expression is valid — including SELECT, WHERE, and ORDER BY.

Searched form

SELECT
    title,
    runtime_minutes,
    CASE
        WHEN runtime_minutes < 90  THEN 'short'
        WHEN runtime_minutes < 120 THEN 'standard'
        ELSE                            'epic'
    END AS length_band
FROM movies;

Simple form

When comparing a single value against several alternatives:

SELECT
    title,
    CASE mpaa_rating
        WHEN 'G'     THEN 'All ages'
        WHEN 'PG'    THEN 'Parental guidance'
        WHEN 'PG-13' THEN 'Teen-plus'
        WHEN 'R'     THEN 'Restricted'
        ELSE             'Other'
    END AS audience
FROM movies;

CASE for conditional aggregation

A classic pattern: pivot summary counts into separate columns by wrapping an aggregate around a CASE.

SELECT
    COUNT(*)                                                          AS total_rentals,
    SUM(CASE WHEN return_date IS NULL  THEN 1 ELSE 0 END) AS still_out,
    SUM(CASE WHEN late_fee > 0        THEN 1 ELSE 0 END) AS had_late_fee
FROM rentals;

INSERT: adding rows

INSERT adds new rows to a table. The basic form specifies the target table, the columns being filled, and the values:

INSERT INTO table_name (col_1, col_2, col_3)
VALUES (value_1, value_2, value_3);

A single row

INSERT INTO genres (name)
VALUES ('Foreign');

We skipped genre_id because it's an INTEGER PRIMARY KEY: SQLite generates the value automatically. We cover this in the AUTOINCREMENT chapter.

Multiple rows at once

INSERT INTO genres (name) VALUES
    ('Silent'),
    ('Noir'),
    ('Biography');

INSERT ... SELECT

You can insert rows produced by a query — useful for backfilling or copying data between tables.

INSERT INTO movies_archive (movie_id, title, release_year)
SELECT movie_id, title, release_year
FROM movies
WHERE release_year < 1950;

Omitting the column list

You can leave out the column list if your VALUES provide every column, in the schema's declared order. It works, but it's fragile — any future ALTER TABLE ... ADD COLUMN silently breaks your INSERT. Prefer the explicit form.

DEFAULT VALUES

-- Insert a row filled entirely with column defaults
INSERT INTO rental_audit DEFAULT VALUES;

UPDATE: modifying rows

UPDATE changes values in existing rows. You specify the table, a SET clause with new values, and almost always a WHERE clause to limit the scope.

UPDATE table_name
SET col_1 = value_1,
    col_2 = value_2
WHERE condition;

Examples

-- Give Margaret a raise
UPDATE employees
SET hourly_wage = 20.00
WHERE employee_id = 1;

-- Raise every rental fee by 10%
UPDATE rentals
SET rental_fee = rental_fee * 1.10;

-- Fill in a missing director for one movie
UPDATE movies
SET director = 'Unknown'
WHERE director IS NULL
  AND movie_id = 42;

UPDATE with a subquery

-- Stamp each customer's last_rental_date from the rentals table
UPDATE customers
SET last_rental_date = (
    SELECT MAX(rental_date)
    FROM rentals
    WHERE rentals.customer_id = customers.customer_id
);

UPDATE FROM

Since SQLite 3.33, you can UPDATE ... FROM to join another table into the update — similar to PostgreSQL.

UPDATE inventory AS i
SET status = 'Available'
FROM rentals r
WHERE r.copy_id = i.copy_id
  AND r.return_date IS NOT NULL
  AND i.status = 'Rented';

DELETE: removing rows

DELETE removes rows from a table. Same caveat as UPDATE: omit the WHERE and the whole table is wiped out.

DELETE FROM table_name
WHERE condition;

-- Retire one physical copy (it got damaged)
DELETE FROM inventory WHERE copy_id = 999;

-- Remove all rentals older than 2001
DELETE FROM rentals WHERE rental_date < '2001-01-01';

-- Wipe a whole table (no undo!)
DELETE FROM rental_audit;

DELETE vs DROP TABLE

DELETE empties a table but leaves its structure and indexes intact. DROP TABLE removes the table itself. Use DELETE when you'll put new data in afterward; use DROP when the table is obsolete.

DELETE with LIMIT

When compiled with the SQLITE_ENABLE_UPDATE_DELETE_LIMIT option (the default in most distributions), you can cap the number of rows deleted:

DELETE FROM rental_audit
WHERE level = 'DEBUG'
ORDER BY created_at
LIMIT 1000;

REPLACE: insert-or-overwrite

REPLACE inserts a new row; if it would violate a UNIQUE or PRIMARY KEY constraint, it first deletes the conflicting row and then inserts the new one. It's a SQLite-specific alias for INSERT OR REPLACE.

REPLACE INTO store_settings (key, value)
VALUES ('late_fee_per_day', '1.00');

UPSERT: insert-or-update

UPSERT is what you usually want when "insert if new, else update". It's written as INSERT ... ON CONFLICT ... DO UPDATE. Unlike REPLACE, UPSERT does not delete and re-insert — it updates in place, preserving triggers' semantics and foreign-key relationships.

-- Try to insert; if the movie already exists, bump its rental tally
INSERT INTO movie_stats (movie_id, rentals)
VALUES (42, 1)
ON CONFLICT(movie_id) DO UPDATE SET
    rentals = rentals + 1;

Referring to the attempted values

In the DO UPDATE clause, excluded.column refers to the value that was about to be inserted. Bare column names refer to the existing row.

INSERT INTO movie_stats (movie_id, rentals, last_rented)
VALUES (42, 1, CURRENT_TIMESTAMP)
ON CONFLICT(movie_id) DO UPDATE SET
    rentals     = rentals + excluded.rentals,
    last_rented = excluded.last_rented;

DO NOTHING

If you just want to ignore conflicts silently:

INSERT INTO genres (name)
VALUES ('Action')
ON CONFLICT(name) DO NOTHING;

RETURNING: getting affected rows back

Since SQLite 3.35, INSERT, UPDATE, and DELETE can end with RETURNING, which makes them return the affected rows like a SELECT would. This saves a roundtrip when you need the generated primary key or the old/new values.

-- Insert and get back the auto-generated id
INSERT INTO genres (name)
VALUES ('Foreign')
RETURNING genre_id, name;

-- Update and see the new values
UPDATE rentals
SET rental_fee = rental_fee * 1.10
WHERE rental_date > '2003-06-01'
RETURNING rental_id, rental_fee;

-- Delete returned rentals older than a cutoff and log what went away
DELETE FROM rentals
WHERE return_date < '2001-01-01'
RETURNING rental_id, customer_id;

Inside RETURNING, use * for every column. You can also compute expressions — e.g., RETURNING rental_id, rental_fee, rental_fee * 0.06 AS tax.

Transactions

A transaction groups multiple SQL statements so they either all succeed or none of them do. SQLite wraps every single statement in an implicit transaction already; the explicit form lets you bundle many statements into one atomic unit.

-- Checking out a movie: create the rental and mark the copy as "Rented"
BEGIN TRANSACTION;

INSERT INTO rentals (copy_id, customer_id, employee_id, rental_date, due_date, rental_fee)
VALUES (42, 100, 5, '2003-11-15', '2003-11-18', 3.49);

UPDATE inventory SET status = 'Rented' WHERE copy_id = 42;

COMMIT;

If anything between BEGIN and COMMIT fails, you can ROLLBACK to throw away all the changes:

BEGIN;
INSERT INTO rentals (...) VALUES (...);
UPDATE inventory SET status = 'Rented' WHERE ...;
-- something went wrong in app code:
ROLLBACK;

Savepoints

A savepoint is a named point inside a transaction you can roll back to without aborting the whole transaction. Useful for nested "try" blocks.

BEGIN;

INSERT INTO customers (...) VALUES (...);

SAVEPOINT before_optional_step;
UPDATE customers SET membership_tier = 'Gold' WHERE ...;
-- oh no, that failed:
ROLLBACK TO before_optional_step;

COMMIT;  -- the customer insert still committed

Why you want them

• Atomicity — related changes commit together or not at all.
• Performance — 1,000 inserts inside one transaction run hundreds of times faster than 1,000 auto-committed inserts, because SQLite only syncs to disk once.
• Consistency — other readers see a single snapshot, not half-applied changes.

SQLite data types

SQLite's type system is unusual. It uses manifest typing: the data type lives on the value, not on the column. A column you declared as INTEGER will cheerfully store text or BLOBs.

The five storage classes

Every value in SQLite belongs to exactly one of these:

Type affinity

Each column has a declared type affinity — a recommendation about what kind of value it should hold. SQLite tries to coerce incoming values to the column's affinity, but doesn't enforce it. Affinities: TEXT, NUMERIC, INTEGER, REAL, BLOB.

Affinity is determined from the declared type name by string matching:

• Type contains INT → INTEGER affinity.
• Type contains CHAR, CLOB, or TEXT → TEXT.
• Type contains BLOB, or no type declared → BLOB affinity.
• Type contains REAL, FLOA, or DOUB → REAL.
• Anything else (NUMERIC, DECIMAL, BOOLEAN, DATE) → NUMERIC affinity.

typeof() for inspection

SELECT
    typeof(100),
    typeof(10.0),
    typeof('100'),
    typeof(x'1000'),
    typeof(NULL);
-- integer | real | text | blob | null

No dedicated Boolean, Date, or Time

SQLite doesn't have a Boolean type — use integers 0 and 1 (or the literals TRUE and FALSE, which are aliases for them since 3.23).

There's no Date or Datetime type either — you store dates as TEXT in ISO 8601 format, as INTEGER unix epoch seconds, or as REAL Julian day numbers. See the next chapter.

STRICT tables

If the dynamic typing makes you uncomfortable, append STRICT to your CREATE TABLE to enforce declared types:

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age  INTEGER
) STRICT;

In a STRICT table, inserting a value of the wrong type raises an error. Only the five canonical types (plus ANY) are allowed in the declaration.

Date and time

SQLite has no date/time storage class, but it ships with a full set of date/time functions that interpret values in three formats:

• TEXT in ISO 8601: '2024-06-15 13:45:00'
• INTEGER unix epoch seconds: 1718458500
• REAL Julian day numbers (rarely useful for apps).

Most applications should use TEXT ISO 8601 — it's human-readable, sorts and compares correctly as a string, and is what datetime() returns by default.

The core functions

Now

SELECT date('now');                     -- today
SELECT datetime('now');                 -- UTC now
SELECT datetime('now', 'localtime');    -- in local zone
SELECT unixepoch();                     -- epoch seconds

Modifiers

Date functions accept a chain of modifiers after the base value. Each modifier shifts the time.

SELECT date('now', '+7 days');             -- a week from today
SELECT date('now', 'start of month');      -- first of this month
SELECT date('now', 'start of month', '+1 month', '-1 day');
-- last day of this month
SELECT datetime('2024-06-15', 'weekday 0');  -- next Sunday

strftime for custom formats

SELECT strftime('%Y', rental_date)    AS year,
       strftime('%Y-%m', rental_date) AS year_month,
       strftime('%w', rental_date)    AS weekday
FROM rentals
LIMIT 5;

Differences between dates

-- Days between two dates
SELECT julianday('2003-12-31') - julianday('2003-01-01');

-- Days each rental stayed out (returned only)
SELECT
    rental_id,
    julianday(return_date) - julianday(rental_date) AS days_out
FROM rentals
WHERE return_date IS NOT NULL;

CREATE TABLE

The full shape of a CREATE TABLE:

CREATE TABLE [IF NOT EXISTS] [schema_name.]table_name (
    column_1 data_type column_constraints,
    column_2 data_type column_constraints,
    ...
    table_constraints
) [WITHOUT ROWID] [STRICT];

A worked example: a contacts app

CREATE TABLE contacts (
    contact_id INTEGER PRIMARY KEY,
    first_name TEXT    NOT NULL,
    last_name  TEXT    NOT NULL,
    email      TEXT    NOT NULL UNIQUE,
    phone      TEXT    NOT NULL UNIQUE
);

CREATE TABLE groups (
    group_id INTEGER PRIMARY KEY,
    name     TEXT    NOT NULL
);

-- Many-to-many link table with a composite primary key
CREATE TABLE contact_groups (
    contact_id INTEGER,
    group_id   INTEGER,

    PRIMARY KEY (contact_id, group_id),

    FOREIGN KEY (contact_id) REFERENCES contacts (contact_id)
        ON DELETE CASCADE ON UPDATE NO ACTION,

    FOREIGN KEY (group_id)   REFERENCES groups (group_id)
        ON DELETE CASCADE ON UPDATE NO ACTION
);

WITHOUT ROWID

Every normal table has an implicit hidden rowid — a 64-bit integer primary key SQLite uses internally. WITHOUT ROWID removes it, storing rows in a B-tree keyed on your declared primary key. It can be significantly faster for tables with non-integer primary keys or many small rows.

CREATE TABLE config (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
) WITHOUT ROWID;

CREATE TABLE AS

Copy the result of a query into a new table:

CREATE TABLE horror_movies AS
SELECT m.*
FROM movies m
JOIN genres g USING (genre_id)
WHERE g.name = 'Horror';

Handy, but note: this does not copy constraints, keys, or indexes from the source — only column types and data.

PRIMARY KEY

A primary key is a column (or set of columns) that uniquely identifies each row. Every table should have one — it's the foundation of referential integrity and of most indexes.

Column-level declaration

CREATE TABLE countries (
    code TEXT PRIMARY KEY,
    name TEXT NOT NULL
);

Composite primary key

CREATE TABLE order_items (
    order_id   INTEGER,
    product_id INTEGER,
    quantity   INTEGER NOT NULL,

    PRIMARY KEY (order_id, product_id)
);

The INTEGER PRIMARY KEY special case

When a column is declared exactly as INTEGER PRIMARY KEY, it becomes an alias for the internal rowid. This is the fastest key in SQLite. Insert without specifying a value and SQLite assigns the next unused rowid automatically.

Note: INT PRIMARY KEY (without the full word INTEGER) does not get this treatment — it's just a normal primary key.

FOREIGN KEY

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It's how you encode "this row belongs to that row."

CREATE TABLE movies (
    movie_id     INTEGER PRIMARY KEY,
    title        TEXT    NOT NULL,
    genre_id     INTEGER NOT NULL,

    FOREIGN KEY (genre_id) REFERENCES genres (genre_id)
);

PRAGMA foreign_keys = ON;

Referential actions

ON DELETE and ON UPDATE control what happens when the parent row is deleted or its key changes.

FOREIGN KEY (genre_id) REFERENCES genres (genre_id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION

NOT NULL constraint

NOT NULL forbids NULL values in a column. Declare it on any column that represents a required piece of data.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT    NOT NULL,
    name  TEXT    NOT NULL DEFAULT 'Anonymous'
);

Attempting to INSERT or UPDATE a NULL into a NOT-NULL column raises an error. If the column has a DEFAULT, omitting the column uses the default instead of NULL.

UNIQUE constraint

UNIQUE requires each value (or combination of values) in the column(s) to be distinct across all rows.

-- Single-column
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT    NOT NULL UNIQUE
);

-- Composite
CREATE TABLE enrollments (
    student_id INTEGER,
    course_id  INTEGER,
    year       INTEGER,
    UNIQUE (student_id, course_id, year)
);

UNIQUE and NULL

In SQLite, NULL is treated as distinct from every other NULL for uniqueness checks — so multiple NULLs are allowed in a UNIQUE column. This is standard SQL behavior and sometimes surprising.

CHECK constraint

CHECK validates each row against a Boolean expression. If the expression is false (but not NULL), the row is rejected.

CREATE TABLE products (
    id         INTEGER PRIMARY KEY,
    name       TEXT    NOT NULL,
    price      REAL    NOT NULL CHECK (price > 0),
    discount   REAL    NOT NULL DEFAULT 0
                       CHECK (discount >= 0 AND discount <= price)
);

-- Table-level check referencing multiple columns
CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    start_at TEXT NOT NULL,
    end_at   TEXT NOT NULL,
    CHECK (end_at > start_at)
);

AUTOINCREMENT

A column of type INTEGER PRIMARY KEY already generates values automatically — SQLite picks "one larger than the largest existing value" for each insert. For most uses, that's all you need.

The AUTOINCREMENT keyword changes this behavior slightly: it guarantees the same rowid is never reused, even after rows are deleted. SQLite tracks the highwater mark in a special sqlite_sequence table.

CREATE TABLE events (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT    NOT NULL,
    at   TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE

SQLite's ALTER TABLE supports a limited set of operations:

• RENAME TO — rename the table.
• RENAME COLUMN ... TO ... (since 3.25).
• ADD COLUMN — append a new column.
• DROP COLUMN (since 3.35).

-- Rename a table
ALTER TABLE old_name RENAME TO new_name;

-- Rename a column
ALTER TABLE customers RENAME COLUMN Phone TO PhoneNumber;

-- Add a column (must have NULL default or a constant default)
ALTER TABLE customers ADD COLUMN Nickname TEXT;
ALTER TABLE customers ADD COLUMN Vip INTEGER NOT NULL DEFAULT 0;

-- Drop a column
ALTER TABLE customers DROP COLUMN Nickname;

What you can't do directly

SQLite doesn't support changing a column's type, reordering columns, adding a foreign key, or changing constraints. For any of these, the standard idiom is:

1. Create a new table with the desired schema.
2. INSERT INTO new_table SELECT ... FROM old_table.
3. DROP TABLE old_table.
4. ALTER TABLE new_table RENAME TO old_table.

Do this inside a transaction. The SQLite docs call this the "12-step procedure" — it's well worth a read.

DROP TABLE

Delete a table, its data, its indexes, and its triggers. Irrevocable — there's no undo short of restoring from backup.

DROP TABLE experiments;

-- Don't fail if it's not there
DROP TABLE IF EXISTS experiments;

If foreign keys are on and other tables reference this one, DROP TABLE will fail unless you first drop the children or clear the references.

Views

A view is a stored SELECT statement you can query as if it were a table. Views don't contain data themselves — they're just a name for a query. Every time you read from a view, SQLite re-runs its underlying query.

-- A reusable "customer with their rental count" view
CREATE VIEW customer_activity AS
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name AS name,
    c.membership_tier,
    COUNT(r.rental_id)                            AS rental_count,
    COALESCE(SUM(r.rental_fee + r.late_fee), 0) AS lifetime_value
FROM customers c
LEFT JOIN rentals r ON r.customer_id = c.customer_id
GROUP BY c.customer_id;

-- Query it like a table
SELECT * FROM customer_activity WHERE lifetime_value > 20;

Views are read-only by default

You can't INSERT, UPDATE, or DELETE against a view directly in SQLite. To make that work you need an INSTEAD OF trigger.

Dropping a view

DROP VIEW IF EXISTS customer_activity;

Temporary views

CREATE TEMPORARY VIEW rentals_out_today AS
SELECT * FROM rentals WHERE date(rental_date) = date('now');

Temporary views disappear when the connection closes.

Indexes

An index is a sorted lookup structure (a B-tree) that lets SQLite find rows by a column's value without scanning the whole table. Every primary key and every UNIQUE constraint already creates an index automatically.

Creating an index

-- Single-column
CREATE INDEX idx_inventory_movie ON inventory (movie_id);

-- Composite (multiple columns)
CREATE INDEX idx_rentals_date_cust ON rentals (rental_date, customer_id);

-- Unique index — enforces uniqueness AND speeds lookups
CREATE UNIQUE INDEX idx_inventory_barcode ON inventory (barcode);

Partial indexes

An index with a WHERE clause — covering only the rows that match. Much smaller and faster when you frequently query a narrow slice.

-- Index only the copies currently rented out
CREATE INDEX idx_copies_out ON inventory (copy_id)
WHERE status = 'Rented';

Expression-based indexes

Index on the result of an expression. Great for case-insensitive searches or for computed columns.

CREATE INDEX idx_customers_last_lower
  ON customers (lower(last_name));

-- To benefit, queries must use the same expression:
SELECT * FROM customers WHERE lower(last_name) = 'young';

When to add indexes

• Columns used in WHERE, JOIN ... ON, and ORDER BY clauses.
• Foreign-key columns (SQLite doesn't index them automatically).
• Columns with high selectivity (many distinct values).

When NOT to add them

• On tables that are small and stay small.
• On columns with very few distinct values (e.g. a status flag with 3 values).
• On columns that are updated constantly — every write has to update every index too.

Seeing what the planner does

EXPLAIN QUERY PLAN
SELECT * FROM inventory WHERE movie_id = 1;
-- SEARCH inventory USING INDEX idx_inventory_movie (movie_id=?)

Look for "SEARCH ... USING INDEX" (good) vs. "SCAN" (a full table scan — fine on small tables, a red flag on big ones).

Listing and dropping

.indexes inventory                -- in the shell
DROP INDEX idx_inventory_movie;

Triggers

A trigger is a stored piece of SQL that runs automatically in response to changes in a table. SQLite supports BEFORE, AFTER, and INSTEAD OF triggers for INSERT, UPDATE, and DELETE.

CREATE TRIGGER [trigger_name]
[BEFORE | AFTER | INSTEAD OF] [INSERT | UPDATE | DELETE]
ON [table_name]
[WHEN condition]
BEGIN
    -- one or more SQL statements
END;

Audit logging example

-- A table to record changes
CREATE TABLE movie_title_audit (
    id         INTEGER PRIMARY KEY,
    movie_id   INTEGER,
    old_title  TEXT,
    new_title  TEXT,
    changed_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Fire after any movie title changes
CREATE TRIGGER log_movie_retitle
AFTER UPDATE OF title ON movies
WHEN old.title IS NOT new.title
BEGIN
    INSERT INTO movie_title_audit (movie_id, old_title, new_title)
    VALUES (old.movie_id, old.title, new.title);
END;

Inside a trigger, old refers to the row's pre-change values (available in UPDATE and DELETE); new refers to the post-change values (available in INSERT and UPDATE).

INSTEAD OF triggers

Since views are read-only, an INSTEAD OF trigger on a view lets you redirect writes into the underlying tables.

CREATE TRIGGER full_customer_names_insert
INSTEAD OF INSERT ON full_customer_names
BEGIN
    INSERT INTO customers (first_name, last_name, phone, street_address, city, state, zip, membership_date, membership_tier)
    VALUES (new.first, new.last, new.phone, '', 'Dary', 'VT', '', date('now'), 'Standard');
END;

Dropping

DROP TRIGGER IF EXISTS log_movie_retitle;

Full-text search (FTS5)

FTS5 is a SQLite extension that provides fast substring and phrase searches over text columns — the kind of thing that LIKE '%...%' can't do efficiently. It's built into the standard sqlite3 distribution.

Creating an FTS5 virtual table

-- Give our movies a searchable full-text index over title + director
CREATE VIRTUAL TABLE movies_fts USING fts5(title, director);

-- Populate it from the main table
INSERT INTO movies_fts (rowid, title, director)
SELECT movie_id, title, director FROM movies;

Querying

-- Movies mentioning "star"
SELECT title
FROM movies_fts
WHERE movies_fts MATCH 'star';

-- Multiple terms (AND)
SELECT title FROM movies_fts WHERE movies_fts MATCH 'star wars';

-- Phrase
SELECT title FROM movies_fts WHERE movies_fts MATCH '"rear window"';

-- Prefix
SELECT title FROM movies_fts WHERE movies_fts MATCH 'spiel*';

-- Restrict a term to one column
SELECT title FROM movies_fts WHERE movies_fts MATCH 'director: hitchcock';

Ranking

SELECT title, rank
FROM movies_fts
WHERE movies_fts MATCH 'star'
ORDER BY rank;

FTS5's rank uses the BM25 algorithm by default — lower is better, because it's internally a negative score.

Aggregate functions

Aggregate functions collapse many rows into a single value. Combine them with GROUP BY to get summaries per group. The core five:

Other useful ones: GROUP_CONCAT(expr [, separator]) joins values into a delimited string; TOTAL(expr) is like SUM but returns 0 instead of NULL when there are no rows.

DISTINCT inside aggregates

-- How many different genres has each customer rented from?
SELECT r.customer_id, COUNT(DISTINCT m.genre_id) AS genre_spread
FROM rentals r
JOIN inventory i USING (copy_id)
JOIN movies m    USING (movie_id)
GROUP BY r.customer_id;

FILTER clause

Since SQLite 3.30, you can attach a FILTER (WHERE ...) to an aggregate to count only a subset.

SELECT
    COUNT(*)                                           AS all_rentals,
    COUNT(*) FILTER (WHERE rental_fee < 3)        AS cheap,
    COUNT(*) FILTER (WHERE rental_fee >= 3)       AS full_price,
    COUNT(*) FILTER (WHERE late_fee > 0)          AS had_late_fee
FROM rentals;

AVG

AVG(expr) returns the arithmetic mean of non-NULL numeric values in the group. NULLs are ignored — they're excluded from both the sum and the count.

-- Average movie runtime, in minutes
SELECT ROUND(AVG(runtime_minutes), 1) AS avg_runtime
FROM movies;

-- Average rental fee, per membership tier
SELECT
    c.membership_tier,
    ROUND(AVG(r.rental_fee), 2) AS avg_fee
FROM rentals r
JOIN customers c USING (customer_id)
GROUP BY c.membership_tier
ORDER BY avg_fee DESC;

COUNT

Three important variants, each counting something different:

• COUNT(*) — every row in the group, including rows full of NULLs.
• COUNT(column) — rows where that column is not NULL.
• COUNT(DISTINCT column) — unique non-NULL values in that column.

SELECT
    COUNT(*)                 AS total_movies,
    COUNT(director)         AS movies_with_director,
    COUNT(DISTINCT director) AS distinct_directors
FROM movies;

MAX

MAX(expr) returns the largest non-NULL value. Works on numbers, strings (lexicographic order), and dates (if stored in ISO 8601 form).

SELECT MAX(runtime_minutes) FROM movies;
SELECT MAX(rental_date)     FROM rentals;
SELECT MAX(title)           FROM movies;

Bare-columns and MAX

A useful SQLite-specific feature: if a query uses MIN or MAX on one column and plain columns in the same SELECT without a GROUP BY, SQLite will pick the values from the row that actually had the min or max.

-- The longest movie, with its title and year
SELECT title, release_year, MAX(runtime_minutes)
FROM movies;

MIN

MIN(expr) is the mirror of MAX — smallest non-NULL value.

-- Oldest copy of each movie (first acquired)
SELECT
    movie_id,
    MIN(acquired_date) AS first_acquired
FROM inventory
GROUP BY movie_id;

-- Earliest rental in the store's history
SELECT MIN(rental_date) FROM rentals;

SUM

SUM(expr) adds up non-NULL numeric values. Returns NULL if every value is NULL (use TOTAL or COALESCE(SUM(...), 0) if you want 0 in that case).

-- Total store revenue across all rentals
SELECT ROUND(SUM(rental_fee + late_fee), 2) AS total_revenue
FROM rentals;

-- Revenue per customer, highest first
SELECT
    c.first_name,
    c.last_name,
    COALESCE(SUM(r.rental_fee + r.late_fee), 0) AS lifetime_value
FROM customers c
LEFT JOIN rentals r USING (customer_id)
GROUP BY c.customer_id
ORDER BY lifetime_value DESC;

Listing tables

The fastest way is the .tables dot command in the shell:

sqlite> .tables
customers   employees   genres      inventory   movies      rentals

-- With a pattern
sqlite> .tables 'e%'
employees

From SQL, query sqlite_schema (formerly sqlite_master):

SELECT name
FROM sqlite_schema
WHERE type = 'table'
  AND name NOT LIKE 'sqlite_%'
ORDER BY name;

The sqlite_% filter hides SQLite's internal bookkeeping tables (like sqlite_sequence).

Inspecting a table's structure

Unlike MySQL, SQLite has no DESCRIBE. You have three options:

1. .schema in the shell

sqlite> .schema movies
CREATE TABLE movies (
    movie_id        INTEGER PRIMARY KEY,
    title           TEXT NOT NULL,
    release_year    INTEGER NOT NULL,
    genre_id        INTEGER NOT NULL,
    mpaa_rating     TEXT,
    runtime_minutes INTEGER,
    director        TEXT,
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

2. PRAGMA table_info

Column-by-column details:

PRAGMA table_info(movies);
-- cid  name             type     notnull  dflt_value  pk
-- 0    movie_id         INTEGER  0                    1
-- 1    title            TEXT     1                    0
-- 2    release_year     INTEGER  1                    0
-- 3    genre_id         INTEGER  1                    0
-- 4    mpaa_rating      TEXT     0                    0
-- 5    runtime_minutes  INTEGER  0                    0
-- 6    director         TEXT     0                    0

3. Query sqlite_schema

SELECT sql
FROM sqlite_schema
WHERE type = 'table' AND name = 'movies';

Dump and restore

The .dump command outputs your entire database as a stream of SQL statements — schema and data together. It's the portable backup format.

Dump to a file

sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

Or from the command line without entering the shell:

$ sqlite3 daryvideorental.db .dump > backup.sql

Restore a dump

$ sqlite3 new.db < backup.sql

-- Or from within the shell:
sqlite> .read backup.sql

Dump just one table

sqlite> .dump movies

.backup for binary backups

For an exact binary copy (faster, smaller, no SQL parsing overhead on restore), use the binary backup:

sqlite> .backup backup.db
sqlite> .restore backup.db

Importing and exporting CSV

Import CSV into a table

-- Switch to CSV mode
sqlite> .mode csv

-- Create the target table first (best practice)
sqlite> CREATE TABLE cities (
   ...>     id      INTEGER,
   ...>     name    TEXT,
   ...>     country TEXT,
   ...>     pop     INTEGER
   ...> );

-- Import — --skip 1 ignores a header row
sqlite> .import --skip 1 cities.csv cities

If the target table doesn't exist, .import will create one using the first row of the CSV as column names — every column is TEXT, which is usually not what you want for numeric data.

Export a table to CSV

sqlite> .headers on
sqlite> .mode csv
sqlite> .output movies.csv
sqlite> SELECT * FROM movies;
sqlite> .output stdout

To export only a subset:

sqlite> .output horror_movies.csv
sqlite> SELECT m.* FROM movies m
   ...> JOIN genres g USING (genre_id)
   ...> WHERE g.name = 'Horror';
sqlite> .output stdout

Other output modes

Cheat sheet

A one-page reference of the most common patterns. Skim it when something feels on the tip of your tongue.

Reading data

-- Columns
SELECT c1, c2 FROM t;

-- All columns
SELECT * FROM t;

-- Filter
SELECT * FROM t WHERE cond;

-- Sort & limit
SELECT * FROM t ORDER BY c1 DESC LIMIT 10;

-- Pagination
SELECT * FROM t ORDER BY c1 LIMIT 20 OFFSET 40;

-- Distinct
SELECT DISTINCT c1 FROM t;

-- Counts & groups
SELECT c1, COUNT(*) FROM t GROUP BY c1 HAVING COUNT(*) > 5;

-- Join
SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
SELECT * FROM t1 LEFT JOIN t2 USING (id);

-- Set operations
SELECT c FROM a UNION     SELECT c FROM b;
SELECT c FROM a INTERSECT SELECT c FROM b;
SELECT c FROM a EXCEPT    SELECT c FROM b;

-- Common table expression
WITH x AS (SELECT c, SUM(n) AS s FROM t GROUP BY c)
SELECT * FROM x WHERE s > 100;

Writing data

-- Insert
INSERT INTO t (c1, c2) VALUES (v1, v2);

-- Insert many
INSERT INTO t (c1, c2) VALUES (v1, v2), (v3, v4);

-- Update
UPDATE t SET c1 = v1 WHERE cond;

-- Delete
DELETE FROM t WHERE cond;

-- Upsert
INSERT INTO t (k, v) VALUES ('a', 1)
ON CONFLICT(k) DO UPDATE SET v = v + excluded.v;

-- Returning affected rows
DELETE FROM t WHERE cond RETURNING *;

-- Transactions
BEGIN; ... COMMIT;   -- or ROLLBACK

Schema

CREATE TABLE [IF NOT EXISTS] t (
    id    INTEGER PRIMARY KEY,
    name  TEXT    NOT NULL UNIQUE,
    email TEXT    NOT NULL CHECK (email LIKE '%@%'),
    owner INTEGER REFERENCES users(id) ON DELETE CASCADE
);

ALTER TABLE t RENAME TO new_t;
ALTER TABLE t ADD COLUMN c3 TEXT;
ALTER TABLE t DROP COLUMN c3;
ALTER TABLE t RENAME COLUMN c1 TO c1_new;

DROP TABLE [IF EXISTS] t;

CREATE [UNIQUE] INDEX idx_name ON t (c1, c2);
CREATE INDEX idx_partial ON t (c1) WHERE c2 IS NOT NULL;
DROP INDEX idx_name;

CREATE VIEW v AS SELECT ...;
DROP VIEW v;

PRAGMA foreign_keys = ON;

Handy built-in functions

Full-text search

CREATE VIRTUAL TABLE docs USING fts5(title, body);
SELECT * FROM docs WHERE docs MATCH 'search query'
ORDER BY rank;

Shell essentials

.help            -- list dot commands
.open FILE       -- open/create a database file
.tables          -- list tables
.schema TBL      -- show CREATE statement
.indexes TBL     -- list indexes
.mode column     -- tabular output
.headers on      -- show column names
.read FILE.sql   -- execute SQL from a file
.dump > b.sql    -- dump schema + data
.backup f.db     -- binary backup
.quit            -- exit

Where to go next

You've now seen the full surface of day-to-day SQLite: reading data in many shapes, combining tables, modifying data safely, and designing schemas with constraints, indexes, views, and triggers. Some directions for further exploration:

• Window functions — ROW_NUMBER(), RANK(), LAG(), running totals. They're a more elegant alternative to correlated subqueries.
• JSON support — SQLite has a rich set of json_* functions for querying JSON stored in TEXT columns.
• PRAGMAs — control journaling (journal_mode), performance (synchronous, cache_size), and integrity (integrity_check).
• Language bindings — sqlite3 is built into Python; Node (better-sqlite3), Go (mattn/go-sqlite3), Rust (rusqlite), and Java (sqlite-jdbc) all have excellent support.
• The official docs — sqlite.org/docs.html. Dense, accurate, and authoritative.

Every example in this tutorial is valid SQL that runs against the daryvideorental.db sample database.