SQLite Cheat Sheet

A complete reference of SQLite syntax with 55 interactive examples. Copy code snippets or click “Try It” to run them directly in your browser.

Creating & Modifying Tables

CREATE TABLE

Create a new table with columns and constraints.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Tip: Use INTEGER PRIMARY KEY for auto-incrementing IDs in SQLite.

CREATE TABLE IF NOT EXISTS

Create a table only if it does not already exist.

CREATE TABLE IF NOT EXISTS logs (
  id INTEGER PRIMARY KEY,
  message TEXT,
  level TEXT DEFAULT 'info'
);

ALTER TABLE ADD

Add a new column to an existing table.

ALTER TABLE users ADD COLUMN phone TEXT;

Tip: SQLite only supports adding columns, not modifying or dropping them directly.

ALTER TABLE RENAME

Rename a table or column.

ALTER TABLE users RENAME TO customers;

DROP TABLE

Delete a table and all its data.

DROP TABLE IF EXISTS temp_data;

Tip: Use IF EXISTS to avoid errors when the table does not exist.

CREATE INDEX

Create an index to speed up queries on a column.

CREATE INDEX idx_users_email ON users(email);

Tip: Indexes speed up SELECT but slow down INSERT/UPDATE.

CREATE VIEW

Create a virtual table based on a SELECT query.

CREATE VIEW active_users AS
SELECT id, name, email FROM users
WHERE created_at > date('now', '-30 days');

Adding & Changing Data

INSERT

Add new rows to a table.

INSERT INTO users (name, email)
VALUES ('Dave Wilson', 'dave@example.com');

SELECT * FROM users WHERE name = 'Dave Wilson';

INSERT Multiple Rows

Insert multiple rows in a single statement.

INSERT INTO products (name, category, price, stock) VALUES
  ('Headphones', 'Electronics', 149.99, 100),
  ('Webcam', 'Electronics', 89.99, 60);

SELECT * FROM products WHERE name IN ('Headphones', 'Webcam');

UPDATE

Modify existing rows in a table.

UPDATE orders SET status = 'shipped'
WHERE id = 3;

SELECT * FROM orders WHERE id = 3;

Tip: Always use WHERE to avoid updating all rows.

DELETE

Remove rows from a table.

DELETE FROM orders WHERE status = 'pending';

SELECT * FROM orders;

Tip: Always use WHERE unless you want to delete all rows.

UPSERT (INSERT OR REPLACE)

Insert a row or replace it if a conflict occurs.

INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Alice Updated', 'alice.new@example.com');

SELECT * FROM users WHERE id = 1;

Tip: Requires a PRIMARY KEY or UNIQUE constraint for conflict detection.

INSERT ON CONFLICT

Handle conflicts with more control than REPLACE.

INSERT INTO users (id, name, email) VALUES (2, 'Bob Updated', 'bob.new@example.com')
ON CONFLICT(id) DO UPDATE SET name = excluded.name, email = excluded.email;

SELECT * FROM users WHERE id = 2;

Tip: Use excluded.column to reference the conflicting row values.

Querying Data

SELECT

Retrieve data from one or more tables.

SELECT name, email FROM users;

SELECT *

Select all columns from a table.

SELECT * FROM products;

Tip: Avoid SELECT * in production; specify needed columns.

WHERE

Filter rows based on conditions.

SELECT * FROM orders WHERE price > 50 AND status = 'completed';

ORDER BY

Sort results by one or more columns.

SELECT * FROM products ORDER BY price DESC;

LIMIT & OFFSET

Limit the number of results and skip rows.

SELECT * FROM products ORDER BY price DESC LIMIT 3 OFFSET 1;

Tip: OFFSET is zero-based. LIMIT 10 OFFSET 20 returns rows 21-30.

GROUP BY

Group rows by column values for aggregation.

SELECT status, COUNT(*) as count, SUM(price) as total
FROM orders GROUP BY status;

HAVING

Filter groups after GROUP BY (like WHERE for groups).

SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 1;

DISTINCT

Return only unique values.

SELECT DISTINCT category FROM products;

INNER JOIN

Return rows where there is a match in both tables.

SELECT users.name, orders.product, orders.price
FROM orders
INNER JOIN users ON orders.user_id = users.id;

LEFT JOIN

Return all rows from left table, with matches from right.

SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Tip: Unmatched right table columns will be NULL.

Subquery

Use a query inside another query.

SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE price > 100);

CTE (WITH clause)

Define a temporary named result set for the query.

WITH high_value_orders AS (
  SELECT user_id, SUM(price) as total
  FROM orders
  GROUP BY user_id
  HAVING total > 100
)
SELECT users.name, high_value_orders.total
FROM high_value_orders
JOIN users ON users.id = high_value_orders.user_id;

Tip: CTEs make complex queries more readable.

UNION

Combine results from multiple SELECT statements.

SELECT name, 'user' as type FROM users
UNION ALL
SELECT name, 'product' as type FROM products;

Tip: UNION removes duplicates; UNION ALL keeps them.

