SQL essentials
Copyable SQLite 3.45.1 queries for filtering, joins, grouping, windows, writes, schema changes, transactions, and plan inspection.
SQLite 3.45.1 1 page when printed
Read and shape rows
SELECT order_id, total FROM orders; return only the named columns from every row SELECT DISTINCT customer_id FROM orders; remove duplicate projected rows SELECT customer_id, total AS order_total FROM orders; name a result column explicitly SELECT * FROM orders ORDER BY total DESC NULLS LAST, order_id; sort descending, place nulls last, and break ties stably SELECT * FROM orders ORDER BY created_at DESC, order_id DESC LIMIT 20 OFFSET 40; skip the first 40 ordered rows, then return at most 20 SELECT CAST(total AS REAL) FROM orders; convert each value to SQLite’s real storage class Filter and handle nulls
SELECT * FROM orders WHERE status = 'paid' AND total >= ?1; filter with a bound minimum instead of interpolated SQL SELECT * FROM orders WHERE shipped_at IS NULL; match missing values with IS NULL SELECT * FROM orders WHERE customer_id IN (1, 3, 5); match any value in an explicit list SELECT * FROM orders WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01'; use a half-open range for September timestamps SELECT COALESCE(discount, 0) FROM orders; replace null discounts in the result SELECT CASE WHEN total >= 100 THEN 'large' ELSE 'small' END FROM orders; map each row through ordered conditions Join and test existence
SELECT o.order_id, c.name FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id; keep orders with a matching customer SELECT c.name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id; keep every customer even without an order SELECT * FROM colors CROSS JOIN sizes; produce every color-size combination SELECT * FROM orders JOIN customers USING (customer_id); join on an equally named column and project it once SELECT * FROM customers AS c WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id); keep customers that have at least one order SELECT * FROM customers AS c WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.customer_id); keep customers with no matching order, even when the inner key is nullable Group and summarize
SELECT COUNT(*) FROM orders; count rows, including rows whose columns contain nulls SELECT COUNT(shipped_at) FROM orders; count only rows with a non-null shipping time SELECT COUNT(DISTINCT customer_id) FROM orders; count distinct non-null customer identifiers SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id; return one total per customer SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id HAVING SUM(total) >= 1000; filter groups after aggregation SELECT COUNT(*) FILTER (WHERE status = 'paid') FROM orders; count matching rows without removing other aggregate inputs Combine and nest queries
SELECT email FROM leads UNION SELECT email FROM customers; combine results and remove duplicate rows SELECT email FROM leads UNION ALL SELECT email FROM customers; combine results and preserve duplicate rows SELECT email FROM leads INTERSECT SELECT email FROM customers; keep rows present in both results SELECT email FROM leads EXCEPT SELECT email FROM customers; keep left-side rows absent from the right result SELECT * FROM orders WHERE total > (SELECT AVG(total) FROM orders); compare each row with one scalar aggregate SELECT * FROM (SELECT * FROM orders WHERE status = 'paid') AS paid_orders; query a derived table named in FROM Stage and recurse
WITH paid_orders AS (SELECT * FROM orders WHERE status = 'paid') SELECT * FROM paid_orders; name one query result for the following statement WITH paid AS (SELECT * FROM orders WHERE status = 'paid'), totals AS (SELECT customer_id, SUM(total) AS spent FROM paid GROUP BY customer_id) SELECT * FROM totals; feed one CTE into the next WITH order_totals AS (SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id) SELECT c.name, t.spent FROM order_totals AS t JOIN customers AS c USING (customer_id); join a summarized CTE to detail data WITH RECURSIVE sequence(n) AS (VALUES (1) UNION ALL SELECT n + 1 FROM sequence WHERE n < 5) SELECT n FROM sequence; generate integers one through five with a stopping condition WITH RECURSIVE tree(id, depth) AS (SELECT category_id, 0 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.category_id, t.depth + 1 FROM categories AS c JOIN tree AS t ON c.parent_id = t.id) SELECT * FROM tree; walk a category hierarchy from its roots VALUES (1, 'draft'), (2, 'paid'); construct a two-row, two-column result directly Calculate over windows
SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS position FROM orders; assign a stable position inside each customer partition SELECT order_id, RANK() OVER (ORDER BY total DESC) AS place FROM orders; give equal totals the same rank and leave gaps SELECT order_id, DENSE_RANK() OVER (ORDER BY total DESC) AS tier FROM orders; give equal totals the same rank without gaps SELECT order_id, LAG(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS previous_total FROM orders; read the preceding ordered value in each partition SELECT order_id, LEAD(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id) AS next_total FROM orders; read the following ordered value in each partition SELECT order_id, SUM(total) OVER (PARTITION BY customer_id ORDER BY created_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders; calculate a running total with an explicit row frame Insert, update, and delete
INSERT INTO customers (customer_id, name, email) VALUES (?1, ?2, ?3); insert one row with bound values and an explicit column list INSERT INTO statuses (status_id, name) VALUES (1, 'draft'), (2, 'paid'); insert multiple rows in one statement INSERT INTO archived_orders SELECT * FROM orders WHERE created_at < '2026-01-01'; insert the rows returned by a query INSERT INTO customers (customer_id, name, email) VALUES (?1, ?2, ?3) ON CONFLICT(customer_id) DO UPDATE SET name = excluded.name, email = excluded.email; insert or update on a specified uniqueness conflict UPDATE orders SET status = 'paid' WHERE order_id = ?1 RETURNING order_id, status; update matching rows and return their new values DELETE FROM orders WHERE order_id = ?1 RETURNING order_id; delete matching rows and return their identifiers Define schema and indexes
CREATE TABLE users (user_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE); create a table with identity, presence, and uniqueness constraints CREATE TABLE order_items (order_id INTEGER REFERENCES orders(order_id), sku TEXT, quantity INTEGER CHECK (quantity > 0), PRIMARY KEY (order_id, sku)); declare a composite key, foreign key, and positive quantity ALTER TABLE users ADD COLUMN active INTEGER NOT NULL DEFAULT 1; add a non-null column with a default for existing rows CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at); index a common equality-and-ordering access path CREATE UNIQUE INDEX customers_email_idx ON customers (email) WHERE email IS NOT NULL; enforce uniqueness only for non-null email values DROP INDEX IF EXISTS orders_customer_created_idx; remove the index only when it exists Control transactions
BEGIN IMMEDIATE; start a SQLite write transaction immediately COMMIT; commit and end the current transaction ROLLBACK; undo the current transaction SAVEPOINT before_import; mark a nested rollback point ROLLBACK TO before_import; undo work after the savepoint while keeping it active RELEASE before_import; remove the savepoint without ending an outer transaction Inspect and maintain
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = ?1; show the high-level query plan without running the query ANALYZE; refresh planner statistics for all attached schemas PRAGMA optimize; run recommended optimizer maintenance for the current workload PRAGMA foreign_keys = ON; enable foreign-key enforcement for the current connection PRAGMA table_info('orders'); list the table’s normal columns and core attributes PRAGMA integrity_check; scan the database for structural and constraint errors