Examice

Exam 1z0-071 All QuestionsBrowse all questions from this exam

Go to Exam

Question 146

Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY + COMMISSION is greater than 20000? (Choose three.)

SELECT * FROM employees WHERE NVL2(salary + commission, salary + commission, salary) >= 20000;

SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000

SELECT * FROM employees WHERE NVL(salary + commission, 0) >= 20000;

SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;

SELECT * FROM employees WHERE COALESCE(salary, commission) >= 20000;

SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

Correct Answer: B, D, F

To return all rows for which SALARY + COMMISSION is greater than 20000, we need to account for cases where the COMMISSION might be NULL. Options B and F correctly handle NULL values by using NVL2 and NVL, respectively, to substitute 0 when COMMISSION is NULL. Option D uses NULLIF which will not change the sum as it returns NULL if the COMMISSION is 0, thus not affecting the comparison. Options A and C are incorrect because they use NVL2 and NVL in a way that does not correctly ensure a proper comparison when commission is NULL. Option E is incorrect because COALESCE evaluates each argument and returns the first non-NULL, and doesn’t handle adding both SALARY and COMMISSION.

Discussion

kesammy9Options: ABF

ABF is the correct answer

lucemqyOptions: ABF

ABF is the correct answer