Ocp-1z0-051-v9.02-39 questions

Evaluate the following command:  CREATE TABLE employees (employee_id      number (2) PR Imary key,  last_name        VARCHAR2 (+) not null,  department_id     Number (2) not null,  job_id           VARCHAR2 (8),  salary        number (10,2)); You issue the following command to create a view that displays the IDs and last names of the sales staff in the Organizati On:create OR REPLACE VIEW sales_staff_vu as    SELECT employee_id, last_name,job_id from Employees WHERE J ob_id like ' sa_% '   with CHECK OPTION which-statements is true regarding the above view? (Choose.) A. It allows the insert rows into the  EMPLOYEES table. B. It allows you to delete details of the existing sales staff from the EMPLOYEES table. C. It allows update job IDs of the existing sales staff To any of the other job IDs in the EMPLOYEES table.  D. It allows you-to-insert IDs, last names, and job IDs of the "Sales staff" from the view if it is used in multitable INS ERT statements. ANSWER:BD answer: If hard to choose two, I think it should be BC
Reference: http://blog.csdn.net/rlhua/article/details/12790467 A error because the department_id of the Employees table has a non-null constraint, if inserted through a view, the department The _id column is empty and will error. b Correct, you can delete employees any row through the view. C error because there is a view with CHECK OPTION that is created according to job_id like ' sa_% ', so it cannot be changed to start with a sa_, but can be updated to start with Sa_. D error, also because DEPARTMENT_ID has a non-null constraint, if inserted through the view, then the department_id column is empty, will be an error. However, you can insert on the view by multitable Insert, and all official documents have errors.
Reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9014.htm#SQLRF55094 Official document description, multitable INSERT cannot be used on a view, so D is wrong.

Restrictions on Multitable Inserts multitable inserts is subject to the following restrictions:

You can perform multitable inserts only on tables, not on views or materialized views.

You cannot perform a multitable insert into a remote table.

You cannot specify a TABLE collection expression when performing a multitable insert.

In a multitable inserts, all of the insert_into_clauses cannot combine to specify more than 999 target columns.

Multitable Inserts is not parallelized if any target table was index organized or if any the target table has a bitmap index D Efined on it.

Plan stability is not a supported for multitable INSERT statements.

You cannot specify a sequence with any part of a multitable INSERT statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to Nextval generates the next number, and all subsequent references in the statement return The same number.

Experimental validation:

Hr@test1107> CREATE TABLE EMP

2 (employee_id number primary key,

3 last_name varchar2 (+) NOT NULL,

4 department_id Number (2) NOT NULL,

5 job_id VARCHAR2 (8),

6 Salary Number (10,2)

7);

Table created.

Hr@test1107> Create or replace view Sales_staff_vu as

2 Select employee_id,last_name,job_id

3 from EMP

4 where job_id like ' sa_% '

5 with CHECK option

6/

View created.

hr@test1107> INSERT INTO EMP select Employee_id,last_name,department_id,job_id,salary from Employees

2 where job_id like ' sa_% ' and rownum<6

3/

5 rows created.

Hr@test1107> select * from EMP;

employee_id last_name department_id job_id SALARY

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

145 Russell Sa_man 14000

146 Partners Sa_man 13500

147 Errazuriz Sa_man 12000

148 Cambrault Sa_man 11000

149 Zlotkey Sa_man 10500

Hr@test1107> select * from Sales_staff_vu;

employee_id last_name job_id

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

145 Russell Sa_man

146 Partners Sa_man

147 Errazuriz Sa_man

148 Cambrault Sa_man

149 Zlotkey Sa_man

A answer: A answer error, department_id is non-null, you cannot insert a null value.

hr@test1107> INSERT INTO SALES_STAFF_VU values (501, ' Lihua ', ' Sa_man ');

INSERT into SALES_STAFF_VU values (501, ' Lihua ', ' Sa_man ')

*

ERROR at line 1:

Ora-01400:cannot insert NULL into ("HR". " EMP "." department_id ")

B Answer: Right, you can delete

Hr@test1107> Delete from Sales_staff_vu where employee_id=145;

1 row deleted.

Hr@test1107> select * from Sales_staff_vu;

employee_id last_name job_id

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

146 Partners Sa_man

147 Errazuriz Sa_man

148 Cambrault Sa_man

149 Zlotkey Sa_man

Hr@test1107> select * from EMP;

employee_id last_name department_id job_id SALARY

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

146 Partners Sa_man 13500

147 Errazuriz Sa_man 12000

148 Cambrault Sa_man 11000

149 Zlotkey Sa_man 10500

C Answer: You can update job_id to start with Sa_, and cannot be updated to the other not sa_.

hr@test1107> Update sales_staff_vu set job_id= ' Sa_rep ' where employee_id=146;

1 row updated.

hr@test1107> Update sales_staff_vu set job_id= ' Mk_man ' where employee_id=147;

Update Sales_staff_vu set job_id= ' Mk_man ' where employee_id=147

*

ERROR at line 1:

Ora-01402:view with CHECK OPTION where-clause violation

D Answer: multitable Insert cannot insert non-empty

hr@test1107> INSERT INTO Sales_staff_vu

2 Select Employee_