Quick Understanding Of Oracle Number Functions

NUMBER FUNCTIONS IN ORACLETRUNC(34.4556,3)
- Particularly in business everything is guided by          34.455
numbers- Functions acting on Group of values.
- •          Fortunately Oracle, providesThese Functions act, on values, of a column, in all rows,
functions which deal with numbers.of a table.
- •          Oracle functions deal with threeThe  most commonly used functions are
classes of numbers: 
            Single values, group values ANDFunction       
list values. Â Â Â Â Â Â Â Â Â Â Â  Description
ü        Single value, is one number, such as----------------------
24.45.AVG(ColumnName)   
ü        Group values, is all numbers in a                   Average of all values
column of a table.in the column.
ü        List values, is a series of numbers inCOUNT(ColumnName)         number - of rows
columns of a row.or of values in column.
Lets best understand by an example consider theSUM(ColumnName)            sum total, of all
climate table below.values in the column.
CityName       MinTemp       MIN(ColumnName)    
MaxTemp       DatedOn                   Returns least of all
Mumbai         33.33               values in that column.
40.5         12-mar-2010MAX(ColumnName)   
Chennai         32.33                    Returns highest of all
               42.50      12-mar-2010values in that column.
Calcutta        34.45                STDDEV(Columnname)                
38.0        12-mar-2010Usual statistical meaning.
Delhi              36.89VARIANCE(ColumnName)             
               46.89      12-mar-2010Usual statistical meaning,
Mumbai         33.45  
               45.89      13-mar-2010Lets' consider the emp table, from the scott/tiger
Single value, is one value in a column. schema of oracle database
Group values, is values in a column, of all rows.forEMPNO   ENAME    JOB         
example all values of MinTemp column.      MGR   HIREDATE    
List Values, is collection of values in columns of aSAL        COMM   DEPTNO
particular row. For example 33.33 and 40.5 are from7369        SMITH        
the first row of the above table.CLERK        7902  17-DEC-80    
- Functions acting on single values.800           20              20
Function       7499        ALLEN        
 Â Â  DescriptionSALESMAN   7698  20-FEB-81     1600 
----------------------       300            30
Value1 + value2       7521        WARD         SALESMAN  
                Here + function, adds7698  22-FEB-81     1250         
value1 with value2.500           30
Value1 – value2       7566        JONES        
                Here – function,MANAGER    7839   02-APR-81       2975
Substracts value2 from value1.        20             20
Value1 * value2       7654        MARTIN       SALESMAN  
                Here * function, multiplies7698  28-SEP-81       1250        
value2 with value1.1400          30
Value1 / value2       7698        BLAKE        
                Here / function, DividesMANAGER    7839   01-MAY-81      
value1 with value2.2850         30            30
ABS(value)                     Returns7782        CLARK        
ABSolute value, ie returns positive value. MANAGER    7839   09-JUN-81       
SIGN(value)                     2450                  10       
Returns,     10
   1  if value positive,7788        SCOTT        ANALYST   
                -1 if value negative,7566  19-APR-87        3000         
      0 if value zero.        20             20
CEIL(value)                     Returns7839        KING          
Integer, just above the given value or equal to thePRESIDENT           17-NOV-81    
value.   5000        10             10
FLOOR(value)                  Returns7844        TURNER       SALESMAN  
Integer, just below the given or equal to the value.7698  08-SEP-81        1500         
MOD(value,divisor)        Returns, reminder of0       30             20
value/divisor.7876        ADAMS       
NVL(value,substitute)              SubstitutesCLERK        7788  23-MAY-87      
value, if value is null.1100          20           20
POWER(value,exponent)   Returns, Value raised to7900        JAMES        
an exponent.CLERK        7698  03-DEC-81       
SQRT(value)                   950            30           30
Returns, Square root of value7902        FORD         
TRUNC(value, precision)   Returns, Value truncatedANALYST    7566  03-DEC-81        3000
to precision.         20           20
ROUND(value,precision)    Returns, value Rounded7934        MILLER       
to precision.CLERK        7782  23-JAN-82       
 1300                    10     
Before we see some examples, lets' understand what     10
does dual mean. Dual is a dummy table in oracle 
database, which is used for testing sql statements.Lets work some examples, the letters in blue are the
Let us work some examples, making use of functionsresults returned by the oracle database,in reply to a
acting on single values. Here letters in blue are resultsquery
given by oracle when respective query is executedFind the maximum salary paid
SQL>select 6 + 2 from dual;SQL> select MAX(sal) from emp;
6 + 2MAX(SAL)
   8    5000
SQL>select 6 - 2 from dual;Find the minimum salary paid
6 - 2SQL> select MIN(sal) from emp;
   4  MIN(SAL)
SQL>select 6 * 2 from dual;       800
6 * 2Find the average salary paid
   12SQL> select AVG(sal) from emp;
SQL>select 6 / 2 from dual;  AVG(SAL)
6/22073.21429
  3Find the total amount paid as salaries to the
SQL>select ABS(-121) from dual;employees
ABS(-121)SQL> select SUM(sal) from emp;
       121  SUM(SAL)
SQL>select ABS(121) from dual;     29025
ABS(121)COUNT functions usage is situational:
     121- Is used to count the number of rows in a table, if the
SQL> select ABS(-121) from dual;argument is *(asterisk)
 ABS(-121)                     SQL> select COUNT(*) from emp;
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  COUNT(*)
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  14
      - Is used to count number of available data in a column
 Â Â Â        of a table.
