Skip to main content

Talking to Oracle Database in plain English

A bit more complex SELECT AI query

My SQL beginnings

It was approximately 35 years ago (gosh, that long!?) when I first encountered SQL—Structured Query Language, for those who might not be 100% sure what I am talking about. It happened during labs at faculty when we visited IBM's training center in Radovljica, Slovenia, to learn and experiment with this (at least for me) new programming language. They called it a 4th generation language. With its roots in Boolean algebra and simple structure, we were told that it was almost like natural language (English, of course—who would have thought one day you would use Slovenian to query databases?). With all those SELECTs, FROMs, WHEREs, GROUP BYs, HAVINGs, ORDER BYs, etc., it actually sounded really cool and easy.

My Select AI beginnings

Fast forward to today, the other day I finally found some time to explore something different from my usual daily work. What caught my attention was one of the latest Oracle blogs: Introducing Select AI - Natural Language to SQL Generation on Autonomous Database. This blog discusses the use of generative AI with Large Language Models (LLMs) such as ChatGPT, Cohere, or Azure OpenAI Service integrated with Oracle Autonomous Database.

More details can be found in Oracle Autonomous Database documentation: Use Select AI to Generate SQL from Natural Language Prompts.

Let's talk now

Enough said. Let's try the simplest query of all and ask my database: what is the total revenue recorded?

select ai what is total revenue

And the response is?

Simple SELECT Ai Query

For experienced ChatGPT users, this might not be very surprising. But for me, it was impressive. The response obviously comes from the underlying database table and is correct. Encouraging.

Let's dig a bit deeper and try to get a more complex result.

In the next query, I try to ask a question that requires a join. For example:

select ai what is the revenue by customer segments in the last year

A bit more complex SELECT AI query

Here, the result is more complex and can be displayed in various formats, for example as a graph.

I believe everyone has figured out by now that the magic phrase is SELECT AI, which is the secret ingredient that enables us to query data using natural language.

So what is happening behind the scenes? I'll borrow the following image from the blog mentioned above, written by Mark Hornick:

SELECT AI - what's happening

The SELECT AI natural language query uses metadata from the schema identified in the user's profile. This information is provided to the LLM (for example, ChatGPT) to create a proper SQL query. The query is then executed within the database, and the results are fed back through the LLM to generate the answer if requested (using the narrate option).

As already indicated, the same natural language query can return different results depending on the keyword added to the SELECT AI syntax.

For example, if the narrate keyword is used in the query above:

select ai narrate what is the revenue by customer segments in the last year

The result is a narrative explanation:

A bit more complex SELECT AI query using narrate keyword

If we want to see the SQL that is generated, then the showSQL keyword can be used:

select ai showSQL what is the revenue by customer segments in the last year

The result displays the generated SQL query:

A bit more complex SELECT AI query using showSQL keyword

If you simply want to execute the query, then the keyword is runsql:

select ai runsql what is the revenue by customer segments in the last year

The result is the same as when no keyword is used:

A bit more complex SELECT AI query using runsql keyword

The last keyword option, chat, is probably the most interesting as it generates a conversational AI response:

select ai chat what is the revenue by customer segments in the last year

The result is more explanatory, similar to when no keyword was used:

A bit more complex SELECT AI query using chat keyword

We can see that in this case, the answer is more detailed. It seems that the query went to the LLM and returned a prescription of what needs to be done, not just the result itself.

A quick idea: we can ask what a SELECT statement actually does. Remember the one generated earlier using the showSQL keyword? I guess now we are more in the ChatGPT territory.

select ai chat explain the following SQL
    SELECT
        C.CUST_SEGMENT,
        SUM(B.REVENUE) AS TOTAL_REVENUE
    FROM OABOOTCAMP.D_CUSTOMERS C INNER JOIN OABOOTCAMP.F_BILL_REV B ON C.CUST_NUMBER = B.CUST_NUMBER
    WHERE
        B.TIME_BILL_DT >= ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)
    GROUP BY
        C.CUST_SEGMENT

The result is a detailed explanation:

A bit more complex SELECT AI query using chat keyword

Let's have some more ...

... and check some additional questions:

Top 5 product by revenue
Top 5 product by revenue comparing previous years
Compare years and show growth - relative
Customer distribution by segments

Well?

To wrap up

My first encounter with LLM integration in a database has been quite positive. While it’s important to be cautious of hallucinations and always verify generated results, the practical value of being able to query databases using natural language is clear.

Rome was not built in a day, and it’s common that not everything works perfectly at first. I still remember crashing that poor IBM mainframe within the first five minutes during those early labs, which ended the session abruptly. Once again, we proved that 3rd generation programming languages ruled the world back then.

Today, however, we stand at the frontier of a new era where LLMs and AI are transforming how we interact with data. The potential is enormous, and I am excited to see where this journey will take us.

And still, look at us now!