We know that there are 9 number data types ( tinyint, smallint, int, bigint, hugeint, decimal, double, float, real ). If we want to convert some value to these data types, we can use CAST and CONVERT functions.
SELECT CAST(true as smallint) AS "Smallint", CONVERT(42, int) AS "Int", CONVERT(123.45, real) AS "Real", CAST('123.45' as double) AS "Double", CAST(23.45 as decimal(5,2)) AS "Decimal";
Arithmetic Operators
We can always make our calculation by using operators + , – , * , / or modulo %.
SELECT 0 + 1 * 4 - ( 5.0 / 2 ) AS "Calculation", -- 5.0 / 2 = 2.5 because of decimal 7 / 2 AS "IntegerDivision", -- 7 / 2 = 3 because of INT 8 % 3 AS "Modulo";
Arithmetic operators have their counterpart in arithmetic functions:
SELECT mod( 8, 3 ) AS "Modulo", sql_add( 3, 3 ) AS "Summation", sql_div( 8, 2 ) AS "Division", sql_mul( 4, 5 ) AS "Multiplication", sql_neg( 1 ) AS "Negation", sql_sub( 7, 2 ) AS "Subtraction";
Bitwise Operators and Functions
We already know operators AND, NOT, OR and XOR. We used them with TRUE and FALSE values. In MonetDB, instead of TRUE and FALSE we can use 1 and 0.
SELECT TRUE AND FALSE;
SELECT 1 AND 0;-- we'll get the same result
If we have two binary numbers, then we can apply AND, NOT, OR and XOR on each of their zeros and ones. That is how bitwise operators work.
Number1
1
0
1
0
1
Number2
1
1
1
0
0
————
————
————
————
————
————
Num1 AND Num2
1
0
1
0
0
Num1 OR Num2
1
1
1
0
1
Num1 XOR Num2
0
1
0
0
1
NOT Num1
0
1
0
1
0
Instead of typing binary numbers, we will show them as regular integers. This is how we use bitwise operators and functions:
Bitwise operator
Bitwise function
Result
AND
SELECT 91 & 15;
SELECT BIT_AND( 91, 15 );
11 -- 01011011 AND 00001111 = 00001011
OR
SELECT 32 | 3;
SELECT BIT_OR( 32, 3 );
35 -- 00100000 OR 00000011 = 00100011
XOR
SELECT 17 ^ 5;
SELECT BIT_XOR( 17, 5 );
20 -- 00010001 XOR 00000101 = 00010100
NOT
SELECT ~1;
SELECT BIT_NOT( 1 )
-2 -- NOT 00000001 = 11111110
Bitwise Shift
Bitwise left shift is when we add a zero on the right side of a binary number. Each time we add another zero, the value of a number is increased double.
00000001 << 1 = 00000010 SELECT 1 << 1; -- 2
00000001 << 2 = 00000100 SELECT 1 << 2; -- 4
00000001 << 3 = 00001000 SELECT 1 << 3; -- 8
It is similar, if we remove one figure from the the right side. Then we have a right shift. Each right shift will reduce number to its half.
00001000 >> 1 = 00000100 SELECT 8 >> 1; -- 4
00000100 >> 1 = 00000010 SELECT 4 >> 1; -- 2
00000010 >> 1 = 00000001 SELECT 2 >> 1; -- 1
Beside using operators, we also have functions that can do bitwise shift.
SELECT left_shift(1, 2);
SELECT right_shift(8, 1);
Min and Max
To calculate the larger/smaller value of two values, we can use the functions "largest" and "smallest". The functions "skl_max" and "skl_min" are just synonyms of the previous two functions.
SELECT greatest( 4, 5 ) AS "Greatest", sql_max( 4, 5 ) AS "Sql_max", least( 2, 100 ) AS "Least", sql_min( 2, 100 ) AS "Sql_min";
Taking a Root
For calculation root we have two functions. One is for square root, and the other one is for the cube root.
SELECT SQRT( 9 ) AS "Square root", CBRT( 8 ) AS "Cube root", POWER( 16.0, 1.0 / 4 ) AS "Fourth root";
If we need to calculate a root of a higher order, then we can use mathematical property . In the last column we calculated the fourth root of a 16 by using this property. For this transformation we had to use POWER function.
Logarithm and Exponential Functions
SELECT POWER( 2, 5 ); --32
This function will raise a number to the power of another number.
SELECT exp(3); -- 20,08553692
We can define this function based on POWER function, because exp(3) = POWER(e,3), where e is Euler number (approximately 2.71).
The opposite function of POWER function is a logarithm. If POWER( b, x ) = Y, then log( b, Y ) = x:
SELECT POWER( 2, 3 ) AS "Power", LOG( 2, 8 ) AS "Logarithm";
If we are working with an Euler number, then we can use LOG function without its first argument (or we can use its alias LN function):
SELECT LOG( 20.08553692 ) AS "Log", LN( 20.08553692 ) AS "Ln" ;
We also can use functions LOG10( x ) or LOG2( x ), which are just shortcuts for LOG10( x ) = LOG( 10, x ) and LOG2( x ) = LOG( 2, x ).
Ceiling and Floor Functions
For each real number N, we can say that it is between two nearest integers. Those two integers we call ceiling and floor. We have SQL functions with such names, they will return us those two nearest integers:
SELECT FLOOR( 7.55 ) AS "Floor", CEILING( 7.55 ) AS "Ceiling";
For CEILING function, we can use its alias CEIL.
Each integer is floor and ceiling for itself.
Rounding and Truncating
We can reduce the number of decimal places in two ways. We can just cut off the excess decimals using the truncate function. The second argument will decide how many decimal places to trim.
SELECT sys.ms_trunc( -5.628, 0 ) AS "No decimals", sys.ms_trunc( –5.628, 1 ) AS "One decimal", sys.ms_trunc( -5.628, 2 ) AS "Two decimals";
Another approach is to round the number. The second argument will again decide how many decimal places will survive. If the last decimal is 5, we will always round up (in absolute value).
SELECT ROUND( 5.5, 0 ) AS "No decimals", ROUND( -5.55, 1 ) AS "One decimal", ROUND( 5.555, 2 ) AS "Two decimals";
If the second argument is negative, the result will be zero.
SELECT sys.ms_trunc( 9.99, -2 ) AS "Truncate", ROUND( 9.99, -3 ) AS "ROUND";
There is also a function that does truncation first and then rounding. SELECT sys.ms_round( 5.546, 2, 1 ), --5.546 => 5.54 (truncate) => 5.54 (rounded) sys.ms_round( 5.546, 2, 0 ); --5.546 => 5.546 (no truncate) => 5.55 (rounded)
Creating Random Numbers
Each time we call RAND() function, we will get random integer between 0 and 2,147,483,647 ( 231-1 ). SELECT RAND() AS "First call", RAND() AS "Second call";
RAND() function can accept seed argument. The seed argument will allow us to get the same random value every time. SELECT RAND(77) AS "First call", RAND(77) AS "Second call";
Other Mathematical Functions
SELECT ABS( -5 ); --to calculate an absolute value
SELECT SIGN( -5 ), SIGN( 5 ); -- to find out the sign of a number
SELECT sys.alpha(5.0, 1.2);
This function is for astronomy. compute alpha 'expansion' of theta for a given declination (used by SkyServer)
Trigonometric and Hyperbolic Functions
SELECT PI(); will return 3.14.
This is one right triangle. The smallest of its angles is 36.87°.
To transform this angle to radians we can use RADIANS function. For transforming it back we will use DEGREES function.
SELECT RADIANS( 36.87 ); --returns 0.6435, that is 36.87 * ( 3.14 / 180 )
SELECT DEGREES( 0.6435 ); --returns36.87, that is 0.6435 * ( 180 / 3.14 )
SELECT SIN( 0.6435 );
Sine function will return the ratio b/c = 3/5 = 0.6. It accepts an angle value in radians, as do all the functions below.
SELECT COS( 0.6435 );
Cosine function will return the ratio a/c = 4/5 = 0.8.
SELECT TAN( 0.6435 );
Tangent function will return the ratio b/a = 3/4 = 0.75.
SELECT COT( 0.6435 );
Cotangent function will return the ratio a/b = 4/3 = 1.33.
Then we have inverse functions:
SELECT ASIN( 0.6 ); -- 0.6435
If we know that b/c = 0.6, then arcsine function will tell us that the angle is 0.6435 radians.
SELECT ACOS( 0.8 ); -- 0.6435
If we know that a/c = 0.8, then arccosine function will tell us that the angle is 0.6435 radians.
SELECT ATAN( 0.75 ); -- 0.6435
If we know that b/a = 0.75, then arctangent function will tell us that the angle is 0.6435 radians.
SELECT ATAN( 3, 4 ); -- 0.6435
The same ATAN function will also work with dividend and divisor ( 3/4 = 0.75 ).
. In the last column we calculated the fourth root of a 16 by using this property. For this transformation we had to use POWER function.
This function is for astronomy. 
This is hyperbolic sine function.
This is hyperbolic cosine function.
This is hyperbolic tangent.