20151219 ocp examA(60-70) examB(1-20)

64.view the exhibit button and examine the structures of orders and order_iten tables.in the 

orders table.order_id is the primary key and in the order_iterms table ,order_id and line_item_id form the composite primary key

which view can have all the dml operations performed on it?

b.create view v4(or_no,or_date,cust_id) as select order_id,order_date,customer_id from orders where 

order_date</...

所有dml操作都可以执行的视图要求是简单视图。视图可以通过创建试图名制定列名，也可以通过select字句制定列名

69.which three statements are true regarding the where and having clauses in a sql statement?

a.the having clause conditions can have aggregate functions

对 having字句是可以使用聚合函数的

b.the having clause conditions can usera liases for the columns

可以使用别名？

放屁 

exam b

2.evaluate the follwoing command 

create table employees(

employee_id number(2) primary key,

last_name varchar2(25) not null,

departtment_id number(2),job_id varchar2(8).

salary number(10,2));

you issuse the following command to create a view that dispalys the ids and last names of the sales 

staff in the organization;

create or replace view sales_staff_vu as select employee_id,last_name job_id

fromemployys where job_id like 'sa_%; with check optino;

a.it allows you to insert details of all new staff into the employees table

no 必须要符合with check option的内容

b.it allows you to delete the details of the existing sales staff from the employees table

delete 一个视图上面的记录会连带将对应表上的记录删除

c.it allows you to update the jobs ids of the exising sales staff to any other job id in the employees table

如果update后的job_id不符合check optin也不行

d.it allows you to insert the IDS,last names and job ids of the sales staff from the view if it is used iun multable insert strarements

from the view而不是向视图中插入数据 这是允许的

5.which two statements best desribe the benefits of using the with clause?

a.it enables users to store the results of a query permanently(永久的)

暂时保存而不是永久

b....还是permanently的问题

9.view the exhibit and examine the data in the products table

which statement would add a column called price,which cannot contain null?

c.alter table products add price number(8,2) default 0 not null;

要求非空的话必须设定默认值

10.evaluate the follwoing sql statemen;

create index upper_name_idx on product_information(upper(product_name));

which query would use the upper_name_idx index?

c.select product_id from t where upper(product_name) in (...)

d。select product_id,upper(product_name) from product_information where upper(product_name)=''.. or list_price>1000

因为List>1000是要走全表扫描的，所以会直接忽略掉索引