Chapter 5: Using Conversion Functions and Conditional Expressions

Of the two parameters the NVL function takes, which are mandatory?

Both of the two parameters are mandatory. Both must be of the same data type or of it must be possible to implicitly convert 'ifnull' to the type of the 'original' parameter.

Which function is a general form of the NVL function?

COALESCE function

Which function implements if-then-else logic by testing its first 2 terms for equality and returning the third if they are equal? Optionally, it returns another term if they are not equal.

DECODE function

In conversion functions, the optional parameter must be enclosed in double quotation marks. (T or F)

False. The optional parameter must be enclosed in single quotation marks.

There are 3 variants of the CASE Expression. (T or F)

False. There are 2: Simple CASE and Searched CASE.

Nested functions are first evaluated before their return values are used as parametric input to other functions. They are evaluated from the outermost to the innermost levels. (T or F)

False. They are evaluated from the innermost to the outermost levels.

Simple functions make it easier to work with columns that potentially contain null values. They accept parameters of all data types and their services are primarily relevant to null values. (T or F)

False. This describes General Functions.

What is the purpose of the COALESCE function?

It returns the first notnull value from its parameter list. If all its parameters are null, then null is returned. It takes 2 mandatory parameters and any number of optional parameters. COALESCE(expr1, expr2, ... ) where expr1 is returned if it is not null, else expr2.

What is the purpose of the NULLIF function?

It tests 2 terms for equality. If they are equal, the function returns a null, else it returns the first of the 2 terms tested. It takes 2 mandatory parameters of any data type.

Which function evaluates whether a column or expression of any data type is null or not? If the term is null, an alternative not null value is returned, otherwise the original term is returned.

NVL(original, ifnull) where 'original' represents the term being tested and 'ifnull' is the result returned if the 'original' term evaluates to null.

What function evaluates whether a column or expression of any data type is null or not? If the first term is not null, the second parameter is returned, else the third parameter is returned.

NVL2(original, ifnotnull, ifnull) where 'original' represents the term being tested. All 3 parameters are mandatory.

Which type of function uses the output of one function as the input to another function?

Nested Functions

What is an explicit data type conversion?

Opposite from that of implicit data type conversion, Oracle offers many functions that convert items from one data type to another. These return a value guaranteed to be the type required and offer a safe and reliable method of converting data items.

Which function would you use to convert a date to a character?

TO_CHAR(date 1, [format], [nls_parameter]),

Which function would you use to convert a number to a character?

TO_CHAR(number 1, [format], [nls_parameter]),

Which function would you use to convert characters to a date?

TO_DATE(string 1, [format], [nls_parameter]),

Which function returns an item of type NUMBER?

TO_NUMBER(string 1, [format], [nls_parameter]),

Explain the Searched CASE expression.

The Searched CASE expression lists a separate condition for each comparison expression.

Explain the Simple CASE expression.

The Simple CASE expression lists the conditional search item once and equality to the search item is tested by each comparison expression.

Conditional functions that cover if-then-else logic return different values based on evaluating comparison conditions. (T or F)

True

SQL conversion functions are single row functions designed to alter the number of the date type of a column value, expression or literal. TO_CHAR, TO_NUMBER and TO_DATE are the most common conversion functions. (T or F)

True

What is meant by implicit conversion in Oracle?

Values that do not share identical data types with function parameters are automatically converted by the compiler to the required format.