SQL Reference

CSharpDB includes a full SQL engine with tokenizer, parser, query planner, and expression evaluator.

Need the full source guide? The original long-form markdown version is preserved as SQL Source Reference.

Data Definition (DDL)

CREATE TABLE

CREATE TABLE Employees (
    Id        INTEGER PRIMARY KEY,
    Name      TEXT NOT NULL,
    DeptId    INTEGER,
    Salary    REAL,
    HireDate  TEXT,
    Photo     BLOB
);

Temporary Tables

CREATE TEMP TABLE StageEmployees (
    Id   INTEGER PRIMARY KEY IDENTITY,
    Name TEXT
);

INSERT INTO StageEmployees (Name) VALUES ('Ada');
PERSIST TEMP TABLE StageEmployees AS ImportedEmployees;
DROP TEMP TABLE IF EXISTS StageEmployees;

Temporary tables are in-memory and session-scoped. They resolve before durable tables, are hidden from sys.tables and backups, and appear only in sys.temp_tables/sys_temp_tables and sys.temp_columns/sys_temp_columns. Stateless HTTP/gRPC SQL execution rejects temp commands; use transaction sessions for remote temp workflows.

CREATE INDEX

CREATE INDEX idx_emp_dept ON Employees (DeptId);
CREATE UNIQUE INDEX idx_emp_name ON Employees (Name);

CREATE VIEW

CREATE VIEW HighEarners AS
    SELECT Name, Salary FROM Employees WHERE Salary > 100000;

CREATE TRIGGER

CREATE TRIGGER log_insert
    AFTER INSERT ON Employees
    BEGIN
        INSERT INTO AuditLog VALUES ('INSERT', NEW.Name);
    END;

ALTER TABLE & DROP

ALTER TABLE Employees ADD COLUMN Title TEXT;
ALTER TABLE Employees DROP CONSTRAINT fk_employees_deptid_a1b2c3d4;
DROP TABLE Employees;
DROP INDEX idx_emp_dept;
DROP VIEW HighEarners;

Data Manipulation (DML)

INSERT

INSERT INTO Employees (Id, Name, DeptId, Salary)
    VALUES (1, 'Alice', 10, 95000.0);

INSERT INTO Employees VALUES
    (2, 'Bob', 20, 88000.0, '2024-01-15', NULL);

SELECT

-- Basic query with filtering and ordering
SELECT Name, Salary FROM Employees
    WHERE Salary > 80000 ORDER BY Salary DESC;

-- DISTINCT, LIMIT, OFFSET
SELECT DISTINCT DeptId FROM Employees LIMIT 10 OFFSET 5;

-- JOINs
SELECT e.Name, d.DeptName
    FROM Employees e
    INNER JOIN Departments d ON e.DeptId = d.Id;

SELECT e.Name, d.DeptName
    FROM Employees e
    LEFT JOIN Departments d ON e.DeptId = d.Id;

-- Aggregates and GROUP BY
SELECT DeptId, COUNT(*), AVG(Salary), MAX(Salary)
    FROM Employees
    GROUP BY DeptId
    HAVING COUNT(*) > 3;

-- CTEs (Common Table Expressions)
WITH TopEarners AS (
    SELECT Name, Salary FROM Employees
    WHERE Salary > 100000
)
SELECT * FROM TopEarners ORDER BY Salary DESC;

-- Subqueries
SELECT Name FROM Employees
    WHERE DeptId IN (SELECT Id FROM Departments WHERE Region = 'West');

SELECT Name FROM Employees e
    WHERE EXISTS (SELECT 1 FROM Reviews r WHERE r.EmpId = e.Id);

-- Set operations
SELECT Name FROM Employees WHERE DeptId = 10
UNION
SELECT Name FROM Contractors WHERE Active = 1;

UPDATE & DELETE

UPDATE Employees SET Salary = Salary * 1.1 WHERE DeptId = 10;
DELETE FROM Employees WHERE Id = 99;

Data Hygiene

Use SQL-first hygiene commands to preview duplicates, deduplicate or merge rows transactionally, store audit-only validation rules, and find orphaned child values.

-- Preview duplicate groups without changing data
FIND DUPLICATES IN Customers ON Email COLLATE NOCASE;

-- Delete non-winners through normal index/FK/trigger mutation paths
DEDUP Customers ON Email KEEP FIRST;

-- Fill NULL winner fields when exactly one duplicate value is available
MERGE DUPLICATES Customers ON Email;

-- Store and evaluate audit-only SQL validation rules
CREATE VALIDATION RULE ValidEmail
    ON Customers.Email
    AS Email LIKE '%@%'
    MESSAGE 'Email must contain @';

VALIDATE TABLE Customers;

-- Check declared FKs or an explicit child/parent relationship
FIND ORPHANS IN Bookings;
FIND ORPHANS IN Bookings.BookId REFERENCES Books.Id;

Hygiene commands return normal query rows, so they work through embedded SQL, ADO.NET, Admin query tabs, the CLI, HTTP, and gRPC. Validation rules are stored in a hidden internal table and exposed through sys.validation_rules and sys_validation_rules.

Built-in Functions

String Functions

Function	Description
`UPPER(text)`	Convert to uppercase
`LOWER(text)`	Convert to lowercase
`LENGTH(text)`	String length
`SUBSTR(text, start, len)`	Substring extraction
`TRIM(text)`	Remove leading/trailing whitespace
`REPLACE(text, from, to)`	String replacement
`INSTR(text, search)`	Find position of substring

Math Functions

Function	Description
`ABS(value)`	Absolute value
`ROUND(value, digits)`	Round to N decimal places
`SIGN(value)`	Sign of a number (-1, 0, 1)
`CEIL(value)`	Round up to nearest integer
`FLOOR(value)`	Round down to nearest integer

Conditional & Type Functions

Function	Description
`TYPEOF(value)`	Returns the type name as text
`COALESCE(a, b, ...)`	First non-null value
`NULLIF(a, b)`	NULL if a equals b
`IFNULL(a, b)`	b if a is null
`IIF(cond, then, else)`	Conditional expression

Aggregate Functions

Function	Description
`COUNT(*)` / `COUNT(col)`	Count rows or non-null values
`SUM(col)`	Sum of values
`AVG(col)`	Average of values
`MIN(col)`	Minimum value
`MAX(col)`	Maximum value

System Catalog

Query database metadata through built-in system views:

View	Description
`sys.tables`	All tables and their root page numbers
`sys.columns`	Column definitions for all tables
`sys.indexes`	Index definitions and backing B+tree info
`sys.foreign_keys`	Foreign key constraints, referenced targets, delete actions, and support-index ownership
`sys.views`	View definitions and SQL
`sys.triggers`	Trigger metadata
`sys.validation_rules`	Database-owned audit validation rules and their SQL expressions
`sys.temp_tables`	Current-session temporary table metadata
`sys.temp_columns`	Current-session temporary column metadata
`sys.table_stats`	Row count and page usage per table
`sys.column_stats`	Cardinality, NULL count, min/max per column

Stored Procedures

CSharpDB supports stored procedures with typed parameters, persisted in the database catalog.

// Create a procedure
await client.CreateProcedureAsync(new ProcedureDefinition
{
    Name = "GetEmployeesByDept",
    BodySql = "SELECT * FROM Employees WHERE DeptId = @deptId",
    Parameters = [
        new ProcedureParameterDefinition { Name = "deptId", Type = DbType.Integer }
    ]
});

// Execute
var result = await client.ExecuteProcedureAsync(
    "GetEmployeesByDept",
    new Dictionary<string, object?> { ["deptId"] = 10 });