Mathematical functions in sql server Part 29 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 29 of signal
server in this session we'll learn about
all the mathematical functions that are
available in sequel server we'll be
looking at all these functions that you
can see on the slide now in order to
find out what are the different
functions that are available in sequence
server go into object Explorer window
expand databases within databases
explain any database let's expand sample
and within the sample database folder
expand programmability and then
functions folder within functions folder
expand system functions and finally look
for mathematical functions folder expand
that you should see all the mathematical
functions that are available in sequel
server today we will examine a few of
them that are commonly used ok we'll
look at this absolute function ABS which
stands for absolute this function
returns the absolute number when we say
absolute the positive number now
whatever you pass to this function let's
say here if you look at the example that
we have here we are passing in a
negative value okay which has got a
minus sign as well now when you pass
that to absolute function it returns an
absolute number ignoring the sign so we
get 101.5
without that minus sign so that's what
basically the absolute function does
which is very simple to understand it
has got just one parameter all right the
next two you know useful functions are
ceiling and floor ok now if you look at
these functions they just take one
parameter okay now let's look at the
example first if you look at the ceiling
example here the first query to the
ceiling function we are actually passing
15.2 so what is the next highest value
of 15.2 it's 16 so ceiling returns 16 so
if you're passing a decimal number
ceiling will return the next highest
value okay
ceiling is the higher one you know
ceiling is on the top and roof is on the
floor maybe those are the reasons why
you know ceiling returns the highest
number and
low returns the lowest number so if you
look at this floor of 15.2 returns 15
okay now if you if you pass a negative
number look at this four ceiling via
pass in 15.2 now since it is a negative
number minus 15 is greater than minus 16
so ceiling of a negative number will
give minus 15 because minus 15 is
greater than minus 16 okay but whereas
when you look at the floor - 15 point 2
returns 16 why because 16 is much lesser
than I mean 16 is lesser than minus 15
okay so basically ceiling and floor
returns the next highest integer value
okay so if you look at the definition
here ceiling and function floors
I mean floor functions except a numeric
expression as a single parameter ceiling
returns the smallest integer value
greater than or equal to the parameter
whereas Flow returns the largest integer
less than or equal to the parameter okay
the next function is the power function
okay
now basically as the name suggests the
power function returns the power of a
number if you look at the example here
power of 2 comma 3 so in this function
this function takes two parameters the
first parameter the expression will act
as the base whereas the second parameter
will act as the power so if I pass 2 3 2
comma 3 to this power function we are
saying calculate 2 to the power of 3 2
to the power of 3 is 8 so this function
returns side so similarly if I pass 4
comma 3 we want to calculate 4 to the
power of 3 4 + 4 + 4 which is 64 4 4 16
16 fours 64 all right the next function
is the square function again which is
very simple and straight forward square
function returns the square of a number
so if I pass for example 3 3 into 3 is 9
so we get 9 back which is simple and
straightforward ok and we have another
function square root again which is very
straightforward you know if you give it
number it will calculate the square root
of that given number so here we are
passing a team
on to this function it returns nine by
the way square root has this
abbreviation SQ RT is the name of
function we don't call it a square root
but sqr RT stands for square root okay
so the next useful function that we'll
look at is the random function rand
function okay so as the name says Rand
random it gives that random number
between zero and one okay so it actually
gives us a decimal number let's look at
that first okay now I'm not going to
execute all these queries that we have
seen because they are pretty
straightforward you can just understand
them and you can run them in sequel
server management studio so let's start
with random function so if you look at
random function when I say select random
look at the intelligence in sequel
server management studio this is
expecting an integer parameter you know
see the value now that is optional so if
you look at the documentation in MSDN
random off seed value seed value
parameter is within the square bracket
so anything within square brackets is
optional according to the MS talking
MSDN documentation so we don't have to
supply a value for the seed value
parameter okay so if we don't supply a
value for seed parameter and I execute
this function look at this I get a
random number between 0 and 1 this will
never cross 1 so when I keep I executed
multiple times look at the value it gets
changed every time I execute and this
value is between 0 and 1 and if you look
at the band call in this function I'm
not passing it any parameter now on the
other hand if I pass a seed value so
let's say I'm passing in seed value and
let me execute this I executed this I
got some 7 1 3 I execute that again look
at that I get the same number
it doesn't change so if you supply a
seed value and execute that query no
matter how many times you execute that
you end up getting the same value ok so
if you want to use random Rand function
want to get the same value you use the
same seed value if you don't provide a
seed value then it gives
you a random number every time a you
execute between zero and one okay so the
next question is let's say I want to
print you know a random number between 0
and 100 how do I do that I can make use
of the floor and ran functions because
look at this
every time I execute random function
what do I get I get some random number
okay but my requirement here is to
generate a random number between 1 and
hundred and to do that if I multiply
this whatever is returned by a random
number if I multiply that with 100 and
if I select the whole thing look at
what's going to happen so I execute this
look at this I get thirty four point
something executed again twenty-one
point something but I don't want the
decimal part I only want the you know
the integer part how do I do that maybe
you can use ceiling or floor methods
just to get you know the integral parts
the next integer okay
so I'm going to use the floor function
maybe here so it's going to knock off
those decimal places and give me the
integer so when I execute that I get 79
I execute again 50 so if you keep on
executing that you get different number
but it will always be between 1 and
hundred ok between 0 and 100 rather all
right
now let's say I want to print 10 random
numbers between 1 and hundred you know
if you want to print just put that in a
loop so what are we doing here we are
creating a variable called counter of
type integer and then we are
initializing that to 1 and we are
checking ok is the counter value less
than or equal to 10 so if it is then we
are we are having the same statement
here but instead of select we are saying
print print the value and increment the
counter value if you don't have this
line this loop will become a
never-ending loop okay
so obviously now when we execute this
you should see you know ten random
numbers between one and hundred I
execute that again you know you get a
different set of ten random numbers
between 1 and
hundred now let's say I want ten random
numbers between 1 and thousand is it
possible absolutely just multiply this
by thousand and you should get ten
random numbers between 1 and thousand
okay so that's about land function so
remember R and function returns a random
float number between 0 & 1
the random function takes an optional
seed parameter when the seed value is
supplied the rand function always
returns the same value for the same seed
okay if you want to generate a random
number between 1 and 100 we have seen
how to do that and we also have seen how
to print 10 random numbers between 1 and
100 the final function that we will look
at today is rounded this is very very
important function and I believe every
developer would have used this at least
one or two times in their life in any
project all right so let's see what's
the significance of this round function
their own function as the name suggests
it rounds the number okay now if I give
it a decimal number you can tell it okay
round it to two places or three places
now if you look at this function it has
got three parameters now rather than
reading the description of these
parameters let's try to understand the
rule of these parameters with the help
of practical examples okay
so if you look at the examples on the
screen you know the first dysfunction
look at this the first parameter is the
number that I want to round actually
let's look at the second pair a second
example first so the first parameter is
the number that I want downed okay and
the second parameter will tell okay you
want to round this first number but how
you know how many decimal places do you
want to round that number two okay so I
want to round this number to two decimal
places that you're that's what you are
telling okay so this number is 850 point
five five six and you are saying I want
to round this to two decimal places with
the help of the second parameter okay so
obviously what happens this will be
rounded to 85 actually I am really
picked the wrong example okay now
let's look at you know the first example
itself because you know we need to
understand the rule of this last
parameter which is nothing but the
function parameter and this parameter
resorption if you look at the first
example we haven't supplied you know
three parameter the third parameter
there we are supplying it only here now
actually round function you know it does
two things it aid around the given
number or it truncates the given number
now whether you want to round a given
number or truncate a given number is
controlled by this third parameter so if
you specify you know one as the third
parameter then you are telling their own
function don't round it truncate it
truncate it to two decimal places so
anything after two decimal places just
ignore that so it's saying truncate this
850 point five five six to two decimal
places so what it does is it converts
this it ignores the six and converts
that to zero so that's why we get eight
fifty point five five zero on the other
hand if you look at the first example
here we are not using that you know the
function parameter the third parameter
so if you don't use that the default is
zero which means when you specify a
value of zero or if you don't specify
any value at all which is where the
default is zero then the function
actually rounds the value so you are
telling it to round to two decimal
places since we have six here so
obviously it will round this number to
eight fifty point five six zero which is
what this one will return I hope this
makes clear you know the first parameter
is the number that we want to round to
the second parameter is the length of
the rounding how many decimal places do
you want to round to and the third
parameter is the function parameter
which you use to decide whether you want
to round the number or truncate the
number a value of zero indicates that
you want to round it and the default if
you don't specify is zero and non zero
value could be one could be 99 and no
zero value indicates that you want to
truncate the number and not round it
okay now if you look at the second exam
I mean the third example here you are
saying okay round it to one decimal
place so obviously 0.55 six you know 56
so these two will be rounded to 0.6 zero
zero which is eight five zero point six
zero zero okay
now look at the fifth I mean the fourth
example here obviously we are saying
truncated to one decimal place so what
happens to five and six they will be
truncated okay after the decimal place
so that's why it says 850 point five
zero zero okay and the fifth example
here if you look at this one look at
this for the length parameter this is
important and interesting for the length
parameter I'm saying I'm providing a
negative value so when you provide a
negative value you are asking the round
function to round you know the numbers
or the digits left to the decimal point
okay so you are telling it you are
telling the zone function to round to
round these two places
five zero round these two numbers that
are to the left of the decimal point so
what's going to happen five zero will be
rounded to nine hundred so 900.000 okay
so when you specify a negative number
you are actually telling the round
function to round the digits to the left
of the decimal point when you use a
positive number
you tell you're telling it to round the
digits that are right to the decimal
point okay so this round function you
know there's almost anything that you
can achieve either rounding or
truncation with the help of these three
parameters okay and finally the last
example if you look at it you are saying
it round the last I mean one digit to
the left of decimal place
okay so obviously it returns 850 point
zero zero zero because zero and it's
five so it just
once that to zero anyways so we get
eighty five point eight fifty point zero
zero zero
alright on this slide you can find
resources for asp.net and C sharp
interview questions that's it for today
thank you for listening have a great day