Using SQL in esProc (II)

5 .Comparison between common SQL statements and esProc syntax

1)  Select * from

Query results are as follows:

2) Select … from 

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

3) As 

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:

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 

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

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 

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:

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

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

7) Order by 

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:

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

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

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 

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

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 

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

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: