Functional

Quick Understanding Of Oracle Number Functions

NUMBER FUNCTIONS IN ORACLE

• Particularly in business everything is guided by numbers
• •          Fortunately Oracle, provides functions which deal with numbers.
• •          Oracle functions deal with three classes of numbers:

            Single values, group values AND list values.

ü        Single value, is one number, such as 24.45.

ü        Group values, is all numbers in a column of a table.

ü        List values, is a series of numbers in columns of a row.

Lets best understand by an example consider the climate table below.

CityName       MinTemp        MaxTemp       DatedOn

——————————————————————

Mumbai         33.33                40.5         12-mar-2010

Chennai         32.33                 42.50      12-mar-2010

Calcutta        34.45                 38.0        12-mar-2010

Delhi              36.89                46.89      12-mar-2010

Mumbai         33.45                 45.89      13-mar-2010

Single value, is one value in a column.

Group values, is values in a column, of all rows.for example all values of MinTemp column.

List Values, is collection of values in columns of a particular row. For example 33.33 and 40.5 are from the first row of the above table.

• Functions acting on single values.

Function                                                                         Description

————————————————————————————————-

Value1 + value2                        Here + function, adds value1 with value2.

Value1 – value2                        Here – function, Substracts value2 from value1.

Value1 * value2                        Here * function, multiplies value2 with value1.

Value1 / value2                        Here / function, Divides value1 with value2.

ABS(value)                     Returns ABSolute value, ie returns positive value. 

SIGN(value)                      Returns,

   1  if value positive,

                -1 if value negative,

      0 if value zero.

CEIL(value)                     Returns Integer, just above the given value or equal to the value.

FLOOR(value)                  Returns Integer, just below the given or equal to the value.

MOD(value,divisor)        Returns, reminder of value/divisor.

NVL(value,substitute)              Substitutes value, if value is null.

POWER(value,exponent)   Returns, Value raised to an exponent.

SQRT(value)                    Returns, Square root of value

TRUNC(value, precision)   Returns, Value truncated to precision.

ROUND(value,precision)    Returns, value Rounded to precision.

 

Before we see some examples, lets’ understand what does dual mean. Dual is a dummy table in oracle database, which is used for testing sql statements.

Let us work some examples, making use of functions acting on single values. Here letters in blue are results given by oracle when respective query is executed

SQL>select 6 + 2 from dual;

6 + 2

——–

   8

SQL>select 6 – 2 from dual;

6 – 2

——

   4

SQL>select 6 * 2 from dual;

6 * 2

——-

   12

SQL>select 6 / 2 from dual;

6/2

—-

  3

SQL>select ABS(-121) from dual;

ABS(-121)

————–

       121

SQL>select ABS(121) from dual;

ABS(121)

————-

     121

SQL> select ABS(-121) from dual;

 ABS(-121)                                                                      

—————-                                                                     

       121                                                                      

SQL> select sign(121) from dual;

 SIGN(121)                                                                     

—————-                                                                     

         1                                                                     

SQL> select sign(-121) from dual;

SIGN(-121)                                                                     

—————–                                                                     

        -1                                                                      

SQL> select sign(0) from dual;

   SIGN(0)                                                                     

—————–                                                                     

         0             

SQL> select CEIL(121.22) from dual;

CEIL(121.22)

——————–

         122

SQL> select CEIL(-121.22) from dual;

CEIL(-121.22)

———————

         -121

SQL> select CEIL(121) from dual;

 CEIL(121)

—————–

       121

SQL> select CEIL(-121) from dual;

CEIL(-121)

—————–

      -121

SQL> select FLOOR(121.22) from dual;

FLOOR(121.22)

———————–

          121

 

SQL> select FLOOR(-121.22) from dual;

FLOOR(-121.22)

————————

          -122

SQL> select FLOOR(121) from dual;

FLOOR(121)

——————

   121

SQL> select FLOOR(-121) from dual;

FLOOR(-121)

——————-

       -121

SQL> select MOD(10,3) from dual;

 MOD(10,3)

—————-

         1

SQL> select MOD(10,2) from dual;

 MOD(10,2)

—————–

         0

SQL> select MOD(10,0) from dual;

 MOD(10,0)

—————–

        10

SQL> select MOD(0,10) from dual;

 MOD(0,10)

