ch04.htm

Learn SQL in an easy way...

<H2><FONT COLOR="#000077">Arithmetic Functions</FONT></H2>
<P>Many of the uses you have for the data you retrieve involve mathematics. Most
implementations of SQL provide arithmetic functions similar to the functions covered
here. The examples in this section use the <TT>NUMBERS</TT> table:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
</B>  2 <B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">        A         B
--------- ---------
   3.1415         4
      -45      .707
        5         9
  -57.667        42
       15        55
     -7.2       5.3
6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">ABS</FONT></H3>
<P>The <TT>ABS</TT> function returns the absolute value of the number you point to.
For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT ABS(A) ABSOLUTE_VALUE
</B>  2<B>  FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ABSOLUTE_VALUE
--------------
        3.1415
            45
             5
        57.667
            15
           7.2
6 rows selected.
</FONT></PRE>
<P><TT>ABS</TT> changes all the negative numbers to positive and leaves positive
numbers alone.
<H3><FONT COLOR="#000077">CEIL and FLOOR</FONT></H3>
<P><TT>CEIL</TT> returns the smallest integer greater than or equal to its argument.
<TT>FLOOR</TT> does just the reverse, returning the largest integer equal to or less
than its argument. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT B, CEIL(B) CEILING
</B>  2  <B>FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">        B   CEILING
--------- ---------
        4         4
     .707         1
        9         9
       42        42
       55        55
      5.3         6
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>And</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, FLOOR(A) FLOOR
 </B> 2 <B> FROM NUMBERS;</B>

        A     FLOOR
--------- ---------
   3.1415         3
      -45       -45
        5         5
  -57.667       -58
       15        15
     -7.2        -8
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">COS, COSH, SIN, SINH, TAN, and TANH</FONT></H3>
<P>The <TT>COS</TT>, <TT>SIN</TT>, and <TT>TAN</TT> functions provide support for
various trigonometric concepts. They all work on the assumption that n is in radians.
The following statement returns some unexpected values if you don't realize <TT>COS</TT>
expects <TT>A</TT> to be in radians.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, COS(A)
 </B> 2<B>  FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">    A        COS(A)
--------- ---------
   3.1415        -1
      -45 .52532199
        5 .28366219
  -57.667   .437183
       15 -.7596879
     -7.2 .60835131
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You would expect the <TT>COS</TT> of <TT>45</TT> degrees to be in the neighborhood
of <TT>.707</TT>, not <TT>.525</TT>. To make this function work the way you would
expect it to in a degree-oriented world, you need to convert degrees to radians.
(When was the last time you heard a news broadcast report that a politician had done
a pi-radian turn? You hear about a 180-degree turn.) Because 360 degrees = 2 pi radians,
you can write</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, COS(A* 0.01745329251994)
 </B> 2<B>  FROM NUMBERS;
</B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">        A COS(A*0.01745329251994)
--------- -----------------------
   3.1415               .99849724
      -45               .70710678
        5                .9961947
  -57.667                .5348391
       15               .96592583
     -7.2                .9921147
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Note that the number <TT>0.01745329251994</TT> is radians divided by degrees.
The trigonometric functions work as follows:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, COS(A*0.017453), COSH(A*0.017453)
 </B> 2 <B> FROM NUMBERS;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">        A COS(A*0.017453) COSH(A*0.017453)
--------- --------------- ----------------
   3.1415       .99849729        1.0015035
      -45       .70711609        1.3245977
        5       .99619483          1.00381
  -57.667       .53485335        1.5507072
       15       .96592696        1.0344645
     -7.2       .99211497        1.0079058
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>And</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, SIN(A*0.017453), SINH(A*0.017453)
 </B> 2<B>  FROM NUMBERS;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">        A SIN(A*0.017453) SINH(A*0.017453)
--------- --------------- ----------------
   3.1415       .05480113        .05485607
      -45       -.7070975        -.8686535
        5       .08715429         .0873758
  -57.667       -.8449449        -1.185197
       15       .25881481        .26479569
     -7.2       -.1253311        -.1259926
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>And</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, TAN(A*0.017453), TANH(A*0.017453)
 </B> 2<B>  FROM NUMBERS;
</B>
        A TAN(A*0.017453) TANH(A*0.017453)
--------- --------------- ----------------
   3.1415       .05488361        .05477372
      -45       -.9999737        -.6557867
        5       .08748719        .08704416
  -57.667       -1.579769        -.7642948
       15       .26794449        .25597369
     -7.2       -.1263272        -.1250043
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">EXP</FONT></H3>
<P><TT>EXP</TT> enables you to raise <I>e</I> (<I>e</I> is a mathematical constant
used in various formulas) to a power. Here's how <TT>EXP</TT> raises <I>e</I> by
the values in column A:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, EXP(A)
 </B> 2 <B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">        A     EXP(A)