Built-in Functions

COUNT

Count the number of rows.

SELECT COUNT(*) as total_orders FROM orders;

SUM & AVG

Calculate the sum or average of a column.

SELECT
  SUM(price) as total_revenue,
  AVG(price) as avg_order_value
FROM orders;

MIN & MAX

Find the minimum or maximum value.

SELECT MIN(price) as cheapest, MAX(price) as most_expensive
FROM products;

COALESCE

Return the first non-NULL value.

SELECT name, COALESCE(email, 'no email') as email
FROM users;

Tip: Useful for providing default values for NULL columns.

NULLIF

Return NULL if two values are equal.

SELECT name, NULLIF(stock, 0) as stock_or_null
FROM products;

Tip: Useful for avoiding division by zero: x / NULLIF(y, 0)

CASE

Conditional logic in SQL (like if/else).

SELECT product, price,
  CASE
    WHEN price > 500 THEN 'expensive'
    WHEN price > 100 THEN 'moderate'
    ELSE 'affordable'
  END as price_tier
FROM orders;

String Functions

Manipulate text values.

SELECT
  UPPER(name) as uppercase,
  LENGTH(name) as name_length,
  SUBSTR(email, 1, INSTR(email, '@') - 1) as username
FROM users;

String Concatenation

Combine strings together.

SELECT name || ' <' || email || '>' as formatted
FROM users;

Tip: SQLite uses || for concatenation, not CONCAT().

Date Functions

Work with dates and times.

SELECT
  date('now') as today,
  date('now', '+7 days') as next_week,
  strftime('%Y', 'now') as current_year;

JSON Functions

Extract and manipulate JSON data.

SELECT json_extract('{"name": "Alice", "age": 30}', '$.name') as name;

Tip: Use ->> for text output, -> for JSON output.

IFNULL

Return a default value if expression is NULL.

SELECT name, IFNULL(email, 'N/A') as email
FROM users;

Tip: IFNULL is SQLite-specific; COALESCE is more portable.

Data Rules & Constraints

PRIMARY KEY

Uniquely identify each row in a table.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER
);

Tip: INTEGER PRIMARY KEY is aliased to ROWID in SQLite.

FOREIGN KEY

Link to a row in another table.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Tip: Enable with PRAGMA foreign_keys = ON;

UNIQUE

Ensure all values in a column are different.

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT UNIQUE,
  username TEXT UNIQUE
);

NOT NULL

Prevent NULL values in a column.

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

DEFAULT

Set a default value when none is provided.

CREATE TABLE logs (
  id INTEGER PRIMARY KEY,
  message TEXT,
  level TEXT DEFAULT 'info',
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CHECK

Validate data against a condition.

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

Transactions

BEGIN TRANSACTION

Start a new transaction.

BEGIN TRANSACTION;

INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');

-- Changes are not visible to other connections yet

Tip: Use IMMEDIATE or EXCLUSIVE to control locking behavior.

COMMIT

Save all changes made in the transaction.

BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('Test', 'test@example.com');
COMMIT;

SELECT * FROM users WHERE name = 'Test';

ROLLBACK

Undo all changes made in the transaction.

BEGIN TRANSACTION;
DELETE FROM users;
ROLLBACK;

-- Users table is unchanged
SELECT * FROM users;

SAVEPOINT

Create a point to rollback to within a transaction.

BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('A', 'a@test.com');
SAVEPOINT my_savepoint;
INSERT INTO users (name, email) VALUES ('B', 'b@test.com');
ROLLBACK TO my_savepoint;
COMMIT;

-- Only user 'A' was inserted
SELECT * FROM users WHERE email LIKE '%test.com';

Utilities & Advanced

PRAGMA table_info

Get information about table columns.

PRAGMA table_info(users);

PRAGMA foreign_keys

Enable or check foreign key enforcement.

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

Tip: Foreign keys are OFF by default in SQLite.

VACUUM

Rebuild the database to reclaim space.

-- After many DELETEs, reclaim disk space:
VACUUM;

Tip: VACUUM can take time on large databases.

EXPLAIN QUERY PLAN

Show how SQLite will execute a query.

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 1;

Tip: Use to identify missing indexes and optimize queries.

ATTACH DATABASE

Connect another database file.

-- Attach another database
-- ATTACH DATABASE 'other.db' AS other;
-- SELECT * FROM other.users;

-- In memory example:
ATTACH DATABASE ':memory:' AS temp_db;

Tip: Use to query across multiple database files.

sqlite_master

System table containing schema information.

SELECT name, sql FROM sqlite_master
WHERE type = 'table' AND name NOT LIKE 'sqlite_%';

Tip: Also available as sqlite_schema in newer SQLite versions.

LIKE Pattern Matching

Match strings with wildcards.

SELECT * FROM users WHERE email LIKE '%@example.com';

GLOB Pattern Matching

Match strings with Unix-style wildcards.

SELECT * FROM users WHERE name GLOB '[A-C]*';

Tip: GLOB is case-sensitive unlike LIKE.