Write a SQL query from plain English

Why this prompt works

• →Declaring the schema as exhaustive is the single biggest fix — without it, models cheerfully JOIN to tables that don't exist.
• →Assumptions-as-comments puts the judgement calls (NULLs, ties, "this year") where the next reader of the query will actually see them.
• →The performance sentence catches the accidental full-table scan while the query is still in a chat window instead of a dashboard.

Part by part

the ONLY tables and columns that exist

The strongest available constraint against invented schema. Without it a model produces a query referencing a plausible column that is not there.

say so instead of inventing a column

Names the failure directly. A query against a column that does not exist is at least an error; one against a column that exists and means something else is silently wrong.

State any assumption you make

NULL handling, tie-breaking and date boundaries are where two correct-looking queries return different numbers. Making them explicit puts the decision back with you.

as comments in the query

Keeps the assumptions attached to the SQL, so they survive being pasted somewhere else — which is where a query usually ends up.

where it could be slow on a large table

A performance note before it becomes a problem, which is the cheapest moment to hear it.

Common mistakes

• Write a SQL query to get the top customers by revenue.

  Without a schema, the query invents table and column names that look right. It runs nowhere, and adapting it is often more work than writing it yourself.

• Pasting the schema without types or nullability.

  Those determine how the query should handle missing values and comparisons. A nullable column joined without care silently drops rows, which is invisible in the result.

• Accepting the query without reading the assumptions.

  The comments are where the interesting decisions are — whether a tie takes both rows, whether a date range includes its endpoint. Those change the answer more often than the SQL structure does.

Questions