Create Database Objects with the SQL Console

1. In SAP HANA Cloud Central, open a SQL console connected to your SAP HANA database instance.

   

   The connected user is DBADMIN. In this step, you will create USER1 and USER2, which are used throughout the rest of this tutorial group.

2. Execute the following SQL.

   SQL Copy

   CREATE USERGROUP HOTEL_USER_GROUP SET PARAMETER 'minimal_password_length' = '8', 'force_first_password_change' = 'FALSE';
   CREATE USER USER1 PASSWORD Password1 no force_first_password_change SET USERGROUP HOTEL_USER_GROUP;
   CREATE USER USER2 PASSWORD Password2 no force_first_password_change SET USERGROUP HOTEL_USER_GROUP;
   --SELECT * from "PUBLIC"."M_EFFECTIVE_PASSWORD_POLICY" where USER_NAME = 'USER1';
   --SELECT * FROM USERS;
   
   GRANT CREATE SCHEMA TO USER1;
   CREATE ROLE HOTEL_ADMIN;
   CREATE ROLE HOTEL_READER;
   
   GRANT TRUST ADMIN TO HOTEL_ADMIN; -- required to create a PSE
   GRANT CERTIFICATE ADMIN TO HOTEL_ADMIN; --required to create a certificate
   GRANT CREDENTIAL ADMIN TO HOTEL_ADMIN; --required to create a credential
   GRANT EXPORT TO HOTEL_ADMIN; --required to enable export of data
   GRANT IMPORT TO HOTEL_ADMIN; --required to enable import of data
   GRANT CREATE REMOTE SOURCE TO HOTEL_ADMIN; --allow setting the PSE purpose to REMOTE SOURCE and to create REMOTE SOURCES
   GRANT RESOURCE ADMIN TO HOTEL_ADMIN; --allow viewing of diagnostic files
   GRANT CATALOG READ TO HOTEL_ADMIN;   --allow access to system views
   GRANT INIFILE ADMIN TO HOTEL_ADMIN;  --allow altering of system settings
   
   GRANT HOTEL_ADMIN TO USER1;
   GRANT HOTEL_READER TO USER2;
   
   CONNECT USER1 PASSWORD Password1;
   CREATE SCHEMA HOTELS;
   GRANT ALL PRIVILEGES ON SCHEMA HOTELS TO HOTEL_ADMIN;
   GRANT SELECT ON SCHEMA HOTELS TO HOTEL_READER;
   
   --view the objects owned by USER1
   SELECT SCHEMA_NAME, OBJECT_NAME, OBJECT_TYPE, OWNER_NAME FROM "PUBLIC"."OWNERSHIP" WHERE OWNER_NAME = 'USER1';

   Show all 30 lines Show less

   A schema provides a way to group database objects together.

   Privileges can be assigned to users directly or, as a better practice, assigned to a role that has a defined set of privileges.

   It is recommended not to use the DBADMIN user for day-to-day operations in production environments. For additional details see Deactivate the DBADMIN User.

   For additional details on the commands above, consult CREATE USERGROUP Statement, CREATE USER Statement, CREATE ROLE Statement, and GRANT Statement.

   Users and roles can also be managed in SAP HANA Cloud Central under the User & Role Management tile in the instance detail panel. Additional details can be found at User and Role Management.

   

3. Open a new SQL console tab by pressing the + in the tab bar. This time we wish to connect with USER1 and to set the schema to HOTELS so we will not use the cached credentials (DBADMIN).

   Click on Select an Instnace and choose the SAP HANA database.

   Choose Connect …

   

   Specify USER1 and Password1.

   Click on Current Schema in the tab bar and choose HOTELS.

   

   Alternatively, the last two actions could have performed the below SQL.

   SQL Copy

   CONNECT USER1 PASSWORD Password1;
   SET SCHEMA HOTELS;

   The details of the connection also be found in the connection details dialog.

   

