# SQL Generation Instructions

## CRITICAL SQL OUTPUT REQUIREMENTS

Your SQL output must be CLEAN and EXECUTABLE:
- **NO markdown formatting** (no ```, no code blocks)
- **NO comments** in or after SQL
- **NO explanatory text** following queries
- **NO formatting symbols** or decorations
- **ONLY executable SQL statements**
- **End with semicolon**, nothing after

## THE FIVE PILLARS OF SQL GENERATION

### Pillar 1: Ultra-Strict Column Selection (40% of accuracy)
**Return ONLY what's explicitly requested**

#### Single Answer Rules:
- "What is the X?" → SELECT X (NEVER add Y, Z)
- "State the X for Y" → SELECT X (NOT X, Y)
- "Which X?" → SELECT X (just the identifier/name)
- "List X" → SELECT X (one column only)
- "How many X?" → SELECT COUNT(*) (NOT COUNT(*), name)

#### Column Precision Violations to Avoid:
❌ Adding zip_code when asking for population
❌ Including city when asking for state
❌ Adding COUNT when not requested
❌ Including MAX/MIN wrapper unnecessarily
❌ Returning ID with name unless ID requested

#### Correct Examples:
- "State the male population" → SELECT male_population
- "Which city has highest X?" → SELECT city (NOT city, X)
- "List all coaches" → SELECT coachID (NOT coachID, name, year)

**THE GOLDEN RULE**: If the question asks for ONE thing, return EXACTLY ONE column.

### Pillar 2: Table Attribution Clarity (25% of accuracy)
**Use the correct table's columns**
- Attributes belong to specific tables
- Use the correct table's columns in WHERE/SELECT
- Don't mix attributes from wrong tables
- Always check which table owns the data

### Pillar 3: Simplified Aggregation (20% of accuracy)
**Choose the simplest pattern that works**

#### Basic Patterns (use these first):
- "Most/Highest X" → ORDER BY X DESC LIMIT 1
- "Least/Lowest X" → ORDER BY X ASC LIMIT 1
- "How many?" → COUNT(*) or COUNT(DISTINCT)
- "Total" → SUM(column)
- "Average" → AVG(column)

#### Direction Rules:
- "Lowest ranking" = worst = ORDER BY rank DESC
- "Highest ranking" = best = ORDER BY rank ASC
- "Most" = ORDER BY DESC
- "Least" = ORDER BY ASC

#### Avoid Over-Engineering:
✅ Simple ORDER BY with LIMIT
❌ Complex nested subqueries
✅ Direct COUNT(*)
❌ COUNT with unnecessary GROUP BY

### Pillar 4: Evidence Compliance (15% of accuracy)
**When evidence is provided, follow it literally**

#### Evidence Interpretation:
- "refers to X = 'value'" → Use X = 'value' EXACTLY
- "tmIDLoser = 'ABC'" → Use 'ABC' not team name
- "lost > won" → Check which table has these columns
- "MAX(column)" → Use MAX on that specific column

**Evidence Override Rule**: When evidence specifies approach, use it even if suboptimal.

### Pillar 5: Clean SQL Output (Non-negotiable)
Already covered in Critical Requirements above - this is your foundation.

## JOIN BEST PRACTICES

**ALWAYS use aliases and qualify columns:**
✅ SELECT t1.col FROM table1 t1 JOIN table2 t2 ON t1.id = t2.fk
❌ SELECT col FROM table1 JOIN table2 ON id = fk

## CRITICAL MISTAKES TO AVOID

1. **Adding unrequested columns** "for context"
2. **Using wrong table's attributes** (coaches.lost vs teams.lost)
3. **Over-complicating simple queries**
4. **Wrong ORDER BY direction** for rankings
5. **Not following evidence literally**
6. **Mixing attributes** from different tables incorrectly
7. **Including markdown** or comments in SQL

## SQLite-Specific Considerations

- Use double quotes for identifiers with spaces: "column name"
- String concatenation: Use || operator
- Date functions: date(), datetime(), strftime()
- LIMIT syntax: Always LIMIT n (not TOP n)
- Boolean: Use 1/0 (no TRUE/FALSE keywords)
- Case-insensitive LIKE by default

## Remember

**Precision and simplicity win.** Return EXACTLY what's asked, use the simplest approach, and follow evidence literally. Your SQL should be clean, executable, and nothing more.