[每日一题] OCP1z0-047 :2013-07-29 视图――别名..........................................................18

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/9571235

本题的考点是如何创建视图，对于视图的详细知识点，可以参考我的博客：

http://blog.csdn.net/guoyjoe/article/details/8614677

 

好，接下来我们来做测试，先登录到oe用户，查相关的表。

gyj@OCM> conn oe/oeConnected.oe@OCM> select table_name from tabs;TABLE_NAME------------------------------PRODUCT_REF_LIST_NESTEDTABSUBCATEGORY_REF_LIST_NESTEDTABPROMOTIONSORDERSPRODUCT_DESCRIPTIONSWAREHOUSESPRODUCT_INFORMATIONORDER_ITEMSCUSTOMERSINVENTORIES10 rows selected.

一、答案A，很明显是错的，视图的字段与表的字段的个数不一样，操作如下报错：

oe@OCM> CREATE OR REPLACE VIEW ord_vu(order_id,order_date)   2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id)  3  GROUP BY o.order_id,o.order_date;CREATE OR REPLACE VIEW ord_vu(order_id,order_date)                              *ERROR at line 1:ORA-01730: invalid number of column names specified在CREATE OR REPLACE VIEW ord_vu(order_id,order_date,ct)添加一列CT,操作如下就没问题：oe@OCM> CREATE OR REPLACE VIEW ord_vu(order_id,order_date,ct)   2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id)  3  GROUP BY o.order_id,o.order_date;View created.

二、答案B是正确的，把视图定义的列名去掉，视图默认这些列名来自select中的显示的列，操作如下：

oe@OCM> CREATE OR REPLACE VIEW ord_vu   2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON(o.order_id=i.order_id)  3  GROUP BY o.order_id,o.order_date;View created.oe@OCM> select * from ord_vu;  ORDER_ID ORDER_DATE                                                                  NO OF ITEMS---------- --------------------------------------------------------------------------- -----------      2354 15-JUL-08 08.18.23.234567 AM                                                         13      2361 14-NOV-07 05.34.21.986210 AM                                                          9      2363 24-OCT-07 07.49.56.346122 AM                                                          9      2367 28-JUN-08 11.53.32.335522 AM                                                          8省略结果。。。。。。。。。。。。。。。

三、答案C是错的，在创建视图时，对这种使用各种函数，或运算表达式的列，一定要起别名，如没有别名视图创建就会失败，操作如下：

oe@OCM> CREATE OR REPLACE VIEW ord_vu   2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) FROM orders o JOIN order_items i ON(o.order_id=i.order_id)  3  GROUP BY o.order_id,o.order_date;AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id) FROM orders o JOIN order_items i ON(o.order_id=i.order_id)                                  *ERROR at line 2:ORA-00998: must name this expression with a column alias把上面的视图改成如下：oe@OCM> CREATE OR REPLACE VIEW ord_vu   2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id)  CT FROM orders o JOIN order_items i ON(o.order_id=i.order_id)  3  GROUP BY o.order_id,o.order_date;View created.

四、答案D也是错的，错误与答案C一个问题

oe@OCM> CREATE OR REPLACE VIEW ord_vu   2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id)||'NO OF ITEMS' FROM orders o JOIN order_items i ON(o.order_id=i.order_id)  3  GROUP BY o.order_id,o.order_date  4  WITH CHECK OPTION;AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id)||'NO OF ITEMS' FROM orders o JOIN order_items i ON(o.order_id=i.order_id)                                                        *ERROR at line 2:ORA-00998: must name this expression with a column alias 把上面的视图改成如下：oe@OCM> CREATE OR REPLACE VIEW ord_vu   2  AS SELECT o.order_id,o.order_date,COUNT(i.line_item_id)||'NO OF ITEMS' CT FROM orders o JOIN order_items i ON(o.order_id=i.order_id)  3  GROUP BY o.order_id,o.order_date  4  WITH CHECK OPTION;View created.

正确答案：B

 

结总：

在创建视图时，对这种使用各种函数，或运算表达式的列，一定要起别名，如没有别名视图创建就会失败。

定义视图的列名可以省略，来自SELECT定义中的列名，如果定义视图的列名不省略，那个列的个数与SELECT定义中的列的个数要一致。

QQ：252803295

学习交流QQ群：
DSI&Core Search  Ⅰ 群：127149411（技术:已满）
DSI&Core Search  Ⅱ 群：177089463（技术:未满）
DSI&Core Search  Ⅲ 群：284596437（技术:未满）
DSI&Core Search  Ⅳ 群：192136702（技术:未满）
DSI&Core Search  Ⅴ 群：285030382（闲聊:未满）

MAIL：[e-mail]

BLOG： http://blog.csdn.net/guoyjoe

WEIBO：http://weibo.com/guoyJoe0218

ITPUB： http://www.itpub.net/space-uid-28460966.html

OCM：   http://education.oracle.com/education/otn/YGuo.HTM