**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

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.

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

__NOTE:__
## No comments:

## Post a Comment