OCP 1Z0 051 90

90. 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? 
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; 

SQL> CREATE TABLE PRICE_LIST  2  (  3  PROD_ID NUMBER(3) NOT NULL,  4  PROD_PRICE VARCHAR2(10)  5  );Table createdSQL> INSERT INTO PRICE_LIST(PROD_ID,PROD_PRICE)  2  SELECT 100,'$234.55' FROM dual UNION ALL  3  SELECT 101,'$6,509.75' FROM dual UNION ALL  4  SELECT 102,'$1,234' FROM dual;3 rows inserted

转为数字用to_number
格式化用to_char
a 字符串不能直接作乘法，错

SQL> SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST; SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LISTORA-01722: 无效数字

b 转换格式不对

SQL> select TO_NUMBER(prod_price) from PRICE_LIST;select TO_NUMBER(prod_price) from PRICE_LIST

SQL> SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; TO_CHAR(TO_NUMBER(PROD_PRICE,'------------------------------     $58.64  $1,627.44    $308.503 rows selected

SQL> SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LIST; SELECT TO_NUMBER(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00') FROM PRICE_LISTORA-01722: 无效数字

Answer: C