Did Someone Say SQL Can Do Mathematics?!

Did Someone Say SQL Can Do Mathematics?!

I thought it was strange at first, the idea of needing mathematics in database queries. I mean are we not just fetching and storing data? However, clearly there is a place and time to use special data manipulating functions. For this reason I am going to explain a few of them and give some examples so all the noobs out there (like me) have idea of what can be done.

• The first few on the list should not need much explaining:

(+)          Addition                              SELECT 150+7

(-)          Subtraction                        SELECT 150-7

(*)          Multiplication                    SELECT 150*7

(/)          Division                              SELECT 150/7

(%)          Modulo                               SELECT 150%7

The only one here which may seem unfamiliar, is Modulo, which is simply the integer remainder resulting from dividing two integers together (5%5 = 0 and 5%2 =1 etc…).

• Now for the “more complex” functions:

ABS(x)                        returns the absolute value of x

SIGN(x)                returns the sign of input x as -1, 0, or 1 (-ve, zero, +ve respectively)

MOD(x,y)                    modulo - returns the integer remainder of x divided by y (same as x%y)

FLOOR(x)                    returns the largest integer value that is less than or equal to x

CEILING(x) or            returns the smallest integer value that is greater than or equal to x

CEIL(x)

POWER(x,y)                returns the value of x raised to the power of y

ROUND(x)                    returns the value of x rounded to the nearest whole integer

ROUND(x,y)                returns the value of x rounded to the number of decimal places specified by the     value y

SQURT(x)                    returns the square-root value of x

A simple example:

SELECT CEIL(salary), firstname

FROM employee_info_table

Which will return the smallest integer that is greater than or equal to salary (ie round up).

The rest of the functions can be executed in a very similar fashion…

I hope this was not to painful for the more experienced out there and is somewhat helpful to the not so experienced, but if all else fails, it is a nice summery to look up mathematical functions in SQL if your brain fails you in remembering some.

Therefore we can see that data contained in our database might need to be manipulated into another form to be used elsewhere, making these functions quite popular. There are quite a few more functions available, which can vary from different database types.

NOTE: Not all of these functions will work for every database types, but should work for most of them.