SQLite

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.

About This SQLite Cheat Sheet

This SQLite cheat sheet covers all the essential SQL commands you need for working with SQLite databases. Whether you're learning SQLite for the first time or need a quick syntax reference, this guide has you covered.

Each SQLite command includes a working example that you can copy or run directly in your browser. The interactive SQL editor lets you experiment with queries against sample data without installing anything.

Looking for more? Check out our SQLite tutorials and guides for in-depth explanations, or try DB Pro for a visual way to explore and edit your SQLite databases.