TL;DR

• This is a SQL generation test: given a schema description, have the model output an executable MySQL query.
• Common failures: wrong join conditions, wrong column names, missing filter conditions, non-MySQL-compatible syntax.
• Production tip: write the schema in a more structured way (PK/FK/index), and require the output to include both an explanation and verifiable assumptions (e.g., join keys).

Background

This prompt tests an LLM's code generation capabilities by prompting it to generate a valid MySQL query given a schema description.

How to Apply

When migrating to real business use, split the input into two parts:

• Schema: tables, columns, PK/FK, field types (if important)
• Requirement: what to query, filter conditions, sorting, whether aggregation is needed

And specify the output format, for example:

• SQL (output only SQL)
• Assumptions (if the schema is incomplete, list assumptions)

How to Iterate

1. Add field types and constraints (nullable/unique) to reduce ambiguity
2. Require output to include join rationale (why a particular key is used for the join)
3. Add evaluation: use a small mock dataset or SQL validator to verify syntax and results
4. Increase complexity: group by / window function / subquery, and observe robustness

Self-check Rubric

• Is the SQL MySQL-compatible? Can it run directly?
• Are all table/column names from the schema? Any hallucinated columns?
• Do the filter conditions meet requirements? Is the Computer Science Department constraint missing?
• Is the join correct? Will it produce duplicate rows?

Practice

Exercise: expand this example into 3 versions:

• Query student names only
• Query student names + department name
• Add sorting (by StudentName) and pagination (limit/offset)

Prompt

"""
Table departments, columns = [DepartmentId, DepartmentName]
Table students, columns = [DepartmentId, StudentId, StudentName]
Create a MySQL query for all students in the Computer Science Department
"""

Code / API

OpenAI (Python)

from openai import OpenAI

client = OpenAI()

response = client.chat.completions.create(
    model="gpt-4",
    messages=[
        {
            "role": "user",
            "content": "\"\"\"\nTable departments, columns = [DepartmentId, DepartmentName]\nTable students, columns = [DepartmentId, StudentId, StudentName]\nCreate a MySQL query for all students in the Computer Science Department\n\"\"\"",
        }
    ],
    temperature=1,
    max_tokens=1000,
    top_p=1,
    frequency_penalty=0,
    presence_penalty=0,
)

Fireworks (Python)

import fireworks.client

fireworks.client.api_key = "<FIREWORKS_API_KEY>"

completion = fireworks.client.ChatCompletion.create(
    model="accounts/fireworks/models/mixtral-8x7b-instruct",
    messages=[
        {
            "role": "user",
            "content": "\"\"\"\nTable departments, columns = [DepartmentId, DepartmentName]\nTable students, columns = [DepartmentId, StudentId, StudentName]\nCreate a MySQL query for all students in the Computer Science Department\n\"\"\"",
        }
    ],
    stop=["<|im_start|>", "<|im_end|>", "<|endoftext|>"],
    stream=True,
    n=1,
    top_p=1,
    top_k=40,
    presence_penalty=0,
    frequency_penalty=0,
    prompt_truncate_len=1024,
    context_length_exceeded_behavior="truncate",
    temperature=0.9,
    max_tokens=4000,
)