ExamFinch.com My Study

Study guide

4.2 Numeric & Mathematical Functions

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

4.2 Numeric & Mathematical Functions

Key Takeaways Explain This Topic AI

ROUND(n [, d]) rounds to d decimal places; positive d rounds to the right of the decimal, d=0 or omitted rounds to the nearest integer, and negative d rounds to the left (tens, hundreds, etc.).

TRUNC(n [, d]) truncates digits without rounding; positive d truncates decimal places, d=0 or omitted removes all decimals, and negative d replaces digits to the left of the decimal point with zeros.

MOD(m, n) returns the remainder using the formula m - n * TRUNC(m/n), so the sign of the result always follows the dividend m; if n is 0, MOD returns m without error.

CEIL(n) returns the smallest integer greater than or equal to n, while FLOOR(n) returns the largest integer less than or equal to n, with critical directional differences on negative numbers.

SIGN(n) returns -1, 0, or 1 for negative, zero, and positive inputs respectively, while ABS(n) returns the positive magnitude.

Last updated: August 2026

4.2 Numeric & Mathematical Functions

Quick Answer: Numeric functions accept numeric inputs ( NUMBER , FLOAT , BINARY_FLOAT , BINARY_DOUBLE ) and return numeric values. The most heavily tested functions on the 1Z0-071 exam are ROUND , TRUNC , MOD , CEIL , FLOOR , ABS , and SIGN . Understanding the second precision parameter d in ROUND(n, d) and TRUNC(n, d) —where negative d operates on digits to the left of the decimal point—and knowing that CEIL(-4.2) is -4 while FLOOR(-4.2) is -5 are critical exam competencies.

The ROUND Function

The ROUND(n [, d]) function rounds n to d decimal places using standard mathematical half-way rounding (digits 5 through 9 round up away from zero; 0 through 4 round down).

Precision Parameter d Rules:

Positive d ( d > 0 ) : Rounds to d decimal places to the right of the decimal point.

Zero or Omitted d ( d = 0 or omitted) : Rounds to 0 decimal places (the nearest whole integer).

Negative d ( d < 0 ) : Rounds to d places to the left of the decimal point (tens, hundreds, thousands, etc.). SELECT

  • ROUND(45.926, 2) AS r1, -- 45.93 (rounds to 2 decimal places)
  • ROUND(45.926, 0) AS r2, -- 46 (rounds to nearest whole integer)
  • ROUND(45.926) AS r3, -- 46 (default d is 0)
  • ROUND(45.926, -1) AS r4, -- 50 (rounds to nearest tens; 5 rounds up)
  • ROUND(44.926, -1) AS r5, -- 40 (rounds to nearest tens; 4 rounds down)
  • ROUND(55.926, -2) AS r6, -- 100 (rounds to nearest hundreds; 55 >= 50)
  • ROUND(45.926, -2) AS r7 -- 0 (rounds to nearest hundreds; 45 < 50)
  • FROM dual;
  • The TRUNC Function

The TRUNC(n [, d]) function truncates n to d decimal places without rounding (simply discarding discarded digits).

Precision Parameter d Rules:

Positive d ( d > 0 ) : Truncates to d decimal places to the right of the decimal point.

Zero or Omitted d ( d = 0 or omitted) : Discards all fractional digits, returning the whole integer part.

  • Negative d ( d < 0 ) : Replaces d digits to the left of the decimal point with zeros. SELECT
  • TRUNC(45.926, 2) AS t1, -- 45.92 (truncates at 2 decimal places)
  • TRUNC(45.926, 0) AS t2, -- 45 (truncates all decimal places)
  • TRUNC(45.926) AS t3, -- 45 (default d is 0)
  • TRUNC(45.926, -1) AS t4, -- 40 (truncates units digit to 0)
  • TRUNC(175.926, -2) AS t5, -- 100 (truncates tens and units digits to 0)
  • TRUNC(45.926, -2) AS t6 -- 0 (replaces tens and units with 0)
  • FROM dual;
  • Comprehensive Comparison: ROUND vs. TRUNC

The following table illustrates the exact behavior of ROUND and TRUNC across diverse positive, negative, and fractional inputs:

  • Input Expression Function Call Result Explanation
  • 156.784 ROUND(156.784, 2) 156.78 4 rounds down
  • 156.784 TRUNC(156.784, 2) 156.78 Truncated after 2nd decimal place
  • 156.786 ROUND(156.786, 2) 156.79 6 rounds up
  • 156.786 TRUNC(156.786, 2) 156.78 Truncated after 2nd decimal place
  • 156.786 ROUND(156.786, 0) 157 0.786 >= 0.5 rounds up
  • 156.786 TRUNC(156.786, 0) 156 Fractional digits removed
  • 156.786 ROUND(156.786, -1) 160 Units digit 6 >= 5 rounds up to 60
  • 156.786 TRUNC(156.786, -1) 150 Units digit 6 replaced with 0
  • 156.786 ROUND(156.786, -2) 200 Tens digit 5 >= 5 rounds up to 200
  • 156.786 TRUNC(156.786, -2) 100 Tens and units digits replaced with 0
  • 156.786 ROUND(156.786, -3) 0 Hundreds digit 1 < 5 rounds down to 0
  • 156.786 ROUND(-156.786, 1) -156.8 8 rounds away from zero to .8
  • 156.786 TRUNC(-156.786, 1) -156.7 Truncated after 1st decimal place
  • The MOD Function