4. The following example demonstrates the privilege assignments that were previously created. USER1 was granted the HOTEL_ADMIN role which has all privileges the HOTELS schema while USER2 was granted the HOTEL_READER role which has only select privileges on the HOTELS schema. Execute the below SQL and notice that USER2 does not have the privilege to perform an insert. Press Skip on the SQL Execution Error dialog so that execution continues and the TEST table is removed.

   SQL Copy

   CREATE TABLE TEST(
     myValue NVARCHAR(50)
   );
   
   --USER1 has all privileges on the HOTELS schema
   SELECT * FROM TEST; --succeeds
   INSERT INTO TEST VALUES('Value1'); --succeeds
   
   --USER2 can only select
   CONNECT USER2 PASSWORD Password2;
   SET SCHEMA HOTELS;
   SELECT * FROM TEST; --succeeds
   INSERT INTO TEST VALUES('Value2'); --fails
   
   --Remove the unused table
   CONNECT USER1 PASSWORD Password1;
   SET SCHEMA HOTELS;
   DROP TABLE TEST;

5. The following statements can be used to delete the schema, users, user group, and roles once the tutorials are complete.

   Do not execute the below until the tutorials are complete.

   SQL Copy

   -- DO NOT EXECUTE THIS UNLESS YOU WISH TO CLEAN UP THE TUTORIAL OBJECTS
   CONNECT DBADMIN PASSWORD myPassword;
   DROP USER USER1 CASCADE;
   DROP USER USER2 CASCADE;
   DROP USERGROUP HOTEL_USER_GROUP;
   DROP ROLE HOTEL_ADMIN;
   DROP ROLE HOTEL_READER;

1. Create tables that represent a basic hotel administration system by running the SQL below in a SQL console connected to USER1 in the HOTELS schema.

   SQL Copy

   --CONNECT USER1 PASSWORD Password1;
   --SET SCHEMA HOTELS;
   
   CREATE COLUMN TABLE HOTEL(
       hno INTEGER PRIMARY KEY,
       name NVARCHAR(50) NOT NULL,
       address NVARCHAR(40) NOT NULL,
       city NVARCHAR(30) NOT NULL,
       state NVARCHAR(2) NOT NULL,
       zip NVARCHAR(6),
       location ST_Point(4326)
   );
   
   CREATE COLUMN TABLE ROOM(
       hno INTEGER,
       type NVARCHAR(6),
       free NUMERIC(3),
       price NUMERIC(6, 2),
       PRIMARY KEY (hno, type),
       FOREIGN KEY (hno) REFERENCES HOTEL
   );
   
   CREATE COLUMN TABLE CUSTOMER(
       cno INTEGER PRIMARY KEY,
       title NVARCHAR(7),
       firstname NVARCHAR(20),
       name NVARCHAR(40) NOT NULL,
       address NVARCHAR(40) NOT NULL,
       zip NVARCHAR(6)
   );
   
   CREATE COLUMN TABLE RESERVATION(
       resno INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
       rno INTEGER NOT NULL,
       cno INTEGER,
       hno INTEGER,
       type NVARCHAR(6),
       arrival DATE NOT NULL,
       departure DATE NOT NULL,
       PRIMARY KEY (
           "RESNO", "ARRIVAL"
       ),
       FOREIGN KEY(cno) REFERENCES CUSTOMER,
       FOREIGN KEY(hno) REFERENCES HOTEL
   );
   
   CREATE COLUMN TABLE MAINTENANCE(
       mno INTEGER PRIMARY KEY,
       hno INTEGER,
       description NVARCHAR(100),
       date_performed DATE,
       performed_by NVARCHAR(40)
   );
   
   COMMENT ON TABLE HOTELS.CUSTOMER IS 'This table contains customer details such as their title, first and last name, or company name, and address';
   COMMENT ON TABLE HOTELS.HOTEL IS 'This table contains information address details for each hotel';
   COMMENT ON TABLE HOTELS.ROOM IS 'This table contains hotel room details including the type of room, the number of availalbe rooms of that type, and the price';
   COMMENT ON TABLE HOTELS.MAINTENANCE IS 'This table contains maintenance and renovation records of past and future work.  Details on the work to be performed, the date and the vendor are included';
   COMMENT ON TABLE HOTELS.RESERVATION IS 'This table contains hotel room reservation details for customers including the check in and check out dates and which room they stayed in.';

   Show all 59 lines Show less

   To find the newly created tables, open the Database Objects app, set the schema filter to HOTELS, and select the Tables tab.

   

   Should you wish to examine the SQL of a table, it can be viewed after selecting a table and choosing CREATE Statement.

   

   For additional details see CREATE TABLE Statement.

2. Execute the following SQL to add data into the tables in the HOTELS schema. Ensure you are connected as USER1 in the HOTELS schema.

   SQL Copy

   INSERT INTO HOTEL VALUES(10, 'Congress', '155 Beechwood St.', 'Seattle', 'WA', '98121', NEW ST_POINT('POINT(-122.347340 47.610546)', 4326));
   INSERT INTO HOTEL VALUES(11, 'Regency', '477 17th Avenue', 'Seattle', 'WA', '98177', NEW ST_POINT('POINT(-122.371104 47.715210)', 4326));
   INSERT INTO HOTEL VALUES(12, 'Long Island', '1499 Grove Street', 'Long Island', 'NY', '11716', NEW ST_POINT('POINT(-73.133741 40.783602)', 4326));
   INSERT INTO HOTEL VALUES(13, 'Empire State', '65 Yellowstone Dr.', 'Albany', 'NY', '12203', NEW ST_POINT('POINT(-73.816182 42.670334)', 4326));
   INSERT INTO HOTEL VALUES(14, 'Midtown', '12 Barnard St.', 'New York', 'NY', '10019', NEW ST_POINT('POINT(-73.987388 40.766153)', 4326));
   INSERT INTO HOTEL VALUES(15, 'Eighth Avenue', '112 8th Avenue', 'New York', 'NY', '10019', NEW ST_POINT('POINT(-73.982495 40.767161)', 4326));
   INSERT INTO HOTEL VALUES(16, 'Lake Michigan', '354 OAK Terrace', 'Chicago', 'IL', '60601', NEW ST_POINT('POINT(-87.623608 41.886403)', 4326));
   INSERT INTO HOTEL VALUES(17, 'Airport', '650 C Parkway', 'Rosemont', 'IL', '60018', NEW ST_POINT('POINT(-87.872209 41.989378)', 4326));
   INSERT INTO HOTEL VALUES(18, 'Sunshine', '200 Yellowstone Dr.', 'Clearwater', 'FL', '33755', NEW ST_POINT('POINT(-82.791689 27.971218)', 4326));
   INSERT INTO HOTEL VALUES(19, 'Beach', '1980 34th St.', 'Daytona Beach', 'FL', '32018', NEW ST_POINT('POINT(-81.043091 29.215968)', 4326));
   INSERT INTO HOTEL VALUES(20, 'Atlantic', '111 78th St.', 'Deerfield Beach', 'FL', '33441', NEW ST_POINT('POINT(-80.106612 26.312141)', 4326));
   INSERT INTO HOTEL VALUES(21, 'Long Beach', '35 Broadway', 'Long Beach', 'CA', '90804', NEW ST_POINT('POINT(-118.158403 33.786721)', 4326));
   INSERT INTO HOTEL VALUES(22, 'Indian Horse', '16 MAIN STREET', 'Palm Springs', 'CA', '92262', NEW ST_POINT('POINT(-116.543342 33.877537)', 4326));
   INSERT INTO HOTEL VALUES(23, 'Star', '13 Beechwood Place', 'Hollywood', 'CA', '90029', NEW ST_POINT('POINT(-118.295017 34.086975)', 4326));
   INSERT INTO HOTEL VALUES(24, 'River Boat', '788 MAIN STREET', 'New Orleans', 'LA', '70112', NEW ST_POINT('POINT(-90.076919 29.957531)', 4326));
   INSERT INTO HOTEL VALUES(25, 'Ocean Star', '45 Pacific Avenue', 'Atlantic City', 'NJ', '08401', NEW ST_POINT('POINT(-74.416135 39.361078)', 4326));
   INSERT INTO HOTEL VALUES(26, 'Delta', '110 Erb St. W', 'Waterloo', 'ON', 'N2L0C6', NEW ST_POINT('POINT(-80.528404 43.463327)', 4326));
   
   INSERT INTO ROOM VALUES(10, 'single', 20, 135.00);
   INSERT INTO ROOM VALUES(10, 'double', 45, 200.00);
   INSERT INTO ROOM VALUES(12, 'single', 10, 70.00);
   INSERT INTO ROOM VALUES(12, 'double', 13, 100.00);
   INSERT INTO ROOM VALUES(13, 'single', 12, 45.00);
   INSERT INTO ROOM VALUES(13, 'double', 15, 80.00);
   INSERT INTO ROOM VALUES(14, 'single', 20, 85.00);
   INSERT INTO ROOM VALUES(14, 'double', 35, 140.00);
   INSERT INTO ROOM VALUES(15, 'single', 50, 105.00);
   INSERT INTO ROOM VALUES(15, 'double', 230, 180.00);
   INSERT INTO ROOM VALUES(15, 'suite', 12, 500.00);
   INSERT INTO ROOM VALUES(16, 'single', 10, 120.00);
   INSERT INTO ROOM VALUES(16, 'double', 39, 200.00);
   INSERT INTO ROOM VALUES(16, 'suite', 20, 500.00);
   INSERT INTO ROOM VALUES(17, 'single', 4, 115.00);
   INSERT INTO ROOM VALUES(17, 'double', 11, 180.00);
   INSERT INTO ROOM VALUES(18, 'single', 15, 90.00);
   INSERT INTO ROOM VALUES(18, 'double', 19, 150.00);
   INSERT INTO ROOM VALUES(18, 'suite', 5, 400.00);
   INSERT INTO ROOM VALUES(19, 'single', 45, 90.00);
   INSERT INTO ROOM VALUES(19, 'double', 145, 150.00);
   INSERT INTO ROOM VALUES(19, 'suite', 60, 300.00);
   INSERT INTO ROOM VALUES(20, 'single', 11, 60.00);
   INSERT INTO ROOM VALUES(20, 'double', 24, 100.00);
   INSERT INTO ROOM VALUES(21, 'single', 2, 70.00);
   INSERT INTO ROOM VALUES(21, 'double', 10, 130.00);
   INSERT INTO ROOM VALUES(22, 'single', 34, 80.00);
   INSERT INTO ROOM VALUES(22, 'double', 78, 140.00);
   INSERT INTO ROOM VALUES(22, 'suite', 55, 350.00);
   INSERT INTO ROOM VALUES(23, 'single', 89, 160.00);
   INSERT INTO ROOM VALUES(23, 'double', 300, 270.00);
   INSERT INTO ROOM VALUES(23, 'suite', 100, 700.00);
   INSERT INTO ROOM VALUES(24, 'single', 10, 125.00);
   INSERT INTO ROOM VALUES(24, 'double', 9, 200.00);
   INSERT INTO ROOM VALUES(24, 'suite', 78, 600.00);
   INSERT INTO ROOM VALUES(25, 'single', 44, 100.00);
   INSERT INTO ROOM VALUES(25, 'double', 115, 190.00);
   INSERT INTO ROOM VALUES(25, 'suite', 6, 450.00);
   
   INSERT INTO CUSTOMER VALUES(1000, 'Mrs', 'Jenny', 'Porter', '1340 N. Ash Street, #3', '10580');
   INSERT INTO CUSTOMER VALUES(1001, 'Mr', 'Peter', 'Brown', '1001 34th St., APT.3', '48226');
   INSERT INTO CUSTOMER VALUES(1002, 'Company', NULL, 'Datasoft', '486 Maple St.', '90018');
   INSERT INTO CUSTOMER VALUES(1003, 'Mrs', 'Rose', 'Brian', '500 Yellowstone Drive, #2', '75243');
   INSERT INTO CUSTOMER VALUES(1004, 'Mrs', 'Mary', 'Griffith', '3401 Elder Lane', '20005');
   INSERT INTO CUSTOMER VALUES(1005, 'Mr', 'Martin', 'Randolph', '340 MAIN STREET, #7', '60615');
   INSERT INTO CUSTOMER VALUES(1006, 'Mrs', 'Sally', 'Smith', '250 Curtis Street', '75243');
   INSERT INTO CUSTOMER VALUES(1007, 'Mr', 'Mike', 'Jackson', '133 BROADWAY APT. 1', '45211');
   INSERT INTO CUSTOMER VALUES(1008, 'Mrs', 'Rita', 'Doe', '2000 Humboldt St., #6', '97213');
   INSERT INTO CUSTOMER VALUES(1009, 'Mr', 'George', 'Howe', '111 B Parkway, #23', '75243');
   INSERT INTO CUSTOMER VALUES(1010, 'Mr', 'Frank', 'Miller', '27 5th St., 76', '95054');
   INSERT INTO CUSTOMER VALUES(1011, 'Mrs', 'Susan', 'Baker', '200 MAIN STREET, #94', '90018');
   INSERT INTO CUSTOMER VALUES(1012, 'Mr', 'Joseph', 'Peters', '700 S. Ash St., APT.12', '92714');
   INSERT INTO CUSTOMER VALUES(1013, 'Company', NULL, 'TOOLware', '410 Mariposa St., #10', '20019');
   INSERT INTO CUSTOMER VALUES(1014, 'Mr', 'Antony', 'Jenkins', '55 A Parkway, #15', '20903');
   
   INSERT INTO RESERVATION VALUES(1, 100, 1000, 11, 'single', '2020-12-24', '2020-12-27');
   INSERT INTO RESERVATION VALUES(2, 110, 1001, 11, 'double', '2020-12-24', '2021-01-03');
   INSERT INTO RESERVATION VALUES(3, 120, 1002, 15, 'suite', '2020-11-14', '2020-11-18');
   INSERT INTO RESERVATION VALUES(4, 130, 1009, 21, 'single', '2019-02-01', '2019-02-03');
   INSERT INTO RESERVATION VALUES(5, 150, 1006, 17, 'double', '2019-03-14', '2019-03-24');
   INSERT INTO RESERVATION VALUES(6, 140, 1013, 20, 'double', '2020-04-12', '2020-04-30');
   INSERT INTO RESERVATION VALUES(7, 160, 1011, 17, 'single', '2020-04-12', '2020-04-15');
   INSERT INTO RESERVATION VALUES(8, 170, 1014, 25, 'suite', '2020-09-01', '2020-09-03');
   INSERT INTO RESERVATION VALUES(9, 180, 1001, 22, 'double', '2020-12-23', '2021-01-08');
   INSERT INTO RESERVATION VALUES(10, 190, 1013, 24, 'double', '2020-11-14', '2020-11-17');
   
   INSERT INTO MAINTENANCE VALUES(10, 24, 'Replace pool liner and pump', '2019-03-21', 'Discount Pool Supplies');
   INSERT INTO MAINTENANCE VALUES(11, 25, 'Renovate the bar area.  Replace TV and speakers', '2020-11-29', 'TV and Audio Superstore');
   INSERT INTO MAINTENANCE VALUES(12, 26, 'Roof repair due to storm', null, null);

   Show all 87 lines Show less

   For additional details see INSERT Statement.

