Learn SQL in 10 Mins: Basic SQL For Non-Developers | SAP HANA SQL

This post is dedicated for those who are not SQL Developers. This is a post for people who (by some reason) need to understand how SQL works and how it flows – quickly.

For example, you are a Business Analyst who needs to do a quick run-through analysis of the database or you are someone who needs to analyze the behavior of SQL in a system architecture etc.

This will cover the bare minimum concepts you need to know to understand SQL. It is assumed that there is an SQL setup that already exists, and you may need to understand how it works.

By the end of this post, I will briefly talk about SQL and its relation to SAP and/or SAP HANA. If you have heard of “SAP HANA SQL”, I will do a quick overview on its importance as well.

Note: It is always helpful to have a background in basic SQL before diving into SQL for SAP HANA related use.

Overview

  • What is SQL?
  • Basic Syntax and Queries
  • SQL Server Agent
  • Stored Procedures
  • Server Objects – Linked Servers
  • Additional Info
  • SQL and SAP : How do they relate to one another?
  • SAP HANA and SQL
  • Helpful Links

What is SQL?

SQL stands for Structured Query Language. According to Tutorialspoint, “SQL is a language to operate databases”. This is by far the simplest definition I could find on the internet.

Let us forget about formal definitions for a moment and try to think of SQL as a foreign language for databases. Simply put, databases can understand this foreign language. Since databases store data / information, SQL will be used to communicate the instructions as to how to handle the data or what to do with the stored data.

Refer to the high-level example below where the Entity (pink) is either a software or a person who needs the data to be arranged alphabetically. The cylindrical object (blue) represents the database. The Table of Letters resides inside the database and it contains jumbled letters.

Since the database cannot understand English, the request was translated into SQL. Once understood, the database did what it was requested to do (arrange data in alphabetical order) and it sent out the result to the Entity.

Notice how the Table of Letters still contained jumbled letters and only the result was alphabetized according to the need of Entity.

Basic SQL Easy

Basic Syntax and Queries

What is Syntax?

What do I mean by “Syntax”? We need to be able to communicate in a way that the database can understand. In order to do that we need to speak their language: SQL. Like basic English, there is a structure or rule when composing sentences. This is the syntax part.

SQL needs to be “grammatically” and “used” accurately. If we consider “THE DOG GET” vs “GET THE DOG”, each would mean different things. Moreover, one of them would be used incorrectly. As such, it is important that the SQL follows a syntax for effective communication.

Basic Commands / Actions

There are basic commands that you can utilize and be familiarized with as a Non-SQL developer. We will categorize them into two parts: Viewing and Manipulating.

As a Non-SQL Developer, it is often best practice that only Viewing commands may be done on your part. You might want to do this for the purpose of checking the data only.

IMPORTANT: Manipulating the data is highly discouraged on your part because this already involves tampering data. This is usually done by actual developers and/or in some cases database administrators. I will still discuss these commands for the purpose that you know if there is an instruction that manipulates the data. After all, our end goal is to help you understand basic SQL and what it is currently doing in your application.

Command Key Words

Let us start with some Key Words for each category.

  1. Viewing Commands – View Only
    • SELECT
  2. Manipulating Commands – Change Something (BE CAREFUL!)
    • CREATE
    • INSERT INTO
    • ALTER
    • UPDATE
    • DELETE FROM
    • TRUNCATE

Syntax

ROWSYNTAXNOTES
1COMMAND *What action do you want the database to do? SELECT data? ALTER data? The asterisk (*) in this example is telling the database that ALL columns are considered in the command.
1.1COMMAND COLUMN1, COLUMN2If you want to limit the columns, you can replace the asterisk and enumerate them one by one. Separate each with a comma.
2FROM TABLEWhat table are you talking about?
3CONDITIONSWhat conditions need to be met? If you want to filter out the data, conditions need to be mentioned.
In layman’s terms: “Get me all the green apples from the apple basket”.
“Green” in this example talks about the condition. Instead of getting all the apples, we just want all the green ones.  
4;Don’t forget the semicolon to signify the end of the SQL query.
/* The slash + asterisk signifies the start of a comment 

COMMAND *   
FROM TABLE
CONDITIONS;

The asterisk + slash signifies the end of a comment */
-- FYI, double dashes are also comments

SELECT Apple
FROM AppleBasket
WHERE Color = 'Green';

Conditions

The main use of conditions is to narrow down the scope of data or filter out relevant data. For example, if we just want to see specific details, we don’t need to ask the database to get everything. We can ask the database to bring back specific information using conditions.

Condition Key Words

  • WHERE
  • ORDER BY
  • GROUP BY
  • HAVING
  • IS [NOT] NULL
  • IS NULL
  • LIKE
  • IN

Refer to the table below for examples on the usage. Note that the first 4 keywords are in order. If by chance WHERE, ORDER BY, GROUP BY, and HAVING needs to be used at the same time, the order below is followed. Those in ‘Bold’ font refers to a column name.

WHEREWHERE DogColor = ‘Brown’WHERE the dog color is brown
ORDER BYORDER BY BreedORDER the data by dog breed
GROUP BYGROUP BY GenderGROUP the data by gender
HAVINGHAVING COUNT (Siblings) > 2HAVING more than 2 siblings
IS [NOT] NULLWHERE Adopted IS [NOT] NULLWHERE the dog is adopted
IS NULLWHERE Adopted IS NULLWHERE the dog is not adopted
LIKEWHERE DogName LIKE ‘%UFFY’
WHERE DogName LIKE ‘FL%’
WHERE DogName LIKE ‘FLUFFY’
WHERE the dog name is similar to “UFFY’ at the end.
WHERE the dog name is similar to ‘FL’ at the beginning.
WHERE the dog name is similar to ‘FLUFFY’
INWHERE Home IN (‘SINGAPORE’, ‘INDIA’, ‘EUROPE’)WHERE the dogs home is in SINGAPORE, INDIA, or EUROPE.
/* Here are some examples */
SELECT DogName
FROM TableOfDogs
WHERE DogColor = 'Brown'
ORDER BY Breed ASC -- Order the results by Breed in Ascending Order
GROUP BY Gender DESC -- Group the results by Gender in Descending Order
HAVING COUNT (Siblings) > 2;--Get dog names who have more than 2 siblings

SELECT DogName
FROM TableOfDogs
WHERE Adopted IS [NOT] NULL; -- Dog is adopted

SELECT DogName
FROM TableOfDogs
WHERE Adopted IS NULL; -- Dog is not adopted

SELECT DogName
FROM TableOfDogs
WHERE DogName LIKE '%UFFY'; -- It will show FLUFFY and BUFFY

SELECT DogName
FROM TableOfDogs
WHERE DogName LIKE 'FL%'; -- It will show FLOOF and FLUFFY

SELECT DogName 
FROM TableOfDogs
WHERE Home IN ('Singapore', 'India', 'Europe'); 

SQL Server Agent

If you are looking for SQL Jobs, you will find them in the SQL Server Agent. These jobs will call “Stored Procedures” and will execute the contained “procedures”. Once it reaches the end of the procedure, the job is completed.

SQL Server Agent SQL Jobs

Note: This is assuming you have the MySQL software and view rights access.

Stored Procedures

If you are looking for executable SQL codes, you will find them in the Stored Procedures (SP). It helps to know the behavior of the SP so you are aware of what data it is accessing, what it does to the data, how the data flows etc.

This is usually complicated for non-developers to understand so if you are having difficulties, you can seek for a developer to assist you or read up on the documentation.

SQL Stored Procedures SP

Note: This is assuming you have the MySQL software and view rights access.

Server Objects – Linked Servers

Server Objects are especially useful in case there are linked servers. By locating a certain linked server, you can double check on it stored data. For example, a certain stored procedure may try to pull data from a certain linked server.

In this case, it is possible that the data may originate from the linked server and stored it in the current database you are viewing vice versa.

If there are issues such as “missing data” from the current database, you can check on the linked server to see if the data existed there. If it does, then it is possible that the data you are looking for was not transferred or saved to the current database.

SQL Linked Servers Server Objects

Note: This is assuming you have the MySQL software and view rights access.

Additional Info

Overall

Overall, it helps to have existing documentation that you can read to understand the data flow of the SQL. By knowing the keywords or basic “commands”, you can check the contents of the database. You can even try to understand the data flow by checking the stored procedures and jobs.

At the end of the day, it’s a matter of understanding it’s purpose and behavior (what does it do to the data?) through its language.

SQL and SAP : How do they relate to one another?

In relation to SAP, there are instances where data is originated from the 3rd party local application. This application can store details in its own database. After which, data will be passed on to SAP through BAPI.

This is where the SQL Server Agent, Stored Procedures, and Linked Servers come in handy. In this case, you can analyze the end to end data flow. This approach is especially useful for those who are analyzing the system architecture of a business.

SAP HANA and SQL

  1. What is SAP HANA SQL?
  2. Why is SQL Important in SAP HANA?

With the rise of SAP HANA, you may notice an increasing need to understand or use SQL. Why? SAP HANA is able to support many SQL statements that will accommodate helpful tasks such as:

  • Create database objects
  • Administer your system
  • Manipulate data

This is something that you definitely want to leverage on. Let’s take a step back and visualize SAP HANA as a database. During the first few years of its release, it was introduced to us using a data scenario.

A business user may not need ALL THE DATA. A business user may just need a few details for his / her report. In ECC, you would expect SAP to search through everything and then provide the few details. This would take a lot of time if you consider accessing a huge database. This is where SAP HANA kicks in. This time, you do not need to go through everything.

SAP HANA in this context is now a relational database management system. Key word here being “DATABASE”. Earlier, we talked about how databases understand a specific foreign language: SQL.

That being said:

If you want to really leverage SAP HANA and work with the data in the SAP HANA database, you will need to effectively communicate with it through SQL.

Note: It is always helpful to have a background in basic SQL before diving into the SQL Reference for SAP HANA related use.

  1. What is the difference between SAP HANA and SAP S/4HANA?
SAP HANASAP S/4HANA
– “Backend”
– Used as core technology / database platform in systems
– In-memory database technology
– Relational Database Management System (RDBMS)
– New generation “business suite” or ERP solution that runs on SAP HANA database architecture
– SAP S/4HANA = SAP Business Suite 4 SAP HANA
– Replaces old SAP ECC/ERP System

For more information, you can check sap.com products.

Helpful Links

In case you need more information on SQL, you may refer to the following tutorial links below.

I hope this helps. Good luck! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.