Monday, February 14, 2011

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 6th 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>

No comments: