Introducing SAP HANA SQL

As stated, you will not learn SQL as a whole new concept, but will just revise the traditional SQL concepts at a glance and focus on a few new topics that are of importance from SAP HANA perspective. Our key focus here will be on the SAP HANA SQL script, creating procedures, and learning to create SAP HANA specific JOINS.

Classical SQL

SQL is used to retrieve, store, and manipulate data in the database. SQL can be studied under three subheads:

These subheads are explained as follows:

• DDL: These statement that are used to define the data: create, alter, drop tables
• DML: These statements are used to manipulate the data, select, deselect, insert, and update
• DCL: These statements that are used to control the table, grant, and revoke

The followings are the elements of SQL:

• Identifiers: These are used to represent names in SQL statements including table/view name, column name, username, role name and so on. There are two types of Identifiers: ordinary and delimited.
• Data types: These define the characteristics of the data and its value. Data types in SQL are as follows:

  Categories	Data type
  Numeric	float, real, integer, decimal, double, tinyint, small int, and small decimal
  Large	blob, clob, nclob, and text
  Binary	varbinary
  Character string	varchar, nvarchar, alphanum, and shorttext.
  Date	time, date, secondtime, and timestamp

• Expressions: These are clause evaluated to return values. We have different types of expressions in SQL. For example, if…then…..else (case expression) or nested queries (Select (Select ……)).
• Functions: These are used in expressions for retrieving information from the database. We have a number of functions and data type conversion functions. The number functions take numeric values or alphanumeric/strings with numeric character values and return numeric values, whereas, data type conversion functions are used to convert arguments from one data type to another. For example, to_alphanum, concat, current_date, and so on.
• Operators: These are used for value comparison, assigning values, or can also be used for calculation. We have different types of operators like Unary, Binary, arithmetic, and string operators to name a few. For example, +, =, subtraction, and or.
• Predicates: A predicate is specified by combining one or more expressions or logical operators and returning one of the following logical or truth values: true, false, or unknown. Examples are null, in, and like.

In the upcoming chapters, we will learn how to work with SAP HANA studio and open SQL editor, so as to complete the concepts. I will show you how we work with the preceding SQL concepts. For our examples and exercises, we will use the following tables. We will create more tables in further chapters as we progress.

The following table shows you the sales_facts:

PRODUCT_KEY	REGION_KEY	AMOUNT_SOLD	QUANTITY_SOLD
01	100	50000	500
02	200	60000	600
03	300	20000	200

The following table shows you the CUSTOMERS data:

CUSTOMER_KEY	CUST_LAST_NAME	CUST_FIRST_NAME
C1	Mehta	Yatin
C2	Aguirre	Tomas
C3	Huber	Ralf

The following is a REGION table:

REGION_ID	REGION_NAME	SUB_AREA
100	Europe	Germany
200	Asia	Japan
300	US	Northfields

The following table shows you details of the PRODUCT table:

PRODUCT_KEY	PRODUCT_NAME
01	GasKit
02	RubberWasher

Let's see how we can create the preceding tables in SAP HANA:

1. In SAP HANA studio, right-clicking on your schema (here, HANA_DEMO) will display Open SQL Console; click on it.
   
2. We will cover some of the following SQL queries to create the tables:

   Create a schema first, if it hasn't already been created for you—HANA_DEMO; you can choose any name.

   A database schema is the skeleton structure that represents the logical view of the entire database (objects such as tables, views, and stored procedures). It defines how the data is organized and how the relations among them are associated. It formulates all the constraints that are to be applied on the data, whereas Table is one of the objects contained in schema. It is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows:

   CREATE SCHEMA "HANA_DEMO";
   GRANT SELECT ON SCHEMA HANA_DEMO TO _SYS_REPO WITH GRANT OPTION; if you do not run Grant , later when you will activate your views it will give you erros.
   

   The following command creates the SALES_FACTS table:

   CREATE  COLUMN TABLE "HANA_DEMO"."SALES_FACTS"(
   "PRODUCT_KEY" INTEGER NOT NULL,
   "REGION_KEY" INTEGER NOT NULL,
   "AMOUNT_SOLD" DECIMAL NOT NULL,
   "QUANTITY_SOLD" INTEGER NOT NULL,
   PRIMARY KEY ("PRODUCT_KEY","REGION_KEY") );
   

   The following command creates the CUSTOMER table:

   CREATE  COLUMN TABLE "HANA_DEMO"."CUSTOMER"(
   "CUSTOMER_KEY" VARCHAR(8) NOT NULL,
   "CUST_LAST_NAME" VARCHAR(100) NULL,
   "CUST_FIRST_NAME" VARCHAR(30) NULL,
   PRIMARY KEY ("CUSTOMER_KEY ") );
   

   The following command creates the PRODUCTS table:

   CREATE  COLUMN TABLE "HANA_DEMO"."PRODUCTS" (
   "PRODUCT_KEY" INTEGER NOT NULL,
   "PRODUCT_NAME" VARCHAR(50) NULL,
   PRIMARY KEY ("PRODUCT_KEY") );
   

   The following command creates the REGION table:

   CREATE  COLUMN TABLE "HANA_DEMO"."REGION"(
   "REGION_ID" INTEGER NOT NULL,
   "REGION_NAME" VARCHAR(100) NULL,
   "SUB_AREA" VARCHAR(30) NULL,
   PRIMARY KEY ("REGION_ID") );
   

   The following are sample insert queries:

   insert into "<YOUR SCHEMA>"."TABLE NAME" values(columns1,Columns2,..,); 
   insert into "HANA_DEMO"."SALES_FACTS" values(01,100,50000,500); 
   insert into "HANA_DEMO"."PRODUCTS" values(01,'GasKit');
   insert into "HANA_DEMO"."REGION" values(01,'Europe','Germany'); 
   

   Tip

   I am inserting single values, but you can insert or re-run the query with different values or download the Excel file from our website for demo data.

3. After executing the scripts, you should have three tables created. If there are no tables, try right-clicking on your schema and then refresh it.

   In the following screenshot, you can see the tables we just created under the HANA_DEMO schema:

   

Tip

We need to Grant schema SELECT rights to _SYS_REPO user.

In SQL, the editor of our schema needs to execute the following command line:

GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;
GRANT SELECT ON SCHEMA HANA_DEMO TO _SYS_REPO WITH GRANT OPTION

If we miss this step, an error will occur when you activate your views later.