Generating SQL using Natural Language

1. In SAP BTP Cockpit, navigate to your subaccount. Open the left navigation panel and choose Instances and Subscriptions.
2. In the Instances and Subscriptions screen, select SAP HANA Cloud to launch SAP HANA Cloud Central in a new browser tab.
3. On the SAP HANA Cloud Central - All Instances page, locate your SAP HANA Cloud database instance.
4. Open the SQL Console using one of these options:
   • Select the SQL Console icon in the left navigation bar to open the console (not connected by default).
   • In the Actions column for your instance, choose the More (°°°) button and select Open SQL Console to open a console tab connected to that instance.
   • Select the top Ask Joule (Search anything) field and type Open SQL Console. From the drop-down list choose Open SQL Console item.
5. Choose Select an instance to connect to the database instance you want to use. You can use your stored credentials mapped to your SAP BTP user or connect with a different user, provided you have access permissions.
6. Once connected, choose the target schema, enter your SQL in the editor, and run it using the Run options (Run, Run Statement, Run Line, or Run Statement in Background).

In the SAP HANA Cloud Central - SQL Console it is now possible to describe in natural language table definitions and relationships you need. In the SQL Console the Generative AI will create the required SQL statements for you when you use the function Run Prompt as shown in the image.

The previous example was just a very simple example to show how to run a prompt within the SQL Console. For the next examples the following data model for the schema My Company with the tables Departments, Job Titles, and Employees. The columns Department ID and Job Title ID in the table Employees are foreign key references to the tables Departments and Job Titles.

Using the given example data model, I would write the following design document:

12345678910111213141516171819202122232425

Setting Up the My Company Database Schema

Schema, table and column names must be written in snake_case.

First, a new schema called my company needs to be created. Within this schema, three interconnected tables 
must be established.

To begin, an employees table should be created. This table will serve as the main record for all staff 
members. The employee id will act as the unique identifier for each employee. 
The first name and last name columns will be added to store the employee's name. Since employees belong to 
departments and have specific job roles, a department id column should be included to reference the 
departments table, and a job title id column should be included to reference the job titles table. Finally,
a hire date column should be added to track when each employee was hired.

Next, the job titles table should be created to define all possible job positions in the company. Each job 
title will have a unique job title id (a short two-character code) to identify it, and a job title name 
field where the actual name of the position will be stored. The name field is required and cannot be left 
empty.

Finally, the departments table should be set up to organize the company's structure. Each department will 
have  a department id (a short three-character acronym) that uniquely identifies it, a department name 
field for the full name of the department, and a department head field to record who leads that department.

The tables will work together to create a complete employee management system for the My Company schema.

Paste this text in the SAP HANA Cloud Central - SQL Console, and execute it with the Run Prompt option from the Run menu.

The resulting SQL output is:

123456789101112131415161718192021222324252627

CREATE SCHEMA "my_company";

-- Create the job_titles table to store all job positions
CREATE COLUMN TABLE "my_company"."job_titles" (
    "job_title_id" NVARCHAR(2) PRIMARY KEY,
    "job_title_name" NVARCHAR(100) NOT NULL
);

-- Create the departments table to organize company structure
CREATE COLUMN TABLE "my_company"."departments" (
    "department_id" NVARCHAR(3) PRIMARY KEY,
    "department_name" NVARCHAR(100),
    "department_head" NVARCHAR(100)
);

-- Create the employees table with foreign key references
CREATE COLUMN TABLE "my_company"."employees" (
    "employee_id" INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "first_name" NVARCHAR(50),
    "last_name" NVARCHAR(50),
    "department_id" NVARCHAR(3),
    "job_title_id" NVARCHAR(2),
    "hire_date" DATE,
    FOREIGN KEY ("department_id") REFERENCES "my_company"."departments"("department_id"),
    FOREIGN KEY ("job_title_id") REFERENCES "my_company"."job_titles"("job_title_id")
);

The SQL code looks good, and running it gave no syntax error. In the schema my_company the tables job_titles, departments, and employees were created in the correct sequence. The correct sequence was important because of the foreign keys.

For testing the created tables set up we need demo data. It is even possible to generate some demo data using a generative AI. As an example, use the following prompt to generate 5 rows of demo data for the "my_company"."departments":

12345

Use the table "my_company"."departments" with the fields "department_id" which is a acronym for the 
department name which is max 3 characters long, but shorter is fine, "department_name", "department_head" 
and generate 5 rows demo data.

The resulting SQL output is:

1234567891011

INSERT INTO "my_company"."departments" ("department_id", "department_name", "department_head") VALUES 
('IT', 'Information Technology', 'John Smith');
INSERT INTO "my_company"."departments" ("department_id", "department_name", "department_head") VALUES 
('HR', 'Human Resources', 'Sarah Johnson');
INSERT INTO "my_company"."departments" ("department_id", "department_name", "department_head") VALUES 
('FIN', 'Finance', 'Michael Chen');
INSERT INTO "my_company"."departments" ("department_id", "department_name", "department_head") VALUES 
('OPS', 'Operations', 'Emily Davis');
INSERT INTO "my_company"."departments" ("department_id", "department_name", "department_head") VALUES 
('MKT', 'Marketing', 'David Brown');

