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.
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,droptablesDML: These statements are used to manipulate the data,
select,deselect,insert, andupdateDCL: 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, andsmall decimalLarge
blob,clob,nclob, andtextBinary
varbinaryCharacter string
varchar,nvarchar,alphanum, andshorttext.Date
time,date,secondtime, andtimestampExpressions: 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, andor.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:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The following table shows you the CUSTOMERS data:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The following is a REGION table:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The following table shows you details of the PRODUCT table:
|
|
|
|
|
|
|
|
|
Let's see how we can create the preceding tables in SAP HANA:
In SAP HANA studio, right-clicking on your schema (here, HANA_DEMO) will display Open SQL Console; click on it.
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
Tableis 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_FACTStable: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
CUSTOMERtable: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
PRODUCTStable: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
REGIONtable: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
insertqueries: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');
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:
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.