# SQL Generation Instructions

## Output Format
Generate **ONLY** the SQL query:
- No markdown, no code blocks, no comments
- No explanatory text before or after
- End with semicolon
- Clean, executable SQL only

## Core Principles

### 1. Column Selection Precision
**Return EXACTLY what is requested - no more, no less:**
- "What is X?" → SELECT X
- "List the Y" → SELECT Y 
- "Show me X and Y" → SELECT X, Y
- "How many?" → SELECT COUNT(*)
- Never add ID columns unless specifically requested
- Never add extra context columns

### 2. Evidence is Law
**When evidence is provided, follow it EXACTLY:**
- Use the exact column names from evidence
- Use the exact values from evidence (including case, spacing, special characters)
- Apply formulas exactly as specified
- If evidence says `ColumnName = 'Value'`, use it precisely
- Evidence overrides any other patterns

### 3. Literal Value Handling
**Values must match EXACTLY as stored:**
- Preserve case sensitivity ('FROM' is different from 'from')
- Preserve trailing/leading spaces ('West ' is different from 'West')
- Preserve special characters and formatting
- String literals: Always use single quotes
- Numeric literals: No quotes unless evidence shows quotes

### 4. Aggregation Patterns
**Simple, direct aggregations:**
- COUNT(*) for "how many" (unless counting specific non-null values)
- COUNT(DISTINCT column) for unique counts
- SUM(column) for totals
- AVG(column) for averages
- MAX(column) for maximum
- MIN(column) for minimum

### 5. JOIN Syntax Rules
**Consistent, qualified joins:**
```sql
SELECT t1.column
FROM table1 t1
JOIN table2 t2 ON t1.key = t2.key
WHERE t1.column = 'value';
```
- Always use table aliases
- Always qualify column names with aliases
- Use JOIN (not INNER JOIN) for brevity
- Join order: Start with the table containing the main entity

### 6. Complex Patterns

**GROUP BY Requirements:**
- Include all non-aggregated columns in GROUP BY
- Use HAVING for post-aggregation filters
- ORDER BY before LIMIT for top-N queries

**Subqueries for Comparisons:**
```sql
WHERE column = (SELECT MAX(column) FROM table)
```

**CASE Statements (only when evidence shows):**
```sql
CASE WHEN condition THEN 'result1' ELSE 'result2' END
```

### 7. SQLite-Specific Functions
- String concatenation: `||` operator
- String extraction: `SUBSTR(string, start, length)`
- String search: `INSTR(string, substring)`
- Date functions: `date()`, `datetime()`, `strftime()`
- Type casting: `CAST(column AS type)`

### 8. Special Value Handling

**NULL Handling:**
- Use `IS NULL` or `IS NOT NULL`
- COUNT(column) excludes NULLs
- COUNT(*) includes all rows

**Boolean Values:**
- Check against 'TRUE', 'FALSE', 1, 0 as they appear in the database
- Use exact format from evidence

**Path Separators:**
- Backslashes may appear without escaping in evidence
- Match the exact format shown in the database analysis

### 9. Performance Considerations
- Add WHERE clauses early to filter data
- Use indexes when available (primary/foreign keys)
- Avoid unnecessary DISTINCT unless required
- Limit result sets with LIMIT when appropriate

### 10. Final Checklist
Before generating SQL, verify:
1. ✓ Am I returning ONLY the requested columns?
2. ✓ Am I following evidence EXACTLY?
3. ✓ Are my literal values precise (case, spaces, format)?
4. ✓ Are all columns qualified with table aliases?
5. ✓ Is my aggregation pattern correct?

## Remember
**Precision over elegance.** Follow patterns literally. Match values exactly. Return only what's asked.