-- Create table with constraints
CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    score REAL DEFAULT 0.0,
    data  BLOB
);

-- Drop table
DROP TABLE users;

-- Table with CHECK constraint
CREATE TABLE products (
    price INT CHECK (price > 0)
);

-- Insert with explicit values
INSERT INTO users VALUES (1, 'Alice', 99.5, X'CAFE');

-- Insert with column list (reorder/defaults/NULLs)
INSERT INTO users (name, score, id) VALUES ('Bob', 88.0, 2);

-- Update with WHERE
UPDATE users SET score = 100.0 WHERE id = 1;

-- Delete with WHERE
DELETE FROM users WHERE id = 1;

-- Basic queries
SELECT * FROM users;
SELECT name, score FROM users;
SELECT DISTINCT name FROM users;

-- Filtering
SELECT * FROM users WHERE score > 50 AND name != 'Bob';
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE id IN (1, 3, 5);

-- Sorting & pagination
SELECT * FROM users ORDER BY score DESC;
SELECT * FROM users ORDER BY name ASC LIMIT 5 OFFSET 10;

-- Aggregates & grouping
SELECT COUNT(*), AVG(score), SUM(score) FROM users;
SELECT name, COUNT(*) FROM users GROUP BY name HAVING count > 1;

-- JOINs
SELECT * FROM t1 INNER JOIN t2 ON t1.x = t2.y;
SELECT * FROM t1 LEFT JOIN t2 ON t1.x = t2.y;
SELECT * FROM t1 CROSS JOIN t2;

-- Subqueries
SELECT * FROM (SELECT * FROM t WHERE x > 1) AS sub;

-- EXPLAIN
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Query data as of a specific snapshot ID
SELECT * FROM users AS OF SNAPSHOT 5;

-- Query data as of a wall-clock timestamp (microseconds)
SELECT * FROM users AS OF TIMESTAMP 1719000000000000;

BEGIN;
INSERT INTO users VALUES (3, 'Charlie', 75.0);
COMMIT;
-- or
ROLLBACK;