YT YouTube2Text

YouTube2Text → Transcripts → Mathematical functions in sql server Part 29

Mathematical functions in sql server Part 29 — Transcript

by kudvenkat · 2,652 words · 372 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

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

About this transcript

This page contains the full transcript of Mathematical functions in sql server Part 29 by kudvenkat, generated from the public captions YouTube serves with the video. The transcript has 2,652 words across 372 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.