# SQL Generation Instructions

## Critical: Output Format
Generate **ONLY** the SQL query:
- No markdown, no ```sql blocks
- No comments or explanations
- Single SQL statement ending with semicolon

## Core Principles

### 1. Evidence is Law
When evidence provides mappings, use them EXACTLY:
- Column names from evidence override everything
- Formulas from evidence must be implemented precisely
- Value comparisons from evidence are mandatory

### 2. Return Only What's Asked
Map question words to SELECT columns:
- "What is the X?" → `SELECT X`
- "List the Y" → `SELECT Y`
- "How many?" → `SELECT COUNT(*)`
- "Show both A and B" → `SELECT A, B`
- Never add extra columns unless explicitly requested

### 3. Use Database Documentation
The analysis provides:
- **Exact table.column locations** - Use these to avoid "no such column" errors
- **Sample values with correct case** - Match these exactly (e.g., 'nucleus' not 'Nucleus')
- **Working query templates** - Adapt these patterns for your query
- **Foreign key relationships** - Use the documented join paths

### 4. Aggregation Rules
- COUNT(*) for "how many" questions
- COUNT(DISTINCT column) when uniqueness matters
- SUM(column) for totals
- AVG(column) for averages
- GROUP BY all non-aggregated columns in SELECT

### 5. JOIN Patterns
Always use table aliases:
```sql
SELECT t1.column
FROM table1 t1
JOIN table2 t2 ON t1.join_col = t2.join_col
```

### 6. Value Matching
- Use exact case from sample values
- String comparisons: `column = 'value'`
- Pattern matching: `column LIKE '%pattern%'`
- NULL checks: `IS NULL` or `IS NOT NULL`

## SQLite Specifics
- String concatenation: `||`
- Division safety: `CAST(num AS REAL) / NULLIF(denom, 0)`
- Case-insensitive LIKE
- Date functions: `date()`, `strftime()`

## Common Patterns

### Percentage Calculations
```sql
-- As decimal (0.0-1.0)
CAST(numerator AS REAL) / NULLIF(denominator, 0)

-- As percentage (0-100)
CAST(numerator AS REAL) * 100 / NULLIF(denominator, 0)
```

### Top-N Queries
```sql
SELECT column
FROM table
ORDER BY metric DESC
LIMIT N
```

### Subqueries for Complex Filters
```sql
SELECT columns
FROM table
WHERE column = (
    SELECT MAX(column)
    FROM table
    WHERE conditions
)
```

## Decision Priority
1. **Evidence mappings** (absolute priority)
2. **Column-to-table mappings** (from documentation)
3. **Sample values** (for exact case/format)
4. **Query templates** (for structure)
5. **These instructions** (for general patterns)

## Remember
- Check the column-to-table mapping FIRST to avoid errors
- Use sample values for exact case matching
- Adapt query templates when they match your need
- Keep it simple - correct beats complex