In this article, we’ll look at both the POWER and SQRT functions, explain how they work, and see some examples.
Purpose of the SQL POWER and SQRT Functions
The purpose of the SQL POWER function is to raise one number to the power of another number.
In other words, this could be n1^n2
It’s useful for squaring or cubing numbers.
The purpose of the SQL SQRT function is to find and return the square root of a provided number.
The square root of a particular number answers this question:
“Which number, when multiplied by itself, will give me my original number?”
For example, the square root of 25 is equal to 5. This is because 5 * 5 = 25.
The square root of 9 is 3, because 3 * 3 = 9.
It doesn’t always have to be a whole number, as you’ll see in the examples section below.
This function exists in Oracle, SQL Server, MySQL, and Postgres. In MySQL, the POWER function is the same as the POW function.
SQL POWER Function Syntax and Parameters
The syntax of the SQL POWER function is:
POWER ( n2, n1 )
The parameters of the POWER function are:
- n2 (mandatory): This is the base number of the calculation.
- n1 (mandatory): This is the exponent, the power, or the number of times that n2 is multiplied by itself to get the result.
Some other points to note about this function:
- n2 and n1 can be any numeric data type.
- If n2 is negative, n1 must be an integer
- If any input is a BINARY_FLOAT or BINARY_DOUBLE, then the function returns BINARY_DOUBLE, otherwise it returns a NUMBER.
SQL SQRT Function Syntax and Parameters
The SQRT syntax in SQL is pretty simple:
SQRT (number)
The parameters of the SQRT function are:
- number (mandatory): The number to find the square root for.
The number can be any numeric data type. The SQRT function returns the same data type as the specified number.
Examples of the SQL POWER and SQRT Functions
Here are some examples of the POWER and SQRT functions.
Example 1
This example shows how to square a number.
SELECT POWER(4, 2);Result:
16
The result is 16, which is 4^2.
Example 2
This example shows how to cube a number.
SELECT POWER(5, 3);Result:
125
The result is 125, which is 5^3.
Example 3
This example uses a negative base number.
SELECT POWER(-8, 5);Result:
-32768
The result is -8 to the power of 5.
Example 4
This example uses a negative exponent or power number.
SELECT POWER(6, -3);Result:
0.00462962963
The result is a decimal number, as that is what happens when a negative exponent is used.
Example 5
This example uses a decimal number
SELECT POWER(12.3, 3);Result:
1860.867
It works the same way as a whole number.
Example 6
SELECT SQRT(25);Result:
5
The result is 5 because 5*5 = 25.
Example 7
SELECT SQRT(1029);Result:
32.07802986
Example 8
SELECT SQRT(4.1094156);Result:
2.027169357
If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.
Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!