SQL Reference¶

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at INT,
    CHECK (name IS NULL OR LENGTH(name) > 0)
);

CREATE TEMP TABLE temp_results (id INT, value TEXT);
CREATE TEMP VIEW temp_summary AS SELECT category, COUNT(*) AS cnt FROM products GROUP BY category;

-- B-Tree index (default)
CREATE INDEX index_name ON table_name(column_name);

-- Trigram index for text search
CREATE INDEX index_name ON table_name USING trigram(column_name);

-- Unique index
CREATE UNIQUE INDEX index_name ON table_name(column_name);

-- Partial/filtered index (BTREE only)
CREATE INDEX index_name ON table_name(column_name) WHERE column_name IS NOT NULL;

-- Expression index (BTREE only; single expression)
CREATE INDEX index_name ON table_name((LOWER(column_name)));

DROP TABLE table_name;
DROP INDEX index_name;

ALTER TABLE table_name ADD COLUMN column_name datatype [constraints];

-- Add a new column with no default
ALTER TABLE users ADD COLUMN age INT;

-- Add a column with NOT NULL constraint
-- (will fail if table has existing rows)
ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE users DROP COLUMN age;

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

ALTER TABLE users RENAME COLUMN name TO full_name;

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;

ALTER TABLE users ALTER COLUMN age TYPE TEXT;

CREATE VIEW view_name AS SELECT ...;
CREATE VIEW IF NOT EXISTS view_name AS SELECT ...;
CREATE OR REPLACE VIEW view_name AS SELECT ...;

DROP VIEW [IF EXISTS] view_name;

ALTER VIEW view_name RENAME TO new_name;

CREATE VIEW active_users AS
  SELECT id, email FROM users WHERE status = 'active';

ALTER VIEW active_users RENAME TO active_users_v1;
DROP VIEW active_users_v1;

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE OR DELETE ON table_name
FOR EACH ROW
EXECUTE FUNCTION decentdb_exec_sql('single_dml_sql');

CREATE TRIGGER trigger_name
INSTEAD OF INSERT OR UPDATE OR DELETE ON view_name
FOR EACH ROW
EXECUTE FUNCTION decentdb_exec_sql('single_dml_sql');

DROP TRIGGER [IF EXISTS] trigger_name ON object_name;

CREATE TABLE audit (tag TEXT);
CREATE TRIGGER users_ins_audit
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION decentdb_exec_sql('INSERT INTO audit(tag) VALUES (''I'')');

INSERT INTO table_name VALUES (val1, val2, ...);
INSERT INTO table_name (col1, col2) VALUES (val1, val2);
INSERT INTO table_name (...) VALUES (...) ON CONFLICT DO NOTHING;
INSERT INTO table_name (...) VALUES (...) ON CONFLICT (col1, col2) DO NOTHING;
INSERT INTO table_name (...) VALUES (...) ON CONFLICT ON CONSTRAINT constraint_name DO NOTHING;
INSERT INTO table_name (...) VALUES (...) ON CONFLICT (col1, col2) DO UPDATE SET col3 = EXCLUDED.col3;
INSERT INTO table_name (...) VALUES (...) ON CONFLICT ON CONSTRAINT constraint_name DO UPDATE SET col3 = EXCLUDED.col3 WHERE table_name.col4 > 0;
INSERT INTO table_name (...) VALUES (...) RETURNING *;
INSERT INTO table_name (...) VALUES (...) RETURNING col1, col2;