The MOD(m, n) function returns the remainder of m divided by n .

Formal Mathematical Definition:

Oracle calculates MOD(m, n) using the formula: MOD ( m , n ) = m − ( n × TRUNC ( m / n ) ) \text{MOD}(m, n) = m - (n \times \text{TRUNC}(m / n)) MOD ( m , n ) = m − ( n × TRUNC ( m / n ))

Note that Oracle uses TRUNC , not FLOOR . This is exactly why Oracle's MOD diverges from the classical mathematical modulus whenever the two operands have opposite signs: MOD(-11, 4) returns -3 in Oracle, whereas a FLOOR -based modulus would return 1 .

Key Rules for MOD:

Sign Follows Dividend : The result of MOD(m, n) has the same sign as m (the dividend).

Zero Divisor Rule : If n = 0 , MOD(m, 0) returns m . It does not raise a division by zero error ( ORA-01476 ).

  • Decimal Support : MOD works with fractional numbers as well as integers. SELECT
  • MOD(11, 4) AS m1, -- 3 (11 - (4 * 2) = 3)
  • MOD(11, -4) AS m2, -- 3 (Sign follows dividend 11)
  • MOD(-11, 4) AS m3, -- -3 (Sign follows dividend -11)
  • MOD(-11, -4) AS m4, -- -3 (Sign follows dividend -11)
  • MOD(10.5, 3) AS m5, -- 1.5 (10.5 - (3 * 3) = 1.5)
  • MOD(25, 0) AS m6 -- 25 (If divisor is 0, returns dividend without error)
  • FROM dual;

Tip

A classic use of MOD in queries is testing for even/odd numbers: WHERE MOD(employee_id, 2) = 0 filters for even employee IDs.

CEIL and FLOOR Functions

CEIL and FLOOR map real numbers to adjacent integers.

CEIL(n) : Returns the smallest integer greater than or equal to n ("ceiling" / rounding up toward positive infinity).

FLOOR(n) : Returns the largest integer less than or equal to n ("floor" / rounding down toward negative infinity). SELECT

  • CEIL(2.1) AS c1, -- 3 (Smallest integer >= 2.1)
  • CEIL(2.0) AS c2, -- 2 (Smallest integer >= 2.0)
  • CEIL(-2.1) AS c3, -- -2 (Smallest integer >= -2.1; notice -2 is GREATER than -2.1)
  • FLOOR(2.9) AS f1, -- 2 (Largest integer <= 2.9)
  • FLOOR(2.0) AS f2, -- 2 (Largest integer <= 2.0)
  • FLOOR(-2.1) AS f3 -- -3 (Largest integer <= -2.1; notice -3 is LESS than -2.1)
  • FROM dual;

Negative Number Traps on the Exam:

CEIL(-4.8) is -4 , NOT -5 .

FLOOR(-4.8) is -5 , NOT -4 .

  • ABS and SIGN Functions
  • ABS(n)

Returns the absolute value (magnitude) of a number n . Always returns a non-negative number. SELECT ABS(-150.75) AS a1, ABS(150.75) AS a2, ABS(0) AS a3

  • FROM dual;
  • - Returns: a1 = 150.75, a2 = 150.75, a3 = 0
  • SIGN(n)

Determines the sign of a number n . Returns:

  • 1 if n < 0 n < 0 n < 0
  • 0 if n = 0 n = 0 n = 0
  • 1 if n > 0 n > 0 n > 0 SELECT SIGN(-450) AS s1, SIGN(0) AS s2, SIGN(892.5) AS s3
  • FROM dual;
  • - Returns: s1 = -1, s2 = 0, s3 = 1
  • Summary of 1Z0-071 Numeric Function Traps

Negative d in ROUND/TRUNC : Negative precision moves to the left of the decimal point. ROUND(58.2, -1) = 60 , TRUNC(58.2, -1) = 50 .

Omitted Precision : Omitting d in ROUND(n) or TRUNC(n) defaults to d = 0 (nearest whole integer).

MOD Zero Divisor : MOD(n, 0) returns n and does NOT produce ORA-01476: divisor is equal to zero .

MOD Sign Convention : The sign of MOD(m, n) matches the sign of m , irrespective of n .

CEIL vs. FLOOR on Negative Numbers : CEIL moves toward zero for negative numbers ( CEIL(-1.2) = -1 ), while FLOOR moves away from zero ( FLOOR(-1.2) = -2 ).