number functions. 2 home back first prev next last review single-row character functions...

Number Functions

2home back first prev next last

Review

• single-row character functions– character case-manipulation functions

LOWER, UPPER, INITCAP

– character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTRLPAD, RPADTRIMREPLACE


What Will I Learn?

• single-row number functions– ROUND, TRUNC, and MOD– Distinguish between TRUNC and ROUND

when they are applied to a numeric value– State the implications for business when

applying TRUNC and ROUND to numeric values


Why Learn It?

• One of the reasons we put our money in a bank is to take advantage of the interest it accumulates over time.

• Banks adjust the interest rate with various economic indicators such as inflation and the stock market.

• Typically, interest rates are expressed as a percent such as 3.45%.


Why Learn It?

• What if a bank decided to round the percentage rate to 3.5%? Would it be to your advantage?

• What if they decided to just drop the decimal values and calculate the interest at 3%, would you be happy then?

• Rounding and truncating numbers play an important part in business and in turn with the databases that support these businesses as they store and access numeric data.


number functions

• accept numeric input and return numeric values

• The three number functions are:– ROUND– TRUNC– MOD


ROUNDROUND

• Used to round numbers to a specified number of decimal places.

• ROUND can also be used to round numbers to the left of the decimal point.

• ROUND can also be used with dates.

• Syntax– ROUND(column|expression, decimal places)

select round(2.55,1) from dual;


ROUNDROUND

• If the number of decimal places is a positive number, the number is rounded to that number of decimal places.

• If the number of decimal places is a negative number, numbers to the left of the decimal are rounded.

• if the number of decimal places is not specified or is zero,

the number will round to no decimal places.


ROUNDROUND

• For NUMBER values, the value n is rounded away from 0 (for example, to x+1 when x.5 is positive and to x-1 when x.5 is negative).

• For BINARY_FLOAT and BINARY_DOUBLE values, the function rounds to the nearest even value. Please refer to the examples that follow.


TRUNC

• Used to terminate the column,expression, or value to a specified number of decimal places.

• TRUNC can also be used with dates.• Syntax

– TRUNC(column|expression, decimal places) TRUNC (45.926, 2) yields 45.92

• if the TRUNC expression does not specify the number of decimal places or specifies a zero, the number is truncated to zero decimal places.

TRUNC (45.926) yields 45


TRUNC

•For NUMBER and BINARY_FLOAT and BINARY_DOUBLE values, the rule is the same now.


MOD

• Used to return the remainder when one number is divided by another.– For example, the MOD of 5 divided by 2 = 1.


MOD

• Returns n2 if n1 is 0.• This function behaves differently from the classical

mathematical modulus function when m is negative. The classical modulus can be expressed using the MOD function with this formula:

m - n * FLOOR(m/n)

FLOOR(n) returns largest integer equal to or less than n.


MOD

• MOD can be used to determine whether a value is odd or even.– If MOD(n,2) equals 0,n is even– If MOD(n,2) equals 1,n is odd

SELECT last_name, salary

FROM f_staffs

WHERE MOD(salary, 2)=0;

The above query will show the staffs whose salary is an even

number.


Terminology

• Key terms used in this lesson include:– Number functions– MOD– ROUND– TRUNC


Summary

• Select and apply the single-row number functions ROUND, TRUNC, and MOD in a SQL query

• Distinguish between the results obtained when TRUNC is applied to a numeric value and ROUND is applied to a numeric value

• State the implications for business when applying TRUNC and ROUND to numeric values

number functions. 2 home back first prev next last review single-row character functions...

Documents