The EMPLOYEE tables has these columns: LAST_NAME VARCHAR2(35) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(5,2) You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column. Which SQL statement displays the desired results?

  1. SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;

  2. SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;

  3. SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;

  4. SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;


Correct Option: D
Explanation:

Explanation: To display the name and annual salary multiplied by the commission_pct for all employees, we need to use the multiplication (*) operator. For records that have a NULL commission_pct, a zero must be displayed against the calculated column, we need to use the NVL function.

Option A) SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES; This option is incorrect because it does not account for NULL commission_pct values and does not use the NVL function.

Option B) SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES; This option is incorrect because the IFNULL function is not valid in Oracle SQL, and it does not use the NVL function.

Option C) SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES; This option is incorrect because the NVL2 function is not valid in Oracle SQL. It should be replaced with the NVL function.

Option D) SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES; This option is correct because it uses the multiplication operator to calculate the annual salary multiplied by the commission_pct and the NVL function to handle NULL commission_pct values.

Therefore, the correct answer is D.

Find more quizzes: