Query the Database Using SQL Statements

1. Go to the Instances tab in SAP HANA Cloud Central and open SQL Console.

   

   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 center of the console.

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

   SQL Copy

   SET SCHEMA SFLIGHT;

   

   You can also change the schema by clicking on the current schema name in the center of the console, selecting SFLIGHT from the list, and then clicking the Change button.

   

   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. 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.