Introducing Autonomous Enterprise

Discovering SAP Business AI Platform

Investigating Generative AI in SAP HANA Cloud Central

Exploring generative AI in the SQL Console

Using Generative AI to Better Understand SQL

Objective

After completing this lesson, you will be able to use generative AI tools to analyze, explain, and understand SQL code written by other developers, enabling you to quickly comprehend complex queries and improve your own coding practices.

Using Generative AI to Better Understand SQL

In collaborative development environments, encountering complex SQL code written by others is inevitable. Generative AI tools can quickly analyze and explain SQL queries, breaking down intricate logic into understandable components. By using these AI assistants to decipher unfamiliar code, you can reduce learning time, improve collaboration, and adopt best practices from experienced developers.

Using Generative AI to better understand SQL

In the SAP HANA Cloud Central - SQL Console it is now possible to let the generative AI explain SQL code in the SAP HANA Cloud Central - SQL Console. In the SQL Console the Generative AI will explain the selected SQL statements for you when you use the function Explain SQL Statement using AI as shown in the image.

The image shows an example how you can use the Explain SQL Statement using AI functionality in the SQL Console.

As a source I used the SQL code that was generated by the AI in the previous lesson. I selected the all the SQL statements and let the generative AI explain the code to me. The Explain SQL Statement using AI generated a short explanation per SQL statement. The result is show here:

SQL

1234567891011121314151617181920212223242526272829303132333435
AI Explanation Result: 
-- Create a new schema called "my_company" to logically group related tables.
CREATE SCHEMA "my_company";

-- Create a table named "job_titles" in the "my_company" schema to store job positions.
-- The table has a primary key column "job_title_id" and a required column "job_title_name".
CREATE COLUMN TABLE "my_company"."job_titles" (
    "job_title_id" NVARCHAR(2) PRIMARY KEY,
    "job_title_name" NVARCHAR(100) NOT NULL
);

-- Create a table named "departments" in the "my_company" schema to represent company departments.
-- The table has a primary key "department_id" and columns for department name and head.
CREATE COLUMN TABLE "my_company"."departments" (
    "department_id" NVARCHAR(3) PRIMARY KEY,
    "department_name" NVARCHAR(100),
    "department_head" NVARCHAR(100)
);

-- Create a table named "employees" in the "my_company" schema to store employee information.
-- The table includes an auto-incrementing primary key "employee_id", employee names, department and 
-- job references, and hire date.
-- The "department_id" column is a foreign key referencing the "departments" table.
-- The "job_title_id" column is a foreign key referencing the "job_titles" table.
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")
);

In this example, the, as a comment, added descriptions per SQL statement made the whole code easier to understand.

Generate a Data Model Design Document

As a next step, I removed the SQL code from the response and asked Joule:

Code Snippet

1234567891011121314
Write me, in human readable natural language, a data model definition document from the following text:

Create a new schema called "my_company" to logically group related tables.

Create a table named "job_titles" in the "my_company" schema to store job positions. The table has a 
primary key column "job_title_id" and a required column "job_title_name".

Create a table named "departments" in the "my_company" schema to represent company departments. The 
table has a primary key "department_id" and columns for department name and head.

Create a table named "employees" in the "my_company" schema to store employee information. The table 
includes an auto-incrementing primary key "employee_id", employee names, department and job references, 
and hire date. The "department_id" column is a foreign key referencing the "departments" table. The 
"job_title_id" column is a foreign key referencing the "job_titles" table.

Joule responded with the following:

Data Model Definition: my_company Schema

Overview

This data model defines the core organizational structure for a company, capturing information about job positions, departments, and employees. It is designed to support workforce management by establishing clear relationships between employees, the departments they belong to, and the job titles they hold.

Schema

Property	Detail
Schema Name	my_company
Purpose	Logical grouping of all tables related to company organizational and workforce data

Tables

Table 1: job_titles

This table serves as a reference catalog for all job positions that exist within the company. Each record represents a distinct job role that can be assigned to one or more employees. It ensures that job designations are standardized and consistently referenced across the organization.

Column Name	Data Type	Constrains	Description
job_title_id	INTEGER	Primary Key	Unique identifier for each job title
job_title_name	VARCHAR	NOT NULL	The name or label of the job position (e.g., "Software Engineer", "HR Manager")

Table : departments

This table represents the various organizational units or divisions within the company. Each department record captures its identity and leadership, enabling the organization to map employees to their respective business units.

Column Name	Data Type	Constrains	Description
department_id	INTEGER	Primary Key	Unique identifier for each department
department_name	VARCHAR	NOT NULL	The official name of the department (e.g., "Finance", "Engineering")
department_head	VARCHAR	NULLABLE	The name or identifier of the person leading the department

Table : employees

This is the central table of the data model, storing all employee records. It links each employee to their assigned department and job title, and tracks when they joined the company. The auto-incrementing primary key ensures every employee receives a unique system-generated identifier without manual input.

Column Name	Data Type	Constrains	Description
employee_id	INTEGER	Primary Key, Auto-Increment	System-generated unique identifier for each employee
first_name	VARCHAR	NOT NULL	The employee's given name
last_name	VARCHAR	NOT NULL	The employee's family name
department_id	INTEGER	Foreign Key → departments	References the department the employee belongs to
job_title_id	INTEGER	Foreign Key → job_titles	References the employee's assigned job position
hire_date	DATE	NOT NULL	The date on which the employee was officially hired

Relationships

From Table	Column	Relationship	To Table	Column
employees	department_id	Many-to-One	departments	department_id
employees	job_title_id	Many-to-One	job_titles	job_title_id

Key Design Notes

The following design decisions were made:

Auto-Increment for Employee Identity: The employee_id column uses an auto-increment mechanism, meaning the database automatically assigns a new, unique numeric value each time an employee record is inserted. This removes the burden of manually managing unique IDs and prevents duplication.
Referential Integrity via Foreign Keys: The department_id and job_title_id columns in the employees table are defined as foreign keys. This means the database enforces that any value entered in these columns must already exist in the corresponding departments or job_titles table, preventing orphaned or inconsistent records.
Schema-Level Logical Grouping: All three tables are placed within the my_company schema. This acts as a logical namespace that groups related tables together, making it easier to manage access permissions, avoid naming conflicts with other schemas, and clearly communicate that these tables belong to the same business domain.

Summary

This lesson showed how the generative AI can be used to explain SQL code, and how Joule can create a data model definition document using the SQL code explanation.

Next lesson