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 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.
- Viewing Commands – View Only
- SELECT
- Manipulating Commands – Change Something (BE CAREFUL!)
- CREATE
- INSERT INTO
- ALTER
- UPDATE
- DELETE FROM
- TRUNCATE
Syntax
| ROW | SYNTAX | NOTES |
| 1 | COMMAND * | 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.1 | COMMAND COLUMN1, COLUMN2 | If you want to limit the columns, you can replace the asterisk and enumerate them one by one. Separate each with a comma. |
| 2 | FROM TABLE | What table are you talking about? |
| 3 | CONDITIONS | What 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.
| WHERE | WHERE DogColor = ‘Brown’ | WHERE the dog color is brown |
| ORDER BY | ORDER BY Breed | ORDER the data by dog breed |
| GROUP BY | GROUP BY Gender | GROUP the data by gender |
| HAVING | HAVING COUNT (Siblings) > 2 | HAVING more than 2 siblings |
| IS [NOT] NULL | WHERE Adopted IS [NOT] NULL | WHERE the dog is adopted |
| IS NULL | WHERE Adopted IS NULL | WHERE the dog is not adopted |
| LIKE | WHERE 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’ |
| IN | WHERE 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.
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.
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.
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
- What is SAP HANA SQL?
- 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.
- What is the difference between SAP HANA and SAP S/4HANA?
| SAP HANA | SAP 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.
- General SQL W3Schools
- General SQL TutorialsPoint
- SAP HANA SQL Reference help.sap.com
I hope this helps. Good luck! 🙂
Techlorean. 