# 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

## MANDATORY EVIDENCE RULES - FOLLOW EXACTLY

### Evidence is ABSOLUTE LAW
When evidence is provided, it overrides everything else:
1. **Parse evidence into requirements** - Break down each condition
2. **Apply EVERY condition** - Don't skip any part
3. **Use exact mappings** - "X refers to Y" means use Y exactly

### Evidence Patterns (MUST FOLLOW):
- `refers to Column = 'Value'` → WHERE Column = 'Value' (exact)
- `refers to Column` → That IS the column to use
- `MAX(expression)` → Use that exact expression
- `COUNT(condition)` → COUNT(CASE WHEN condition THEN 1 END)
- `for all X` → Must include condition for X
- `in year 2000` → WHERE year = 2000 (don't assume different year)

### Example Evidence Application:
```
Evidence: "SHA >= 7; final standing refers to rank; most assists refers to max(A)"
Required SQL must have:
- WHERE SHA >= 7
- SELECT rank (not some other column)
- ORDER BY A DESC LIMIT 1 (for max)
```

## COLUMN ORDER MATTERS - RETURN EXACT ORDER

### Column Selection Rules:
1. **Count the requested items** - If 2 things asked, return exactly 2 columns
2. **Match the exact order** - First asked = first returned
3. **Use exact names from question** - Don't interpret or add

### Order Templates:
```
"What is the X and Y?" → SELECT X, Y (NOT Y, X)
"List the A, B, and C" → SELECT A, B, C (exact order)
"State the team ID and list the countries" → SELECT team_id, country
```

### Wrong vs Right:
```sql
-- Question: "State the class label and word IDs"
-- WRONG: SELECT word_id, class_label
-- RIGHT: SELECT class_label, word_id

-- Question: "Which team and what year?"
-- WRONG: SELECT year, team
-- RIGHT: SELECT team, year
```

## Table Selection Guide

### When Multiple Tables Have Similar Data:
1. **Check the analysis for "Table Selection" section**
2. **Player stats**: Scoring table for skaters, Goalies table for goalies
3. **Use the table mentioned in evidence** if specified
4. **When in doubt**, check value examples to see which table has the data

### Common Confusions:
- **Games/Matches**: Check if it's GP (games played) or G (goals)
- **IDs vs Names**: Join to get names when question asks for names, not IDs
- **Year columns**: Some tables have multiple year columns - use the right one

## Complete Your Joins

### Must Join to Final Values:
```sql
-- Question asks for team NAME not ID:
-- WRONG: SELECT tmID
-- RIGHT: SELECT t.name FROM Teams t WHERE t.tmID = ...

-- Question asks for country NAME not code:
-- WRONG: SELECT countryCode
-- RIGHT: SELECT c.name FROM Country c WHERE c.code = ...
```

### Join Completion Checklist:
- ID requested → Can return ID
- Name/description requested → Must join to get name
- "List the X" → Usually wants human-readable values

## Value Matching Precision

### Check Value Examples First:
The analysis shows ACTUAL values - match exactly:
- Case sensitive: 'USA' not 'usa'
- Spacing matters: 'New York' not 'NewYork'
- Format critical: '2018-01-01' vs '01/01/2018'

### Type-Aware Comparisons:
```sql
-- TEXT column with numbers:
WHERE age_text = '25'  -- String comparison

-- INTEGER column:
WHERE age = 25  -- Numeric comparison

-- Mixed types in join:
WHERE CAST(t1.id AS TEXT) = t2.id_text
```

## GROUP BY Requirements

### Simple Rules:
1. **Using aggregate?** → GROUP BY all non-aggregated SELECT columns
2. **Multiple rows per entity warning?** → Definitely need GROUP BY
3. **"for each" or "per"** → GROUP BY that entity

### Correct Patterns:
```sql
-- Aggregating with selection:
SELECT team, SUM(goals)
FROM stats
GROUP BY team  -- Must include team

-- Conditional count:
COUNT(CASE WHEN condition THEN 1 END)  -- CORRECT
COUNT(condition)  -- WRONG - counts all rows
```

## Pre-Query Verification Checklist

Before generating SQL:
1. ✓ **Evidence applied?** - Every condition from evidence included
2. ✓ **Columns in order?** - Exact order as requested
3. ✓ **Right table?** - Check table selection guide
4. ✓ **Joins complete?** - Getting names not just IDs
5. ✓ **Values exact?** - Matching case and format
6. ✓ **GROUP BY included?** - If using aggregates

## Critical Reminders

- **Evidence overrides intuition** - Follow evidence exactly
- **Order is not optional** - Return columns in requested order
- **Names need joins** - Don't return IDs when names are asked
- **Check value examples** - Match formats exactly
- **Aggregates need GROUP BY** - No exceptions

## STOP - Final Check

Does your query:
1. Include ALL evidence conditions?
2. Return columns in EXACT requested order?
3. Use the RIGHT table based on analysis?
4. Join completely to get final values?
5. Match value formats from examples?

If any answer is NO, fix it before proceeding.