3. The data can now be queried. Identifiers such as table names are automatically upper-cased unless they are enclosed in double quotes.

   SQL Copy

   SELECT * FROM RoOm;  --succeeds
   SELECT * FROM "RoOm"; --fails
   SELECT * FROM "ROOM"; --succeeds

   For further details, consult Identifiers and Case Sensitivity.

Auto-commit is a setting that, when enabled, causes each SQL statement to be immediately committed to the database. When auto-commit is off, multiple statements can be executed and then committed or rolled back together.

There are two auto-commit settings:

• DML auto-commit — applies to INSERT, UPDATE, DELETE statements; configurable in the SQL Console settings
• DDL auto-commit — applies to CREATE TABLE, ALTER TABLE, and similar schema-change statements; configurable via SQL

1. Execute the following SQL. By default, auto-commit is on.

   SQL Copy

   CREATE COLUMN TABLE TEST1(
     ID INTEGER PRIMARY KEY,
     FIRSTNAME NVARCHAR(50) NOT NULL
   );
   INSERT INTO TEST1 VALUES (1, 'Bob');
   INSERT INTO TEST1 VALUES (2, 'Sue');
   INSERT INTO TEST1 VALUES (2, 'John');  --fails due to duplicate primary key
   --DROP TABLE TEST1;

   An error will occur and the table TEST1 will exist with two rows. There is no option to rollback as DML or data manipulation language autocommit is on by default. To undo the operation, drop the table.

