SQL Reference

AVA Database® implements a comprehensive SQL interface that combines ANSI SQL compatibility with powerful extensions designed for analytical workloads and high-performance data processing.

ANSI SQL Compatibility

AVA DB provides extensive support for ANSI SQL standards, allowing you to use familiar SQL syntax and semantics. The database engine understands standard SQL commands and follows SQL conventions for query execution, data manipulation, and schema management.

Supported ANSI SQL Features

• Data Query Language (DQL) - Full SELECT statement support with WHERE, GROUP BY, HAVING, ORDER BY, TOP, BOTTOM, and LIMIT clauses
• Data Manipulation Language (DML) - INSERT, UPDATE, DELETE operations
• Data Definition Language (DDL) - CREATE TABLE, ALTER TABLE, DROP TABLE statements
• Joins - INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
• Subqueries - Nested queries in SELECT, FROM, and WHERE clauses
• Aggregation - COUNT, SUM, AVG, MIN, MAX, and more
• Set Operations - UNION, INTERSECT, EXCEPT
• Window Functions - OVER, PARTITION BY, ROW_NUMBER, RANK

Standard SQL Example

-- Standard ANSI SQL query
SELECT
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary
FROM employees
WHERE hire_date >= '2023-01-01'
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Core SQL Statements

AVA DB supports all essential SQL statements for data querying and manipulation:

Extended SQL Capabilities

Beyond ANSI SQL, AVA DB provides powerful extensions for analytical processing and advanced data operations:

Analytical Extensions

Regression Example

-- Linear regression analysis
REGRESS sales_amount ON advertising_spend, seasonality
FROM quarterly_sales
WHERE year >= 2020;

Transpose Example

-- Pivot data from rows to columns
TRANSPOSE revenue
FROM monthly_data
KEY BY product_id
GROUP BY year, quarter;

System Commands

AVA DB provides system commands that start with a dot (.) for database management, metadata access, and utility operations. These commands are not part of standard SQL but provide essential functionality for database administration and data management.

Meta-Information Commands

Data Import/Export Commands

Storage Management Commands

Connection and Session Commands

Multi-Threading Commands

System Command Examples

-- List all tables in database
.tables

-- Show schema for specific table
.schema employees

-- Load CSV data into table
.load "data/employees.csv" as employees

-- Export table to CSV
.save employees "output/employees_backup.csv"

-- Compress table for storage efficiency
.compress sales_data by lz4

-- Sort table physically by date column
.sort transactions by transaction_date

-- View active sessions
.sessions

-- List background jobs
.jobs

Performance Features

AVA DB's SQL implementation is optimized for analytical workloads with several high-performance features:

• Columnar Storage - Data stored column-wise for efficient analytical queries
• Vectorized Execution - SIMD operations (AVX-512) for processing multiple rows simultaneously
• Parallel Query Execution - Multi-threaded query processing for large datasets
• Intelligent Compression - Automatic compression selection based on data characteristics
• Dictionary Encoding - Efficient encoding for string columns
• Predicate Pushdown - Early filtering to reduce data movement
• Late Materialization - Delayed column access until required

SQL Execution Model

AVA DB processes SQL queries through a sophisticated execution pipeline:

1. Parsing - SQL text is parsed into an Abstract Syntax Tree (AST)
2. Analysis - Semantic validation and type checking
3. Optimization - Query rewriting and execution plan generation
4. Vectorization - Operations compiled to SIMD instructions where possible
5. Execution - Parallel execution across multiple threads
6. Result Materialization - Final results assembled and returned

SQL Query
    ↓
Parser (AST Generation)
    ↓
Semantic Analyzer
    ↓
Query Optimizer
    ↓
Vectorization Layer (AVX-512)
    ↓
Parallel Execution Engine
    ↓
Result Set

Key Differences from Standard SQL

While AVA DB maintains high ANSI SQL compatibility, there are some intentional differences designed for performance and analytical use cases:

Next Steps

Explore the detailed documentation for specific SQL features:

• Data Types Reference - Complete guide to AVA DB data types
• SQL Statements - Detailed syntax for each SQL command
• Functions Reference - Built-in functions and aggregations
• System Commands - In-depth guide to dot commands
• Python API - Execute SQL from Python applications