SQLite Reference

# Open or create a database file
sqlite3 project.db

# Use an ephemeral in-memory database
sqlite3 :memory: "SELECT 'volatile' AS type;"

.tables             -- List all tables
.schema users       -- Show CREATE statement for 'users'
.mode csv           -- Set output mode to CSV
.import data.csv t1 -- Import CSV into table 't1'
.quit               -- Exit the CLI

-- Single-line comment
/* Multi-line
   comment block */
SELECT 1; -- Trailing comment

SELECT 
    42 AS i_int,           -- INTEGER (signed 1, 2, 3, 4, 6, or 8 bytes)
    3.14159 AS f_real;     -- REAL (8-byte IEEE float)

SELECT 
    'UTF-8 text' AS t_text, -- TEXT
    x'53514C' AS b_blob;    -- BLOB (raw bytes)

SELECT 
    date('now') AS d,               -- YYYY-MM-DD
    datetime('now', '+1 hour') AS t,-- YYYY-MM-DD HH:MM:SS
    strftime('%s', 'now') AS ts;    -- Unix Epoch

SELECT 
    1 AS is_true,          -- Boolean TRUE
    0 AS is_false,         -- Boolean FALSE
    'active' AS status;    -- No ENUM type; use CHECK constraints

SELECT 
    id, 
    name AS username,
    score * 10 AS points
FROM players;

SELECT * FROM files
WHERE size > 1024
  AND name LIKE '%.log'     -- Standard case-insensitive
  AND name GLOB 'test_[0-9]*'; -- Unix shell glob (case-sensitive)

SELECT * FROM tasks
ORDER BY priority DESC, deadline ASC;

SELECT id FROM logs
LIMIT 5 OFFSET 10; -- Skip 10, take 5

SELECT u.name, s.val
FROM users u
LEFT JOIN settings s ON u.id = s.user_id; -- Standard outer join

-- Self join for parent-child nodes
SELECT p.name AS parent, c.name AS child
FROM nodes p
JOIN nodes c ON p.id = c.parent_id;

SELECT id FROM local_cache
EXCEPT
SELECT id FROM remote_server; -- In local but not remote

SELECT a.name, b.avg_val
FROM authors a
JOIN (SELECT author_id, AVG(rating) AS avg_val FROM books GROUP BY author_id) b
  ON a.id = b.author_id;

SELECT category, COUNT(*)
FROM items
GROUP BY category;

SELECT tag, SUM(bits)
FROM data
GROUP BY tag
HAVING SUM(bits) > 1024;

SELECT 
    COUNT(DISTINCT user_id) AS distinct_users,
    group_concat(name, '|') AS pipe_list -- SQLite specific
FROM sessions;

SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products);

WITH top_users AS (
    SELECT id FROM users WHERE karma > 1000
)
SELECT * FROM posts WHERE user_id IN top_users;

WITH RECURSIVE fiber(n, prev) AS (
    SELECT 1, 0
    UNION ALL
    SELECT n + prev, n FROM fiber WHERE n < 100
)
SELECT n FROM fiber;

SELECT 
    dept, 
    salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank
FROM staff;

SELECT 
    val, 
    ROW_NUMBER() OVER (ORDER BY val) AS row_num
FROM stats;

SELECT 
    val,
    LAG(val) OVER (ORDER BY id) AS previous
FROM sensor_data;

INSERT INTO settings (key, val) VALUES ('port', 8080);

UPDATE users SET last_seen = strftime('%s','now') WHERE id = 1;

DELETE FROM sessions WHERE expiry < strftime('%s','now');

INSERT INTO kv(key, val) VALUES ('hits', 1)
ON CONFLICT(key) DO UPDATE SET val = val + 1;

CREATE TABLE hosts (
    id INTEGER PRIMARY KEY AUTOINCREMENT, -- Auto-incrementing PK
    hostname TEXT UNIQUE NOT NULL,
    config JSON                           -- JSON check-constraint validation
);

ALTER TABLE hosts ADD COLUMN status INTEGER DEFAULT 0;

CREATE TABLE parts (
    id INTEGER PRIMARY KEY,
    weight REAL CHECK(weight > 0),
    pid INTEGER REFERENCES hosts(id) ON DELETE SET NULL
);

CREATE INDEX idx_host_status ON hosts(status);
EXPLAIN QUERY PLAN SELECT * FROM hosts WHERE status = 1;

CREATE VIEW active_hosts AS SELECT * FROM hosts WHERE status = 1;

BEGIN TRANSACTION;
UPDATE wallet SET bal = bal - 10 WHERE id = 1;
UPDATE wallet SET bal = bal + 10 WHERE id = 2;
COMMIT;

CREATE TRIGGER log_update AFTER UPDATE ON hosts
BEGIN
    INSERT INTO audit(host_id, ts) VALUES (OLD.id, strftime('%s','now'));
END;

EXPLAIN SELECT * FROM hosts WHERE id = 5;

PRAGMA journal_mode = WAL;      -- Write-Ahead Logging for concurrency
PRAGMA foreign_keys = ON;       -- Enable FK enforcement
PRAGMA synchronous = NORMAL;    -- Faster but less safe on power loss