---------  ---------
   3.1415  23.138549
      -45  2.863E-20
        5  148.41316
  -57.667  9.027E-26
       15  3269017.4
     -7.2  .00074659
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">LN and LOG</FONT></H3>
<P>These two functions center on logarithms. <TT>LN</TT> returns the natural logarithm
of its argument. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> SELECT A, LN(A)
 </B> 2<B>  FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">ERROR:
ORA-01428: argument '-45' is out of range
</FONT></PRE>
<P>Did we neglect to mention that the argument had to be positive? Write</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, LN(ABS(A))
 </B> 2 <B> FROM NUMBERS;</B>

        A LN(ABS(A))
--------- ----------
   3.1415  1.1447004
      -45  3.8066625
        5  1.6094379
  -57.667  4.0546851
       15  2.7080502
     -7.2   1.974081
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice how you can embed the function <TT>ABS</TT> inside the <TT>LN</TT> call.
The other logarith-mic function, <TT>LOG</TT>, takes two arguments, returning the
logarithm of the first argument in the base of the second. The following query returns
the logarithms of column B in base 10.</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT B, LOG(B, 10)
 </B> 2<B>  FROM NUMBERS;</B>

          B LOG(B,10) 
----------- ---------
          4  1.660964
       .707 -6.640962
          9 1.0479516
         42 .61604832
         55 .57459287
        5.3 1.3806894
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">MOD</FONT></H3>
<P>You have encountered <TT>MOD</TT> before. On Day 3, &quot;Expressions, Conditions,
and Operators,&quot; you saw that the ANSI standard for the modulo operator <TT>%</TT>
is sometimes implemented as the function <TT>MOD</TT>. Here's a query that returns
a table showing the remainder of A divided by B:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> SELECT A, B, MOD(A,B)
 </B> 2 <B> FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">        A         B  MOD(A,B)
--------- --------- ---------
   3.1415         4    3.1415
      -45      .707     -.459
        5         9         5
  -57.667        42   -15.667
       15        55        15
     -7.2       5.3      -1.9
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">POWER</FONT></H3>
<P>To raise one number to the power of another, use <TT>POWER</TT>. In this function
the first argument is raised to the power of the second:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, B, POWER(A,B)
 </B> 2<B>  FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">ERROR:
ORA-01428: argument '-45' is out of range
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>At first glance you are likely to think that the first argument can't be negative.
But that impression can't be true, because a number like -4 can be raised to a power.
Therefore, if the first number in the <TT>POWER</TT> function is negative, the second
must be an integer. You can work around this problem by using <TT>CEIL</TT> (or <TT>FLOOR</TT>):</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, CEIL(B), POWER(A,CEIL(B))
  </B>2<B>  FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">       A   CEIL(B) POWER(A,CEIL(B))
--------- --------- ----------------
   3.1415         4          97.3976
      -45         1              -45
        5         9          1953125
  -57.667        42        9.098E+73
       15        55        4.842E+64
     -7.2         6        139314.07
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>That's better!
<H3><FONT COLOR="#000077">SIGN</FONT></H3>
<P><TT>SIGN</TT> returns <TT>-1</TT> if its argument is less than <TT>0</TT>, <TT>0</TT>
if its argument is equal to <TT>0</TT>, and <TT>1</TT> if its argument is greater
than <TT>0</TT>, as shown in the following example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, SIGN(A)
</B>  2<B>  FROM NUMBERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">        A   SIGN(A)
--------- ---------
   3.1415         1
      -45        -1
        5         1
  -57.667        -1
       15         1
     -7.2        -1
        0         0
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<P>You could also use <TT>SIGN</TT> in a <TT>SELECT WHERE</TT> clause like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A
 </B> 2<B>  FROM NUMBERS
 </B> 3 <B> WHERE SIGN(A) = 1;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">        A
---------
   3.1415
        5
       15
</FONT></PRE>
<H3><FONT COLOR="#000077">SQRT</FONT></H3>
<P>The function <TT>SQRT</TT> returns the square root of an argument. Because the
square root of a negative number is undefined, you cannot use <TT>SQRT</TT> on negative
numbers.</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT A, SQRT(A)
 </B> 2<B>  FROM NUMBERS;
</B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">ERROR:
ORA-01428: argument '-45' is out of range
</FONT></PRE>
<P>However, you can fix this limitation with <TT>ABS</TT>:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT ABS(A), SQRT(ABS(A))
 </B> 2<B>  FROM NUMBERS;
</B>
   ABS(A) SQRT(ABS(A))
--------- ------------