This Week we will be learning "Restricting and Sorting Data" covers the following topics. WHERE Clause - Comparison Operators; Logical Operator; Using the Logical Operator, AND; Using the logical Operator, OR; Using the Logical Operator, NOT; Nesting Logical Operators; Other Comparison Keywords; Using the Keyword, LIKE; Using the Keywords, BETWEEN...AND; Using the Keyword, IN; Using the Keyword, IS NULL ORDER BY Clause - Using the ORDER BY Clause

Expressions in a SELECT statement: Arithmetic operations can be performed using SELECT   statement as shown below.

SQL> SELECT 10+5 FROM DUAL;
      10+5
----------
        15

SQL> SELECT 10-5 FROM DUAL;
      10-5
----------
         5

SQL> SELECT 10*5 FROM DUAL;

      10*5
----------
        50

SQL> SELECT 10/5 FROM DUAL;

      10/5
----------
         2
Numeric Functions:  These functions operate on Numeric data hence is the name.

Note: Argument “num” in the following functions is any float-valued number.
ABS(num): Returns absolute value of the given number.(i.e.  Always positive value)

SQL> SELECT ABS(10) FROM DUAL;
   ABS(10)
----------
        10
SQL> SELECT ABS(-10) FROM DUAL;
  ABS(-10)
----------
        10
CEIL(num): It returns the smallest integer greater than the given number.

SQL> SELECT CEIL(123.456) FROM DUAL;

CEIL(123.456)
-------------
          124
FLOOR(num): It returns the largest integer smaller than the given value.
SQL> SELECT FLOOR(123.456) FROM DUAL;

FLOOR(123.456)
--------------
           123
LN(num) : It returns natural logarithm value of “num” .

SQL> SELECT LN(10) FROM DUAL;
    LN(10)
----------
2.30258509

LOG(m, n): It returns logarithm of  “n”  with base “m”.
SQL> SELECT LOG(100,10) FROM DUAL;
LOG(100,10)
-----------
         .5
SQL> SELECT LOG(10,100) FROM DUAL;
LOG(10,100)
-----------
          2
MOD(m, n) :  It returns remainder of “m” divided by “n”.

SQL> SELECT MOD(10,3) FROM DUAL;

 MOD(10,3)
----------
         1
POWER(m, n):  It returns value equal to “m” raised by “n”.
SQL> SELECT POWER(10,2) FROM DUAL;
POWER(10,2)
-----------
        100
ROUND(m, n): It rounds the given float-valued number “m” to the “n” places after the decimal
SQL> SELECT ROUND(1.23456) FROM DUAL;
ROUND(1.23456)
--------------
              1
SQL> SELECT ROUND(1.23456,3) FROM DUAL;

ROUND(1.23456,3)
----------------
           1.235

SQRT(m):  It calculates square root value of number “m”

SQL> SELECT SQRT(9) FROM DUAL;

   SQRT(9)
----------
         3
TRUNC(m, n): It truncates given float-valued number “m” to “n” places after the decimal.
SQL> SELECT TRUNC(1.23456) FROM DUAL;
TRUNC(1.23456)
--------------
             1
SQL> SELECT TRUNC(1.23456,3) FROM DUAL;

TRUNC(1.23456,3)
----------------
             1.234

GREATEST(expr1, expr2, …) : It finds the greatest value among the given expressions.
SQL> SELECT GREATEST(4,7,3,5,9,2) FROM DUAL;
GREATEST(4,7,3,5,9,2)
---------------------
                    9
LEAST(expr1, expr2, …): It finds the Lowest value among the given expressions.
SQL> SELECT LEAST(4,7,3,5,9,2) FROM DUAL;

LEAST(4,7,3,5,9,2)
------------------
                  2

Next Page: Tutorial 4: SQL functions - Page 2