dbxlite uses DuckDB's SQL dialect, which is PostgreSQL-compatible with analytical extensions.
Basic Queries
-- Select all columns
SELECT * FROM table_name;
-- Select specific columns
SELECT col1, col2 FROM table_name;
-- Filter rows
SELECT * FROM table_name WHERE col1 > 100;
-- Sort results
SELECT * FROM table_name ORDER BY col1 DESC;
-- Limit results
SELECT * FROM table_name LIMIT 10;
Aggregations
-- Count rows
SELECT COUNT(*) FROM table_name;
-- Group by with aggregates
SELECT category,
COUNT(*) as count,
AVG(value) as avg_value,
SUM(value) as total
FROM table_name
GROUP BY category;
Window Functions
-- Row number
SELECT *, ROW_NUMBER() OVER (ORDER BY date) as rn
FROM table_name;
-- Running total
SELECT date, value,
SUM(value) OVER (ORDER BY date) as running_total
FROM table_name;
-- Rank within groups
SELECT category, value,
RANK() OVER (PARTITION BY category ORDER BY value DESC) as rank
FROM table_name;
Common Table Expressions (CTEs)
WITH filtered AS (
SELECT * FROM table_name WHERE status = 'active'
),
aggregated AS (
SELECT category, COUNT(*) as cnt
FROM filtered
GROUP BY category
)
SELECT * FROM aggregated ORDER BY cnt DESC;
JSON Functions
-- Extract JSON field
SELECT json_data->>'name' as name FROM table_name;
-- Query nested JSON
SELECT json_data->'address'->>'city' as city FROM table_name;
DuckDB Documentation
For complete SQL reference, visit the official DuckDB documentation.