Overview

3 Using a Coding Companion with SQL

The chapter shows how an AI coding companion, especially ChatGPT, can help write, debug, and refine SQL when used with the right prompting strategy. Since SQL is central to data engineering, the key skill is learning how to guide the model with enough context, then validate the generated query in a database tool rather than trusting the response blindly. The Pagila dataset is used throughout because its schema is already familiar to the model, making it a convenient setting for testing different prompting methods.

It begins with the simplest approach, zero-shot prompting, where a direct request can often produce a useful query with little setup. From there, the chapter builds toward few-shot prompting, where examples help the model imitate a preferred structure and formatting style, especially for recurring report patterns. It also emphasizes the value of uploaded schema files or other reference material, since real-world datasets are usually private and unfamiliar to the model.

More advanced techniques such as chain-of-thought, self-consistency, and tree-of-thought are presented as ways to improve clarity, reliability, and debugging. Step-by-step reasoning can expose faulty assumptions, multiple solutions can reveal inconsistencies, and comparing alternative query paths can highlight the clearest or most maintainable option. The overall message is that AI is most effective for SQL when prompts are precise, context is supplied carefully, and outputs are treated as drafts that must be tested and verified.

This diagram presents a visual summary of the prompting strategies explored in Chapter 3. It shows how each technique builds on the basic prompt–response pattern by introducing added context, reasoning steps, or multiple outputs. Starting with zero-shot prompting, each method increases in complexity and interpretability—from few-shot and chain-of-thought to self-consistency and tree-of-thought—helping you guide large language models with greater control and clarity.
Result of the Zero-Shot Prompt response for the Pagila data set.
Demonstrating the file upload capability of ChatGPT. Uploading files works much like any web-based tool: you click to add a file, and it becomes part of the active conversation. Once uploaded, ChatGPT can read and reference the contents of your schema or data files to generate more accurate and context-aware SQL.
Result of the Few-Shot Prompt response.
Result of the Chain-of-Thought Prompt response.
Result of the Tree-of-Thought Prompt response.

Lab Answers

Keep in mind that your SQL code may not match these answers exactly. The AI may use different alias names, formatting, or logic structures, but the results should be consistent when run against the Pagila dataset. The key is that the output is correct, not that the code looks identical.

1. Chain-of-Thought Prompting

Prompt:

“Using the Pagila dataset, calculate the average number of rentals per customer, but exclude customers who have never rented anything. Think through the problem step by step before writing the SQL.”

 

Response:

“To solve this, we need to count how many rentals each customer has made. We'll start with the customer table and join it to the rental table using customer_id. This will automatically exclude customers who have never rented anything, because they won’t appear in the rental table. After grouping by customer_id, we can count the number of rentals per customer, and then take the average of those counts.

SELECT AVG(rental_count) AS avg_rentals_per_customer
FROM (
  SELECT c.customer_id, COUNT(r.rental_id) AS rental_count
  FROM customer c
  JOIN rental r ON c.customer_id = r.customer_id
  GROUP BY c.customer_id
) AS sub;”

2. Few-Shot Prompting

Prompt:

“Using the Pagila dataset, and following the same CTE format used in earlier report-style queries, write a report that returns the top five film categories by total revenue.”

 

Possible SQL:

WITH category_revenue AS (
  SELECT c.name AS category, SUM(p.amount) AS revenue
  FROM payment p
  JOIN rental r ON p.rental_id = r.rental_id
  JOIN inventory i ON r.inventory_id = i.inventory_id
  JOIN film f ON i.film_id = f.film_id
  JOIN film_category fc ON f.film_id = fc.film_id
  JOIN category c ON fc.category_id = c.category_id
  GROUP BY c.name
)
SELECT category, revenue
FROM category_revenue
ORDER BY revenue DESC
LIMIT 5;

3. Self-Consistency Prompting

Prompt:

“Using the Pagila dataset, list the top five films with the longest average rental durations. Provide two different approaches to solving the problem and compare them.”

 

Possible SQL (Approach 1):

SELECT f.title, AVG(r.return_date - r.rental_date) AS avg_duration
FROM rental r
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
GROUP BY f.title
ORDER BY avg_duration DESC
LIMIT 5;

Possible SQL (Approach 2 - with CTE):

