Thursday, 11 July 2013

Number Functions


"Number functions takes  numeric data as input and returns numeric values."

The following are numeric functions in SQL.
1)    Ceil()
2)    Floor()
3)    Sign()
4)    Power()
5)    Abs()
6)    Mod()
7)    Round()
8)    Trunc()
9)    Sqrt()

1.Ceil(): Returns smallest integer greater than or equal to ‘arg’.

Syntax:    select ceil(arg) from dual;

                         Arg may be a integer or float.
Ex:
1.SQL> select ceil(15.7) from dual;

CEIL(15.7)
---------- 16

2.SQL> select ceil(15.734) from dual;

CEIL(15.734)
------------ 16

3.SQL> select ceil(-15.734) from dual;

CEIL(-15.734)
------------ -15

4.SQL> select ceil(0) from dual;

   CEIL(0)
---------   0

5.SQL> select ceil(null) from dual;

CEIL(NULL)
----------

6.SQL> select ceil() from dual;
select ceil() from dual
       *
ERROR at line 1:
ORA-00909: invalid number of arguments

7.SQL> select ceil(123.9) from dual;

CEIL(123.9)
----------- 124

2.floor(): Returns largest integer lower or equal to ‘arg’.

Syntax:        select floor(arg) from dual;

                           Arg may be a integer or float.
Ex:
1.SQL> select floor(123.9) from dual;

FLOOR(123.9)
------------  123

2.SQL> select floor(-123.9) from dual;

FLOOR(-123.9)
------------- -124

3.SQL> select floor(-123.00) from dual;

FLOOR(-123.00)
-------------- -123

4.SQL> select floor(-123.) from dual;

FLOOR(-123.)
----------   -123

5.SQL> select floor(0) from dual;

  FLOOR(0)
---------- 0

6.SQL> select floor(null) from dual;

FLOOR(NULL)
-----------

3.abs(): returns absolute value of ‘arg’.

Syntax:        select abs(arg) from dual;

                       Arg may be a integer or float
Ex:
1.SQL> select abs(12) from dual;

   ABS(12)
----------   12

2.SQL> select abs(-12) from dual;

  ABS(-12)
----------   12

3.SQL> select abs(-12.56) from dual;

ABS(-12.56)
----------    12.56

4.SQL> select abs(12.56) from dual;

ABS(12.56)
----------  12.56

5.SQL> select abs(null) from dual;

 ABS(NULL)
----------

4.sqrt(): Returns square root value.

Syntax:     select sqrt(arg) from dual;

                    Arg may be integer/float.
Ex:

1.SQL> select sqrt(23) from dual;

  SQRT(23)
------       4.79583152

2.SQL> select sqrt(25) from dual;

  SQRT(25)
---------- 5

3.SQL> select sqrt(-25) from dual;
select sqrt(-25) from dual
            *
ERROR at line 1:
ORA-01428: argument '-25' is out of range

4.SQL> select sqrt(null) from dual;

SQRT(NULL)
----------

5.SQL> select sqrt(563.75) from dual;

SQRT(563.75)
------------   23.7434201

4.power(): returns m raised nth power ….

Syntax:     select power(m,n) from dual;

     Here m,n are may be integers/floate.
Ex:
1.SQL> select power(25,5) from dual;

POWER(25,5)
----------- 9765625

2.SQL> select power(-25,5) from dual;

POWER(-25,5)
------------    -9765625

3.SQL> select power(25,-5) from dual;

POWER(25,-5)
------------ 1.0240E-07

4.SQL> select power(-25,-5) from dual;

POWER(-25,-5)
------------- -1.024E-07

5.SQL> select power(25.23,5) from dual;

POWER(25.23,5)
-------------- 10223185.8

6.SQL> select power(25.23,5.34) from dual;

POWER(25.23,5.34)
----------------   30636427.3

7.SQL> select power(-25.23,5.34) from dual;
select power(-25.23,5.34) from dual
             *
ERROR at line 1:
ORA-01428: argument '-25.23' is out of range


8.SQL> select power(-25.23,-5.34) from dual;
select power(-25.23,-5.34) from dual
             *
ERROR at line 1:
ORA-01428: argument '-25.23' is out of range
9.SQL> select power(25.23,-5.34) from dual;

POWER(25.23,-5.34)
------------------ 3.2641E-08

10.SQL> select power(25.23,null) from dual;

POWER(25.23,NULL)
-----------------

11.SQL> select power(null,null) from dual;

POWER(NULL,NULL)
----------------


12.SQL> select power(25.23) from dual;
select power(25.23) from dual
       *
