Oracle Database 12c: SQL Fundamentals — Question 7
Examine the structure and data in the PRICE_LIST table:
Name . Null . Type -
----------------------
PROD_ID . NOT NULL . NUMBER(3)
PROD_PRICE . VARCHAR2(10)
PROD_ID PROD_PRICE -
----------------------
100 $234.55
101 $6, 509.75
102 $1, 234
You plan to give a discount of 25% on the product price and need to display the discount amount in the same format as the PROD_PRICE.
Which SQL statement would give the required result?
Answer options
- A. SELECT TO_CHAR(prod_price* .25, '$99, 999.99') FROM PRICE_LIST;
- B. SELECT TO_CHAR(TO_NUMBER(prod_price)* .25, '$99, 999.00') FROM PRICE_LIST;
- C. SELECT TO_CHAR(TO_NUMBER(prod_price, '$99, 999.99')* .25, '$99, 999.00') FROM PRICE_LIST;
- D. SELECT TO_NUMBER(TO_NUMBER(prod_price, '$99, 999.99')* .25, '$99, 999.00') FROM PRICE_LIST;
Correct answer: B
Explanation
The correct answer is B because it converts the PROD_PRICE to a number before calculating the discount and then formats the result as a string in the desired currency format. Option A fails since it does not convert the string to a number, leading to an incorrect multiplication. Option C incorrectly attempts to format the PROD_PRICE before converting it to a number, while option D incorrectly uses TO_NUMBER twice and does not return a formatted string.