SELECT 42;           -- integer literal
SELECT -17;          -- negative integer
SELECT 3.14;         -- real (floating-point) literal
SELECT 2.5e10;       -- scientific notation
SELECT 0xFF;         -- hexadecimal integer (255)

SELECT 'hello world';        -- text literal
SELECT 'it''s a test';       -- embedded single quote
SELECT '';                   -- empty string

SELECT x'CAFEBABE';          -- blob literal
SELECT X'48454C4C4F';        -- blob literal (case insensitive prefix)

SELECT NULL;                 -- null value

SELECT 1;    -- true
SELECT 0;    -- false

SELECT 10 / 3;           -- 3 (integer division)
SELECT 10 / 3.0;         -- 3.333... (real division)
SELECT CAST(10 AS REAL) / 3;  -- 3.333...

CAST(expression AS type-name)

SELECT CAST(3.7 AS INTEGER);    -- 3 (truncates toward zero)
SELECT CAST(42 AS TEXT);         -- '42'
SELECT CAST('123' AS INTEGER);   -- 123
SELECT CAST('abc' AS INTEGER);   -- 0
SELECT CAST(NULL AS INTEGER);    -- NULL

expression COLLATE collation-name

SELECT 'ABC' = 'abc';                        -- 0 (BINARY comparison)
SELECT 'ABC' = 'abc' COLLATE NOCASE;          -- 1 (case-insensitive)
SELECT 'abc ' = 'abc' COLLATE RTRIM;           -- 1 (trailing space ignored)

SELECT name FROM users ORDER BY name COLLATE NOCASE;

expression [NOT] LIKE pattern [ESCAPE escape-char]

SELECT 'Hello World' LIKE 'hello%';         -- 1 (case-insensitive)
SELECT 'Hello World' LIKE 'H_llo%';         -- 1 (_ matches 'e')
SELECT 'Hello World' LIKE '%World';          -- 1
SELECT '10%' LIKE '10\%' ESCAPE '\';        -- 1 (escaped % literal)

expression [NOT] GLOB pattern

SELECT 'Hello' GLOB 'H*';      -- 1
SELECT 'Hello' GLOB 'h*';      -- 0 (case-sensitive)
SELECT 'Hello' GLOB 'H?llo';   -- 1
SELECT 'Hello' GLOB 'H[a-z]*'; -- 1 (character class)

expression [NOT] REGEXP pattern

SELECT 'Hello123' REGEXP '[A-Za-z]+[0-9]+';  -- 1
SELECT '[email protected]' REGEXP '^[^@]+@[^@]+\.[^@]+$';  -- 1

expression [NOT] BETWEEN low AND high

SELECT 5 BETWEEN 1 AND 10;     -- 1
SELECT 5 NOT BETWEEN 1 AND 3;  -- 1
SELECT 'b' BETWEEN 'a' AND 'c'; -- 1

expression [NOT] IN (value1, value2, ...)
expression [NOT] IN (select-statement)

SELECT 3 IN (1, 2, 3, 4, 5);           -- 1
SELECT 'red' NOT IN ('blue', 'green');   -- 1

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

[NOT] EXISTS (select-statement)

SELECT EXISTS (SELECT 1 FROM users WHERE name = 'Alice');  -- 1 if Alice exists

SELECT name FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e
    WHERE e.department_id = d.id
);

expression IS NULL
expression IS NOT NULL

SELECT NULL IS NULL;       -- 1
SELECT NULL = NULL;        -- NULL (not 1!)
SELECT 42 IS NOT NULL;     -- 1
SELECT NULL IS NOT NULL;   -- 0

expression IS [NOT] DISTINCT FROM expression

SELECT 1 IS DISTINCT FROM 2;       -- 1 (different values)
SELECT 1 IS DISTINCT FROM 1;       -- 0 (same value)
SELECT NULL IS DISTINCT FROM NULL;  -- 0 (both NULL)
SELECT NULL IS DISTINCT FROM 1;     -- 1 (NULL vs non-NULL)
SELECT 1 IS NOT DISTINCT FROM 1;    -- 1

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE default_result]
END

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE default_result]
END

-- Simple CASE
SELECT name,
    CASE status
        WHEN 'A' THEN 'Active'
        WHEN 'I' THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_text
FROM users;

-- Searched CASE
SELECT name, score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'F'
    END AS grade
FROM students;

(select-statement)

SELECT name,
    (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

RAISE(IGNORE)
RAISE(ROLLBACK, error-message)
RAISE(ABORT, error-message)
RAISE(FAIL, error-message)

-- In a trigger
CREATE TRIGGER validate_age BEFORE INSERT ON users
BEGIN
    SELECT CASE
        WHEN NEW.age < 0 THEN RAISE(ABORT, 'age must be non-negative')
    END;
END;

-- In a custom type ENCODE expression
CREATE TYPE positive_int BASE integer
    ENCODE CASE WHEN value > 0 THEN value
                ELSE RAISE(ABORT, 'value must be positive') END
    DECODE value;

SELECT 2 + 3 * 4;       -- 14 (multiplication first)
SELECT (2 + 3) * 4;     -- 20 (parentheses override)