The generated SQL statements look good, and running these SQL statements populated the table "my_company"."departments" with some demo data. As a quick verify, running SELECT * FROM "my_company"."departments"; in the SQL Console results in:

In this simple example data model, using the generative AI worked very well, but what about more complex scenarios? Use the following best practices when writing a prompt to allow generative AI to generate usable SQL code.

Think of Generative AI in the SQL Console as a brilliant intern who has all the textbook knowledge, but zero understanding of your specific business, data structure, or goals. Without clear instructions this brilliant intern will struggle even with the simple question "Give an overview of the company wide best sales data" as the context on the question isn't clear.

To get the best results when using Generative AI in SQL Console use following the rule: More is Better. The following best practices will help you to write useful prompt to be good result from the generative AI in the SAP HANA Cloud Central - SQL Console.

Provide Context About Your Schema

• Mention relevant table and column names
• Describe relationships between tables
• Specify data types or formats if relevant

Example: "I have a customers table with id, email, signup_date and an orders table with customer_id, order_date, total_amount"

State Your Business Goal

Explain why you need the query, not just what you want, this helps the generative AI make better assumptions about filters and logic.

Example: I need to identify high-value customers for a retention campaign" is better than "Get top customers

Be Specific and Detailed

• Good: "Write a query to find customers who made purchases over $500 in the last 90 days and haven't made any purchases in the past 30 days"
• Avoid: "Get customer data"

Iteratively Refine Your Prompt

Start with a basic request, then ask for refinements like adding filters or sort orders. Build complexity gradually.

Test and Validate

• Always review generated queries before running them on production
• Start with small datasets or LIMIT clauses
• Verify results match your expectations
• Check for NULL handling and edge cases

It is easier to write a Generative AI prompt in your mother tongue than in English because you can express intent and nuance more precisely in the language you know best, and the SAP Generative AI tools can deliver outputs in a chosen language regardless of the prompt’s language.

Currently, the supported languages are English, German, French, Spanish, Portuguese, Japanese, Korean, Chinese (Simplified), Vietnamese, Greek, Polish, Arabic, and Indonesian. Some other languages, like Dutch, work but are not yet officially supported. As I am Dutch, I rewrote the English design document into Dutch and ran it against the Generative AI in SQL Console. Here is the prompt in Dutch:

1234567891011121314151617181920212223242526
Een databaseschema voor Mijn Bedrijf opzetten.
De schema-, tabel- en kolomnamen moeten in het Engels en in snake_case worden geschreven.

Er moet een nieuw schema genaamd mijn bedrijf worden aangemaakt. Binnen dit schema moeten drie onderling 
verbonden tabellen worden opgezet.

Om te beginnen moet er een tabel werknemers worden aangemaakt. Deze tabel zal dienen als het 
hoofdregister voor alle personeelsleden.
Het werknemer id zal fungeren als de unieke identificatie voor elke werknemer.
De kolommen voornaam en achternaam worden toegevoegd om de naam van de werknemer op te slaan. Omdat 
werknemers bij afdelingen horen en specifieke functies hebben, moet er een kolom afdeling id worden opgenomen 
om te verwijzen naar de tabel afdelingen, en een kolom functietitel id om te verwijzen naar de tabel 
functietitels. Ten slotte moet er een kolom datum indiensttreding worden toegevoegd om bij te houden wanneer 
elke werknemer is aangenomen.

Vervolgens moet de tabel functietitels worden aangemaakt om alle mogelijke functies binnen het bedrijf te 
definiëren. Elke functietitel krijgt een uniek functietitel id (een korte code van twee tekens) om deze te 
identificeren, en een veld functietitel naam waarin de werkelijke naam van de functie wordt opgeslagen. Het 
naamveld is verplicht en mag niet leeg worden gelaten.

Ten slotte moet de tabel afdelingen worden opgezet om de structuur van het bedrijf te organiseren. Elke 
afdeling krijgt een afdeling id (een afkorting van drie tekens) dat de afdeling uniek identificeert, een veld 
afdelingsnaam voor de volledige naam van de afdeling, en een veld afdelingshoofd om te registreren wie die 
afdeling leidt.

De tabellen zullen samenwerken om een compleet werknemersbeheersysteem te vormen voor het schema van Mijn Bedrijf.

I won't bore you with the Generative AI SQL output for this Dutch prompt, as it is identical to the English version. Try it out yourself, nobody is stopping you.

SAP HANA Cloud Central's SQL Console uses generative AI to enable users to create, explain, and optimize SQL queries through natural language prompts, significantly reducing technical barriers and accelerating data-driven decision-making for both business users and developers. The solution generates syntactically correct, enterprise-aligned SQL statements while preserving data integrity.

To maximize results, organizations should adopt a structured approach to prompt engineering: provide comprehensive schema context, explicitly state business objectives rather than vague requests. Treating generative AI as a knowledgeable but context-dependent tool, requiring clear guidance to overcome domain-specific blindspots, ensures optimal query generation and organizational value realization.