Examine the structure of the PROMOS table:
You want to display the list of promo names with the message ‘Same Day’ for promos that started and ended
on the same day.
Which query gives the correct output?
A.
SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), ‘Same Day’) FROM promos;
B.
SELECT promo_name, NVL(TRUNC(promo_end_date – promo_start_date), ‘Same Day’) FROM promos;
C.
SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL, ‘Same
Day’)
FROM promos;
D.
SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL, ‘Same day’)
FROM promos;
Explanation:
The NULLIF Function
The NULLIF function tests two terms for equality. If they are equal the function returns a null, else it returns the
first of the two terms tested.
The NULLIF function takes two mandatory parameters of any data type. The syntax is NULLIF(ifunequal,
comparison_term), where the parameters ifunequal and comparison_term are compared. If they are identical,
then NULL is returned. If they differ, the ifunequal parameter is returned
ANSWER A – date and String incompatibl;a datatypes for NVL function
The Date TRUNC Function
The date TRUNC function performs a truncation operation on a date value based on a specified date precision
format.
The date TRUNC function takes one mandatory and one optional parameter.
Its syntax is TRUNC(source date, [date precision format]). The source date parameter represents any value
that can be implicitly converted into a date item. The date precision format parameter specifies the degree of
truncation and is optional. If it is absent, the default degree of truncation is day. This means that any time
component
I think A is right answer
Example of how Decode work
Examples
This example decodes the value warehouse_id. If warehouse_id is 1, then the function returns ‘Southlake’; if warehouse_id is 2, then it returns ‘San Francisco’; and so forth. If warehouse_id is not 1, 2, 3, or 4, then the function returns ‘Non domestic’.
SELECT product_id,
DECODE (warehouse_id, 1, ‘Southlake’,
2, ‘San Francisco’,
3, ‘New Jersey’,
4, ‘Seattle’,
‘Non domestic’)
“Location of inventory” FROM inventories
WHERE product_id < 1775;
A. False – ‘Same Day’ has a character field that is different than original value.
(Values that do not share identical data types with function parameters are implicitly converted to the required format if possible. VARCHAR2 and CHAR data types are collectively referred to as character types. Character fields are flexible and allow the storage of almost any type of information. Therefore, DATE and NUMBER values can easily be converted to their character equivalents. These conversions are known as number to character and date to character conversions. Consider the following queries:
image
Both queries use the LENGTH function, which takes a character string parameter. The number 1234567890 in query 1 is implicitly converted into a character string, “1234567890”, before being evaluated by the LENGTH function, which returns the number 10. Query 2 first evaluates the SYSDATE function, which is assumed to be 07-APR-38. This date is implicitly converted into the character string “07-APR-38” and the LENGTH function returns the number 9.
IT IS UNCOMMON FOR CHARACTER DATA TO BE IMPLICITLY CONVERTED INTO NUMERIC DATA TYPES since the only condition under which this occurs is if the character data represents a valid number. )
D is correct
” The return data type is the same as that of the first matching comparison item. The expression expr1 is implicitly converted to the data type of the first comparison parameter comp1. As the other comparison parameters comp2…compn are evaluated, they too are implicitly converted to the same data type as comp1. Decode considers two nulls to be equivalent, so if expr1 is null and comp3 is the first null comparison parameter encountered, then the corresponding result parameter iftrue3 is returned.”
– All date type will convert into type that comp1 has