2. Navigate to the user settings by clicking on your profile. Then in the SQL console section set auto-commit to off.

   

3. Execute the following SQL.

   SQL Copy

   CREATE COLUMN TABLE TEST2(
     ID INTEGER PRIMARY KEY,
     FIRSTNAME NVARCHAR(50) NOT NULL
   );
   INSERT INTO TEST2 VALUES (1, 'Bob');
   INSERT INTO TEST2 VALUES (2, 'Sue');
   INSERT INTO TEST2 VALUES (2, 'John');  --fails due to duplicate primary key

   An error will occur. You can then execute a ROLLBACK to undo all inserted rows, or a COMMIT to keep the successfully inserted rows.

   SQL Copy

   SELECT * FROM TEST2;
   --COMMIT;
   ROLLBACK;
   SELECT * FROM TEST2;
   --DROP TABLE TEST2;

   Until a COMMIT is executed, the table will appear to have no rows when viewed from another SQL console or connection.

   Additional details can be found at ROLLBACK Statement and COMMIT Statement.

4. Execute the following SQL to explore data definition language autocommit.

   SQL Copy

   SET TRANSACTION AUTOCOMMIT DDL OFF;
   CREATE COLUMN TABLE TEST3(
     ID INTEGER PRIMARY KEY,
     FIRSTNAME NVARCHAR(50) NOT NULL
   );
   INSERT INTO TEST3 VALUES (1, 'Bob');
   INSERT INTO TEST3 VALUES (2, 'Sue');
   SELECT * FROM TEST3;
   INSERT INTO TEST3 VALUES (2, 'John');
   --DROP TABLE TEST3;
   --ROLLBACK;
   --COMMIT;

   An error will occur. You can then execute a ROLLBACK to undo both the inserted rows and the table creation, or a COMMIT to keep both.

   Until a COMMIT is executed, the table will not appear in the Database Objects app or in another SQL console.

   The following query can be used to determine if AUTOCOMMIT DDL is enabled.

   SQL Copy

   SELECT key, value
   FROM m_session_context
   WHERE connection_id = current_connection
       AND key = 'DDL_AUTO_COMMIT';

   Additional details can be found at SET TRANSACTION AUTOCOMMIT DDL Statement.

