• Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

Card Range To Study

through

image

Play button

image

Play button

image

Progress

1/100

Click to flip

Use LEFT and RIGHT arrow keys to navigate between flashcards;

Use UP and DOWN arrow keys to flip the card;

H to show hint;

A reads text to speech;

100 Cards in this Set

  • Front
  • Back

What are SQL conversion functions?

Single row functions designed to alter the nature of the data type of a column value, expression or literal.

What are the three most widely used conversion functions?

TO_CHAR, TO_NUMBER, TO_DATE

What does the TO_CHAR function do?

It converts numeric and date information into characters

What does the TO_NUMBER function do?

Converts character data into numbers

What does the TO_DATE function do?

converts character date into dates.

T/F Each column has an associated data type that constrains the nature of the data it can store.

True.



T/F A NUMBER column can sometimes store character information.

False. It cannot store any character information.

T/F A DATE column cannot store random characters or numbers.

True

T/F The character equivalents of both number and date information can be stored in a VARCHAR2 field.

True

What happens when a function that accepts a character input parameter finds a number instead?

Oracle automatically converts it into its character equivalent.

What happens when a function that accepts a number or a date parameter encounters a character value?

There are specific conditions under which automatic data type conversion occurs. DATE and NUMBER data types are very strict compared to VARCHAR2 and CHAR.




Converting character information to NUMBER and DATE relies on format masks.

T/F Values that do not share identical data types with function parameters are implicitly converted to the required format if possible.

True

NUMBER and DATE items can be converted explicitly into character items using the _____ function.

TO_CHAR

A character string can be explicitly changed into a NUMBER using the _______ function.

TO_NUMBER

The _____ function is used to convert character strings into DATE items.

TO_DATE

The TO_CHAR function returns an item of data type ______.

VARCHAR2

What is the syntax for converting number to characters using TO_CHAR function?

TO_CHAR (number1, [format], [nls_parameter])




- The number1 parameter is mandatory and must be a value that either is or can be implicitly converted into a number.




- The optional format parameter may be used to specify numeric formatting information like width, currency symbol, the position of a decimal point, and group (or thousands) separators and must be enclosed in single quotation marks.

What is the result of the following query?




select to_char(00001)||'is a special number' from dual

This query evaluates the number 00001, removes the leading zeros, converts the number 1 into the character '1' and returns the character string '1 is a special number'.

T/F Converting numbers into characters is a reliable way to ensure that functions and general SQL syntax, which expects character input, do not return errors when numbers are encountered.

True

What is the syntax for converting dates to characters using the TO_CHAR function?

TO_CHAR (date1, [format], [nls_parameter])




- Only the date1 parameter is mandatory and must take the form of a value that can be implicitly converted to a date.




- The optional format parameter is case sensitive and must be enclosed in single quotes.




- The format mask specifies which elements are extracted and whether the element should be described by a long or an abbreviated name.

What is the result of the following query if the SYSDATE is 03-JAN-09?




select to_char(sysdate)||'is today''s date' from dual

The query returns the character string '03/JAN/09 is today's date'

What is the syntax for converting characters to dates using the TO_DATE function?

TO_DATE(string1, [format], [nls_parameter])




- Only the string1 parameter is mandatory and if no format mask is supplied, string1 must take the form of a value that can be implicitly converted into a date.




- The optional format parameter is almost always used and is specified in single quotation marks.



What is the result of the following query:




select to_date ('25-DEC-2010') from dual;

This query evaluates the string 25-DEC-2010 and has sufficient information to implicitly convert it into a DATE item with a default mask of DD_MON-YYYY. The hyphen separator could be substituted with another punctuation character. Since no time components are provided, the time for this converted date is set to midnight or 00:00:00.

What is the result of the following query:




select to_date('25-DEC') from dual;

This query cannot implicitly convert the string into a date because there is insufficient information and an "ORA-01840:input value is not long enough for date format" error is returned.

What is the result of the following query:




