OCP-1Z0-051-V9.02-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;

Answer: C

答案解析：

参考：http://blog.csdn.net/rlhua/article/details/12832885

实验环境：

scott@TEST0924> create table price_list

  2   (prod_id number(3) not null,

  3   prod_price varchar2(10));

Table created.

scott@TEST0924>  insert into price_list values(100,'$234.55');

1 row created.

scott@TEST0924>  insert into price_list values(101,'$6,509.75');

1 row created.

scott@TEST0924>  insert into price_list values(102,'$1,234');

1 row created.

scott@TEST0924>  select * from price_list;

   PROD_ID PROD_PRICE

---------- ----------

       100 $234.55

       101 $6,509.75

       102 $1,234

A，prod_price为字符，不能*.25

scott@TEST0924> SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST;

SELECT TO_CHAR(prod_price* .25,'$99,999.99') FROM PRICE_LIST

               *

ERROR at line 1:

ORA-01722: invalid number

B，TO_NUMBER(prod_price)里面需要转换格式

scott@TEST0924>  SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST;

 SELECT TO_CHAR(TO_NUMBER(prod_price)* .25,'$99,999.00') FROM PRICE_LIST

                *

ERROR at line 1:

ORA-01722: invalid number

C正确

scott@TEST0924> SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')* .25,'$99,999.00')  FROM PRICE_LIST;

TO_CHAR(TO_

-----------

     $58.64

  $1,627.44

    $308.50

D，本来(TO_NUMBER(prod_price,'$99,999.99')* .25已经是数值型，就不用再TO_NUMBER转换一回了。

scott@TEST0924> 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_LIST

                                                   *

ERROR at line 1:

ORA-01722: invalid number