SQL General Reference
Index:
Logical Operators,
Equality and Inequality Signs,
Aggregate Functions,
Mathematical Operators,
Mathematical Function,
Trigonometric Function
Logical Operators
The logical operators (which are AND, OR, and NOT) can be used in the SQL statement. They must be written in the WHERE block. The descriptions of them are shown in the table below;
| Logical Operator | Description |
|---|---|
AND |
If all the conditions are met, then TRUE. |
OR |
If one of the give conditions is met, then TRUE. |
NOT |
If none of the given condition is met, then TRUE. |
Equality and Inequality Signs
The equality and inequality signs can be written in a SQL statement. The descriptions of them are shown in the table below;
| Equality and Inequality Sign | Description |
|---|---|
= |
equal to |
> |
larger than |
< |
less than |
>= |
larger than or equal to |
<= |
less than or equal to |
!= |
not equal to |
Aggregate Functions
The aggregated functions do calculations and obtain a single result from the multiple input values based on a table column, which meet your requirements. The following functions are called as aggregated functions;
| Aggregate Function | Description |
|---|---|
min(x) |
The minimum value in the column x. |
max(x) |
The maximum value in the column x. |
avg(x) |
The average value in the column x. |
stddev(x) |
The standard deviation of the values in the column x. |
count(x) |
The number of values in the column x. |
count(*) |
The number of rows. |
Mathematical Operators
You can use a variety of mathematical operators in your own SQL statement. The descriptions of the mathematical operators are shown in the table below;
| Mathematical Operator | Description | Example | Result |
|---|---|---|---|
+ |
addition | 3 + 4 |
7 |
- |
subtraction | 5 - 4 |
1 |
* |
multiplication | 2 * 4 |
8 |
/ |
division | 9 / 3 |
3 |
% |
remainder | 3 % 2 |
1 |
^ |
power | 3.0 ^ 3.0 |
27 |
|/ |
square root | |/ 36.0 |
6 |
||/ |
cubic root | ||/ 8.0 |
2 |
! |
factorial | 3! |
6 |
@ |
absolute value | @(-7.0) |
7 |
& |
binary AND | 5 & 3 |
1 |
| |
binary OR | 2 | 8 |
10 |
# |
binary EXOR | 5 # 6 |
3 |
~ |
binary NOT | ~(-2) |
1 |
<< |
binary left shift | 1 << 3 |
8 |
>> |
binary right shift | 8 >> 3 |
1 |
Mathematical Function
The available Mathematical functions are shown in the table below. They include single argument functions or multiple argument functions. In "Return Type" column in the table, "dp" means double precision data.
| Mathematical Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
abs(x) |
same as x | absolute value | abs(-20.8) |
20.8 |
cbrt(x) |
dp | cubic root | cbrt(64.0) |
4 |
ceil(dp or numeric) |
same as input | smallest integer not less than argument | ceil(-79.3) |
-79 |
ceiling(dp or numeric) |
same as input | smallest integer not less than argument (alias for ceil) | ceiling(-89.7) |
-89 |
degrees(dp) |
dp | radians to degrees | degrees(0.79) |
45.263665815 |
exp(dp or numeric) |
same as input | exponential | exp(2.0) |
7.3890560989 |
floor(dp or numeric) |
same as input | largest integer not greater than argument | floor(-32.9) |
-33 |
ln(dp or numeric) |
same as input | natural logarithm | ln(5.0) |
1.6094379124 |
log(dp or numeric) |
same as input | base 10 logarithm | log(10.0) |
1.0 |
log(b numeric, x numeric) |
numeric | logarithm to base b | log(9.0, 81.0) |
2.0 |
mod(y, x) |
same as arguments | remainder of y/x | mod(7,5) |
2 |
pi() |
dp | π constant | pi() |
3.14159265 |
power(a dp, b dp) |
dp | a raised to the power of b | power(8.0, 4.0) |
4096.0 |
power(a numeric, b numeric) |
numeric | a raised to the power of b | power(8.0, 4.0) |
4096.0 |
radians(dp) |
dp | degrees to radians | radians(45.0) |
1.047197551 |
random() |
dp | random value between 0.0 and 1.0 | random() |
|
round(dp or numeric) |
same as input | round to nearest integer | round(57.6) |
58 |
round(v numeric, s int) |
numeric | round to s decimal places | round(55.7865, 3) |
55.787 |
sign(dp or numeric) |
same as input | sign of the argument (-1, 0, +1) | sign(-9.9) |
-1 |
sqrt(dp or numeric) |
same as input | square root | sqrt(5.0) |
2.2360679775 |
trunc(dp or numeric) |
same as input | truncate toward zero | trunc(64.7) |
64 |
trunc(v numeric, s int) |
numeric | truncate to s decimal places | trunc(64.5768, 3) |
64.576 |
width_bucket(op numeric, b1 numeric, b2 numeric, count int) |
int | bucket to which operand would be assigned in an equidepth histogram with count buckets, an upper bound of b1, and a lower bound of b2 | width_bucket(6.78, 0.038, 15.08, 8) |
4 |
Trigonometric Function
You can also use trigonometric functions in your own SQL statement. They return a double precision value. The descriptions of them are shown in the table below;
| Trigonometric Function | Description |
|---|---|
acos(x) |
arccosine |
asin(x) |
arcsine |
atan(x) |
arctangent |
atan2(y,x) |
arctangent of y/x |
cos(x) |
cosine |
cot(x) |
cotangent |
sin(x) |
sine |
tan(x) |
tangent |