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:
Query employees who are younger than 30 years old, sort them by age in descending order; meanwhile, sort employees of the same age by full name 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.
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 full names 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 esProcto 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:
没有评论:
发表评论