In my previous article, I shared my experience with ChatGPT and described how I was able to generate working SQL queries using the tool. If you missed it, you could read the article to learn more about my journey exploring ChatGPT.
In today’s article I want to show you how you can get more advanced SQL Queries. I will also show you the proof that it works great with a real-world example.
What I also want to highlight is that as long as you ask the right questions, you will be able to get a fully working SQL Query from ChatGPT.
Starting Slow: Joining Two Tables
I wanted to see how it can process the question and if ChatGPT is able to give me a valid SQL statement.
Question 1: I have two tables AP_INVOICES and AP_INVOICE_LINES. The tables are connected using a common column called INVOICE_ID. Write a SQL Script to get INVOICE_NUM and SUPPLIER_NUM from AP_INVOICES table, and Sum of INVOICE_LINE_AMOUNT from AP_INVOICE_LINES table.
And here is the result from ChatGPT
SELECT i.INVOICE_NUM, i.SUPPLIER_NUM, SUM(l.INVOICE_LINE_AMOUNT)
FROM AP_INVOICES i
JOIN AP_INVOICE_LINES l ON i.INVOICE_ID = l.INVOICE_ID
GROUP BY i.INVOICE_NUM, i.SUPPLIER_NUM;Filtering Data on Grouped Column
In the next question I wanted to see if it can include advanced SQL filters like “HAVING” clause.
Question 2: Refine the previous query to show me records where Sum of INVOICE_LINE_AMOUNT is greater than 1000.
And here is the result from ChatGPT.
SELECT i.INVOICE_NUM, i.SUPPLIER_NUM, SUM(l.INVOICE_LINE_AMOUNT)
FROM AP_INVOICES i
JOIN AP_INVOICE_LINES l ON i.INVOICE_ID = l.INVOICE_ID
GROUP BY i.INVOICE_NUM, i.SUPPLIER_NUM
HAVING SUM(l.INVOICE_LINE_AMOUNT) > 1000;As you can see ChatGPT was able to correctly identify the need and included the HAVING clause to filter the result. It is amazing!!
Adding Additional Column to the SELECT Clause
In the next question I asked ChatGPT to include an additional column and filter the results for the last 7 days.