WITH rental_durations AS (
  SELECT f.film_id, f.title, (r.return_date - r.rental_date) AS duration
  FROM rental r
  JOIN inventory i ON r.inventory_id = i.inventory_id
  JOIN film f ON i.film_id = f.film_id
)
SELECT title, AVG(duration) AS avg_duration
FROM rental_durations
GROUP BY title
ORDER BY avg_duration DESC
LIMIT 5;

FAQ

What is zero-shot prompting in SQL with a coding companion?Zero-shot prompting means giving ChatGPT a direct SQL request with no examples, schema details, or extra setup. It relies on the model’s pre-trained knowledge and the wording of your prompt to generate a query.
Why does the Pagila dataset work well with ChatGPT?Pagila is widely known and appears in public training data, so ChatGPT often already understands its tables, foreign keys, and relationships. A prompt like “Using the Pagila dataset...” is often enough to produce useful SQL.
What should you do after ChatGPT generates SQL for a zero-shot prompt?You should extract only the SQL, paste it into pgAdmin, and validate it against the actual database. Even if the formatting or aliases differ, the key test is whether the query returns the expected result.
What can go wrong when using zero-shot prompting with SQL?If the schema is not well known to the model, or if your conversation exceeds the context window, ChatGPT may use outdated assumptions, ignore important details, or generate incorrect SQL. In those cases, you may need explicit schema context or example tables.
What is few-shot prompting and why is it useful for SQL?Few-shot prompting provides multiple examples before asking for a new query. It helps ChatGPT learn the pattern you want, making it especially useful for consistent formatting, aliasing, and multi-table queries.
How do file uploads help when prompting ChatGPT for SQL?File uploads let you give ChatGPT schema documents, seed files, or other reference material as extended context. This improves accuracy because the model can reference the uploaded content instead of relying only on memory.
What is chain-of-thought prompting in SQL?Chain-of-thought prompting asks the model to think step by step before producing SQL. This is helpful for complex queries involving joins, filters, and aggregations because it reveals the model’s reasoning and makes errors easier to catch.
What is self-consistency prompting?Self-consistency prompting asks ChatGPT to solve the same SQL problem in two different ways and compare the results. If both approaches agree, you gain more confidence; if they differ, it signals that the logic needs review.
What is tree-of-thought prompting?Tree-of-thought prompting encourages the model to explore multiple possible solutions, compare them, and recommend the best one. In SQL, this can help when choosing between approaches like a direct aggregation versus a CTE-based query.
What is the main lesson about prompting AI for SQL in this chapter?Prompting is not magic, and LLMs are not mind readers. Good SQL results depend on giving the model clear context, schema details, and a precise question, then validating the output in your database.

pro $24.99 per month

  • access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
  • choose one free eBook per month to keep
  • exclusive 50% discount on all purchases
  • renews monthly, pause or cancel renewal anytime

lite $19.99 per month

  • access to all Manning books, including MEAPs!

team

5, 10 or 20 seats+ for your team - learn more

choose your plan

pro

monthly
annual
$24.99
$249.99
only $20.83 per month
  • access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
  • choose another free product every time you renew
  • choose twelve free products per year
  • exclusive 50% discount on all purchases
  • renews monthly, pause or cancel renewal anytime
  • renews annually, pause or cancel renewal anytime
  • Learn AI Data Engineering ebook for free

team

monthly
annual
$49.99
$499.99
only $41.67 per month
  • five seats for your team
  • access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
  • choose another free product every time you renew
  • choose twelve free products per year
  • exclusive 50% discount on all purchases
  • renews monthly, pause or cancel renewal anytime
  • renews annually, pause or cancel renewal anytime
  • Learn AI Data Engineering ebook for free
choose your plan

pro

monthly
annual
$24.99
$249.99
only $20.83 per month
  • access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
  • choose another free product every time you renew
  • choose twelve free products per year
  • exclusive 50% discount on all purchases
  • renews monthly, pause or cancel renewal anytime
  • renews annually, pause or cancel renewal anytime
  • Learn AI Data Engineering ebook for free

team

monthly
annual
$49.99
$499.99
only $41.67 per month
  • five seats for your team
  • access to all Manning books, MEAPs, liveVideos, liveProjects, and audiobooks!
  • choose another free product every time you renew
  • choose twelve free products per year
  • exclusive 50% discount on all purchases
  • renews monthly, pause or cancel renewal anytime
  • renews annually, pause or cancel renewal anytime
  • Learn AI Data Engineering ebook for free