CUUG内部OCP题库解析-071-文档2

CUUG内部OCP题库解析-071-文档2题库新特点：oracle 11g版本的ocp认证考试题库，来自cuug整理，考试题大部分都在这里能找到，如果想考ocp认证可以参考这些文档。（071-sql部分考试）下面的题是（071考试-第2个文档）里的一些考题。其他题找相关文档，或者在群里讨论101-5267-481Administrator CUUG内部OCP题库解析-052-文档2 7/17/191、（4-2）choose the best answer:View the Exhibit and examine the structure of the CUSTOMERS table.CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERStable.CUSTOMERS needs to be updated to reflect the latest information about the customers.What is the error in the following MERGE statement?MERGE INTO customers cUSING customer_vu cvON (c.customer_id = cv.customer_id)WHEN MATCHED THENUPDATE SETc.customer_id = cv.customer_id,c.cust_name = cv.cust_name,c.cust_email = cv.cust_email,c.income_level = cv.income_levelWHEN NOT MATCHED THENINSERT VALUES(cv.customer_id,cv.cust_name,cv.cust_email,cv.income_level)WHERE cv.income_level >100000;A) The INTO clause is misplaced in the command.B) The WHERE clause cannot be used with INSERT.C) CU TOMER_VU cannot be used as a data source.D) The CUSTOMER_ID column cannot be updated.2、(4-7) choose the best answer:You need to display the first names of all customers from the CUSTOMERS table that contain thecharacter 'e' and have the character 'a' in the second last position.Which query would give the required output?A) SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>'' ANDSUBSTR(cust_first_name, -2, 1)='a'B) SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, -2, 1)='a'C) SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')<>0 ANDSUBSTR(cust_first_name, LENGTH(cust_first_name -2)='a'D) SELECT cust_first_nameFROM customersWHERE INSTR(cust_first_name, 'e')IS NOT NULL ANDSUBSTR(cust_first_name, 1,-2)='a'Answe :B051的题3、(4-10) choose the best answer:The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:GRANT ALLON orders, order_itemsTO PUBLIC;What must be done to fix the statement?A) ALL should be replaced with a list of specific privileges.B) WITH GRANT OPTION should be added to the statement.C) PUBLIC should be replaced with specific usernames.D) Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.Answer:D(SQL> grant all on sales,products to public;grant all on sales,products to public*第 1 行出现错误:ORA-00905: 缺失关键字)提问改动了，这道题出现很多次,注意语法。4、(4-11) choose two:View he Exhibit and examine the data in the PRODUCT_INFORMATION table.Which two tasks would require subqueries? (Choose two.)A) displaying all supplier IDs whose average list price is more than 500B) displaying the total n ber of products supplied by supplier 102071 and having product statusOBSOLETEC) displaying all the products whose minimum list prices are more than the average list priceof products having the product status orderabD) displaying the number of products whose list prices are more than the average list priceE) displaying the minimum list price for each product statusAnswe ：CD(解析：子查询用在查询未知的值。C的答案类似于：SQL> select sal from emp where sal > (select avg(sal) from emp where job='SALESMAN');D的答案类似于：SQL> select count(*) from emp where sal > (select avg(sal) from emp);）047 第 119题5、（4-12） choose two:You executed the following CREATE TABLE statement that resulted in an error:SQL> CREATE TABLE employees(emp_id NUMBER(10) PRIMARY KEY,ename VARCHAR2(20),email NUMBER(3) UNIQUE,address VARCHAR2(500),phone VARCHAR2 (20),resume LONG,hire_date DATE,remarks LONG,dept_id NUMBER(3) CONSTRAINT emp_dept_id_fk REFERENCES departments(dept_id) ,CONSTRAINT ename_nn NOT NULL(ename);Ident fy two reasons for the error.A) The PRIMARY KEY constraint in the EMP_ID column must have a name and must be defined at thetable level only.B) The FOREIGN KEY keyword is missing in the constraint definition.C) Only one LONG c lumn can be used per table.D) FOREIGN KEY defined on the DEPT_ID column must be at the table level only.E) The NOT NULL constraint on the ENAME column must be defined at the column level.Answer:CE6、（4-21） choose the best answer:View the Exhibit and examine the structure of the CUSTOMERS table.Evaluate the following SQL statement:SQL> SELECT cust_city, COUNT(cust_last_name)FROM customersWHERE cust_credit_limit > 1000GROUP BY cust_cityHAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;Which statement is true regarding the outcome of the above query?A) It executes successfully.B) It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.C) It returns an error because WHERE and HAVING clauses cannot be used to apply conditions onthe same column.D) It returns an error because the BETWEEN operator cannot be used in the HAVING clause.Answer:A(验证过)原来 051的题7、（5-1） choose two:View the