sqlite.sql (1413B)
1 -- Deterministic SQL exercising the SQLite shell built from the amalgamation. 2 -- Output must not depend on RNG, time, or rowid order, so every result set is 3 -- explicitly ordered and no random()/datetime() functions are used. 4 .mode list 5 .headers off 6 7 CREATE TABLE emp(id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER); 8 INSERT INTO emp(name, dept, salary) VALUES 9 ('alice', 'eng', 120), 10 ('bob', 'eng', 100), 11 ('carol', 'ops', 90), 12 ('dave', 'ops', 110), 13 ('erin', 'sales', 80); 14 15 -- Aggregate + group + order. 16 SELECT dept, count(*), sum(salary), avg(salary) 17 FROM emp GROUP BY dept ORDER BY dept; 18 19 -- Join a CTE against the base table. 20 WITH raise(dept, pct) AS (VALUES('eng', 10), ('ops', 5)) 21 SELECT e.name, e.salary, e.salary + e.salary * r.pct / 100 AS adjusted 22 FROM emp e JOIN raise r ON e.dept = r.dept 23 ORDER BY e.name; 24 25 -- Recursive CTE (sum of 1..10). 26 WITH RECURSIVE seq(x) AS ( 27 VALUES(1) UNION ALL SELECT x + 1 FROM seq WHERE x < 10 28 ) SELECT group_concat(x, ','), sum(x) FROM seq; 29 30 -- Scalar + string + built-in JSON functions. 31 SELECT upper('kit'), length('database'), printf('%05d', 42), 32 json_extract('{"a":[7,8,9]}', '$.a[2]'); 33 34 -- Window function: running total ordered by salary. 35 SELECT name, salary, 36 sum(salary) OVER (ORDER BY salary, name 37 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running 38 FROM emp ORDER BY salary, name;