SELECT Queries

Query data with projections, aliases, and the DISTINCT keyword.

Basic SELECT

SELECT * FROM t;
SELECT id, name FROM t;

Column Aliases

SELECT id, val * 2.0 AS doubled FROM t;
SELECT id + 10 AS shifted FROM t;

DISTINCT

SELECT DISTINCT grp FROM data;
SELECT DISTINCT cat, region FROM sales;

Constant Expressions (SELECT without FROM)

You can evaluate constant expressions without a FROM clause. The query returns a single row.

SELECT 1;
SELECT 'hello';
SELECT 3.14;
SELECT true;

-- Multiple constants
SELECT 1, 'hello', 3.14;

-- Arithmetic
SELECT 1 + 2;

-- String concatenation
SELECT 'foo' || 'bar';

-- CAST with temporal literals
SELECT CAST('2025-01-15' AS DATE);
SELECT CAST('12:30:00' AS TIME);
SELECT CAST('2025-01-15 09:30:00' AS TIMESTAMP);

Counting Rows

SELECT COUNT(*) FROM t;
SELECT COUNT(*) FROM t WHERE val >= 30.0;

ORDER BY with Expressions

You can sort by expressions, not just column names:

-- Sort by a computed expression
SELECT id, val * 2.0 AS doubled FROM t ORDER BY val * 2.0;

-- Sort descending
SELECT id, name FROM t ORDER BY id DESC;

-- Sort by alias
SELECT id, val * 2.0 AS doubled FROM t ORDER BY doubled;

-- Sort with LIMIT
SELECT id, val FROM t ORDER BY val DESC LIMIT 3;

Example from tests:

CREATE TABLE t (id INTEGER, val REAL, name VARCHAR);
INSERT INTO t VALUES (1, 10.0, 'alice'), (2, 20.0, 'bob'),
  (3, 30.0, 'carol'), (4, 40.0, 'dave'), (5, 50.0, 'eve');

-- Column projection with alias
SELECT id, val * 2.0 AS doubled FROM t ORDER BY id LIMIT 3;

Output: (1, 20.0), (2, 40.0), (3, 60.0)