1z0-061 Exam - Question 8

The SQL statement makes use of several Oracle SQL functions: NULLIF and NVL2. The NULLIF function returns NULL if the two arguments are equal; otherwise, it returns the first argument. Here, NULLIF(amt_spent, credit_limit) will return NULL if 'amt_spent' is equal to 'credit_limit'. The NVL2 function works by returning the second value if the first argument is not NULL, and the third value if the first argument is NULL. In this query, if NULLIF returns NULL (which happens when 'amt_spent' is equal to 'credit_limit'), NVL2 will return the third argument, which is 1000. If NULLIF does not return NULL, NVL2 will take 'amt_spent' and return 0. Therefore, the query will display a bonus of 1000 for all customers whose 'amt_spent' equals 'credit_limit' or 'amt_spent' is NULL.