Calculating Square Roots and Powers

(All tests done in Oracle 9.)

You can calculate the square root of a numeric column value as follows:

TEST9 > sqlplus /

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 14 15:09:25 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning option

JServer Release 9.2.0.7.0 - Production

SQL> create table andrew as

  2  select 5 numeric_column from dual;

Table created.

SQL> select sqrt(numeric_column) from andrew;

SQRT(NUMERIC_COLUMN)

--------------------

          2.23606798

SQL>

You can also supply a value directly to SQRT:

SQL> select sqrt(5) from dual;

   SQRT(5)

----------

2.23606798

SQL>

According to some old course notes I had (dated 6^th August 1990), Oracle returns a null if you try to calculate the square root of a negative number. This is no longer the case as Oracle simply returns an error message:

SQL> select sqrt(-1) from dual;

select sqrt(-1) from dual

            *

ERROR at line 1:

ORA-01428: argument '-1' is out of range

SQL>

You can test the accuracy of a square root calculation by squaring the result and comparing the final answer with the original value. The power function will do this for you:

SQL> select sqrt(8) from dual;

   SQRT(8)

----------

2.82842712

SQL> select power(2.82842712,2) from dual;

POWER(2.82842712,2)

-------------------

         7.99999997

SQL>

The course notes also said that you could not do exponentiation to fractional powers.This restriction has been removed too:

SQL> select power(11,3/7) from dual;

POWER(11,3/7)

-------------

   2.79454528

SQL> select power(2.79454528,7/3) from dual;

POWER(2.79454528,7/3)

---------------------

                   11

SQL>