—————-

         0

SQL> select MOD(0,0) from dual;

  MOD(0,0)

—————

         0

Consider  the climate table below

 

CityName           MinTemp       MaxTemp              DatedOn

——————————————————————————-

Secunderabad           27                33              12-feb-2008

Hyderabad               24                35              23-nov-2008

Ahmedabad              34                                        26-mar-2008

Gaziabad                 23                34              24-sep-2008

 

On observing the above table, we find that in 3rd row, value in, column MaxTemp is unavailable. Now by making use of NVL function we can substitute the unavailable data with some value.

Select CityName, NVL(MaxTemp,33.5) from dual;

CityName               MaxTemp

—– ———————————-

 Secunderabad          33

 Hyderabad                35 

 Ahmedabad              33.5

 Gaziabad                  34

Remember

ü       NVL function does not update the data base. It temporarily substitutes and displays on the screen.

SQL> select POWER (2, 3) from dual;

POWER (2,3)

——————-

         8

SQL> select SQRT(4) from dual;

   SQRT(4)

—————–

         2

SQL> select ROUND(34.4556,2) from dual;

ROUND(34.4556,2)

—————————-

           34.46

SQL> select ROUND(34.4556,3) from dual;

ROUND(34.4556,3)

—————————-

          34.456

SQL> select ROUND(-34.4556,2) from dual;

ROUND(-34.4556,2)

—————————-

           -34.46

SQL> select TRUNC(34.4556,2) from dual;

TRUNC(34.4556,2)

—————————

           34.45

SQL> select TRUNC(34.4556,3) from dual;

TRUNC(34.4556,3)

—————————

          34.455

• Functions acting on Group of values.

These Functions act, on values, of a column, in all rows, of a table.

The  most commonly used functions are

 

Function                                                   Description

————————————————————————————————-

AVG(ColumnName)                       Average of all values in the column.

COUNT(ColumnName)         number – of rows or of values in column.

SUM(ColumnName)            sum total, of all values in the column.

MIN(ColumnName)                        Returns least of all values in that column.

MAX(ColumnName)                       Returns highest of all values in that column.

STDDEV(Columnname)                 Usual statistical meaning.

VARIANCE(ColumnName)              Usual statistical meaning,

 

Lets’ consider the emp table, from the scott/tiger  schema of oracle database

EMPNO   ENAME    JOB                MGR   HIREDATE     SAL        COMM   DEPTNO

7369        SMITH         CLERK        7902  17-DEC-80     800           20              20

7499        ALLEN         SALESMAN   7698  20-FEB-81     1600         300            30

7521        WARD         SALESMAN   7698  22-FEB-81     1250          500           30

7566        JONES         MANAGER    7839   02-APR-81       2975         20             20

7654        MARTIN       SALESMAN   7698  28-SEP-81       1250         1400          30

7698        BLAKE         MANAGER    7839   01-MAY-81       2850         30            30

7782        CLARK         MANAGER    7839   09-JUN-81        2450                  10             10

7788        SCOTT        ANALYST    7566  19-APR-87        3000                  20             20

7839        KING           PRESIDENT           17-NOV-81        5000        10             10

7844        TURNER       SALESMAN   7698  08-SEP-81        1500          0       30             20

7876        ADAMS        CLERK        7788  23-MAY-87       1100          20           20

7900        JAMES         CLERK        7698  03-DEC-81        950            30           30

7902        FORD          ANALYST    7566  03-DEC-81        3000          20           20

7934        MILLER        CLERK        7782  23-JAN-82        1300                    10           10

 

Lets work some examples, the letters in blue are the results returned by the oracle database,in reply to a query

Find the maximum salary paid

SQL> select MAX(sal) from emp;

MAX(SAL)

————–

    5000

Find the minimum salary paid

SQL> select MIN(sal) from emp;

  MIN(SAL)

—————

       800

Find the average salary paid

SQL> select AVG(sal) from emp;

  AVG(SAL)

—————–

2073.21429

Find the total amount paid as salaries to the employees

SQL> select SUM(sal) from emp;

  SUM(SAL)

—————–

     29025

COUNT functions usage is situational:

• Is used to count the number of rows in a table, if the argument is *(asterisk)

 SQL> select COUNT(*) from emp;

  COUNT(*)

——————

        14

• Is used to count number of available data in a column of a table.

Find the number of,  NOT NULL values, in the column mgr of emp table?

SQL> select COUNT(mgr) from emp;

COUNT(MGR)

——————–

        13

• 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)

———————————-

                 5

There are 5 types of JOB are in emp table.

 

Lets write a query to find number, of NULL values in mgr column of emp table

SQL> select COUNT(*)-COUNT(mgr) from emp;

COUNT(*)-COUNT(MGR)

———————————–

                  1

There is one NULL, in mgr column, of emp table.

Remember

ü       Asterisk can not be used with any other GROUP function, other than COUNT.

ü       DISTINCT finds its usage mostly with COUNT function.

ü       COUNT can be used either with character column or number column, where as other GROUP functions can be used only with numerical data.

ü       By now you should have observed, COUNT does not perform any arithmetic operations with the values of a column but it either counts number of values in a column or number rows in a table.

ü       The alternative to DISTINCT is ALL, which is the default.

• Functions acting on list of values

These functions act on values, in two or more columns, of a row, in a table

The most frequently used functions are described in table below

 

FUNCTION                                                                   DESCRIPTION

—————————————————————————————————————————–

LEAST(value1,value2,value3,..)                 Returns least value,among the values in the argument list.

GREATEST(value1,value2,value3,..)    Returns greatest value, among the values in the argument                

                                                               List

Consider the StudentMarks  table below, with columns as student name, marks in physics, maths,

Chemistry

StudentName             MathMarks   PhysicsMarks             ChemistryMarks

——————————————————————————————————

Lova Raju                       67                  76                          89

Siri                               100                 99                          99

Viji                                99                  90                          88

Koti                               98                   88                                   87

 

Now write a query that would display student name and highest mark obtained, among the three subjects

Sql>select StudentName,Greatest(MathMarks,PhysicsMarks,ChemistryMarks) as HighestMark from StudentMarks;

ENAME      HIGHESTMARK

————————————-

Lova Raju             89

Siri                      100

Viji                        99

Koti                       98

 

Write a query that would display student name and the least mark obtained, among the three subjects

Sql> select StudentName, LEAST(MathMarks,PhysicsMarks,ChemistryMarks) as LowestMark from StudentMarks;

• NULLs in -List Value Functions  and Single Value Functions:
  • List value functions treat NULL values same as that of single value functions.
  • Single value functions produce NULL result, if any value is NULL.
  • List value functions produce NULL result, if any value in the list is NULL
• NULLS in – GROUP Value Functions :
  • Group value functions treat NULL values differently than single value functions.
  • Group value functions ignore NULL values and go ahead with the result.

            Remember

ü       Group value functions, ignore NULL values

ü       Single value and List value functions, don’t  ignore NULL values.

 

About the Author

The author is a trainer in oracle and Java

u can get in toch with me on raju.allu@yahoo.com

Tangible Functional Programming

Joseph Joseph 8-Piece Food Preparation Nesting Set $22.99 Save space with the nesting 8 piece multi-color preparation bowl set from Joseph Joseph. This set of preparation bowls comes with four measuring cups, small mixing bowl, sieve, colander, and a large mixing bowl that are designed to be stacked together to take up the least amount of space possible. Each part of the set has carrying handles to make food preparation easier and the base of the set has…

simplehuman Tension Arm Paper Towel Holder, Stainless Steel $19.78 Make things a little easier in the kitchen with the simplehuman® Tension-Arm Paper Towel Holder. It features a sturdy, weighted base allows for easy one-handed operation and keeps the holder steady when you grab a sheet. A variable tension arm applies jus…

Escali Primo Digital Multifunctional Scale $29.95 Accurately measures in .01 oz or 1 gram increments. 11-lb. or 5 kg maximum weight limit….

Thelonious Monk With John Coltrane $4.81 Among Thelonious Monk’s long stays at New York’s legendary Five Spot was a six-month period in 1957 with possibly his most brilliant band, with John Coltrane finding fuel in Monk’s music for his harmonic explorations. The quartet only recorded three studio tracks: a sublime reading of Monk’s ballad “Ruby, My Dear”; a loping version of “Nutty”; and a stunning version of “Trinkle Tinkle” on which Tr…

