Chapter 3: Using a Coding Companion with SQL
The chapter introduces using ChatGPT as a coding companion for writing and debugging SQL, with prompting strategies that become more guided as queries grow more complex.
After this chapter you will be able to
- Prompting strategy progression. Describe the chapter’s move beyond zero-shot prompting to few-shot prompting, chain-of-thought, and prompt chaining, with examples and exercises intended to build SQL prompting fluency.
- Zero-shot SQL prompting. Explain that zero-shot prompting gives an LLM a direct instruction without examples, schema, or setup, and describe how naming the familiar Pagila dataset can be enough to produce SQL for a request such as the five most-rented films.
- Generated SQL validation. Describe extracting the SQL from the model's response, running it in pgAdmin, and judging it by whether it returns the expected result rather than by matching a particular format or set of aliases.
- Schema and context limits. Explain why zero-shot SQL can fail when the model has not been given a modified or unfamiliar schema, or when a long conversation exceeds its context window. Describe the section's remedies: provide schema excerpts or uploaded files, keep essential context concise, and reset a drifting conversation.
- Few-shot examples and SQL patterns. Explain how a few-shot prompt supplies multiple structured examples before asking for a similar result. Describe how example Pagila reports can guide a new query’s CTE, aliases, and final ordered result set.
- Uploaded files as dataset context. Describe uploading the Pagila schema and seed files into a ChatGPT session, then asking questions about the data using those files as reference context. Explain why this simulates working with a private dataset that the model may not already recognize.
- Step-by-step prompting and purpose. Explain that chain-of-thought prompting asks the AI to work through intermediate steps before its final answer, making assumptions and logic easier to inspect in complex SQL.
- Pagila query reasoning sequence. Trace the proposed steps for finding Pagila's top three customers by spending: use payments, join customers by customer_id, group and sum payment amounts, then sort and limit the results.
- Reasoning checks and failure cascades. Describe how an early wrong assumption can undermine the whole reasoning chain, and explain how to inspect each step, challenge its logic, and verify the SQL results in pgAdmin rather than treating the explanation as proof.
- Multiple paths to one answer. Explain self-consistency prompting as asking a model to solve the same problem in multiple ways and comparing the solutions. Describe how SQL alternatives might use joins, subqueries, CTEs, or different aggregations.
- Database verification of alternatives. Describe checking the generated SQL by comparing its logic and running every version in pgAdmin. Relate this to the Pagila example, where a subquery with an outer count and a direct distinct count both return 599 customers who rented more than 10 times.
- Scrutiny of divergent results. Explain why differing results from one model or across models are a warning to investigate, not automatic proof that one answer is wrong. Describe running all versions, checking assumptions and reasoning, using comparable prompts across tools, and cross-validating critical results.
- Tree-of-Thought solution comparison. Explain that Tree-of-Thought prompting asks the model to generate multiple possible solutions, evaluate them, and recommend one rather than commit to its first answer.
- Pagila SQL approach choice. Describe how the Pagila example compares direct aggregation with a CTE-based query for average rental duration by film category, and why it recommends the CTE version. Explain how to check a similar recommendation by reading and running both queries.
- Precision and context for SQL. Explain why SQL prompts need precise, structured instructions that translate the business question into a task the model can follow, with relevant context such as schema details, table relationships, examples, or expected results.
- SQL output risks and validation. Describe plausible failures of AI-generated SQL and how to respond: clarify ambiguous requests, provide targeted details, and test the query rather than treating it as production-ready.
What it builds on
- Introduction to the Pagila Dataset (chapter 2) · Introduces the Pagila dataset.
- Installing PostgreSQL and pgAdmin (chapter 1) · Using pgAdmin to work with PostgreSQL.
Section by section
Introduction
The chapter introduces using ChatGPT as a coding companion for writing and debugging SQL, with prompting strategies that become more guided as queries grow more complex.
- SQL coding companion scope: Describe how the chapter uses ChatGPT to write and debug SQL, adapting prompts to task complexity and guiding it through joins, subqueries, and window functions. Mention file uploads as another feature the chapter will explore.
Why it matters: This sets the purpose of the prompting techniques that follow. - Pagila schema familiarity: Explain why a prompt beginning with “Using the Pagila dataset” can work with little setup: ChatGPT is already familiar with this widely used DVD-rental database’s schema, foreign keys, and relationships.
Why it matters: The familiar dataset lets the chapter concentrate on prompting rather than repeatedly introducing the database. - Prompting strategy progression: Describe the chapter’s move beyond zero-shot prompting to few-shot prompting, chain-of-thought, and prompt chaining, with examples and exercises intended to build SQL prompting fluency.
Why it matters: These are the approaches the chapter develops for getting more useful help with SQL.
Key terms: AI coding companion, ChatGPT, SQL, file uploads, joins, subqueries, Pagila dataset, schema, foreign keys, relationships
3.1 Zero-Shot Prompting
This section introduces zero-shot SQL prompting with Pagila, shows how to validate generated queries, and explains when missing or lost schema context makes them unreliable.
- Zero-shot SQL prompting: Explain that zero-shot prompting gives an LLM a direct instruction without examples, schema, or setup, and describe how naming the familiar Pagila dataset can be enough to produce SQL for a request such as the five most-rented films.
Why it matters: It establishes the simplest way to ask a coding companion for SQL and the context that approach relies on. - Generated SQL validation: Describe extracting the SQL from the model's response, running it in pgAdmin, and judging it by whether it returns the expected result rather than by matching a particular format or set of aliases.
Why it matters: A plausible-looking response is not evidence that the query works against the actual data. - Schema and context limits: Explain why zero-shot SQL can fail when the model has not been given a modified or unfamiliar schema, or when a long conversation exceeds its context window. Describe the section's remedies: provide schema excerpts or uploaded files, keep essential context concise, and reset a drifting conversation.
Why it matters: Recognizing missing or lost context tells you when to supply reliable schema information instead of repeatedly tweaking the wording.
Key terms: zero-shot prompting, Pagila dataset, SQL extracted, pgAdmin, top 5 most rented films, context window, schema drift, schema context, hallucinating
3.2 Few-Shot Prompting
The section teaches how examples and uploaded reference files guide an AI companion to produce SQL in a desired style for a dataset.
- Few-shot examples and SQL patterns: Explain how a few-shot prompt supplies multiple structured examples before asking for a similar result. Describe how example Pagila reports can guide a new query’s CTE, aliases, and final ordered result set.
Why it matters: Examples can make generated reports more consistent in structure and formatting. - Uploaded files as dataset context: Describe uploading the Pagila schema and seed files into a ChatGPT session, then asking questions about the data using those files as reference context. Explain why this simulates working with a private dataset that the model may not already recognize.
Why it matters: Real datasets often require you to provide the companion with information it cannot infer from a familiar public example. - Generated report verification: Describe generating a new report from the example queries, copying only its SQL into pgAdmin, and checking both whether it runs and whether its structure follows the examples. Distinguish a minor formatting difference from an unusable or illogical result.
Why it matters: Matching a sample’s style is useful, but the resulting query still needs to produce a usable report.
Key terms: few-shot prompting, Pagila, Common Table Expression (CTE), aliasing, file uploads, Pagila schema, seed files, extended context, pgAdmin, CTE
3.3 Chain-of-Thought Prompting
This section shows how to ask a coding companion for step-by-step reasoning about SQL, then inspect and verify that reasoning rather than trusting it automatically.
- Step-by-step prompting and purpose: Explain that chain-of-thought prompting asks the AI to work through intermediate steps before its final answer, making assumptions and logic easier to inspect in complex SQL.
Why it matters: Seeing the proposed logic helps you assess a query before using it. - Pagila query reasoning sequence: Trace the proposed steps for finding Pagila's top three customers by spending: use payments, join customers by customer_id, group and sum payment amounts, then sort and limit the results.
Why it matters: Walking through the tables and operations lets you catch a missing join or aggregation before running the query. - Reasoning checks and failure cascades: Describe how an early wrong assumption can undermine the whole reasoning chain, and explain how to inspect each step, challenge its logic, and verify the SQL results in pgAdmin rather than treating the explanation as proof.
Why it matters: A confident, methodical explanation can still lead to an incorrect query.
Key terms: chain-of-thought (CoT) prompting, step by step, assumptions, Pagila, payment, customer, customer_id, total amount spent, chain-of-thought cascades, debugging
3.4 Self-Consistency Prompting
The section shows how to compare independently generated SQL solutions, verify their results in a database, and investigate disagreements rather than trusting the first plausible query.
- Multiple paths to one answer: Explain self-consistency prompting as asking a model to solve the same problem in multiple ways and comparing the solutions. Describe how SQL alternatives might use joins, subqueries, CTEs, or different aggregations.
Why it matters: Different approaches can expose mistakes that one plausible-looking query might hide. - Database verification of alternatives: Describe checking the generated SQL by comparing its logic and running every version in pgAdmin. Relate this to the Pagila example, where a subquery with an outer count and a direct distinct count both return 599 customers who rented more than 10 times.
Why it matters: Executing both queries tests whether their apparent agreement holds against the actual data. - Scrutiny of divergent results: Explain why differing results from one model or across models are a warning to investigate, not automatic proof that one answer is wrong. Describe running all versions, checking assumptions and reasoning, using comparable prompts across tools, and cross-validating critical results.
Why it matters: Differences can reveal hidden filters, join-path assumptions, or other logic gaps.
Key terms: self-consistency prompting, joins, subqueries, CTEs, aggregation strategies, Pagila dataset, pgAdmin, subquery, outer count, distinct count
3.5 Tree-of-Thought Prompting
This section shows how Tree-of-Thought prompting helps a coding companion compare SQL approaches and recommend one based on qualities such as readability and maintainability.
- Tree-of-Thought solution comparison: Explain that Tree-of-Thought prompting asks the model to generate multiple possible solutions, evaluate them, and recommend one rather than commit to its first answer.
Why it matters: Comparing alternatives gives you a basis for choosing a solution rather than accepting the first plausible answer. - Pagila SQL approach choice: Describe how the Pagila example compares direct aggregation with a CTE-based query for average rental duration by film category, and why it recommends the CTE version. Explain how to check a similar recommendation by reading and running both queries.
Why it matters: A correct SQL result is not the only consideration; query structure affects readability, debugging, and future changes.
Key terms: Tree-of-Thought (ToT) prompting, solution paths, Pagila dataset, direct aggregation, Common Table Expression (CTE), pgAdmin
3.6 Role-Playing, Domain Priming, Prompt Chaining and Beyond
This section introduces additional prompting techniques and explains why effective SQL assistance depends on precise context and validation rather than trust in a plausible-looking query.
- Additional prompting techniques: Describe role-playing, domain priming, and prompt chaining, and explain how each can tailor a coding companion’s response.
Why it matters: Changing the prompting approach can help when an AI response does not fit the task. - Precision and context for SQL: Explain why SQL prompts need precise, structured instructions that translate the business question into a task the model can follow, with relevant context such as schema details, table relationships, examples, or expected results.
Why it matters: A model cannot reliably infer your database structure or business intent from a vague question. - SQL output risks and validation: Describe plausible failures of AI-generated SQL and how to respond: clarify ambiguous requests, provide targeted details, and test the query rather than treating it as production-ready.
Why it matters: A convincing query can still reference nonexistent fields, use the wrong logic, or fail when executed.
Key terms: role-playing, domain priming, prompt chaining, SQL, context, table relationships, schema details, expected outputs, example rows, missing joins
3.7 Lab
This lab practices three SQL prompting techniques by having the reader generate report queries and check alternative solutions.
3.8 Lab Answers
This lab answer section illustrates three ways to prompt for Pagila SQL and emphasizes checking results rather than matching the sample code exactly.
Where this is used later
- Fetching Data from an API (chapter 4) · Introduces zero-shot prompting.
- Using ChatGPT for Sentiment Analysis (chapter 5) · Introduces prompting with a coding companion before prompts are placed inside a Python function.
- (chapter intro) (chapter 4) · Introduces guiding a coding companion with examples.
- Advanced Transformations (chapter 9) · Role-playing and domain priming provide prompting techniques used here.
- The Transformation Prompt (chapter 8) · Writing a transformation prompt calls for clear instructions and relevant task context.
Concepts explained in this chapter
- chain-of-thought promptingChain-of-thought prompting is a prompting technique that asks a language model to produce or follow an explicit sequence of intermediate reasoning steps before an answer.
- SQLA search query is a set of terms or structured input submitted to a search system to retrieve relevant results.
- self-consistencySelf-consistency prompting is a prompting technique that elicits multiple solution paths and compares or aggregates them to improve answer reliability.
- zero-shot promptingZero-shot prompting is a prompting technique that asks a model to perform a task without providing task-specific examples.
- context windowA context window is the finite amount of text or tokens that a language model can process in one interaction.
- schemaA database schema is the formal organization of a database's tables, fields, relationships, constraints, and related definitions.
- hallucinationAn AI hallucination is a confident but incorrect or misleading output produced by an artificial intelligence model.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.
