Question 28

Explanation

Correct Answers:

1. CEIL: can be used for positive and negative numbers
   → True – CEIL returns the smallest integer greater than or equal to the number, and it works with both positive and negative numbers.

2. TRUNC: can be used with NUMBER and DATE values
   → True – TRUNC can truncate numbers to a specific decimal place or dates to a specific unit (like year, month, etc.).

3. MOD: returns the remainder of a division operation
   → True – MOD(n, m) returns the remainder after dividing n by m.

Incorrect Statements:

• FLOOR: returns the smallest integer greater than or equal to a specified number
  → False – That describes CEIL.
  FLOOR actually returns the largest integer less than or equal to a specified number.

• CONCAT: can be used to combine any number of values
  → False – CONCAT in Oracle takes only two arguments.
  To combine more than two, you'd need to nest CONCATs or use the || operator.

Oracle CEIL

The Oracle CEIL function and FLOOR function are opposites of each other and are very useful functions when dealing with numbers.

The word CEIL is short for CEILING.

The purpose of the Oracle FLOOR function is to return the highest integer value that is equal to or less than a number. It basically rounds down to a whole number.

It’s the opposite of the CEILING function, and is often used as an alternative to ROUND.

CEIL returns smallest integer greater than or equal to n.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

The Oracle/PLSQL FLOOR function returns the largest integer value that is equal to or less than a number.

SELECT
CEIL(41.8) AS ceil_test,
FLOOR(41.8) AS floor_test
FROM dual;
 
CEIL Result: 42
FLOOR Result: 41

CEIL: can be used for positive and negative numbers

CEIL: returns the smallest integer greater than or equal to a specified number

FLOOR: returns the largest integer less than or equal to a specified number

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm

The TRUNC (number) function returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. If you omit n2, then the function returns the same datatype as the numeric datatype of the argument. If you include n2, then the function returns NUMBER.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm

TRUNC: can be used with NUMBER and DATE values

CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1. Its datatype depends on the datatypes of the arguments.

In concatenations of two different datatypes, Oracle Database returns the datatype that results in a lossless conversion. Therefore, if one of the arguments is a LOB, then the returned value is a LOB. If one of the arguments is a national datatype, then the returned value is a national datatype. For example:

CONCAT(CLOB, NCLOB) returns NCLOB

CONCAT(NCLOB, NCHAR) returns NCLOB

CONCAT(NCLOB, CHAR) returns NCLOB

CONCAT(NCHAR, CLOB) returns NCLOB

This function is equivalent to the concatenation operator ||.

The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. If either argument is a CLOB, the result is a temporary CLOB. Trailing blanks in character strings are preserved by concatenation, regardless of the datatypes of the string or CLOB.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm#i997789

CONCAT: CANNOT be used to combine any number of values

Definition of modulo

Modulo, often abbreviated “mod,” is a mathematical operation. It’s like a division problem, except that the answer is the remainder of an integer division operation, rather than a decimal result.

https://www.quora.com/What-is-a-MOD-in-mathematics?share=1

MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.

This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions088.htm

The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. If either argument is a CLOB, the result is a temporary CLOB. Trailing blanks in character strings are preserved by concatenation, regardless of the datatypes of the string or CLOB.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators003.htm

Definition of quotient

The answer after we divide one number by another.

dividend ÷ divisor = quotient.

Example: in 12 ÷ 3 = 4, 4 is the quotient.

https://www.mathsisfun.com/definitions/quotient.html

MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.

This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

Consider:

SELECT MOD(11,4) "Modulus" FROM DUAL;
 
   Modulus
----------
         3

The solution is, 11 ÷ 4 = 2, remainder 3 so the output of the query is 3.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions088.htm

MOD: DOES NOT return the quotient of a division operation, it returns the remainder.