OCP-1Z0-051-2015-41题

QUESTION NO: 41

See the structure of the PROGRAMS table:

Which two SQL statements would execute successfully? (Choose two.)
A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)
FROM programs;
B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))
FROM programs;
C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')
FROM programs;
D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing') FROM
programs;
Answer: A,D
Explanation:
NVL Function
Converts a null value to an actual value:
Data types that can be used are date, character, and number.
Data types must match:
– NVL(commission_pct,0)
– NVL(hire_date,'01-JAN-97')
– NVL(job_id,'No Job Yet')
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2
The result can be positive or negative. If date1 is later than date2, the result is positive;(正数）if date1 is earlier than date2, the result is negative.（负数） 

The noninteger part of the result represents a portion of the month.

MONTHS_BETWEEN returns a numeric value. - answer C NVL has different datatypes - numericand strings, which is not possible! 选项C数据类型错误
The data types of the original and if null parameters must always be compatible. They must either
be of the same type, or it must be possible to implicitly convert if null to the type of the original
parameter. The NVL function returns a value with the same data type as the original parameter. nvl函数应用中比较的两者数据类型必须一致。