select to_date ('25-DEC, 'DD-MON') from dual;

By supplying a format mask DD-MON to the string 25-DEC, Oracle can match the number 25 to DD and the abbreviated month name DEC to the MON component.




Year and time components are absent, so the current year is returned by the SYSDATE function is used and the time is set to midnight.




If the current year is 2009, this query returns the date 25/DEC/09 00:00:00.

What is the syntax for converting characters to numbers using the TO_NUMBER function?

TO_NUMBER(string1, [format], [nls_parameter]




- Only the string1 parameter is mandatory and if no format mask is supplied, it must be a value that can be implicitly converted into a number.




- The optional format parameters is specified in single quotation marks.

What is the result of the following query:




select to_number ('$1,000.55') from dual;

This query cannot perform an implicit conversion to a number because of the dollar sign, comma, and period and returns the error, "ORA-1722: invalid number".

What is the result of the following query:




select to_number ('$1,000.55', $999,999.99') from dual;

This query matches the dollar symbol, comma, and period from the string to the format mask and, although the numeric width is larger than the string width, the number 1000.55 is returned.

T/F If you convert a number using a shorter format mask, an error is returned. If you convert a number based on a longer format mask, the original number is returned.

True.

What are nested functions?

Nested functions use the output from one function as the input to another. Functions always return exactly one result.

T/F Single row functions can be nested to any level of depth.

True.

What is the general form of nested functions?

Function1(parameter1, parameter2,...)=result1

T/F Nested functions are evaluated from left to right.

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

Must all functions in a nested expression return the same data type?

No. The data types of the parameters of nested functions may be different from each other. It is important to ensure that the correct data types are always supplied to functions to avoid errors.

Are nested functions evaluated from the outermost level to the innermost level?

No. Nested functions are resolved from the innermost nest level moving outward.

________ functions simply working with columns that potentially contain null values. These functions accept input parameters of all data types. The services they offer are primarily relevant to null values.

General

What do the NVL function do?

It 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 initial term is returned.

What two mandatory parameters does the NVL function require?

The parameters of original and ifnull.

What is the syntax for the NVL function?

NVL(original, ifnull)




where original represents the term being tested and ifnull is the result returned if the original term evaluates to null.

T/F The data types of the original and ifnull parameters must always be compatible. They must either be of the same type, or it must be possible to implicitly convert ifnull to the type of the original parameter.

True.

T/F The NVL function returns a value with the same data type as the original parameter.

True

What is the result of the following query:




select nvl(1234) from dual;

Since the NVL function takes two mandatory parameters, this query returns the error, "ORA-00909: invalid number of arguments."

What is the result of the following query:




select nvl (null, 1234) from dual;

This query returns 1234 after the null keyword is tested and found to be null.

What is the result of the following query:




select nvl (substr ('abc', 4), 'No substring exists') from dual;

This query involves a nested SUBSTR function that attempts to extract the fourth character from a 3-character string. The inner function returns null, leaving the NVL(null, 'No substring exists') function to execute, which then returns the string 'No substring exists'.

What does the NVL2 function do?

It provides an enhancement to the NVL function, but serves a very similar purpose. It evaluates whether a column or expression of any data type is null or not.




If the first term is not null, the second paramter is returned, else the third parameter is returned.




Recall that the NVL function is different since it returns the original term if it is not null.

What three mandatory parameters does NVL2 require?

Original, ifnotnull, ifnull

What is the syntax for NVL2 function?

NVL2(original, ifnotnull, ifnull),




where original represents the term being tested. Itnotnull is returned if original is not null, and ifnull is returned if original is null.




The datatypes of the ifnotnull and ifnull parameters must be compatible, and they cannot be of type LONG.

T/F The data type returned by the NVL2 function is the same as that of the ifnotnull parameter.

True.

What is the result of the following query:




select nvl2(1234, 1, 'a string') from dual;

The ifnotnull term in this query is a number and the ifnull parameter is 'a string'. Since there is a data type incompatibility between them, an "ORA-01722: invalid number" error is returned.

What is the result of the following query:



select nvl2(null, 1234, 5678) from dual;

This query returns the ifnull parameter, which is 5678.

What is the result of the following query:




select nvl2(substr ('abc', 2), 'Not bc', 'No substring') from dual;

This query extracts the characters "bc" using the SUBSTR function and the NVL2 ('bc', Not bc', 'No Substring') function is evaluated. The ifnotnull parameter, the string 'Not bc', is returned.

What does the NULLIF function do?

It tests two terms for equality. If they are equal the function returns a null, else it returns the first of the two terms tested.

What are the two mandatory parameters the NULLIF function requires?

inunequal, comparison_term

What is the syntax for the NULLIF function?

NULLIF (ifunequal, comparison_term)




where parameters ifunequal and comparison_term are compared. If they are identical, then NULL is returned. If they differ, the ifunequal parameter is returned.

What is the result of the following query:




select nullif(1234, 1234) from dual;

This query returns a null values since the parameters are identical.

What is the result of the following query:




select nullif(1234, 123+1) from dual;

The arithmetic equation in this query is not implicitly evaluated, and the NULLIF function finds 1234 different from 123+1, so it returns the ifunequal parameter, which is 1234.

What is the result of the following query:




select nullif('24-JUL-2009','24-JUL-09') from dual;

The character literals in this query are not implicitly converted to DATE items and are compared as two character strings by the NULLIF function. Since the strings are of different lengths, the ifunequal parameter 24-JUL-2009 is returned.

What does the COALESCE function do?

It returns the first nonnull value from its parameter list. If all its parameters are null, then null is returned.

T/F The COALESCE function takes three mandatory parameters.

False. It takes two mandatory parameters and any number of optional parameters.

What is the syntax for the COALESCE function?

COALESCE(expr1, expr2,..., exprn),




where expr1 is returned if it is not null, else expr2 if it is not null, and so on.

T/F The data type COALESCE returns if a not null value is found is the same as that of the first not null parameter.

True

What is the result of the following query:




select coalesce (null, null, null, 'a string') from dual;

This query returns the fourth parameter string: a string, since this is the first not null parameter encountered.

What is the result of the following query:




select coalesce (null, null, null) from dual;

This query returns null because all its parameters are null

What is the result of the following query:




select coalesce (substr ('abc', 4), 'Not bc', 'No substring') from dual;

This query evaluates its first parameter, which is nested SUBSTR function, and finds is to be null. The second parameter is not null so the string "Not bc" is returned.

_______returns original if it is not null, else ifnull is returned. The ____ function returns ifnotnull if original is not null, else ifnull is returned.

NVL(original, ifnull)




NVL2(original, ifnotnull, ifnull)

________ ________, also known as if-then-else logic, refers to choosing a path of execution based on data values meeting certain conditions.

Conditional logic

What are conditional functions?

Conditional functions (like DECODE and CASE expression) return different values based on evaluating comparison conditions. These conditions are specified as parameters to the DECODE function and the CASE expression.

The ______ _______ is specific to Oracle, while the _______ expression is ANSI SQL compliant.

DECODE funciton, CASE

What does the DECODE function do?

It implements if-then-else conditional logic by testing its first two terms for equality and returns the third if they are equal and optionally returns another term if they are not.

T/F The DECODE function takes only two mandatory parameters.

FALSE. It takes at least three mandatory parameters, but can take many more.

What is the syntax for the DECODE function?

DECODE (expr1, comp1, iftrue1, [comp2, iftrue2...[ compN, iftrueN]], [iffalse]).




Expr1 is compared to comp1. If they are equal, then iftrue1 is returned.




If expr1 is not equal to comp1, then what happens next depends on whether the optional parameters comp2 and iftrue2 are present. If they are, then expr1 is compared to comp2. If they are equal, then iftrue2 is returned.

T/F If no matches have been found in the DECODE function and if the iffalse parameter is defined, then iffalse is returned. If the iffalse parameter does not exist and no matches are found, a null value is returned.

True.

T/F All parameters to the DECODE function may be expressions.

True.

What is the result of the following query:




select decode (1234, 123, '123 is a match') from dual;

This query compares the number 1234 with the first comparison term 123. Since they are not equal, the first result cannot be returned.




Further, as there is no default iffalse parameter defined, a null is returned.

What is the result of the following query:




select decode (1234, 123, '123 is a match', 'No match') from dual;

This is almost identical to the last query except that an iffalse parameter is defined. Since 1234 is not equal to 123, the string 'No match' is returned.

What is the result of the following query:




select decode ('search', 'comp1', 'true1, 'comp2', 'true2', 'search', 'true2', substr ('2search', 2, 6)), 'true4', 'false') from dual;

This query searches through the comparison parameters for a match. The character terms 'comp1' and 'comp2' are not equal to search, so the results true1 and true2 are not returned.




A match is found in the third comparison term 'comp3' (parameter 6), which contains the string search. Therefore the third result term iftrue3 containing the string 'true3' is returned.




Note that since a match has been found, no further searching takes place.

What are the two variants of the CASE expression?

1. The simple CASE expression: lists the conditional search item once, and equality to the search item is tested by each comparison expression.


2. The searched CASE expression: lists a separate condition for each comparison expression.

T/F The CASE expression takes at least three mandatory parameters but can take many more.

True.

What is the syntax for the simple CASE expression?

CASE search_expr


WHEN comparison_expr1 THEN iftrue1


[WHEN comparison_expr2 THEN iftrue2


...


WHEN comparison_exprN THEN iftrueN


ELSE iffalse]


END

The simple CASE expression is enclosed within a ____....._______ block and consists of at least one ______.....________ statement.

CASE....END


WHEN....THEN

T/F When more than one WHEN...THEN statement exists in the CASE expression, searching for a matching comparison expression continues until a match is found.

True.

T/F The search, comparison, and result parameters can be column values, expressions or literals but must all be of the same data type.

True.

What is the syntax for the searched CASE expression?

CASE


WHEN condition1 THEN iftrue1


[WHEN condition2 THEN iftrue2


....


WHEN conditionN THEN iftrueN


ELSE iffalse]


END

When values do not match the defined parameters of functions, Oracle attempts to convert them into the required data types. This is known as _____ _____.

implicit conversion

_______ ______ occurs when a function like TO_CHAR is invoked to change the data type of a value.

Explicit conversion

The _____ function performs date to character and number to character data type conversions.

TO_CHAR

Character items are explicitly transformed into date values using the ______ conversion function.

TO_DATE

Character items are changed into number values using the __________ conversion function.

TO_NUMBER

The TO_CHAR function returns an item of type _______.

VARCHAR2

_____ ______ or masks prescribe patterns that character strings must match to facilitate accurate and consistent conversion into number or date items.

Format models

The format masks available when TO_CHAR is used to convert character items to date include: _____, _____, _____, ______, _____, and _____.

day, week, month, quarter, year, century

T/F Format masks must always be specified enclosed in single quotes.

True.

T/F Character terms, like month and day names, extracted from dates with the TO_CHAR function are automatically padded with spaces that may be trimmed by prefixing the format mask with the fm modifier.

True.

______ _______ use the output from one function as the input to another.

nesting functions

The _____ ______ either returns the original item unchanged or an alternative item if the initial term is null.

NVL function

The ____ _____ returns a new if-null item if the original item is null or an alternative if-not-null item if the original term is not null.

NVL2 function

The _____ function test two terms for equality. If they are equal, the function returns null, else it returns the first of the two terms tested.

NULLIF

The ______ ______ returns the first nonnull value from its parameter list. If all its parameters are null, then a null value is returned.

COALESCE function

The ____ _____ implements if-then-else conditional logic by testing two terms for equality and returning the third term if they are equal or, optionally, some other term if they are not.

DECODE function

There are two variants of the CASE expression used to facilitate if-then-else conditional logic. They are ________ _______ and ____ ___ ____.

simple CASE, searched CASE expressions