5. Ensure both DML and DDL auto-commit settings are back to their default values before continuing.

   

   SQL Copy

   SET TRANSACTION AUTOCOMMIT DDL ON;

Partitions divide a large table into smaller parts for performance, load balancing, or storage tiering purposes.

1. Execute the following SQL to create one partition that contains older reservations and one for reservations from 2020 onwards.

   SQL Copy

   ALTER TABLE RESERVATION PARTITION BY RANGE(ARRIVAL)
   ((
       PARTITION '2000-01-01' <= VALUES < '2020-01-01',
       PARTITION OTHERS
   ));

2. Execute the following SQL to make the partition containing older reservations page-loadable from disk using Native Storage Extensions (NSE).

   SQL Copy

   ALTER TABLE RESERVATION ALTER PARTITION 1 PAGE LOADABLE;

   The following select statement shows the load unit type of columns in the HOTELS schema and the distribution of the rows in the RESERVATION table.

   SQL Copy

   SELECT TABLE_NAME, PART_ID, COLUMN_NAME, LOAD_UNIT FROM M_CS_COLUMNS WHERE SCHEMA_NAME = 'HOTELS';
   
   SELECT SCHEMA_NAME, TABLE_NAME, NODE_ID, LOAD_UNIT, RECORD_COUNT FROM M_TABLE_PARTITIONS 
       WHERE TABLE_NAME = 'RESERVATION';

   

For further information see Reduce the Memory Footprint Using Page-Loadable Columns and Table Partitioning.

Another option for less frequently accessed data is the SAP HANA data lake. Additional information on when to use Native Storage Extensions versus data lake can be found at Storage Options.

1. Views can be created to combine columns from multiple tables or to restrict access to certain columns. Execute the following SQL to create a view that combines information from the RESERVATION, HOTEL, and CUSTOMER tables.

   SQL Copy

   CREATE OR REPLACE VIEW RESERVATION_VIEW AS
       SELECT
           R.RESNO,
           H.NAME AS HOTEL_NAME,
           R.ARRIVAL,
           R.DEPARTURE,
           CUS.TITLE,
           CUS.FIRSTNAME,
           CUS.NAME AS CUSTOMER_NAME,
           CUS.ADDRESS AS CUSTOMER_ADDRESS
       FROM RESERVATION R
           LEFT JOIN HOTEL H ON H.HNO = R.HNO
           LEFT JOIN CUSTOMER CUS ON CUS.CNO = R.CNO
       ORDER BY H.NAME, R.ARRIVAL DESC;

2. Query the view.

   SQL Copy

   SELECT * FROM RESERVATION_VIEW;

