Common SQL Statements for SAP HANA

Let’s discuss some of the commonly used SQL statements to define SAP HANA tables.

CREATE SCHEMA

CREATE SCHEMA creates a new schema in the database. The syntax for this statement is as follows:

CREATE SCHEMA <schema_name> [OWNED BY <user_name>]

In this syntax, <schema_name> specifies the schema name, and <user_name> specifies the name of the schema owner. If <user_name> is omitted, the current user is the owner of the schema. An example of this syntax in action is as follows:

CREATE SCHEMA CHICKEN_WINGS;

SET SCHEMA

SET SCHEMA changes the current schema for the session. When schema is set for a session, objects in the specified schema (i.e., tables, views, etc.) can be referenced without the schema name. Objects in other schemas have to be referenced with the schema name, for example, MY_SCHEMA.MY_TABLE. The syntax for this statement is as follows:

SET SCHEMA <schema_name>

In this syntax, <schema_name> specifies the name of the schema. An example of this syntax in action is as follows:

SET SCHEMA CHICKEN_WINGS;

CREATE TABLE

CREATE TABLE creates a new table in the database. The syntax for this statement, in its simplest form, is as follows:

CREATE [<table_type>] TABLE <table_name> (

<column_name> <data_type>,

<column_name> <data_type>,...

PRIMARY KEY (<column_name>,<column_name>..))

In this syntax, <table_type> defines the type of table storage organization such as COLUMN, ROW, HISTORY COLUMN, and so on. The default value is ROW. We’ll be using COLUMN tables for defining our data model.

In addition, <table_name> and <column_name> specify the name of the table and columns to be created, respectively. The <table name> and <column_name> attributes are case sensitive if specified using double quotes (e.g., “Airport” instead of Airport).

Next, <data_type> specifies the data type of the columns in the table. Commonly used data types are INTEGER, NVARCHAR(x), DECIMAL(p,s), DATE, TIME, and so on. Refer to the SAP HANA SQL and System Views Reference guide for a complete list of supported data types. An example of this syntax in action is as follows:

CREATE COLUMN TABLE AIRPORT (

CODE NVARCHAR(3),

NAME NVARCHAR(50),

CITY NVARCHAR(20),

PRIMARY KEY (CODE));

COMMENT ON

COMMENT ON adds or removes a comment to an object in the database. The syntax for this statement is as follows:

COMMENT ON <object_type> <object_name> IS <comment>

In this syntax, <object_type> specifies the type of object, that is, table, view, or column; <object_name> specifies the name of the object to add the comment to; and <comment> specifies the comment itself. If NULL is specified, then any existing comment is dropped. An example of this syntax in action is as follows:

-- Comment on 'Airport' Table

COMMENT ON TABLE AIRPORT is 'Airport Details';

-- Comment on 'CODE' Column of 'AIRPORT' table

COMMENT ON COLUMN AIRPORT.CODE is 'Airport IATA Code';

ALTER TABLE

ALTER TABLE alters the definition of a table, that is, to add/drop table columns, create/ drop constraints such as foreign keys, and so on. The syntax to add a foreign key constraint is as follows:

ALTER TABLE <table_name> ADD CONSTRAINT <constraint_name>

FOREIGN KEY

(<referencing_column_name> [{,<referencing_column_name>}...])

REFERENCE (<table_name>(<referenced_column_name>[{,<referenced _column_

name>}...]);

An example of this syntax in action is as follows:

-- AIRPORT.CODE is referenced from CONNECTION.FROMAP column

ALTER TABLE CONNECTION ADD CONSTRAINT FK_CONNECTION_AIRPORT_FROM

FOREIGN KEY (FROMAP) REFERENCES AIRPORT(CODE)

INSERT INTO

INSERT INTO adds a record to a table, and the syntax is as follows:

INSERT INTO <table_name> [<column_list>] VALUES (

<column_value>, <column_value>, ...)

In this syntax, the optional <column_list> specifies the list of columns for which values are provided. If <column_list> isn’t provided, values must be provided for all columns. In addition, <column_value> specifies the value for each column.

An example of this syntax in action is as follows:

INSERT INTO AIRPORT VALUES ('YYZ','Lester B. Pearson International',

'Toronto','CANADA','EST',43.677718,-79.624820);

SELECT

SELECT retrieves information from the database table, and the syntax in its simplest form is as follows:

SELECT <column_name> [,column_name, column_name] FROM <table_name>;

In this syntax, <column_name> specifies the list of columns to retrieve data from the <table_name> table. An example of this syntax in action is as follows:

-- Retrieves data for all columns

SELECT * FROM AIRPORT;

-- Retrieves data for selected columns

SELECT CODE, NAME, CITY, COUNTRY FROM AIRPORT;

Conclusion

These are just a few of the commonly used SQL statements to define SAP HANA tables. You will need to have a full understanding of these statements to do well on SAP certifications such as the SAP HANA Application Associate Certification exam. Refer to SAP HANA SQL and System Views Reference guide (https://bit.ly/2sR5K4u) for the complete list of SQL statements supported by SAP HANA.

This content was originally posted on the SAP PRESS Blog and has been adapted from a section of the book SAP HANA XSA: Native Development for SAP HANA by Francesco Alborghetti, Jonas Kohlbrenner, Abani Pattanayak, Dominik Schrank, and Primo Sboarina. Used with permission of SAP PRESS. All rights reserved.

SAP Community