Cari Saluti $10.15 All products are BRAND NEW and factory sealed. Fast shipping and 100% Satisfaction Guaranteed….

Beatz & Bobz 3 $7.81 All products are BRAND NEW and factory sealed. Fast shipping and 100% Satisfaction Guaranteed….

Signing Naturally – Student Videotext: Level 1 Functional Notional Approach (American Sign Language Series) $1.98 …

Legs, Legs, Legs, Functional Leg Exercises for Conditioning & Rehabilitation [VHS] $19.95 Vern Gambetta, internationally renowned sports performance enhancement and rehabilitation specialist has created a great innovative approach to training and rehabilitating the legs! This video provides a series of leg exercises designed to enhance function, improve performance, prevent injury, or rehabilitate an injury. The exercises will improve leg strength and joint stability through dynamic ex…

An Immigrant’s Gift – The Life of Quality Pioneer Joseph M. Juran $75.00 For more than 70 years, the teachings and writings of J.M. Juran have had a profound impact on the quality of the products we buy and use everyday. His pioneering work in quality is heralded all around the world, and yet the story of this very private individual has remained unknown, until now. This biographic portrait tells the lyrical story of a humble immigrant who overcame dire poverty and a b…

Smith’s PP1 Pocket Pal Multifunction Sharpener $7.03 Sleek and slim, Smith’s Pocket Pal multi-functional knife sharpener is ideal for backpackers, hikers, hunters, and fishermen who want to ensure their blades have the sharpest edge wherever they roam. Fold-out diamond-coated rod (view larger). Great for sharpening serrated blades (view larger). Smith’s PP1 Pocket Pal Multifunction SharpenerAt a Glance: Slim, lightwei…

Brother LS2300PRW Limited Edition Project Runway Sewing Machine (Refurbished) $49.99 Create fashionable trendy clothing and more with this Limited Edition Project Runway sewing machine by Brother. The sewing machine features 20 stitch functions and an easy bobbin system to get you creating those fabulous pieces for your wardrobe.

Brother LX2500 Heavy Duty 17-Stitch Free-Arm Sewing Machine (Refurbished) $59.99 17 Built-in Stitches, Including an Automatic 4-step Buttonholer38 Stitch FunctionsBrightly Lit LED Work AreaFree Arm for Sewing Cuffs and SleevesEasy Automatic Bobbin Winding SystemEasy-to-use Blind Hem StitchJam Resistant Drop-in Top Bobbin

Indoor 5-light Luxury Crystal Chandelier $250.99 The chrome finish and the delicate crystal accents add distinction to this lighting fixture and coalesces form with function. This indoor 5-light chandelier features an elegant design.

Brother CS5055PRW Project Runway Computerized Sewing Machine $149.99 Fifty (50) built in true stitches with 87 stitch functions5 styles of 1- step automatic buttonholesAccessory kit which includes extra feet

Broadway Black Wall Mounted A/V Console $121.05 Give any room a dash of modern style with this stylish and innovative wall mounted A/V console. Finished in ‘goes-with-everything’ black, this sleek yet functional console offers two generous storage compartments.

Outdoor Chair Cushions (Set of 4) $67.99 All Weather chair cushions are an elegant and functional addition to your patio decorDecorative accessory comes in a set of four cushionsCushions are UV-resistant and can be used for indoors or outdoors

Pacific Gear 30-inch Drop-bottom Wheeled Duffel Bag $42.99 This stylish and functional duffel bag from Pacific Gear features a drop-bottom compartment with compression straps and clasps to give you extra storage. This bag is constructed of lightweight, durable polyester.

Lasko 36-inch Tower Fan with Remote Control $51.99 Add cool comfort to your daily life with this Lasko tower fanHandy fan has a slim, vertical design that doesn’t take up too much floor space36-inch fan comes with a multi-function remote control

Kraus Vessel Sink Pop-up Drain and Mounting Ring $34.95 Enhance the look and function of your vessel sink with a pop-up drain and mounting ringPlumbing hardware is made of solid brassPop-up drain has an umbrella drain design

Eddie Bauer Complete Care Playard in Stonewood $103.99 Fun and functional, this play yard from Eddie Bauer is a definite must-have. This play yard features an elevated changing station with parent organizer.