3. Create a second view that displays room information per hotel.

   SQL Copy

   CREATE OR REPLACE VIEW HOTEL_ROOMS_VIEW AS
       SELECT
           H.NAME AS HOTEL_NAME,
           R.TYPE,
           R.FREE,
           R.PRICE
       FROM ROOM R
           LEFT JOIN HOTEL H ON R.HNO = H.HNO
           ORDER BY H.NAME;
   
   SELECT * FROM HOTEL_ROOMS_VIEW;

   For additional details see CREATE VIEW Statement.

1. User-defined functions and procedures can be used to save a set of SQL statements. Functions are considered read-only in that they cannot make modifications to the data. Stored procedures can modify the data using DDL or DML statements.

   Execute the following SQL to create a function that calculates the average price of a specific room type.

   SQL Copy

   CREATE OR REPLACE FUNCTION AVERAGE_PRICE(room_type CHAR(6))
   RETURNS avg_price NUMERIC(6, 2)
   AS
   BEGIN
       DECLARE EXIT HANDLER FOR SQLEXCEPTION avg_price := '-1';
       SELECT TO_DECIMAL(ROUND(sum(PRICE)/COUNT(*), 2, ROUND_HALF_UP)) INTO avg_price FROM ROOM WHERE TYPE = :room_type GROUP BY TYPE;
   END;

2. Call the function.

   SQL Copy

   SELECT AVERAGE_PRICE('single') from dummy;

   

   For additional details see User-Defined Functions.

3. Functions such as the one above that return single defined values are know as scalar user defined functions. Table user defined functions can return a tabular result set. The following is an example of user defined function that returns results in a table format.

   SQL Copy

   CREATE FUNCTION PAST_VISITS (CNO INT)
   RETURNS TABLE (FIRSTNAME NVARCHAR(20), NAME NVARCHAR(40), HOTEL_NAME NVARCHAR(50), ARRIVAL DATE, STAY_DURATION INT) LANGUAGE SQLSCRIPT AS
   BEGIN
       RETURN SELECT C.FIRSTNAME, C.NAME, H.NAME AS HOTEL_NAME, R.ARRIVAL, DAYS_BETWEEN(R.ARRIVAL, R.DEPARTURE) as STAY_DURATION
           FROM HOTEL H, RESERVATION R, CUSTOMER C
           WHERE C.CNO = R.CNO AND R.ARRIVAL <= CURRENT_DATE AND R.CNO = :CNO AND R.HNO= H.HNO
           ORDER BY R.ARRIVAL DESC;
   END;
   
   SELECT * FROM PAST_VISITS(1001);

4. Stored procedures can be used to save SQL statements. They can contain control statements and can have parameters. The following stored procedure generates and inserts reservations into the RESERVATION table. In order to call the procedure below, RESERVATION_GENERATOR, a parameter must be provided that identifies the number of reservations to insert. The stored procedure also writes to a logging table the number of rows that were inserted.

   SQL Copy

   CREATE TABLE GENERATOR_LOGS (
       ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
       LOG VARCHAR(200),
       TIME DATETIME DEFAULT CURRENT_TIMESTAMP
   );
   
   CREATE OR REPLACE PROCEDURE RESERVATION_GENERATOR(
       IN numToGenerate INTEGER
   )
       LANGUAGE SQLSCRIPT AS
   BEGIN
       USING SQLSCRIPT_PRINT AS PRTLIB;
       DECLARE val INT :=0;
       DECLARE stmt NVARCHAR(256) := '';
       DECLARE rno INT :=0;
       DECLARE cno INT :=0;
       DECLARE hno INT :=0;
       DECLARE roomType STRING := '';
       DECLARE arriveDate DATE := null;
       DECLARE arriveDateString STRING := '';
       DECLARE departDate DATE := null;
       DECLARE departDateString STRING := '';
       DECLARE randomDaysFromCurrent INT :=0;
       DECLARE randomLengthOfStay INT :=0;
       DECLARE rType INT :=0;
       DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301
       SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;
       WHILE (val < numToGenerate) DO
           rno := FLOOR(RAND_SECURE() * 201) + 100;
           cno := FLOOR(RAND_SECURE() * 15) + 1000;
           hno := FLOOR(RAND_SECURE() * 17) + 10;
           rType := FLOOR(RAND_SECURE() * 3) + 1;
           IF (rType = 1) THEN
               roomType := '''single''';
           ELSEIF (rType = 2) THEN
               roomType := '''double''';
           ELSEIF (rType = 3) THEN
               roomType := '''suite''';
           END IF;
           randomDaysFromCurrent := FLOOR(RAND_SECURE() * 730) + 1 - 365;
           arriveDate := ADD_DAYS( TO_DATE( CURRENT_DATE, 'YYYY-MM-DD' ), randomDaysFromCurrent );
           arriveDateString := '''' || TO_NVARCHAR( arriveDate, 'YYYY-MM-DD' ) || '''';
           randomLengthOfStay := FLOOR(RAND_SECURE() * 7) + 1;
           departDate := ADD_DAYS( arriveDate, randomLengthOfStay );
           departDateString := '''' || TO_NVARCHAR( departDate, 'YYYY-MM-DD' ) || '''';
           stmt := 'INSERT INTO RESERVATION (RNO, CNO, HNO, TYPE, ARRIVAL, DEPARTURE) VALUES(' || rno || ',' || cno || ',' || hno || ',' || roomType || ',' || arriveDateString || ',' || departDateString || ');';
           PRTLIB:PRINT_LINE(stmt);
           EXEC(stmt);
           val := val + 1;
       END WHILE;
       PRTLIB:PRINT_LINE('Rows inserted: ' || val);
       EXEC('INSERT INTO GENERATOR_LOGS (LOG) VALUES (' || val || ')');
   END;

   Show all 53 lines Show less

   Details on SQL Error codes such as 301 ERR_SQL_UNIQUE_VIOLATED can be found at SAP HANA Cloud, SAP HANA Database SQL Reference Guide

