The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)
The CUSTOMERS table has a CUST_CREDIT_LIMIT column of data type NUMBER.
Which two queries execute successfully? (Choose two.)
To determine which queries will execute successfully, we need to consider the use of the NVL, NVL2, and TO_CHAR functions. The NVL function requires the second argument to be of the same data type as the first argument. In the case of the column cust_credit_limit, which is of type NUMBER, NVL(cust_credit_limit * .15, 'Not Available') is incorrect because 'Not Available' is a string, not a number. However, converting the result of the multiplication to a string with TO_CHAR makes it valid: NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available'). For NVL2, the function expects three arguments: the first is checked for NULL, the second is returned if the first is NOT NULL, and the third is returned if the first is NULL. In NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available'), this is correctly applied, as TO_CHAR(cust_credit_limit * .15) converts the numeric result to a string when cust_credit_limit is not NULL. Therefore, the two correct queries are the ones using NVL and NVL2 with TO_CHAR.
A) SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers; B) SELECT NVL2(cust_credit_limit * .15, 'Not Available') FROM customers; C) SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; D) SELECT TO_CHAR(NVL(cust_credit_limit * .15, 'Not Available')) FROM customers; E) SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; Correct C & E.
YES C & E
correct, C & E
C , D are corrects!
Nope, C, E are correct. You cannot convert all the function NVL in D, because its result is an error. But, in E, we have a similar situation as in C.
D has no different with A. they aren't correct.
Adopted to HR Schema: SELECT NVL(TO_CHAR(commission_pct * .15), 'Not Available') FROM employees; SELECT NVL2(commission_pct, TO_CHAR(commission_pct * .15), 'Not Available') FROM employees; Correct answers: C, E
I don't know why not A ?
Implicit cast is never easy to understand on fly, so oracle (and many others) recommend to try to avoid them.
C, E are correct. Reason why A is not correct is NVL function expects the cust_credit_limit column to be of same datatype as Replacement value ('Not Available' here). So it has to be converted to string (TO CHAR) to achieve it.
Result will be like: SQL> SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers; SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers * ERROR at line 1: ORA-01722: invalid number SQL> SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; NVL(TO_CHAR(CUST_CREDIT_LIMIT*.15),'NOTA ---------------------------------------- 150 750.0375 Not Available SQL> SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; NVL2(CUST_CREDIT_LIMIT,TO_CHAR(CUST_CRED ---------------------------------------- 150 750.0375 Not Available
You can see that in the First Query given below that 'Not Available' is not accepted as a Replacement for the Column with Number Datatype. So once Number Datatype is converted into CHAR, the 'Not Available' string gets displayed. If the Replacement Value is a Number instead of 'Not Available', NVL will accept it since the cust_credit_limit column's datatype is Number.
C. SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; This query will return the value of the cust_credit_limit column multiplied by 0.15, converted to a character value, if the cust_credit_limit column is not null. Otherwise, it will return the string 'Not Available'. E. SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; This query will return the value of the cust_credit_limit column if it is not null. Otherwise, it will return the value of the expression TO_CHAR(cust_credit_limit * .15), converted to a character value. If the expression TO_CHAR(cust_credit_limit * .15) is null, the NVL2() function will return the string 'Not Available'.
C, E are correct. Reason why A is not correct is NVL function expects the cust_credit_limit column to be of same datatype as Replacement value ('Not Available' here). So it has to be converted to string (TO CHAR) to achieve it.
If the Replacement Value is a Number instead of 'Not Available', NVL will accept it since the cust_credit_limit column's datatype is Number.
Result will be like: SQL> SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers; SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers * ERROR at line 1: ORA-01722: invalid number SQL> SELECT NVL(TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; NVL(TO_CHAR(CUST_CREDIT_LIMIT*.15),'NOTA ---------------------------------------- 150 750.0375 Not Available SQL> SELECT NVL2(cust_credit_limit, TO_CHAR(cust_credit_limit * .15), 'Not Available') FROM customers; NVL2(CUST_CREDIT_LIMIT,TO_CHAR(CUST_CRED ---------------------------------------- 150 750.0375 Not Available You can see that in the First Query 'Not Available' is not accepted as a Replacement for the Column with Number Datatype. So once Number Datatype is converted into CHAR, the 'Not Available' string gets displayed.