262. View the Exhibit and examine the description of the EMPLOYEES table. You want to calculate the total remuneration(薪酬
) for each employee. Total remuneration is the sum of the annual(每年的) salary and the percentage(百分比) commission(佣金
earned for a year. Only a few employees earn commission. Which SQL statement would you execute to get the desired output?
A. SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES;
B. SELECT first_name, salary, salary*12+NVL((salary*commission_pct), 0) "Total" FROM EMPLOYEES;
C. SELECT first_name, salary, salary*12 + NVL(salary, 0)*commission_pct "Total" FROM EMPLOYEES;
D. SELECT first_name, salary, salary*12+(salary*NVL2(commission_pct, salary,salary+commission_pct))"Total" FROM EMPLOYEES;
答案: B
分析: 本题考点NVL()/NVL2()/null
NVL(arg1,arg2)如果arg1 is null 函数返回arg2。
NVL2 (expr1, expr2, expr3) ->expr1不为NULL,返回expr2;为NULL,返回expr3。expr2和expr3类型不同的话,expr3会转换为expr2的类型
null和任何数做四则运算全部为null,包括除以0。
A. 如果没有commission_pct的员工commission_pct记为null,salary*12+salary*commission_pct is null
C. 如果没有commission_pct的员工commission_pct记为null,salary*12 + NVL(salary, 0)*commission_pct is null
D. commission_pct是百分比, salary+commission_pct没有实际意义。
) for each employee. Total remuneration is the sum of the annual(每年的) salary and the percentage(百分比) commission(佣金
earned for a year. Only a few employees earn commission. Which SQL statement would you execute to get the desired output?
A. SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES;
B. SELECT first_name, salary, salary*12+NVL((salary*commission_pct), 0) "Total" FROM EMPLOYEES;
C. SELECT first_name, salary, salary*12 + NVL(salary, 0)*commission_pct "Total" FROM EMPLOYEES;
D. SELECT first_name, salary, salary*12+(salary*NVL2(commission_pct, salary,salary+commission_pct))"Total" FROM EMPLOYEES;
答案: B
分析: 本题考点NVL()/NVL2()/null
NVL(arg1,arg2)如果arg1 is null 函数返回arg2。
NVL2 (expr1, expr2, expr3) ->expr1不为NULL,返回expr2;为NULL,返回expr3。expr2和expr3类型不同的话,expr3会转换为expr2的类型
null和任何数做四则运算全部为null,包括除以0。
A. 如果没有commission_pct的员工commission_pct记为null,salary*12+salary*commission_pct is null
C. 如果没有commission_pct的员工commission_pct记为null,salary*12 + NVL(salary, 0)*commission_pct is null
D. commission_pct是百分比, salary+commission_pct没有实际意义。