Query the Database Using SQL Statements

1. Go to the Instances tab in SAP HANA Cloud Central and open SAP HANA database explorer.

   

   Then open the SQL Console by clicking the icon in the left-hand top corner.

   

   You can also open the SQL console by right-clicking on a database name and selecting Open SQL console or by using the shortcut Ctrl+Alt+C.

2. Once the SQL console loads, ensure that the current schema is SFLIGHT by checking the top right-hand side of the console.

3. If not, copy and paste the following statement to the console and run it:

   SQL

   Copy

   SET SCHEMA SFLIGHT;
   

   The current schema will then display SFLIGHT.

   

Let’s find out which of the Best Run Travel agents are most popular. For this, we will need the agency number and the booking details. This means we need to use the tables SBOOK and STRAVELAG.

1. First, our goal is to extract the total number of bookings made per agency. To achieve this, we will start by creating a new table SAGENCYDATA from the existing tables SBOOK and STRAVELAG.

2. The following query will create a new table and order the agencies based on their number of bookings. Copy and paste query to the console and then click on the Run button:

   SQL

   Copy

   CREATE TABLE SAGENCYDATA as (select SBOOK.AGENCYNUM, count(SBOOK.AGENCYNUM) as NUMBOOKINGS FROM SBOOK, STRAVELAG WHERE SBOOK.AGENCYNUM=STRAVELAG.AGENCYNUM group by SBOOK.AGENCYNUM ORDER BY count(SBOOK.AGENCYNUM) desc)
   

3. You can view the contents of this table by running the following query:

   SQL

   Copy

   SELECT * FROM SAGENCYDATA;
   

   This will show you the contents of the new table:

   

Join the tables STRAVELAG and SAGENCYDATA based on the column AGENCYNUM and extract the top 5 agencies from the result. This will give you the list of agency numbers, names, and the number of bookings for the top 5 agencies.

You can use the following query that will join the tables and select the top 5 entries:

SELECT TOP 5 SAGENCYDATA.AGENCYNUM, STRAVELAG.NAME,SAGENCYDATA.NUMBOOKINGS FROM SAGENCYDATA INNER JOIN STRAVELAG on SAGENCYDATA.AGENCYNUM = STRAVELAG.AGENCYNUM;

In the results panel, you can now see that the travel agency that makes the maximum bookings is Rainy, Stormy, Cloudy with a total of 27870 bookings.

Since Alex also wants to know on which days of the week the top 5 travel agencies make most bookings, we need to use a few more queries.

To find the top booking days, we will first create two new tables:

• STOPAGENCY: name, agency number, and count of the bookings made by top 5 agencies
• SAGBOOKDAYS: details on the number of bookings made per day for each of the agencies

1. First create the STOPAGENCY table by storing the result of the previous query in a new table. Run this query in your console:

   SQL

   Copy

   CREATE TABLE STOPAGENCY AS (SELECT TOP 5 SAGENCYDATA.AGENCYNUM, STRAVELAG.NAME,SAGENCYDATA.NUMBOOKINGS FROM SAGENCYDATA INNER JOIN STRAVELAG ON SAGENCYDATA.AGENCYNUM = STRAVELAG.AGENCYNUM);
   

2. To view all contents of this table, just copy and paste the following query into the SQL console and run it:

   SQL

   Copy

   SELECT * FROM STOPAGENCY;
   

3. Next, create the table SAGBOOKDAYS to store the daily bookings for each of the agencies. Use the following query:

   SQL

   Copy

   CREATE TABLE SAGBOOKDAYS AS (SELECT AGENCYNUM, dayname(ORDER_DATE) as ORDERDAY, count(dayname(ORDER_DATE)) AS DAYCOUNT FROM SBOOK GROUP BY AGENCYNUM, dayname(ORDER_DATE))
   

4. To view all contents of this new table, you can again use the SELECT * FROM query:

   SQL

   Copy

   SELECT * FROM SAGBOOKDAYS;
   

5. Now that you have created the 2 tables, join these tables based on the agency number (column AGENCYNUM). You also need to extract only the day with maximum number of bookings for each of the top 5 agencies. For this, use the following nested queries:

   SQL

   Copy

   SELECT SAGBOOKDAYS.AGENCYNUM, STOPAGENCY.NAME, SAGBOOKDAYS.ORDERDAY, SAGBOOKDAYS.DAYCOUNT FROM SAGBOOKDAYS INNER JOIN STOPAGENCY ON SAGBOOKDAYS.AGENCYNUM=STOPAGENCY.AGENCYNUM WHERE SAGBOOKDAYS.DAYCOUNT IN (SELECT max(DAYCOUNT) FROM SAGBOOKDAYS GROUP BY AGENCYNUM)
   

6. Now you can see that the most bookings for the top 5 agencies have been done on Thursdays.

   

Well done!

You have completed the sixth tutorial of this mission! You learned how to create new tables, view table details, join tables and extract specific data from tables using SQL statements in the SAP HANA database explorer. This way you were able to help Alex get the business inside they were looking for.

Since Alex needs to make these insights available to other departments in Best Run Travel, the next step is to create a calculation view to share these findings with other departments.

Learn in the next tutorial how to get started with SAP Business Application to start creating a calculation view.