ocp1z0-047: View-alias

The test center is how to create a view, for the detailed knowledge of the view point, you can refer to my blog:

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

OK, next we will do the test, first login to the OE user, check the relevant table.

Gyj@ocm> Conn Oe/oe  
Connected.  
Oe@ocm> select table_name from tabs;  
      
table_name  
------------------------------  
product_ref_list_nestedtab  
subcategory_ref_list_ Nestedtab  
Promotions  
ORDERS  
product_descriptions  
warehouses  
product_information  
Order_items  
CUSTOMERS  
inventories  
      
rows selected.

One, the answer a, is obviously wrong, the view field and the number of fields in the table are not the same, the operation of the following error:

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 to (o.order_id=i.order_id)  
  3  GROUP by o.order_id,o.o Rder_date;  
CREATE OR REPLACE VIEW ord_vu (order_id,order_date)  
                              *  
ERROR at line 1:  
ora-01730:invalid number of column name s specified  
      
in CREATE or REPLACE VIEW ord_vu (order_id,order_date,ct) Add a column of CT, the operation is as follows:  
      
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 the orders o JOIN order_items i on (o.order_id=i.order_id)  
  3  GROUP by o.order_id,o.order_date;  
      
View created.

Second, answer b is correct, remove the column name defined by the view, and the view defaults to the column names from the columns displayed in the Select, as follows:

 oe@ocm> CREATE OR REPLACE VIEW ord_vu 2 as SELECT o.order_id,o.order_date,count (i.line_item_id) "NO of item"  
      
S "from Orders o JOIN order_items i to (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                                                          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 omits the result. 。。。。。。。。。。。。。。 

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

Third, the answer c is wrong, in the creation of a view, this use of various functions, or expression of the operation of the column, must alias, such as no alias View creation will fail, the operation is as follows:

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 (o.order_id=i.order_id)  
                                  *  
ERROR at line 2:  
ora-00998:must name the expression with a column alias  
      
to change the above view to the following:  
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 or Der_items I on (o.order_id=i.order_id)  
  3  GROUP by o.order_id,o.order_date;  
      
View created.

The answer d is also wrong, wrong and answer c a question

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 to (o.order_id=i.order_id)  
  3  GROUP by O.order_id,o.order_date  
  4< C6/>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-(o.order_id=i.order_id)  
                                                        *  
ERROR at line 2:  
ora-00998:must NA Me this expression with a column alias  
      
 to change the above view to the following:  
oe@ocm> CREATE OR REPLACE view Ord_vu   
  2 as  Selec T o.order_id,o.order_date,count (i.line_item_id) | | NO for 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.

Correct answer: B

Knot Total:

When you create a view, you must alias a column that uses a variety of functions, or an expression of an operation, and fails if the alias view is not created.

The column names that define the view can be omitted from the column names in the select definition, and if the column names that define the view are not omitted, the number of columns is consistent with the number of columns in the select definition.