SQL Reference

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

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;

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

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

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

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

EXPLAIN SELECT Name FROM Employees WHERE Id = 1;
EXPLAIN ANALYZE SELECT Name FROM Employees WHERE Id = 1;
EXPLAIN ANALYZE UPDATE Employees SET Salary = Salary + 1000 WHERE Id = 1;

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);

-- 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 Employees SET Salary = Salary * 1.1 WHERE DeptId = 10;
DELETE FROM Employees WHERE Id = 99;

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

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

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

-- 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;

// 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 });