CREATE TABLE users (id INT PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Alice');  -- id auto-assigned as 1
INSERT INTO users (name) VALUES ('Bob');    -- id auto-assigned as 2
INSERT INTO users VALUES (10, 'Carol');     -- explicit id = 10
INSERT INTO users (name) VALUES ('Dave');   -- id auto-assigned as 11

SELECT * FROM table_name;
SELECT col1, col2 FROM table_name WHERE condition;
SELECT DISTINCT col1 FROM table_name;
SELECT DISTINCT ON (category) category, name FROM products ORDER BY category, price;
SELECT * FROM table_name ORDER BY col1 ASC, col2 DESC;
SELECT * FROM table_name LIMIT 10 OFFSET 20;
SELECT * FROM table_name OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;  -- SQL:2008 syntax
SELECT id FROM a UNION ALL SELECT id FROM b;

UPDATE table_name SET col1 = val1 WHERE condition;

DELETE FROM table_name WHERE condition;

ANALYZE;
ANALYZE table_name;

SELECT * FROM users WHERE age > 18 AND name LIKE '%son%';
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE age BETWEEN 18 AND 30;
SELECT * FROM users WHERE name LIKE 'a\_%' ESCAPE '\';

SELECT COALESCE(nickname, name) FROM users;
SELECT NULLIF(status, 'active') FROM users;
SELECT LENGTH(name), LOWER(name), UPPER(name), TRIM(name) FROM users;
SELECT REPLACE(name, 'old', 'new') FROM users;
SELECT SUBSTRING(name, 1, 3) FROM users;
SELECT LEFT(name, 3), RIGHT(name, 3) FROM users;
SELECT LPAD(code, 5, '0'), RPAD(name, 20) FROM items;
SELECT REPEAT('*', 5);  -- Returns '*****'
SELECT REVERSE('hello');  -- Returns 'olleh'
SELECT ABS(balance), ROUND(price, 2), CEIL(rating), FLOOR(rating) FROM products;
SELECT SQRT(area), POWER(base, 2), MOD(total, 10) FROM data;
SELECT LN(x), LOG(x), EXP(x), SIGN(x) FROM data;
SELECT RANDOM();  -- Random float in [0, 1)
SELECT 17 % 5;  -- Modulo operator, returns 2
SELECT INSTR('hello world', 'world');  -- Returns 7
SELECT CHR(65);  -- Returns 'A'
SELECT HEX(255);  -- Returns 'FF'
SELECT GEN_RANDOM_UUID();
SELECT UUID_TO_STRING(id) FROM users;
SELECT CAST('550e8400-e29b-41d4-a716-446655440000' AS UUID);
SELECT TRIM(name) || '_suffix' FROM users;
SELECT CAST(id AS TEXT) FROM users;
SELECT CAST('12.34' AS DECIMAL(10,2));
SELECT CASE WHEN active THEN 'on' ELSE 'off' END FROM users;
SELECT JSON_ARRAY_LENGTH('["a","b","c"]');  -- Returns 3
SELECT JSON_EXTRACT('{"name":"Alice"}', '$.name');  -- Returns 'Alice'
SELECT JSON_TYPE('{"a":1}');  -- Returns 'object'
SELECT JSON_VALID('not json');  -- Returns 0
SELECT JSON_OBJECT('name', 'Alice', 'age', 30);
SELECT JSON_ARRAY(1, 'two', 3.0);
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;
SELECT EXTRACT(YEAR FROM '2026-02-24');  -- Returns 2026
SELECT STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP);
SELECT PRINTF('Hello %s, you are %d', name, age) FROM users;

WITH recent AS (
  SELECT id, name FROM users WHERE id > 10
)
SELECT name FROM recent ORDER BY id;

WITH a AS (SELECT id FROM users), b(x) AS (SELECT id FROM a WHERE id > 1)
SELECT x FROM b ORDER BY x;

-- Recursive CTE: generate numbers 1..5
WITH RECURSIVE cnt(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM cnt WHERE x < 5
)
SELECT x FROM cnt;

