sql generation
**SQL generation** (also known as **NL2SQL** or **text-to-SQL**) is the AI task of automatically converting **natural language questions into syntactically and semantically correct SQL queries** — enabling non-technical users to query databases using plain English instead of writing SQL code.
**Why SQL Generation Matters**
- **SQL is powerful but technical**: Writing correct SQL requires understanding of table schemas, JOIN operations, aggregations, subqueries, and database-specific syntax.
- **Most data consumers aren't SQL experts**: Business analysts, managers, and domain experts have questions about their data but often can't express them in SQL.
- **SQL generation democratizes data access** — anyone who can describe what they want in natural language can get answers from a database.
**How SQL Generation Works**
1. **Input**: Natural language question + database schema (table names, column names, types, relationships).
2. **Understanding**: The model interprets the user's intent — what data they want, what filters to apply, what aggregations to perform.
3. **Schema Linking**: Maps natural language terms to specific tables and columns — "revenue" → `sales.total_amount`, "last year" → `WHERE date >= '2025-01-01'`.
4. **SQL Construction**: Generates a syntactically valid SQL query that expresses the user's intent.
5. **Execution**: The generated SQL is executed against the database.
6. **Answer**: Results are returned to the user, optionally with the generated SQL for transparency.
**SQL Generation Example**
```
Schema: employees(id, name, dept, salary, hire_date)
departments(id, name, location)
Question: "What is the average salary in the
engineering department?"
Generated SQL:
SELECT AVG(e.salary)
FROM employees e
JOIN departments d ON e.dept = d.id
WHERE d.name = 'Engineering'
```
**SQL Generation with LLMs**
- Modern LLMs (GPT-4, Claude, Codex) achieve **80–90%+ execution accuracy** on standard benchmarks when provided with the schema.
- **Prompt Engineering**: Include the full schema, example queries, and output format instructions in the prompt.
- **Schema Representation**: Present schemas clearly — table names, column names with types, primary/foreign key relationships, and sample values for disambiguation.
**Key Challenges**
- **Complex Queries**: Nested subqueries, CTEs, window functions, correlated subqueries — harder to generate correctly.
- **Ambiguity Resolution**: "Top customers" — by revenue? by order count? by most recent activity? The model must infer or ask for clarification.
- **Schema Complexity**: Real databases have hundreds of tables and columns — the model must identify relevant ones.
- **Domain Terminology**: Business terms may not match column names — "churn rate" doesn't appear in any column.
- **Safety**: Generated SQL should be read-only (no DELETE, UPDATE, DROP) unless explicitly authorized.
**Evaluation Metrics**
- **Execution Accuracy**: Does the generated SQL return the correct result? (Most important metric.)
- **Exact Match**: Does the generated SQL exactly match the gold standard? (Too strict — many equivalent queries exist.)
- **Valid SQL Rate**: Is the generated SQL syntactically valid and executable?
SQL generation is one of the **most impactful practical applications of LLMs** — it transforms natural language into precise database queries, making organizational data accessible to everyone regardless of technical skill.