《CUUG内部OCP题库解析-071-文档6》由会员分享，可在线阅读，更多相关《CUUG内部OCP题库解析-071-文档6（10页珍藏版）》请在金锄头文库上搜索。
1、CUUG内部OCP题库解析-071-文档6题库新特点：oracle 11g版本的ocp认证考试题库，来自cuug整理，考试题大部分都在这里能找到，如果想考ocp认证可以参考这些文档。（071-sql部分考试）下面的题是（071考试-第6个文档）里的一些考题。其他题找相关文档，或者在群里讨论101-5267-481Administrator CUUG内部OCP题库解析-052-文档2 7/17/19QUESTION 56Which two statements are true about sequences created in a single instance database? (Cho
2、ose two.)A. When the MAXVALUElimit for the sequence is reached, you can increase the MAXVALUElimit by using the ALTERSEQUENCEstatement.B. DELETE would remove a sequence from the database.C. The numbers generated by a sequence can be used only for one table.D. CURRVALis used to refer to the last sequ
3、ence number that has been generated.E. When a database instance shuts down abnormally, the sequence numbers that have b cached but not used would be available once again when the database instance is restarted.Correct Answer: ADSection: (none)Explanationhttps:/ 57View the exhibit and examine the str
4、ucture of the CUSTOMERStable.Which two tasks would require subqueries or joins to e executed in a single statement?A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customersB. finding the average credit limit of male custo ers resi
5、ding in Tokyoor Sydney C. listing of customers who do not have a credit limit and were born before 1980 D. finding the number of customers, in each city, whos marital status is married.E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city
6、 Tokyo.Correct Answer: AE 解析：select country_id,count(cust_id) select CUST_ID,CUST_FIRST_NAME,CUST_LAST_NAME from customers from customerswhere cust_credit_limit where cust_credit_limit in (select cust_credit_limit from customers where cust_city=Tokyo);(select avg(cust_credit_limit) from customers)gr
7、oup by country_id;QUESTION 58Which statement is true about transactions?A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction. B. Each Data Definition Language (DDL) statement executed forms a single transaction.C. A set of
8、DDL statements executed in a sequence ending with a COMMIT forms a single transaction.D. A combination of DDL and DML statements executed in a sequence end g with a COMMIT forms a single transaction.Correct Answer: B Section: (none) Explanation：每个DDL语句都是一个事务，因为DDL语句执行后自动提交。如果A答案SAVEPOINT改为COMMIT或者RO
9、LLBACK那么就是正确的。Explanation/Reference: References:https:/ 59View the exhibit and examine the structure in ORDERS and ORDER_ITEMS tab es. You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.Which CREATE VIEW statement would create the views succ
10、essfully?A . AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) FROM orders o JOIN order_items iON (o.order_id = i.order_id)GROUP BY o.order_id, o.order_date;B. CREATE OR REPLACE VIEW ord_vu (order_id, order_date) AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) NO OF ITEMSFROM ord
11、ers o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;C. CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) NO OF ITEMSFROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date;D. CREATE OR
12、REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) | NO OF ITEMSFROM orders o JOIN order_it ms i ON (o.order_id = i.order_id) GROUP BY o.order_id, o.order_date WHITH CHECK OPTION;Correct Answer: CSection: (none) Explanation：A答案会报错：ORA-00998: 必须使用列别名命名 表达式，缺少一个别名；B答案视图缺少一个
13、列名，如果加上NO OF ITEMS就是 正确的。Explanation/Reference:QUESTION 60Which statement is true about an inner join specified in the WHERE clause of a query?A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.B. It requires the column names to be the same in a
14、l tables used for the join conditions.C. It is applicable for equijoin and nonequijoin conditions.D. It is applicable for only equijoin conditions.Correct Answer: CSection: (none) Explanation：left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 inner join(等值连接) 只返回两个表中联
15、结字段相等的行，就是等连接Explanation/Reference:QUESTION 61Which statement is true regarding the INTERSECToperator?A. The names of columns in all SELECTstatements must be identical. B. It ignores NULLvalues.C. Reversing the order of the intersected tables alters the result.（颠倒表的 序会影响结果）D. The number of columns and data types must be identical for all SELECTstatements in the query.（查询中所有SELECT语句的列数和数据类型必须相同。） Correct Answer: D Section: (none) Explanation：和 UNION 指令类似，INTERSECT 也是对两个 SQL 语句所产生