-- Recursive CTE: tree traversal
WITH RECURSIVE tree(id, name, lvl) AS (
  SELECT id, name, 0 FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.lvl + 1
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

-- Inner join
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

-- Left join
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;

-- Right join (rewritten internally as LEFT JOIN with swapped operands)
SELECT * FROM orders RIGHT JOIN users ON users.id = orders.user_id;

-- Full outer join
SELECT * FROM users FULL OUTER JOIN orders ON users.id = orders.user_id;

-- Cross join (Cartesian product)
SELECT * FROM colors CROSS JOIN sizes;

-- Natural join (matches on shared column names)
SELECT * FROM employees NATURAL JOIN departments;

SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;  -- Count non-NULL
SELECT SUM(amount) FROM orders;
SELECT AVG(price) FROM products;
SELECT MIN(created_at), MAX(created_at) FROM users;
SELECT TOTAL(amount) FROM orders;  -- Like SUM but returns 0.0 for empty sets (never NULL)
SELECT category, SUM(amount) FROM orders GROUP BY category;
SELECT category, COUNT(*) FROM orders GROUP BY category HAVING COUNT(*) > 5;
SELECT GROUP_CONCAT(name, ', ') FROM users;  -- Concatenate with separator
SELECT STRING_AGG(name, ', ') FROM users;    -- Alias for GROUP_CONCAT

-- DISTINCT aggregates: de-duplicate values before aggregating
SELECT COUNT(DISTINCT category) FROM products;
SELECT SUM(DISTINCT amount) FROM orders;
SELECT AVG(DISTINCT score) FROM results;

-- ROW_NUMBER: sequential numbering
SELECT id, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM employees ORDER BY dept, id;

-- RANK: ties get same rank, with gaps
SELECT name, RANK() OVER (ORDER BY score DESC) AS rank
FROM scores ORDER BY score DESC;

-- DENSE_RANK: ties get same rank, no gaps
SELECT name, DENSE_RANK() OVER (ORDER BY score DESC) AS drank
FROM scores ORDER BY score DESC;

-- LAG: previous row's value (default offset = 1, default value = NULL)
SELECT name, score, LAG(score, 1, 0) OVER (ORDER BY id) AS prev_score
FROM scores ORDER BY id;

-- LEAD: next row's value
SELECT name, LEAD(score) OVER (PARTITION BY dept ORDER BY id) AS next_score
FROM scores ORDER BY dept, id;

-- FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT name,
  FIRST_VALUE(score) OVER (PARTITION BY dept ORDER BY id) AS first,
  LAST_VALUE(score) OVER (PARTITION BY dept ORDER BY id) AS last,
  NTH_VALUE(score, 2) OVER (PARTITION BY dept ORDER BY id) AS second
FROM scores ORDER BY dept, id;

BEGIN;
BEGIN IMMEDIATE;   -- Synonym for BEGIN (single-writer engine)
BEGIN EXCLUSIVE;   -- Synonym for BEGIN (single-writer engine)
COMMIT;
ROLLBACK;

-- Savepoints (within a transaction)
SAVEPOINT name;
RELEASE SAVEPOINT name;
ROLLBACK TO SAVEPOINT name;

EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- Iterate a JSON object
SELECT key, value, type FROM json_each('{"name":"Alice","age":30}');
-- Returns: name|Alice|string, age|30|number

-- Iterate a JSON array
SELECT key, value, type FROM json_each('[10, 20, 30]');
-- Returns: 0|10|number, 1|20|number, 2|30|number

SELECT key, value, type, path FROM json_tree('{"a":{"b":1},"c":[2,3]}');

CREATE TABLE products (
    id INT PRIMARY KEY,
    price REAL,
    qty INT,
    total REAL GENERATED ALWAYS AS (price * qty) STORED
);

INSERT INTO products (id, price, qty) VALUES (1, 9.99, 3);
SELECT total FROM products WHERE id = 1;  -- Returns 29.97

CREATE TABLE users (
    id INT PRIMARY KEY,
    ...
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    ...
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    email TEXT UNIQUE,
    ...
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    ...
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    CHECK (name IS NULL OR LENGTH(name) > 0)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    status TEXT DEFAULT 'active',
    ref UUID DEFAULT GEN_RANDOM_UUID()
);

SELECT * FROM users WHERE id = $1 AND name = $2;

decentdb exec --db=my.ddb --sql="SELECT * FROM users WHERE id = \$1" --params=int:42