SAP HANA SQL – An Ultimate Cheat Sheet of SQL Statements in SAP HANA

With this introductory SAP HANA SQL tutorial, we will start with learning the SQL implementation on SAP HANA database. SQL is an integral part of working with SAP HANA. So, let us quickly begin with learning some basics of SQL in SAP HANA.

Introduction to SAP HANA SQL

SQL stands for Structured Query Language which interacts with relational databases. It is a standardized language that is used to store, manipulate and retrieve data from the different relational databases such as Oracle, MySQL, etc.

SAP HANA users can create SQL scripts and apply complex business logic to the database manipulating data as per their requirement.

For using SQL in SAP HANA, the basic knowledge of SQL includes SQL data types, functions, expressions, operators, sequences, triggers, synonyms, script, explain plans, etc.

You can add comments in a SQL script to make notes and improve the understanding of the script. The comments can be added in two ways; either by adding the comment after double hyphens “–” or between “/*” and “*/”. Although, the SQL parser ignores the comments.

Types of SQL Statements in SAP HANA

SAP HANA supports various SQL statements such as:

1. Data Manipulation Language Statements

We use the data manipulation language (DML) within schema objects to manipulate and manage data in them. Generally used DML keywords are SELECT (to retrieve data from database), INSERT (to add data into a data table) and UPDATE (to update data that already exists in a table).

2. Data Definition Language Statements

We use the DDL statements and keywords to define the structure of data in databases and schemas. Commonly used DDL keywords are CREATE (creates objects in the database), ALTER (alters the database structure), DROP (deletes or drops objects from the database).

3. Data Control Language Statements

We use the DCL statements to control user access and privileges to the database. Commonly used DCL keywords are GRANT (to give access privilege to database to the users) and REVOKE (to take back the access privileges given using GRANT command).

4. Backup and Recovery Statements

Backup and recovery statements enable users to perform backup and recovery operations on the HANA database.

5. Access Control Statements

Access control statements allow database administrators to create, alter and drop access to the HANA database.

6. Data Import Export Statements

Data import export statements enable import and export operations on data from the HANA database.

7. JSON Document Store Statements

These statements enable users to use the SAP HANA database in the form of a JSON document store.

8. Encryption Statements

Encryption statements allow database administrators to work with encrypted data in the HANA database.

9. Tenant Database Management Statements

Tenant database management statements manage the SAP HANA tenant databases.

10. Procedural Statements

The procedural statements allow users to manage both the system and user-defined procedures for data in HANA database.

11. Transaction Management Statements

These statements manage transactions in the SAP HANA database.

12. Session Management Statements

We use these statements to manage database sessions.

13. System Management Statements

These statements enable users to manage system configuration settings in the HANA database.

14. Workload Management Statements

These statements manage both the workload classes and mappings.

Key Tasks of SQL in SAP HANA

In SAP HANA, we use SQL statements to perform five basic tasks as mentioned below:

• Data manipulation
• System management
• Schema definition and manipulation
• Session management
• Transaction management

Significance of SQL in SAP HANA

SQL and SQL scripting holds great importance in SAP HANA because a database only understands a standardized language like SQL. Whenever an SAP HANA user wishes to work with data stored in the HANA database, the interaction happens through SQL.

Using SQL keywords, SQL expressions, and SQL functions, one can efficiently access data from the databases, transform it, and perform other management tasks on the system.

In addition to this, while making information models, SQL plays an important role. Especially in creating SAP HANA calculation views, SQL scripting is used to create procedures and apply complex logic and calculations to the data residing in the database.

Summary

This concludes our introductory discussion on SQL in SAP HANA. In the upcoming tutorials, we will cover different elements of SQL used in SAP HANA.

Did you like our explanation? We will wait for your feedback and suggestions. See you in our next tutorial!