Create Database Objects with SAP HANA Database Explorer

1. In the SAP HANA database explorer, select the previously created SAP HANA Cockpit or SAP HANA database and open a SQL console. Do not use the HDI container.

   

2. Create a user named User1.

   CREATE USER USER1 PASSWORD Password1 no force_first_password_change;
   

   For additional details see on creating users see CREATE USER Statement (Access Control).

   With SAP HANA Cloud, HANA database, if the DBAdmin user has privileges on more than one USERGROUP, then it must be specified which USERGROUP USER1 will be added to as shown below.

   CREATE USER USER1 PASSWORD Password1 no force_first_password_change SET USERGROUP DEFAULT;
   

   The following statement deletes the user in case it already exists or if you wish to remove the user after completing the group. Make sure you really wish to delete USER1 and the objects it owns before proceeding, as this operation cannot be undone.

   DROP USER USER1 CASCADE;
   

3. The list of users can be seen by executing the following statement:

   SELECT USER_NAME FROM USERS;
   

4. Create a schema named HOTEL and grant User1 access to it. A schema provides a way to group database objects together.

   CREATE SCHEMA HOTEL;
   GRANT ALL PRIVILEGES ON SCHEMA HOTEL TO USER1;
   

   The following statement deletes the schema in case it already exists or if you wish to remove the schema after completing the group. Make sure you really wish to delete the HOTEL schema and the objects it contains before proceeding, as this operation cannot be undone.

   DROP SCHEMA HOTEL CASCADE;
   

   For additional details see Database Users and CREATE SCHEMA Statement.

1. Create tables that represent a basic hotel administration system by running the SQL statements below:

   CREATE COLUMN TABLE hotel.hotel(
   	hno INTEGER PRIMARY KEY,
   	name VARCHAR(50) NOT NULL,
   	address VARCHAR(40) NOT NULL,
     city VARCHAR(30) NOT NULL,
     state CHAR(2) NOT NULL,
     zip CHAR(6)
   );
   CREATE COLUMN TABLE hotel.room(
   	hno INTEGER,
   	type CHAR(6),
   	free NUMERIC(3),
   	price NUMERIC(6, 2),
   	PRIMARY KEY (hno, type)
   );
   CREATE COLUMN TABLE hotel.customer(
     cno INTEGER PRIMARY KEY,
     title CHAR(7),
     firstname VARCHAR(20),
     name VARCHAR(40) NOT NULL,
     address VARCHAR(40) NOT NULL,
     zip CHAR(6)
   );
   CREATE COLUMN TABLE hotel.reservation(
     resno INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
   	rno INTEGER NOT NULL,
   	cno INTEGER,
   	hno INTEGER,
   	type CHAR(6),
   	arrival DATE NOT NULL,
   	departure DATE NOT NULL,
   	PRIMARY KEY (
   		"RESNO", "ARRIVAL"
   	)
   );
   CREATE COLUMN TABLE hotel.maintenance(
   	mno INTEGER PRIMARY KEY,
   	hno INTEGER,
   	desc VARCHAR(100),
   	date_performed DATE,
   	performed_by VARCHAR(40)
   );
   

   To find the newly created tables in the database browser, select the Tables folder and set the schema to HOTEL. If needed, the contents of the database browser can be updated by pressing the refresh button.

   

2. Execute the following SQL statements to add data into the tables in the hotel schema.

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

   For additional details see CREATE Table statement and Insert Statement.

Partitions can be created to divide the data in a large table into smaller parts.

1. Execute the following SQL statement to create one partition that contains older reservations and one that contains reservations made in 2019 or later.

   alter table hotel.reservation partition by range(ARRIVAL)
   ((
       partition '2000-01-01' <= VALUES < '2019-01-01',
       partition others
   ));
   

   In the example in this section, older reservation data will be stored on disk rather than in memory. Another reason for partitioning is for load balancing across multiple hosts in a distributed system. For more details see Table Partitioning.

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

   alter table hotel.reservation ALTER PARTITION 1 PAGE LOADABLE;
   

   The partition information can be seen in the Runtime Information tab of the reservation table, which can be shown by right-clicking on the reservation table and choosing Open.

   

   The following select statement shows the load unit type of columns in the schema HOTEL.

   SELECT TABLE_NAME, PART_ID, COLUMN_NAME, LOAD_UNIT FROM M_CS_COLUMNS where schema_Name = 'HOTEL';
   

   

   Notice above that the partition 1 (containing older reservations) is page loadable and partition 2 (containing recent reservations) is column loadable.

For further information see Reduce the Memory Footprint Using Page-Loadable Columns and SAP Note: 2799997 - FAQ: SAP HANA Native Storage Extension (NSE).

