19.Functions (Number Functions)

SQL

Functions : Functions are pre-defined programs which returns always one value

Types of Functions: 1. Single Row Functions 2. Group Functions

Single Row Functions : The function which acts on each and every row.

Types of single row functions

  1. Characters / String Functions
  2. Numbers Functions
  3. Date functions
  4. Conversion Functions

Number Functions

SQL> select 5+3 from dual;

5+3

     8

SQL> select mod(9,2) from dual; — To return the remainder 9/2 = 1

MOD(9,2)

     1

SQL> select mod(8,2) from dual;

MOD(8,2)

     0

SQL> select greatest(10,20,12) from dual; — To return the biggest from the given list of values

GREATEST(10,20,12)

            20

SQL> select least(10,20,12) from dual; — To return the small number for the list

LEAST(10,20,12)

         10

SQL> select sin(90) from dual;

SIN(90)

.893996664

SQL> select cos(90) from dual;

COS(90)

-.44807362

SQL> select tan(45) from dual;

TAN(45)

1.61977519

SQL> select round(12.345,2) from dual; — Used to roundup the value when the last digit is > or < 5

ROUND(12.345,2)

      12.35

SQL> select round(12.342,2) from dual;

ROUND(12.342,2)

      12.34

SQL> select round(345.231,2) from dual;

ROUND(345.231,2)

      345.23

SQL> select round(345.237,2) from dual;

ROUND(345.237,2)

      345.24

SQL> select round(sin(90)) from dual;

ROUND(SIN(90))

         1

SQL> select round(cos(90)) from dual;

ROUND(COS(90))

         0

SQL> select round(tan(45)) from dual;

ROUND(TAN(45))

         2

SQL> select trunc(34.55) from dual; — To remove the decimal part and return only the integer

TRUNC(34.55)

      34

SQL> select trunc(99.99) from dual;

TRUNC(99.99)

      99

SQL> select trunc(tan(45)) from dual;

TRUNC(TAN(45))

         1

SQL>