title
Mathematical functions in sql server Part 29

description
In this video we will learn about the mathematical functions in sql server like Abs, Ceiling, Floor, Power, Rand, Square, Sqrt, and Round. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/mathematical-functions-in-sql-server.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-29-mathematical-functions.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Mathematical functions in sql server Part 29', 'heatmap': [{'end': 304.592, 'start': 274.799, 'weight': 0.754}, {'end': 442.222, 'start': 427.187, 'weight': 0.906}, {'end': 585.667, 'start': 570.048, 'weight': 0.811}, {'end': 935.845, 'start': 908.267, 'weight': 1}], 'summary': 'Covers sql server mathematical functions, including access in object explorer, commonly used functions like abs, ceiling, floor, power, square, and square root, and the rand function generating random decimal numbers with seed value impact, as well as generating random numbers using loops and practical examples.', 'chapters': [{'end': 33.416, 'segs': [{'end': 40.702, 'src': 'embed', 'start': 15.979, 'weight': 0, 'content': [{'end': 23.968, 'text': 'Now, in order to find out what are the different functions that are available in SQL Server, Go into Object Explorer window, expand databases.', 'start': 15.979, 'duration': 7.989}, {'end': 26.71, 'text': 'Within databases, expand any database.', 'start': 24.428, 'duration': 2.282}, {'end': 33.416, 'text': "Let's expand sample and within the sample database folder, expand programmability and then functions folder.", 'start': 26.75, 'duration': 6.666}, {'end': 40.702, 'text': 'Within functions folder, expand system functions and finally look for mathematical functions folder, expand that.', 'start': 33.876, 'duration': 6.826}], 'summary': 'To find sql server functions, expand databases, sample, programmability, system functions, and mathematical functions.', 'duration': 24.723, 'max_score': 15.979, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc15979.jpg'}], 'start': 0.109, 'title': 'Sql server math functions', 'summary': 'Covers all the mathematical functions available in sql server, including how to find and access them in the object explorer window. it is part 29 of the sql server series at prejume technologies.', 'chapters': [{'end': 33.416, 'start': 0.109, 'title': 'Sql server math functions', 'summary': 'Covers all the mathematical functions available in sql server, including how to find and access them in the object explorer window, and it is part 29 of the sql server series at prejume technologies.', 'duration': 33.307, 'highlights': ['Accessing mathematical functions in SQL Server through Object Explorer window is demonstrated, which includes navigating through databases, expanding sample database, and accessing the programmability and functions folders.', 'The session focuses on learning about the various mathematical functions available in SQL Server, providing a comprehensive overview of these functions and their usage in SQL queries.']}], 'duration': 33.307, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc109.jpg', 'highlights': ['The session focuses on learning about the various mathematical functions available in SQL Server, providing a comprehensive overview of these functions and their usage in SQL queries.', 'Accessing mathematical functions in SQL Server through Object Explorer window is demonstrated, which includes navigating through databases, expanding sample database, and accessing the programmability and functions folders.']}, {'end': 480.732, 'segs': [{'end': 60.784, 'src': 'embed', 'start': 33.876, 'weight': 2, 'content': [{'end': 40.702, 'text': 'Within functions folder, expand system functions and finally look for mathematical functions folder, expand that.', 'start': 33.876, 'duration': 6.826}, {'end': 44.686, 'text': 'You should see all the mathematical functions that are available in SQL Server.', 'start': 41.042, 'duration': 3.644}, {'end': 48.429, 'text': 'Today, we will examine a few of them that are commonly used.', 'start': 45.286, 'duration': 3.143}, {'end': 55.199, 'text': "Okay, we'll look at this absolute function, abs, which stands for absolute.", 'start': 50.535, 'duration': 4.664}, {'end': 60.784, 'text': 'This function returns the absolute number, when we say absolute, the positive number.', 'start': 55.599, 'duration': 5.185}], 'summary': 'Explore sql server mathematical functions, focusing on the abs function.', 'duration': 26.908, 'max_score': 33.876, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc33876.jpg'}, {'end': 107.982, 'src': 'embed', 'start': 81.664, 'weight': 0, 'content': [{'end': 85.688, 'text': "So that's what basically the absolute function does, which is very simple to understand.", 'start': 81.664, 'duration': 4.024}, {'end': 87.59, 'text': 'It has got just one parameter.', 'start': 86.028, 'duration': 1.562}, {'end': 93.853, 'text': 'Alright, the next two useful functions are ceiling and floor.', 'start': 88.689, 'duration': 5.164}, {'end': 98.496, 'text': 'Now, if you look at these functions, they just take one parameter.', 'start': 95.033, 'duration': 3.463}, {'end': 101.598, 'text': "Now, let's look at the example first.", 'start': 99.677, 'duration': 1.921}, {'end': 107.982, 'text': 'If you look at the ceiling example here, the first query to the ceiling function, we are actually passing 15.2.', 'start': 101.658, 'duration': 6.324}], 'summary': 'Transcript explains absolute, ceiling, and floor functions with one parameter each.', 'duration': 26.318, 'max_score': 81.664, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc81664.jpg'}, {'end': 242.405, 'src': 'embed', 'start': 213.282, 'weight': 4, 'content': [{'end': 219.43, 'text': 'The first parameter, the expression, will act as the base, whereas the second parameter will act as the power.', 'start': 213.282, 'duration': 6.148}, {'end': 226.938, 'text': 'So if I pass 2,3 to this power function, we are saying calculate 2 to the power of 3.', 'start': 219.71, 'duration': 7.228}, {'end': 230.48, 'text': '2 to the power of 3 is 8, so this function returns 8.', 'start': 226.938, 'duration': 3.542}, {'end': 242.079, 'text': 'So similarly, if I pass 4, 3, we want to calculate 4 to the power of 3, 4 and 4 and 4, which is 64, 4, 4, 16, 16, 4, 64.', 'start': 230.48, 'duration': 11.599}, {'end': 242.405, 'text': 'All right.', 'start': 242.085, 'duration': 0.32}], 'summary': 'Demonstrates a power function; 2^3=8, 4^3=64.', 'duration': 29.123, 'max_score': 213.282, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc213282.jpg'}, {'end': 304.592, 'src': 'heatmap', 'start': 274.799, 'weight': 0.754, 'content': [{'end': 279.383, 'text': "We don't call it a square root, but sqrt stands for square root.", 'start': 274.799, 'duration': 4.584}, {'end': 288.33, 'text': "Okay, so the next useful function that we'll look at is the random function, rand function, okay?", 'start': 280.644, 'duration': 7.686}, {'end': 295.946, 'text': 'So, as the name says, rand random, it gives a random number between 0 and 1..', 'start': 288.7, 'duration': 7.246}, {'end': 297.747, 'text': 'So it actually gives us a decimal number.', 'start': 295.946, 'duration': 1.801}, {'end': 298.948, 'text': "Let's look at that first.", 'start': 297.787, 'duration': 1.161}, {'end': 304.592, 'text': "Now, I'm not going to execute all these queries that we have seen because they're pretty straightforward.", 'start': 300.089, 'duration': 4.503}], 'summary': 'Introduction to sqrt and rand functions for generating random numbers.', 'duration': 29.793, 'max_score': 274.799, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc274799.jpg'}, {'end': 456.822, 'src': 'heatmap', 'start': 427.187, 'weight': 0.906, 'content': [{'end': 431.068, 'text': 'But my requirement here is to generate a random number between 1 and 100.', 'start': 427.187, 'duration': 3.881}, {'end': 442.222, 'text': 'And to do that, if I multiply this whatever is written by a random number, if I multiply that with 100, and if I select the whole thing,', 'start': 431.068, 'duration': 11.154}, {'end': 443.384, 'text': "look at what's going to happen.", 'start': 442.222, 'duration': 1.162}, {'end': 445.429, 'text': 'So I execute this.', 'start': 444.647, 'duration': 0.782}, {'end': 446.119, 'text': 'Look at this.', 'start': 445.779, 'duration': 0.34}, {'end': 447.98, 'text': 'I get 34 point something.', 'start': 446.259, 'duration': 1.721}, {'end': 449.12, 'text': 'Execute that again.', 'start': 448.44, 'duration': 0.68}, {'end': 450.2, 'text': '21 point something.', 'start': 449.14, 'duration': 1.06}, {'end': 452.441, 'text': "But I don't want the decimal part.", 'start': 450.5, 'duration': 1.941}, {'end': 456.822, 'text': 'I only want the integer part.', 'start': 452.521, 'duration': 4.301}], 'summary': 'Generate random number between 1 and 100, obtain integer part, not decimal.', 'duration': 29.635, 'max_score': 427.187, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc427187.jpg'}], 'start': 33.876, 'title': 'Sql mathematical and rand functions', 'summary': 'Covers commonly used mathematical functions in sql server such as abs, ceiling, floor, power, square, and square root, and explains the rand function which generates random decimal numbers between 0 and 1, with examples of usage and impact of providing a seed value.', 'chapters': [{'end': 288.33, 'start': 33.876, 'title': 'Sql mathematical functions overview', 'summary': 'Covers commonly used mathematical functions in sql server including abs, ceiling, floor, power, square, and square root, demonstrating their functionality and examples of usage.', 'duration': 254.454, 'highlights': ['The abs function returns the absolute number, ignoring the sign, demonstrated with an example where -101.5 is passed and the function returns 101.5. The abs function returns the absolute number, ignoring the sign, demonstrated with an example where -101.5 is passed and the function returns 101.5.', 'The ceiling function returns the smallest integer value greater than or equal to the parameter, and the floor function returns the largest integer less than or equal to the parameter, exemplified with various examples including positive and negative numbers. The ceiling function returns the smallest integer value greater than or equal to the parameter, and the floor function returns the largest integer less than or equal to the parameter, exemplified with various examples including positive and negative numbers.', 'The power function calculates the power of a number, with examples showcasing the calculation of 2 to the power of 3 resulting in 8 and 4 to the power of 3 resulting in 64. The power function calculates the power of a number, with examples showcasing the calculation of 2 to the power of 3 resulting in 8 and 4 to the power of 3 resulting in 64.', 'The square function returns the square of a number, exemplified with an example where 3 is passed, resulting in 9. The square function returns the square of a number, exemplified with an example where 3 is passed, resulting in 9.', 'The square root function calculates the square root of a given number, demonstrated with an example where 81 is passed and the function returns 9. The square root function calculates the square root of a given number, demonstrated with an example where 81 is passed and the function returns 9.']}, {'end': 480.732, 'start': 288.7, 'title': "Understanding sql's rand function", 'summary': 'Explains the rand function in sql, which generates a random decimal number between 0 and 1, and also demonstrates how to use it to generate random numbers within a specific range, with examples showing the impact of providing a seed value.', 'duration': 192.032, 'highlights': ['The RAND function in SQL generates a random decimal number between 0 and 1, and when executed multiple times, it produces different values each time.', 'Providing a seed value to the RAND function in SQL ensures that the same value is returned each time it is executed, useful for obtaining consistent random values.', 'To generate a random number within a specific range, the floor and rand functions can be combined, with the floor function used to obtain the integer part of the result, effectively achieving a random integer between the specified range.']}], 'duration': 446.856, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc33876.jpg', 'highlights': ['The power function calculates the power of a number, with examples showcasing the calculation of 2 to the power of 3 resulting in 8 and 4 to the power of 3 resulting in 64.', 'The square root function calculates the square root of a given number, demonstrated with an example where 81 is passed and the function returns 9.', 'Providing a seed value to the RAND function in SQL ensures that the same value is returned each time it is executed, useful for obtaining consistent random values.', 'To generate a random number within a specific range, the floor and rand functions can be combined, with the floor function used to obtain the integer part of the result, effectively achieving a random integer between the specified range.', 'The abs function returns the absolute number, ignoring the sign, demonstrated with an example where -101.5 is passed and the function returns 101.5.']}, {'end': 948.836, 'segs': [{'end': 559.025, 'src': 'embed', 'start': 529.171, 'weight': 1, 'content': [{'end': 530.172, 'text': 'I execute that again.', 'start': 529.171, 'duration': 1.001}, {'end': 535.495, 'text': 'You get a different set of 10 random numbers between 1 and 100.', 'start': 531.033, 'duration': 4.462}, {'end': 539.437, 'text': "Now let's say I want 10 random numbers between 1 and 1000.", 'start': 535.495, 'duration': 3.942}, {'end': 540.738, 'text': 'Is it possible? Absolutely.', 'start': 539.437, 'duration': 1.301}, {'end': 548.723, 'text': 'Just multiply this by 1000 and you should get 10 random numbers between 1 and 1000.', 'start': 541.098, 'duration': 7.625}, {'end': 551.144, 'text': "OK, so that's about rand function.", 'start': 548.723, 'duration': 2.421}, {'end': 556.565, 'text': 'So remember, rand function returns a random float number between 0 and 1.', 'start': 551.164, 'duration': 5.401}, {'end': 559.025, 'text': 'Random function takes an optional seed parameter.', 'start': 556.565, 'duration': 2.46}], 'summary': 'The rand function returns a random float number between 0 and 1, and it can be manipulated to produce 10 random numbers between 1 and 1000 by multiplying the result by 1000.', 'duration': 29.854, 'max_score': 529.171, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc529171.jpg'}, {'end': 599.549, 'src': 'heatmap', 'start': 570.048, 'weight': 0.811, 'content': [{'end': 574.383, 'text': 'And we also have seen how to print 10 random numbers between 1 and 100.', 'start': 570.048, 'duration': 4.335}, {'end': 577.184, 'text': 'The final function that we will look at today is round.', 'start': 574.383, 'duration': 2.801}, {'end': 585.667, 'text': 'This is a 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.', 'start': 577.204, 'duration': 8.463}, {'end': 590.028, 'text': "Alright, so let's see what's the significance of this round function.", 'start': 586.647, 'duration': 3.381}, {'end': 593.429, 'text': 'Round function as the name suggests, it rounds the number.', 'start': 590.168, 'duration': 3.261}, {'end': 599.549, 'text': 'Now, if I give it a decimal number, you can tell it, round it to two places or three places.', 'start': 594.767, 'duration': 4.782}], 'summary': "The 'round' function is important for rounding numbers, used frequently in development projects.", 'duration': 29.501, 'max_score': 570.048, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc570048.jpg'}, {'end': 891.397, 'src': 'embed', 'start': 858.783, 'weight': 2, 'content': [{'end': 869.066, 'text': 'So when you provide a negative value, you are asking the round function to round the numbers or the digits left to the decimal point.', 'start': 858.783, 'duration': 10.283}, {'end': 884.051, 'text': "So you're telling this round function to round these two places, 5, 0, round these two numbers that are to the left of the decimal point.", 'start': 870.126, 'duration': 13.925}, {'end': 891.397, 'text': "So what's going to happen? 5, 0 will be rounded to 900, so 900.000.", 'start': 884.432, 'duration': 6.965}], 'summary': 'Using a negative value in the round function rounds digits left to the decimal point.', 'duration': 32.614, 'max_score': 858.783, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc858783.jpg'}, {'end': 935.845, 'src': 'heatmap', 'start': 908.267, 'weight': 1, 'content': [{'end': 917.212, 'text': "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.", 'start': 908.267, 'duration': 8.945}, {'end': 927.377, 'text': "And finally, the last example, if you look at it, you're saying it round the last, I mean, one digit to the left of the decimal place.", 'start': 918.052, 'duration': 9.325}, {'end': 935.845, 'text': "Okay, so obviously it returns 850.000 because 0 and it's 5, so it just rounds that to 0 anyways.", 'start': 928.379, 'duration': 7.466}], 'summary': 'The round function can achieve rounding or truncation with three parameters, as demonstrated by rounding 850.000 to 850.', 'duration': 27.578, 'max_score': 908.267, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc908267.jpg'}, {'end': 948.836, 'src': 'embed', 'start': 948.175, 'weight': 0, 'content': [{'end': 948.836, 'text': 'Have a great day.', 'start': 948.175, 'duration': 0.661}], 'summary': 'Wishing you a great day.', 'duration': 0.661, 'max_score': 948.175, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc948175.jpg'}], 'start': 480.792, 'title': 'Generating random numbers', 'summary': 'Covers the process of using a loop to print 10 random numbers between 1 and 100 and explains the rand and round functions with practical examples and parameter explanations.', 'chapters': [{'end': 529.171, 'start': 480.792, 'title': 'Printing 10 random numbers', 'summary': "Covers the process of using a loop to print 10 random numbers between 1 and 100 by creating a counter variable to control the loop and ensuring it doesn't become a never-ending loop.", 'duration': 48.379, 'highlights': ['The process involves creating a counter variable of type integer and initializing it to 1, then checking if the counter value is less than or equal to 10 before printing the value and incrementing the counter to avoid a never-ending loop.', 'The range for the random numbers is between 1 and 100, and the loop ensures the printing of 10 random numbers.']}, {'end': 948.836, 'start': 529.171, 'title': 'Understanding the rand and round functions', 'summary': 'Explains the rand function, which returns random float numbers between 0 and 1, and the round function, which rounds or truncates numbers with specific decimal places, using practical examples and parameter explanations.', 'duration': 419.665, 'highlights': ['The round function rounds or truncates numbers with specific decimal places, controlled by the third parameter, with practical examples demonstrating the impact of different parameter values, such as rounding 850.556 to 85.0 and truncating it to 85.550.', 'The rand function returns random float numbers between 0 and 1, and can be used to generate random numbers within a custom range, such as obtaining 10 random numbers between 1 and 1000 by multiplying the result by 1000.', 'The significance of the round function is highlighted, emphasizing its common usage in development projects and its ability to round or truncate numbers based on the third parameter, with practical examples illustrating the impact of parameter values and the default behavior of rounding with a value of 0.', 'The parameters of the round function, including the number to round, the length of rounding, and the function parameter to decide between rounding and truncation, are explained thoroughly using practical examples and the default behavior of rounding with a value of 0.', "The impact of providing negative and positive values for the length parameter in the round function is demonstrated, showcasing the ability to round specific digits to the left or right of the decimal point, expanding the understanding of the function's capabilities."]}], 'duration': 468.044, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/gJ4PDXFHjbc/pics/gJ4PDXFHjbc480792.jpg', 'highlights': ['The range for the random numbers is between 1 and 100, and the loop ensures the printing of 10 random numbers.', 'The round function rounds or truncates numbers with specific decimal places, controlled by the third parameter, with practical examples demonstrating the impact of different parameter values.', 'The rand function returns random float numbers between 0 and 1, and can be used to generate random numbers within a custom range, such as obtaining 10 random numbers between 1 and 1000 by multiplying the result by 1000.', 'The parameters of the round function, including the number to round, the length of rounding, and the function parameter to decide between rounding and truncation, are explained thoroughly using practical examples and the default behavior of rounding with a value of 0.', "The impact of providing negative and positive values for the length parameter in the round function is demonstrated, showcasing the ability to round specific digits to the left or right of the decimal point, expanding the understanding of the function's capabilities."]}], 'highlights': ['Accessing mathematical functions in SQL Server through Object Explorer window is demonstrated, which includes navigating through databases, expanding sample database, and accessing the programmability and functions folders.', 'The session focuses on learning about the various mathematical functions available in SQL Server, providing a comprehensive overview of these functions and their usage in SQL queries.', 'The power function calculates the power of a number, with examples showcasing the calculation of 2 to the power of 3 resulting in 8 and 4 to the power of 3 resulting in 64.', 'The square root function calculates the square root of a given number, demonstrated with an example where 81 is passed and the function returns 9.', 'Providing a seed value to the RAND function in SQL ensures that the same value is returned each time it is executed, useful for obtaining consistent random values.', 'To generate a random number within a specific range, the floor and rand functions can be combined, with the floor function used to obtain the integer part of the result, effectively achieving a random integer between the specified range.', 'The abs function returns the absolute number, ignoring the sign, demonstrated with an example where -101.5 is passed and the function returns 101.5.', 'The range for the random numbers is between 1 and 100, and the loop ensures the printing of 10 random numbers.', 'The round function rounds or truncates numbers with specific decimal places, controlled by the third parameter, with practical examples demonstrating the impact of different parameter values.', 'The rand function returns random float numbers between 0 and 1, and can be used to generate random numbers within a custom range, such as obtaining 10 random numbers between 1 and 1000 by multiplying the result by 1000.', 'The parameters of the round function, including the number to round, the length of rounding, and the function parameter to decide between rounding and truncation, are explained thoroughly using practical examples and the default behavior of rounding with a value of 0.', "The impact of providing negative and positive values for the length parameter in the round function is demonstrated, showcasing the ability to round specific digits to the left or right of the decimal point, expanding the understanding of the function's capabilities."]}