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));
Learn more about creating a table with SQLScript here.
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.
Editor’s note: This post 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.
Comments