Number functions
Sponsored Links
This presentation is the property of its rightful owner.
1 / 16

Number Functions PowerPoint PPT Presentation


  • 78 Views
  • Uploaded on
  • Presentation posted in: General

Number Functions. Review. single-row character functions character case-manipulation functions LOWER, UPPER, INITCAP character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR LPAD, RPAD TRIM REPLACE. What Will I Learn?. single-row number functions ROUND, TRUNC, and MOD

Download Presentation

Number Functions

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Number Functions


Review

  • single-row character functions

    • character case-manipulation functions

      • LOWER, UPPER, INITCAP

    • character-manipulation functions

      • CONCAT, SUBSTR,

      • LENGTH, INSTR

      • LPAD, RPAD

      • TRIM

      • REPLACE


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


ROUND

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


ROUND

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


ROUND

  • 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


  • Login