# SQL Generation Instructions - Precision Synthesis Enhanced

## THE CARDINAL RULE (50% of all errors)

### RETURN EXACTLY WHAT IS ASKED - NOTHING MORE

**This is the single most important rule. Everything else is secondary.**

#### WRONG vs RIGHT Examples

```sql
-- Question: "Who has participated in the most Olympic Games?"
-- WRONG (returns extra column):
SELECT full_name, COUNT(*) as games_count 
FROM person p JOIN games_competitor gc ON p.id = gc.person_id
GROUP BY p.id ORDER BY games_count DESC LIMIT 1;

-- RIGHT (returns ONLY what was asked):
SELECT full_name 
FROM person p JOIN games_competitor gc ON p.id = gc.person_id
GROUP BY p.id ORDER BY COUNT(*) DESC LIMIT 1;
```

```sql
-- Question: "What is the population of California?"
-- WRONG (returns state name too):
SELECT state_name, population FROM states WHERE state_name = 'California';

-- RIGHT (returns ONLY population):
SELECT population FROM states WHERE state_name = 'California';
```

```sql
-- Question: "How many customers are there?"
-- WRONG (returns the actual customers):
SELECT * FROM customers;

-- RIGHT (returns ONLY the count):
SELECT COUNT(*) FROM customers;
```

### The Test: Before returning ANY query, ask:
1. What EXACTLY was requested? (name? count? list? single value?)
2. Am I returning ONLY that?
3. Have I added ANY extra columns?

If you added ANYTHING beyond what was asked → DELETE IT

## The Seven Commandments (Remaining 50% of errors)

### 1. ATTRIBUTION ACCURACY (20% of errors)
**The table that OWNS the data is the ONLY source**
- Check ownership map for EVERY column
- Foreign keys are REFERENCES, not sources
- When joining, the owning table provides the value

### 2. EVIDENCE COMPLIANCE (10% of errors)
**Evidence is mandatory - use it exactly**
- Evidence values → use verbatim (case-sensitive)
- Evidence approach → follow precisely
- Evidence mappings → apply directly

### 3. AGGREGATION PATTERNS (8% of errors)
**Most/Least require specific patterns**
```sql
-- "Most/Highest" → ORDER BY DESC LIMIT 1
-- "Least/Lowest" → ORDER BY ASC LIMIT 1
-- "Average" → AVG() not SUM()/COUNT()
-- "Total" → SUM() not COUNT()
```

### 4. JOIN CORRECTNESS (7% of errors)
- Use documented foreign keys only
- Follow shortest path between tables
- Junction tables own nothing

### 5. GROUP BY COMPLETENESS (3% of errors)
- GROUP BY every non-aggregated column in SELECT
- No exceptions to this rule

### 6. NULL HANDLING (1% of errors)
- IS NULL / IS NOT NULL (never = NULL)
- Handle NULLs in aggregations appropriately

### 7. OUTPUT FORMAT (1% of errors)
- No markdown, no comments
- Just the SQL query
- End with semicolon

## Query Pattern Templates

### Finding a Single Entity
```sql
-- Return ONE column about ONE entity
SELECT requested_column
FROM owning_table
WHERE condition
LIMIT 1;  -- If needed
```

### Finding the Most/Least
```sql
-- Return the entity with extreme value (NOT the value itself)
SELECT entity_column
FROM table
ORDER BY measure_column [DESC|ASC]
LIMIT 1;
```

### Counting
```sql
-- Return ONLY the count
SELECT COUNT(*)
FROM table
WHERE condition;
```

### Listing
```sql
-- Return the actual values (not count)
SELECT requested_column
FROM table
WHERE condition;
```

## Critical Decision Points

### When Question Asks "What is X of Y?"
→ Return ONLY X, not X and Y

### When Question Asks "Who/Which has the most/least?"
→ Return ONLY the who/which, not the count

### When Question Asks "How many?"
→ Return COUNT(*), not the rows

### When Question Asks "List/Show"
→ Return actual values, not counts

## Pre-Return Validation Checklist

**STOP before returning any query and verify:**

- [ ] **COLUMN COUNT**: Am I returning EXACTLY the number of columns requested?
- [ ] **COLUMN IDENTITY**: Is each column EXACTLY what was asked for?
- [ ] **NO EXTRAS**: Have I added ANY unrequested columns?
- [ ] **OWNERSHIP**: Is each column from its OWNING table?
- [ ] **EVIDENCE**: Have I followed evidence exactly?

## The Simplicity Principle

When in doubt:
1. Return less, not more
2. Use simpler queries over complex
3. Trust the ownership map completely
4. Follow patterns exactly

## Common Violations to Avoid

### NEVER DO:
- Add COUNT when asking for WHO
- Add names when asking for COUNT  
- Add context columns "for clarity"
- Return multiple columns when one requested
- Interpret what user "probably wants"
- Use SELECT * for specific requests

### ALWAYS DO:
- Return EXACTLY what's asked
- Check ownership before every join
- Follow evidence literally
- Count columns before returning
- Use simplest working pattern

**Remember: Precision in column selection is worth more than all other rules combined.**