In this article, we’ll look at both the POWER and SQRT functions, explain how they work, and see some examples.
Purpose of the Oracle POWER and SQRT Functions
The purpose of the Oracle 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 Oracle 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.
Get Your SQL Cheat Sheet
Oracle POWER Function Syntax and Parameters
The syntax of the Oracle 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.
Oracle SQRT Function Syntax and Parameters
The SQRT syntax in Oracle is pretty simple:
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 Oracle POWER and SQRT Functions
Here are some examples of the POWER and SQRT functions. I find that examples are the best way for me to learn about code, even with the explanation above.
This example shows how to square a number.
SELECT POWER(4, 2) as POWER_TEST FROM dual;
The result is 16, which is 4^2.
This example shows how to cube a number.
SELECT POWER(5, 3) as POWER_TEST FROM dual;
The result is 125, which is 5^3.
This example uses a negative base number.
SELECT POWER(-8, 5) as POWER_TEST FROM dual;
The result is -8 to the power of 5.
This example uses a negative exponent or power number.
SELECT POWER(6, -3) as POWER_TEST FROM dual;
The result is a decimal number, as that is what happens when a negative exponent is used.
This example uses a decimal number
SELECT POWER(12.3, 3) as POWER_TEST FROM dual;
It works the same way as a whole number.
SELECT SQRT(25) FROM dual;
The result is 5 because 5*5 = 25.
SELECT SQRT(1029) FROM dual;
SELECT SQRT(4.1094156) FROM dual;
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!