Oracle Database 11g: SQL Fundamentals I — Question 1

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

Option B is correct because it converts PROD_PRICE from a VARCHAR2 to a NUMBER and then calculates the 25% discount, formatting it back to a string with the desired currency format. Option A fails because it does not convert the string to a number before performing the calculation. Option C is incorrect as it attempts to format the number before the calculation, and option D incorrectly uses TO_NUMBER on the final result, which is unnecessary for formatting.