sql-cli 1.81.0

SQL query tool for CSV/JSON with both interactive TUI and non-interactive CLI modes - perfect for exploration and automation
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

# Tier 6 — CTEs and set operations.

[[case]]
id = "cte_single"
data = "international_sales.csv"
sql = "WITH x AS (SELECT region, amount FROM international_sales) SELECT region, amount FROM x"

[[case]]
id = "cte_multiple"
data = "international_sales.csv"
sql = "WITH a AS (SELECT region, SUM(amount) AS total FROM international_sales GROUP BY region), b AS (SELECT region FROM a WHERE total > 5000) SELECT region FROM b"

[[case]]
id = "cte_referencing_cte"
data = "international_sales.csv"
sql = "WITH base AS (SELECT region, amount FROM international_sales), agg AS (SELECT region, SUM(amount) AS total FROM base GROUP BY region) SELECT region, total FROM agg"

[[case]]
id = "union"
data = "trades.csv"
sql = "SELECT symbol FROM trades WHERE price > 186 UNION SELECT symbol FROM trades WHERE price < 184"

[[case]]
id = "union_all"
data = "international_sales.csv"
sql = "SELECT region FROM international_sales WHERE amount > 2000 UNION ALL SELECT region FROM international_sales WHERE amount < 100"

[[case]]
id = "intersect"
data = "international_sales.csv"
sql = "SELECT region FROM international_sales WHERE amount > 2000 INTERSECT SELECT region FROM international_sales WHERE amount < 1000"
# "INTERSECT is not yet implemented".
expect = "GAP"

[[case]]
id = "except"
data = "international_sales.csv"
sql = "SELECT region FROM international_sales EXCEPT SELECT region FROM international_sales WHERE amount > 2000"
# "EXCEPT is not yet implemented".
expect = "GAP"

[[case]]
id = "recursive_cte"
data = "trades.csv"
sql = "WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5) SELECT i FROM n"
# Recursive CTEs are a known, considered, deferred design item (not supported).
expect = "GAP"

[[case]]
id = "cte_in_expression_position"
data = "trades.csv"
sql = "SELECT symbol, price FROM trades WHERE price > (WITH avg_cte AS (SELECT AVG(price) AS a FROM trades) SELECT a FROM avg_cte)"
# P12: WITH is rejected anywhere in expression position ("Unexpected token in
# primary expression: With"). DuckDB allows a CTE inside a scalar subquery.
expect = "GAP"