5. Run the stored procedure.

   SQL Copy

   CALL RESERVATION_GENERATOR(NUMTOGENERATE => 100);

   

   Once the procedure has been successfully executed, execute the below SQL and notice that dates both in the past and future are generated and that there are now 110 reservations.

   SQL Copy

   SELECT * FROM RESERVATION_VIEW;

   For additional details see Procedures.

Monitoring views contain information about the objects within a database. For further details see Monitoring Views.

--List of tables and record counts in the schema HOTELS
SELECT SCHEMA_NAME, TABLE_NAME, RECORD_COUNT, TABLE_SIZE
FROM M_TABLES WHERE SCHEMA_NAME = 'HOTELS' ORDER BY RECORD_COUNT DESC;

--List of columns and data types for the tables in schema HOTELS
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, DATA_TYPE_NAME
FROM TABLE_COLUMNS WHERE SCHEMA_NAME = 'HOTELS' ORDER BY TABLE_NAME ASC, COLUMN_NAME ASC;

Procedures can be scheduled to run automatically in SAP HANA Cloud.

SELECT CURRENT_DATE, CURRENT_TIME FROM DUMMY;  --be sure to schedule an event in the future
CREATE SCHEDULER JOB GEN_RESERVATIONS_JOB CRON '2026 07 30 * 09 00 0' ENABLE PROCEDURE RESERVATION_GENERATOR PARAMETERS numtogenerate=10;

--DROP SCHEDULER JOB GEN_RESERVATIONS_JOB;
SELECT * FROM RESERVATION_VIEW;  --After the procedure is run, there should be 10 new reservations
SELECT * FROM SCHEDULER_JOBS WHERE SCHEDULER_JOB_NAME = 'GEN_RESERVATIONS_JOB';  --shows the cron value and if the job is enabled or disabled
SELECT * FROM M_SCHEDULER_JOBS WHERE SCHEDULER_JOB_NAME = 'GEN_RESERVATIONS_JOB'; --Shows the start time and end time of when the stored procedure was run.

Details about a scheduled job — including its properties, parameters, history, and CREATE statement — can be viewed in the Database Objects app under Job Scheduler. Filter by the HOTELS schema to locate the job. Navigate to Select Object Types to add Job Scheduler if unavailable.

For additional details see Scheduling Administrative Tasks and CREATE SCHEDULER JOB Statement.

Congratulations! You have now created tables and inserted data, as well as created partitions, views, functions, stored procedures, and scheduled jobs using the SQL console in SAP HANA Cloud Central.

In the next tutorial, you will use the Database Objects app to browse and explore the catalog objects you have created.