SQL General Reference

Index:
Logical OperatorsEquality and Inequality SignsAggregate FunctionsMathematical OperatorsMathematical FunctionTrigonometric 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

Last Modified: 24 July 2013