Using SQL in esProc (II)

5 Comparison between common SQL statements and esProc syntax

1) Select * from

esProc_sql_21

Query results are as follows:

 

esProc_sql_22

2) Select … from

 

 

 

 

esProc_sql_23

Get designated fields from the table. Both A2 and A3 have the same query results as follows:

 

 

esProc_sql_24

3) As

 

esProc_sql_25

Compute FULLNAME according to NAME and SURNAME, and meanwhile, compute AGE according to BIRTHDAY. Basically, both A2 and A3 have the same query results as follows:

esProc_sql_26

Note that AGE is computed in A3 by subtracting years and exact computations will be more complicated since SQL hasn’t functions to directly compute age.

4) Where

 

esProc_sql_27

Query employees who are younger than 30 years old, then compute in esProc using existed results. Query results of A3 are as follows:

 

esProc_sql_28

Query the same results in A4 with SQL but the syntax is much more complicated. As the process of computing age is inexact, errors occur in the results.

5)  Count, sum, avg, max and min

 

esProc_sql_29

Query the total number of employees who are younger than 30 years old, compute in esProc using existed results. Query result of A3 is as follows:

 

esProc_sql_30

 

This time, A4 uses a more exact method to compute AGE and gets a query result that is consistent with that of A3. But it cannot use the existed results and statements are more complicated.

The usage of SQL functions, such as sum, avg, max and min, is similar to that of count.

6) Distinct

esProc_sql_31

Query which departments does the employee information come from.Both A2 and A3 have the same results. Query results are as follows:

esProc_sql_32

 

7) Order by

esProc_sql_33

Query employees who are younger than 30 years old, sort them by age in descending order; meanwhile, sort employees of the same age by fullname in ascending order. Both A3 and A4 have the same query results as follows:

esProc_sql_34

Since it is complicated to compute age with SQL and existed results cannot be used, this time A4 simplifies statements with nested query. However, the process is still complex.

8) And、or、not and <>

 

esProc_sql_35

Query employees who are younger than 30 years old and whose initials of full names are S. Results are as follows:

 

esProc_sql_36

It can be seen that and is represented in esProc by the operator && and two equal signs == are used to judge whether things are equal or not. These are in line with the customs of many program languages. Similarly, in esProc, or is represented by the operator “||“, not by“!”, and<>by“!=”.

9) Like

 

esProc_sql_37

Query full names of employees whose names are ended by a. Query results are as follows:

 

esProc_sql_38

In using like function, different databases use different wildcard characters. In this example, for instance, percent “%” is used to represent zero or multiple arbitrary characters; while in some other databases, asterisk “*” is used to represent the same things. But with esProc, syntax of any database is the same.

10) Group

 

esProc_sql_39

If grouping by departments employees work for, group function can be used in esProc to group records as follows:

 

esProc_sql_40

It can be seen that the result of grouping with esProc is that records are divided into multiple groups. These groups can be used to perform further computations as required in esProc.

A3 directly computes grouping and summarizing with esProc function while A4 does the job with SQL. They get the same results. SQL doesn’t have the real “group” concept, so it can only perform aggregate computations in query by groups. Results are as follows:

esProc_sql_41

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Application, Big Data, Data Analytics, Program Language, Reporting tool and tagged , . Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s