AI Dev Tips #6: AI Generates Complex DB Queries and Lists
Step-by-step: More complex queries AI-generated from our DB schema
In this step-by-step tutorial #6 in the series, we will continue with:
- Quick recap of links in this series — setup for turbocharging your databases with AI like ChatGPT and Claude 3.5.
- Review the basics of generating SQL queries using ChatGPT. This is what we ended with in the last tutorial
- More complex queries with joins.
- Making lists of queries, and headlines for documentation and collaboration..
- Use cases and best practices
Recap: Building Your Database with ChatGPT
If you’ve been following our series, you already know how we used ChatGPT to create a database schema for an online bookstore. Here’s a quick recap:
- Schema Creation: ChatGPT created a PostgreSQL database schema. I used a book store (and for query examples here), but you could use something different. See: AI Dev Tips 1: Business Idea to PostgreSQL Database Schema in 5 Minutes
- Schema Validation: We copied these statements into pg-sql.com, a tool that helps us validate SQL queries and schemas online.
- Mock Data for Populating the Tables: We used AI-generated fake data to populate our tables. This helped us create realistic test scenarios to validate our SQL queries. AI Dev Tips #4 — ChatGPT Creates Fake Data for our DB Schema
- Basic Queries: We tried some techniques for basic queries including both with and without gherkin syntax blocks. See: AI Dev Tips #5: AI/ChatGPT Postgres Query Generation from Schema
NEXT: Now we are going to do:
- More Complex Queries: Try some more complex queries, generated by ChatGPT using only our schema.
- Make a List of Queries: For collaboration, brainstorming and documentation we’re going to make a list of queries
If you want to follow along Step-by-Step I do recommend getting setup with the articles above mentioned. Create a schema, generate fake data and get familiar with the pg-sql.com tool.
Each article above takes only 5–10 minutes to complete. It’s worth it!
Defining Scenarios for Your Queries
To make the most of ChatGPT, start by defining the scenarios you need to query.
Let’s do first without Gherkins so it’s easier to understand initially. For example:
- Find the top 3 most expensive products in the database.
- Calculate the average number of items in all orders.
I have a couple new Ebooks you may want to check out:
Ebook: Buy my Cloud Audit ebook / store: SystemsArchitect Store
Warmup: Get Easy Queries from ChatGPT
Once you have your scenarios, you can input them into ChatGPT to generate the corresponding SQL queries.
We’re starting with easier queries but will get more complex with each one.
Example Scenario: Find the top 5 most expensive products.
ChatGPT 4o prompt:
When I request the five most expensive products, I should receive a list containing exactly five products sorted in descending order of price
(Submit to ChatGPT)
ChatGPT Output:
Output:
pg-sql.com > Run:
ChatGPT 4o prompt:
Calculate the average number of items in all orders.
(Submit to ChatGPT)
ChatGPT Output:
pg-sql.com > Run:
Same with Gherkin syntax:
Feature: Calculate the average number of items per order
Scenario: Calculate the average number of items in all orders
Given the database contains a list of all orders and their items
When I request the average number of items per order
Then I should receive the average calculated by dividing the total number of items by the total number of ordersChatGPT Input and Output:
Gives the same result as the informal syntax above.
Reminder: Testing Queries with pg-sql.com
After generating your queries, test them in an online SQL console like pg-sql.com to ensure they work as expected.
- Run the Query: Paste the SQL query into the console and execute it.
- Check the Results: Ensure the output matches your expectations (e.g., a list of the top 5 most expensive products).
Handling More Complex Queries
As you become more comfortable with ChatGPT, you can tackle more complex scenarios:
Example Scenario: The product analyst needs: “Total Revenue and Number of Orders by Shipping Address” so they can pass this over to the marketing team.
ChatGPT 4o prompt:
Using my same schema, I need the query for Total Revenue and Number of Orders by Shipping Address
(Submit to ChatGPT)
ChatGPT Output:
pg-sql.com > Run:
Let’s try a scenario with some joins
ChatGPT 4o prompt:
Get all reviews along with the book title and reviewer username
Output:
pg-sql.com > Run:
As you become more proficient, you can use ChatGPT to generate even more advanced queries:
We’re going to handle more advanced queries in the next tutorial.
Creating Lists of Queries
One thing that is pretty amazing is making a long list of queries.
Just think of it! We could probably get a list of any possible query!!!!
ChatGPT 4o prompt
Given my schema I gave you, please give me 25 useful Aggregation and Grouping queries.
(Submit to ChatGPT)
ChatGPT Output:
This is an excerpt! It gave me 25 queries!
Next I ask for “just the headline”
Output:
- Count Total Number of Users
- Count Total Number of Books
- Count Total Number of Orders
- Count Total Number of Order Items
- Count Total Number of Reviews
- Sum of All Total Amounts in Orders
- Average Rating for Each Book
- Total Number of Books Sold for Each Book
- Total Revenue Generated by Each Book
- Number of Orders Placed by Each User
- Total Amount Spent by Each User
- Number of Books in Each Genre
- Total Stock Quantity for Each Book
- Average Price of Books in Each Genre
- Total Revenue Generated Per Genre
- Number of Reviews Per Book
- Number of Orders with Each Status
- Number of Orders Placed on Each Date
- Average Total Amount of Orders Per User
- Maximum and Minimum Order Amount for Each User
- Count of Each Rating Given in Reviews
- Number of Books Written by Each Author
- Total Number of Books Ordered Per Day
- Number of Users Who Have Placed More Than One Order
- Total Revenue and Number of Orders by Shipping Address
Use your imagination!
- How could you use this for an app?
- To offer new features, or
- To make a dashboard?
- To save time helping your Analytics team?
- For data-mining?
We are ONLY JUST GETTING STARTED!
Tips and Best Practices
- Mistakes: While ChatGPT is a powerful tool, it can make mistakes. Always validate the generated queries.
- Start simple: Begin with basic queries to understand how ChatGPT formulates SQL based on your prompts.
- Iterate and refine: If the initial query isn’t quite right, refine your prompt and try again.
- Collaborate on Scenarios: Work with product managers or UX designers to create detailed Gherkin scenarios, which can help generate more accurate queries.
- Validate thoroughly: Always test the queries in a real SQL environment to catch any errors or inconsistencies.
In this series — check out #7 next:
I have a couple new Ebooks you may want to check out:
Ebook: Buy my Cloud Audit ebook / store: SystemsArchitect Store
About me
I’m a cloud architect, senior developer and tech lead who enjoys solving high-value challenges with innovative solutions.
I’m always open to discussing projects. If you need help, have an opportunity or simply want to chat, you can reach me on X/Twitter @csjcode and same username gmail.
I’ve worked 20+ years in software development, both in an enterprise setting such as NIKE and the original MP3.com, as well as startups like FreshPatents, SystemsArchitect.io, API.cc, and Instantiate.io.
My experience ranges from cloud ecommerce, API design/implementation, serverless, AI integration for development, content management, frontend UI/UX architecture and login/authentication. I give tech talks, tutorials and share documentation of architecting software. Also previously held AWS Solutions Architect certification.
Cloud Ebook Store — check for 50% off sales cloud architect and engineering books, “Cloud Metrics” (800 pages+) and “Cloud Audit” (800 pages+) and more — https://store.systemsarchitect.io
And my website:
Recently I’m working on Instantiate.io, a value creation experiment tool to help startup planning with AI. I write a reference manual on cloud metrics.
Also, an enthusiast of blockchain, I’m active working on applications in the innovative Solana blockchain ecosystem.
Thanks for reading ! 🥰 If you like this topic, please give some claps (it’s free), bookmark, add to your list and share with colleagues. I follow back. I’m here to meet people, learn, and contribute. 🥰