SQL> select sign(121) from dual;Find the number of,  NOT NULL values, in the column
 Â Â Â Â Â Â Â Â Â Â Â Â Â mgr of emp table?
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â SQL> select COUNT(mgr) from emp;
        COUNT(MGR)
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â  13
SQL> select sign(-121) from dual;- DISTINCT key word, can be used, with every
 Â Â Â Â Â Â Â Â Â Â Â Â Â GROUP Function, but its usage is more felt, with
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â COUNT function. Let us see apply DISTINCT to find,
       how many types of jobs are there in emp table
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â SQL> select COUNT(DISTINCT JOB) from emp;
               COUNT(DISTINCTJOB)
SQL> select sign(0) from dual;                 5
  There are 5 types of JOB are in emp table.
 Â Â Â Â Â Â Â Â Â Â Â Â 
 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Lets write a query to find number, of NULL values in
         0             mgr column of emp table
SQL> select CEIL(121.22) from dual;SQL> select COUNT(*)-COUNT(mgr) from emp;
CEIL(121.22)COUNT(*)-COUNT(MGR)
         122                  1
SQL> select CEIL(-121.22) from dual;There is one NULL, in mgr column, of emp table.
CEIL(-121.22)Remember
         -121ü       Asterisk can not be used with any
SQL> select CEIL(121) from dual;other GROUP function, other than COUNT.
 CEIL(121)ü       DISTINCT finds its usage mostly with
       121COUNT function.
SQL> select CEIL(-121) from dual;ü       COUNT can be used either with
CEIL(-121)character column or number column, where as other
      -121GROUP functions can be used only with numerical
SQL> select FLOOR(121.22) from dual;data.
FLOOR(121.22)ü       By now you should have observed,
          121COUNT does not perform any arithmetic operations
 with the values of a column but it either counts number
SQL> select FLOOR(-121.22) from dual;of values in a column or number rows in a table.
FLOOR(-121.22)ü       The alternative to DISTINCT is ALL,
          -122which is the default.
SQL> select FLOOR(121) from dual;- Functions acting on list of values
FLOOR(121)These functions act on values, in two or more
   121columns, of a row, in a table
SQL> select FLOOR(-121) from dual;The most frequently used functions are described in
FLOOR(-121)table below
       -121 
SQL> select MOD(10,3) from dual; Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
 MOD(10,3)             DESCRIPTION
         1LEAST(value1,value2,value3,..)     
SQL> select MOD(10,2) from dual;           Returns least value,among the
 MOD(10,2)values in the argument list.
         0GREATEST(value1,value2,value3,..)    Returns
SQL> select MOD(10,0) from dual;greatest value, among the values in the
 MOD(10,0)argument                
        10 Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 
SQL> select MOD(0,10) from dual;            List
 MOD(0,10)Consider the StudentMarks  table below, with
         0columns as student name, marks in physics, maths,
SQL> select MOD(0,0) from dual;Chemistry
  MOD(0,0)StudentName             MathMarks  
         0PhysicsMarks             ChemistryMarks
Consider  the climate table below---------------------------
 Lova Raju                      
CityName           MinTemp      67                 
MaxTemp              DatedOn76                          89
Secunderabad           27     Siri                        
          33              12-feb-2008      100                
Hyderabad               24     99                          99
          35             Viji                        
23-nov-2008       99                 
Ahmedabad              34     90                          88
                            Koti                       
     26-mar-2008       98                  
Gaziabad                 23     88               
          34                                87
24-sep-2008 
 Now write a query that would display student name
On observing the above table, we find that in 3rd row,and highest mark obtained, among the three subjects
value in, column MaxTemp is unavailable. Now bySql>select
making use of NVL function we can substitute thestryMarks) as HighestMark from StudentMarks;
unavailable data with some value.ENAME      HIGHESTMARK
Select CityName, NVL(MaxTemp,33.5) from dual;Lova Raju             89
CityName               MaxTempSiri                      100
 Secunderabad          33Viji                        99
 Hyderabad                35 Koti                       98
 Ahmedabad              33.5 
 Gaziabad                  34Write a query that would display student name and
Rememberthe least mark obtained, among the three subjects
ü       NVL function does not update the dataSql> select StudentName,
base. It temporarily substitutes and displays on theLEAST(MathMarks,PhysicsMarks,ChemistryMarks) as
screen.LowestMark from StudentMarks;
SQL> select POWER (2, 3) from dual;- NULLs in -List Value Functions  and Single Value
POWER (2,3)Functions:
         8- List value functions treat NULL values same as that
SQL> select SQRT(4) from dual;of single value functions.
   SQRT(4)- Single value functions produce NULL result, if any
         2value is NULL.
SQL> select ROUND(34.4556,2) from dual;- List value functions produce NULL result, if any value
ROUND(34.4556,2)in the list is NULL
           34.46NULLS in - GROUP Value Functions :
SQL> select ROUND(34.4556,3) from dual;- Group value functions treat NULL values differently
ROUND(34.4556,3)than single value functions.
          34.456- Group value functions ignore NULL values and go
SQL> select ROUND(-34.4556,2) from dual;ahead with the result.
ROUND(-34.4556,2)            Remember
           -34.46ü       Group value functions, ignore NULL
SQL> select TRUNC(34.4556,2) from dual;values
TRUNC(34.4556,2)ü       Single value and List value functions,
           34.45don't  ignore NULL values.
SQL> select TRUNC(34.4556,3) from dual;