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 from ChatGPT. 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
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.
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.
Question 3: Include INVOICE_DATE column from AP_INVOICES table to the previous query and use INVOICE_DATE column to show me invoices for the last 7 days.
And here is the result from ChatGPT.
As you can see ChatGPT included the additional column in the SELECT Query. ChatGPT was able to also add the WHERE clause to check the current date against the INVOICE_DATE column to restrict the results to the invoices from the last 7 days. Even used the SYSDATE variable!!!
Date Formatting
My next attempt was to ask ChatGPT to change the data format.
Question 4: Change the INVOICE_DATE date format in SELECT statement to MM/DD/YYYY.
And here is the result.
No surprises here. ChatGPT was able to provide me the correct date formatting option. However, what is interesting is that ChatGPT added the column alias as well. That is clever!!
Adding an Additional Table and Column
In my next query I asked ChatGPT to join with an additional column and asked to include a new column in the SELECT clause. It was almost accurate.
Question 5: Join with AP_SUPPLIERS table to get the SUPPLIER_NAME in the select. SUPPLIER_ID is the common column between AP_INVOICES and AP_SUPPLIERS tables.
And here is the result.
ChatGPT was able to include the additional join to the new table. The AI was also able include the SUPPLIER_NAME column in the SELECT statement. However, to my surprise, it went ahead and removed the SUPPLIER_NUM column from the SELECT clause.
I asked ChatGPT to include the SUPPLIER_NUM column as well. And lo and behold the SUPPLIER_NUM column is back!!
Complex Sorting Option
My next attempt was to ask ChatGPT to include a SORT option.
Question 6: Please order the results in descending order of Sum of INVOICE_LINE_AMOUNT and ascending order of INVOICE_NUM.
As you can see I have have asked for two sort options.
Piece of cake!! Both sort options were included correctly.
Add Column Alias
My last attempt was to ask ChatGPT to add a column alias to the last column.
Question 7: Add a column alias for the last column in the SELECT clause to “Invoice Amount”.
The trick here is that I have not specified the column name. I included the statement “last column” in the SELECT clause. And here is the response from ChatGPT.
Real World Test: Does this Actually Work?
I wanted to use the Query framework to check if it actually works. I created a new data model in ERP Cloud. I had to make few changes to the column names and table names. However, I have kept the SQL Query framework the same.
Query Provided by ChatGPT
Here is the modified Query to include Oracle ERP Cloud specific tables and columns. I also changed the date range since the data is pretty old in the instance I am using.
And it works!! Here is the report output from the data model.
Conclusion
I believe this is great for end users and business users, and even for new joiners and beginners learning SQL. You have to ask the write question and ChatGPT can help you formulate the query and syntax. It even provides explanation of the SQL query. Here is an example:
What are your thoughts? What have you tried? I am interested to know what you have been trying. Let me know your comments and feedback. I will be trying other real world use cases as well.