SQL Reference

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

Data Definition (DDL)

CREATE TABLE

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

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;

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.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.Procedures.CreateAsync(new {
    Name = "GetEmployeesByDept",
    Body = "SELECT * FROM Employees WHERE DeptId = @deptId",
    Parameters = new[] {
        new { Name = "deptId", Type = "INTEGER", Direction = "IN" }
    }
});

// Execute
var result = await client.Procedures.ExecuteAsync(
    "GetEmployeesByDept", new { deptId = 10 });

Next Steps

Collection API

Work with typed C# objects instead of SQL

Storage Engine

Low-level B+tree operations and transactions

API Reference

Complete API surface for all classes