Another option for data that is accessed less frequently is the SAP HANA Data Lake. Additional information on when to use Native Store Extensions and Data Lake can be found at Introduction to SAP HANA Cloud - Storage Options.

1. Views can be created to combine columns from multiple tables into one view or to provide access to certain columns of a table. Executing the following SQL statements creates a view that displays all information from the reservation table. The joins allow for more information about the customer and hotel to be displayed.

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

2. The result of querying RESERVATION_VIEW can be seen below.

   SELECT * FROM HOTEL.RESERVATION_VIEW;
   

   

3. The following view displays information about hotel rooms.

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

   To find the newly created views in the database browser, select the Views folder.

   

4. The result of calling HOTEL_ROOMS_VIEW can be seen below.

   SELECT * FROM HOTEL.HOTEL_ROOMS_VIEW;
   

   

   For additional details see CREATE VIEW Statement (Data Definition).

1. User-created functions can be useful in saving a set of commands that return a value. They can also be used in other statements. Functions and procedures can also make use of control statements such as if else and loops.

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

   CREATE OR REPLACE FUNCTION HOTEL.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 HOTEL.ROOM WHERE TYPE = :room_type GROUP BY TYPE;
   END;
   

   To find the newly created function in the database browser, select the Functions folder.

   

2. An example of how to use the Average_Price function is shown below:

   SELECT HOTEL.AVERAGE_PRICE('single') from dummy;
   

   

   For additional details see User-Defined Functions.

3. 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 hotel.reservation table. In order to run, a parameter must be entered that identifies the number of reservations to insert.

   CREATE OR REPLACE PROCEDURE HOTEL.RESERVATION_GENERATOR(
       IN numToGenerate INTEGER
   )
   	LANGUAGE SQLSCRIPT AS
   BEGIN
   	USING SQLSCRIPT_PRINT AS PRTLIB;
   	DECLARE val INT :=0;
   	DECLARE stmt VARCHAR(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
   		-- generate random room number from 100-300
   		rno := FLOOR(RAND_SECURE() * 200) + 1 + 100;
   		-- generate random customer number from 1000-1014
   		cno := FLOOR(RAND_SECURE() * 14) + 1 + 1000;
   		-- generate random hotel number from 10-26
   		hno := FLOOR(RAND_SECURE() * 16) + 1 + 10;
   		-- generate random number from 1-3 to determine room type
   		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;
   
           -- generate random number of days to be used for arrival date.  
   		-- date range is one year in the past to one year in the future
   		randomDaysFromCurrent := FLOOR(RAND_SECURE() * 730) + 1 - 365;
   		arriveDate := ADD_DAYS( TO_DATE( CURRENT_DATE, 'YYYY-MM-DD' ), randomDaysFromCurrent );
   		arriveDateString := '''' || TO_VARCHAR( arriveDate, 'YYYY-MM-DD' ) || '''';
   		-- generate a random number of days to stay
   		randomLengthOfStay := FLOOR(RAND_SECURE() * 7) + 1;
   		departDate := ADD_DAYS( arriveDate, randomLengthOfStay );
   		departDateString := '''' || TO_VARCHAR( departDate, 'YYYY-MM-DD' ) || '''';
   
           -- Reservations Columns: RNO, CNO, HNO, Type, Arrival, Departure
   		stmt := 'INSERT INTO hotel.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);
   END;
   

4. To run this procedure, execute the SQL statement below.

   CALL HOTEL.RESERVATION_GENERATOR(NUMTOGENERATE => 100);
   

   

   Once the procedure has been successfully executed, notice that dates both in the past and future are generated.

   Another way to run this procedure is to generate a CALL statement using the UI. Right-click the procedure name and select Generate CALL Statement With UI.

   

   The parameters are listed in the parameters tab and once they are manually filled in or loaded from a file, the procedure can be called.

   For additional details see Procedures.

   Procedures can also be scheduled in SAP HANA Cloud. An example follows. For additional details see Scheduling Administrative Tasks.

      SELECT current_date, current_time FROM dummy;  --be sure to schedule an event in the future
      CREATE SCHEDULER JOB GEN_RESERVATIONS_JOB CRON '2021 12 23 * 14 25 0' ENABLE PROCEDURE "HOTEL"."RESERVATION_GENERATOR" PARAMETERS numtogenerate=10;
      SELECT * FROM M_SCHEDULER_JOBS WHERE SCHEDULER_JOB_NAME = 'GEN_RESERVATIONS_JOB';
   

Congratulations! You have now created tables and inserted data, as well as created partitions, views, functions, and stored procedures.