SQL Reference

Data types

• INTEGER
• BOOLEAN
• VARCHAR
• BLOB
• TIMESTAMP

Creating tables

CREATE TABLE table1 (id INTEGER, PRIMARY KEY id);
CREATE TABLE table1 (id INTEGER, ts INTEGER, title VARCHAR, active BOOLEAN, payload BLOB, PRIMARY KEY id);
CREATE TABLE IF NOT EXISTS table1 (id INTEGER, PRIMARY KEY id);

Indexes

CREATE INDEX ON table1(name);

Inserting or updating data

INSERT INTO table1 (id, title) VALUES (1, 'some title')

UPSERT will update the value if a row with the same primary key already exists:

UPSERT INTO table1 (id, title) VALUES (1, 'some title')
UPSERT INTO table1 (id, ts, title, active, payload) VALUES (2, NOW(), 'title', true, x'a blob')

Querying

SELECT id, title FROM db1.table1 AS t1
SELECT t1.id, title FROM (db1.table1 AS t1)
SELECT id, time, name FROM table1 WHERE country = 'US' AND time <= NOW() AND name = @pname
SELECT id, title, year FROM table1 ORDER BY title ASC, year DESC
SELECT id, name, table2.status FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE name = 'John' ORDER BY name DESC
SELECT country, SUM(amount) FROM table1 GROUP BY country
SELECT id FROM table1 WHERE (id > 0 AND NOT table1.id >= 10) OR table1.title LIKE 'J.*'

Parameters

SELECT t.id as d FROM (people AS t) WHERE id <= 3 AND active = @active

Aggregations

• COUNT
• SUM
• MAX
• MIN
• AVG

SELECT COUNT() AS c, SUM(age), MIN(age), MAX(age), AVG(age) FROM table1 AS t1
SELECT active, COUNT() as c, MIN(age), MAX(age) FROM table1 GROUP BY active HAVING COUNT() > 0 ORDER BY active DESC

Transactions

BEGIN TRANSACTION; UPSERT INTO table1 (id, label) VALUES (100, 'label1'); UPSERT INTO table2 (id) VALUES (10) COMMIT;

Time travel

USE SNAPSHOT BEFORE TX 1000