The sql Script Language

sql Script v Sql

SQL stands for Structured Query Language and is used to communicate with a database. The ability of sql to handle structured data has been a driving force behind the digital age and e-commerce. The ability to perform complex data definition and data manipulation functions coupled with storing structured data in an intuitive tabular view have embedded sql in almost every application in the world.

The most notable feature of sql is its ability to process data in sets rather than individual records. It allows users to work with data on a logical level by using a set of powerful instructions to query data, update data rows in tables, control access and manipulate objects.

SQL was first standardized in ANSI X3.135 in 1986, and ISO 9075-1987. However, each vendor has extended or altered sql to fit their own purpose.

SAP sql script is an extension of sql allows developers to dramatically speed up their applications by pushing data-intensive logic to the HANA database. These extensions are also key to avoiding massive data transfers between the application server and database server. The logic is executed on the database server, utilising powerful paralysation strategies.

SAP sql script introduces the following features to take advantage of the HANA database:

• Local variables can be declared within procedures to hold interim results. This is an improvement over sql where decomposition of a query can only be done in a view, which has to be explicitly typed
• Sql script includes features to express business logic, such as currency and unit of measure conversions.
• Imperative constructs can be mixed with declarative constructs
• Sql script allows multiple result sets to be passed back to a calling procedure

sql Script language elements

Notation

During this book we will follow utilise BNF (Backus-Naur Form), the same notation used in the SAP sql reference. We will focus on the interpretation of the notation as the point of departure. While we will cover many statements and function explicitly in this guide, being able to interpret the notation effectively will enable you to effetely build complex sql statements on your own.

The SELECT statement’s notation is as follows:

<select_statement> ::=

 [ <with_clause>] <subquery> [ <for_update> | FOR SHARE LOCK |

 <time_travel> | <for_system_time> ] [<hint_clause>]  | [<with_clause>] ( <subquery> )

[ <for_update> | <for_xml> | <time_travel> ] [<hint_clause>]

<subquery> ::= <select_clause> <from_clause> [<where_clause>]

 [<group_by_clause>]

 [<having_clause>]

 [<set_operator> <subquery> [{, <set_operator> <subquery>}...]]

 [<order_by_clause>]

 [<limit>] 

Symbols used in the BNF Notation

The following symbols appear in the statement (from the SAP documentation):

Symbol	Description
< >	Angle brackets are used to surround the name of a syntactic element (BNF nonterminal) of the SQL language.
::=	The definition operator is used to provide definitions of the element appearing on the left side of the operator in a production rule.
[ ]	Square brackets are used to indicate optional elements in a formula. Optional elements can be specified or omitted.
{ }	Braces group elements in a formula. Repetitive elements (zero or more elements) can be specified within brace symbols.
|	The alternative operator indicates that the portion of the formula following the bar is an alternative to the portion preceding it.
...	Ellipsis indicates that the element can be repeated any number of times. If ellipsis appears after grouped elements, the grouped elements enclosed with braces can be repeated any number of times. If ellipsis appears after a single element, only this element can be repeated any number of times.
!!	Introduces normal English text. This is used when the definition of a syntactic element is not expressed in BNF.

If we remove all the optional elements (Denoted by the [ ] square bracket symbols) from the statement and strip it down to only the required elements, we get the following:

<select_statement> ::=

<subquery>

<subquery> ::= <select_clause> <from_clause>

The SELECT statement consists ( ::= ) of a subquery, which in turn requires only a SELECT and a FROM clauses. All other clauses, operator and statements are optional or alternatives.

But we will not stop here. Both the <select_clause> and the <from_clause> can be broken down further. For example, the select clause is defined as:

<select_clause> ::=

 SELECT [TOP <unsigned_integer>] [ ALL | DISTINCT ] <select_list>

We can thus continue to follow the same rules until we get to a very basic statement, which in this case will be a very simple statement:

select *

from dummy

;

DUMMY table:

The dummy table is a special single row, single table created by default in the HANA and other databases. It is the equivalent of the Oracle DUAL table and provides a convenient way to generate temporary tables .

 Once we understand how to get to the most basic statement, building it up and adding elements becomes very easy and intuitive. 

<< Return to table of contents

       Lesson 3: Lowest Term Representation >>>