You need to create a report to display the ship date and order totals of your ordid table. If the order has not been shipped your report must display not shipped. If the total is not available your report must say not available. In the ordid table the ship date column has a data type of date the total column has a data type of number. Which statement do you use to create this report?

A: SELECT ordid, shipdate ‘Not shipped’, total ‘Not available’ FROM order;
B: SELECT ordid, NVL(shipdate, ‘Not shipped’), NVL (total, ‘Not available’) FROM order;
C: SELECT ordid, NVL(TO_CHAR(shipdate), ‘Not shipped’), NVL(TO_CHAR(total), ‘Not available’) FROM order;
D: SELECT ordid, TO_CHAR(shipdate, ‘Not shipped’), TO_CHAR(total, ‘Not available’) FROM order;

ANSWER: C

EXPLANATION: Answer C shows correct syntax of command NVL.

INCORRECT ANSWERS:

A: This command will show ALL data with name substitution of columns shipdate and total.
B: Incorrect usage for NVL command, because shipdate and total are needed to be converted into VARCHAR2 type with TO_CHAR function. Both parameters of NVL command have to have the same data type.
D: Incorrect syntax. TO_CHAR command is used just to convert data type into VARCHAR2 data type, it have nothing to do with NULL values in columns.