ERROR at line 1:
ORA-00909: invalid number of argumen


6.Mod():  It will the remainder obtained by dividing arg1 by arg2.

Syntax:   select  mod(arg1,arg2) from dual;

                 Here arg1,arg2 are integers/floats


Ex: 
 1.SQL> select mod(34,4) from dual;

MOD(34,4)

----------  2

2.SQL> select mod(34,0) from dual;

 MOD(34,0)

----------34

3.SQL> select mod(-34,0) from dual;

MOD(-34,0)

----------    -34

4.SQL> select mod(-34,-2) from dual;

MOD(-34,-2)

-----------  0

5.SQL> select mod(-34,-34) from dual;

MOD(-34,-34)

-----------      0

6.SQL> select mod(-34,-3) from dual;

MOD(-34,-3)

----------- -1

7.SQL> select mod(34,-3) from dual;

MOD(34,-3)

----------1

8.SQL> select mod(34,3.5) from dual;

 MOD(34,3.5)

----------  2.5

9.SQL> select mod(34.5,3.5) from dual;

MOD(34.5,3.5)

-------------3

10.SQL> select mod(-34.5,3.5) from dual;

MOD(-34.5,3.5)

-------------- -3

11.SQL> select mod(-34.5,-3.5) from dual;

MOD(-34.5,-3.5)

--------------- -3

12.SQL> select mod(34.5) from dual;

select mod(34.5) from dual

       *
ERROR at line 1:

ORA-00909: invalid number of arguments.

  7.Sign():  It will returns “0” for zero

             It will returns “-1” for negative integers/floating  point  values.

             It will returns “+1” for positive integers/floating  point  values. 

Syntax:    select sign(arg) from dual;

                Here arg maybe an integer/float


Ex:
1.SQL> select sign(34.5) from dual;

SIGN(34.5)

---------- 1

2.SQL> select sign(-34.5) from dual;

SIGN(-34.5)

----------- -1

3.SQL> select sign(0) from dual;

    SIGN(0)

---------- 0

4.SQL> select sign() from dual;

select sign() from dual

       *
ERROR at line 1:

ORA-00909: invalid number of arguments



5.SQL> select sign( " ") from dual;
select sign( " ") from dual

             *

ERROR at line 1:

ORA-00904: " ": invalid identifier

6.SQL> select sign('%') from dual;

select sign('%') from dual

            *

ERROR at line 1:

ORA-01722: invalid number 

8.Round():  Returns a “number rounded at  a certain number  of decimal places”.

Syntax:         select round(arg,n) from dual;

                      Here “arg” may be integer/float.

                      “n” is decimal places should be an integer.

Ex:
1.SQL> select round(12.46) from dual;

ROUND(12.46)
-----------    12

2.SQL> select round(12.46,0) from dual;

ROUND(12.46,0)
--------------      12

3.SQL> select round(12.46,1) from dual;

ROUND(12.46,1)
--------------    12.5

4.SQL> select round(12.46,2) from dual;

ROUND(12.46,2)
--------------    12.46

5.SQL> select round(12.46,-1) from dual;

ROUND(12.46,-1)
---------       10

6.SQL> select round(12.46,-2) from dual;

ROUND(12.46,-2)
---------------    0

7.SQL> select round(12.46) from dual;

ROUND(12.46)
-----------       12

8.SQL> select round(1) from dual;

  ROUND(1)
---------    1

9.SQL> select round(4736.4628,-1) from dual;

ROUND(4736.4628,-1)
-------------------   4740

10.SQL> select round(-12.56) from dual;

ROUND(-12.56)
-------------   -13

11.SQL> select round(-12.56,1) from dual;

ROUND(-12.56,1)
---------------     -12.6

12.SQL> select round(-12.56,2) from dual;

ROUND(-12.56,2)
---------------   -12.56

13.SQL> select round(-12.56,-1) from dual;

ROUND(-12.56,-1)
-------------   -10

14.SQL> select round(-12.56,-2) from dual;

ROUND(-12.56,-2)
--------------   0

15.SQL> select round(-.56,-2) from dual;

ROUND(-.56,-2)
--------------      0

16.SQL> select round(-.56,2) from dual;

ROUND(-.56,2)
------------    -.56

17.SQL> select round(-.56,1) from dual;

ROUND(-.56,1)
----------     -.6

18.SQL> select round(-125.815,-1.2) from dual;

ROUND(-125.815,-1.2)
--------------------
                -130

19.SQL> select round(-125.815,-1.5) from dual;

ROUND(-125.815,-1.5)
--------------     -130

20.SQL> select round(-125.815,-2.5) from dual;

ROUND(-125.815,-2.5)
-------------------     -100


9.Trunk(): returns a number truncated at certain number of decimal places.

Syntax:         select round(arg,n) from dual;

                      Here “arg” may be integer/float.

                      “n” is decimal places should be an integer.

Ex:
1.SQL> select trunc(125.815) from dual;

TRUNC(125.815)
---------        125

2.SQL> select trunc(125.815,0) from dual;

TRUNC(125.815,0)
----------------  125

3.SQL> select trunc(125.815,1) from dual;
TRUNC(125.815,1)
----------------   125.8

4.SQL> select trunc(125.815,2) from dual;

TRUNC(125.815,2)
----------------  125.81

5.SQL> select trunc(125.815,-1) from dual;

TRUNC(125.815,-1)
 120

 6.SQL> select trunc(125.815,-2) from dual

TRUNC(125.815,-2)
-------------     100

7.SQL> select trunc(-125.815,2) from dual;

TRUNC(-125.815,2)
--------------- -   -125.81

8.SQL> select trunc(-125.815,0) from dual;

TRUNC(-125.815,0)
-----------------  -125

9.SQL> select trunc(-123.45,-1.4) from dual;

TRUNC(-123.45,-1.4)
------------------   -120







Posted by at No comments:

Group Functions


  “An aggregate function summarizes the results of an expression over a number of rows, returning a single value”.


The general syntax for most of the aggregate functions is as follows:  

SYNTAX: aggregate_function ([DISTINCT|ALL] expression) 

DISTINCT doesn’t takes duplicate values.

ALL takes duplicate values.

All aggregate functions ignore null values except
count(*).                                                                                                                               

Some of the commonly used aggregate functions are :
•         SUM
•         COUNT
•         AVG
•         MIN
•         MAX
•         If we use the aggregate functions then you cannot use  the WHERE clause. In order to get the result what we need to use is
the HAVING clause

.SQL> select * from employee;

    EMP_ID NAME                 DEPT_NAME                SALARY
---------- -------------------- -------------------- ----------
       100 ABC                  ENG                       50000
       101 DEF                  ENG                       60000
       102 GHI                  PS                        50000
       103 JKL                  PS                        70000
       104 MNO                  SALES                     75000
       105 PQR                  MKTG                      70000
       106 STU                  SALES

7 rows selected.

1.sum(): calculate sum of values on a specified col/attribute.

Syntax select sum(col name) from table name;

 

Ex:
1. SQL> SELECT SUM(SALARY) FROM EMPLOYEE;

SUM(SALARY)
----------- 375000

2.SQL> select sum(distinct salary) from employee;

SUM(DISTINCTSALARY)
------------------- 255000

2.min(): Give minimum value on the specified col/attribute.

Syntax:  select min(col name) from table name;

Ex:
1.SQL> select  min(salary) from employee;

MIN(SALARY)
-----------50000

2.SQL> select  min(name) from employee;

MIN(NAME)
--------------------ABC

3.SQL> select  min(dept_name) from employee;

MIN(DEPT_NAME)
--------------------ENG

4.SQL> select min(distinct salary) from employee;

MIN(DISTINCTSALARY)
-------------------50000

3.max(): give maximum valu on specified col/attribute.

Syntax:  select max(col name) from table name;

Ex:
1.SQL> select  max(dept_name) from employee;

MAX(DEPT_NAME)
--------------------SALES

2.SQL> select  max(name) from employee;

MAX(NAME)
--------------------STU

3.SQL> select  max(salary) from employee;

MAX(SALARY)
----------   75000

4.SQL> select  max(emp_id) from employee;

MAX(EMP_ID)
----------- 106

4.avg():calculate avg on specified col/attribute.

Syntax:    select avg(col name) from table name;

Ex:
1.SQL> select avg(salary) from employee;

AVG(SALARY)
----------   62500

2.SQL> select avg(name) from employee;
select avg(name) from employee
           *
ERROR at line 1:
ORA-01722: invalid number

3.SQL> select avg(distinct salary) from employee;

AVG(DISTINCTSALARY)
-------------------63750

5.count(): count number ogf records on the specigic col/attribute.

Syntax : select count(col name) from table name;

Ex:
1.SQL> select count(name) from employee;

COUNT(NAME)
----------- 7

2.SQL> select count(salary) from employee;

COUNT(SALARY)
----------     6
3.SQL> select count(*) from employee;

  COUNT(*)
-----         7

4.SQL> select count(distinct dept_name) from employee;

COUNT(DISTINCTDEPT_NAME)
------------------------ 4




Posted by at No comments:
Subscribe to: Posts (Atom)