CUUG内部OCP题库解析-071-文档7
CUUG内部OCP题库解析-071-文档7题库新特点：oracle 11g版本的ocp认证考试题库，来自cuug整理，考试题大部分都在这里能找到，如果想考ocp认证可以参考这些文档。（071-sql部分考试）下面的题是（071考试-第7个文档）里的一些考题。其他题找相关文档，或者在群里讨论101-5267-481Administrator CUUG内部OCP题库解析-052-文档2 7/17/19题库解析：QUESTION 66Examine the data in the CUST_NAME column of the CUSTOMERS table.CUST_NAME-Renske Ladwig JasonMallin Samuel McCainAllan MCEwen IreneMikilineniJulia NayerYou need to display customers' second names where the second name starts with "Mc" or "MC".Which query gives the required output?A. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE SUBSTR (cust_name, INSTR (cust_name, ' ')+1) LIK INITCAP ('MC%');B. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1) = 'Mc'C. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ' ')+1) LIKE 'Mc%'D. SELECT SUBSTR (cust_name, INSTR (cust_name, ' ')+1)FROM customersWHERE INITCAP (SUBSTR( st_name, INSTR (cust_name, ' ')+1) = INITCAP 'MC%'Correct Answer: CSection: (none) Explanation：注意 INSTR 和 substr 的用法，还有 initcap 的用法。QUESTION 67View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMEStables.The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES tablereferencing the CUSTOMERS and TIMES tables, respectively.Evaluate the following CREATE TABLE command:CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE) ASSELECT prod_id, cust_id time_id FROM sales;Which statement is true regarding the above command?A. The NEW_SALES table would get created and all the NOTNULL constraints defined on thespecified columns would be passed to the new table.B. The NEW_SALES table would not get created because the DEFAULTvalue cannot bespecified in the column definition.C. The NEW_SALES table would not get created because the column names in the CREATETABLEcommand and the SELECTclause do not match.D. The NEW_SALES table would get created and all the FOREIGNKEYconstraints defined onthe specified columns would be passed to the new table.Correct Answer: A Section: (nonExplanation(解析：经过测试发现表能够创建成功，非空约束被传递到新表，以前没有注意这一点，其它约束不会传递，defualt 选项只会影响以后插入的行，对当前的行没有任何影响；如果在 order_date 列上加一个非空约束，则会报错，因为当前表的行可能有空值。)QUESTION 68Evaluate the following SELECTstatement and view the exhibit to examine its output:SELECT constraint_name, constraint_type,search_condition, r_constraint_name, delete_rule,status, FROM user_constraints WHERE table_name = 'ORDERS'Which two statements are true about the output? (Choose two.)A. The R_CONSTRAINT_NAMEcolumn gives the alternative name for the constraint.B. In the second column, 'c' indicates a check constraint.C. The STATUS column indicates whether the table is currently in use.D. The column DELETE_RULE decides the state of the related rows in the child table whenthe corresponding row is deleted from the parent table.Correct Answer: BD Section: (none)Explanation（R_CONSTRAINT_NAME 给出参考的约束的名字，而不是该约束的替代的名字）QUESTION 69Which three statements are true regarding group functions? (Choose three.)A. They can be used on columns or expressions.B. They can be passed as an argument to another group function.C. They can be used only with a SQL statement that has the GROUPBYclause.D. They can be used on only one column in the SELECT clause of a SQL statement.E. They can be used along with the single-row function in the SELECTclause of a SQLstatement.Correct Answer: ABE Section: (none)Explanation(函数可以用在表达式中，比如 avg(sal+100)；函数可以嵌套；也可以跟单行函数 一起使用：select substr(d.dname,1,3),avg(e.sal)from dept d,emp egroup by d.dname;QUESTION 70Which statements are true? (Choose all that apply.)A. The data dictionary is created and maintained by the database administrator.B. The data dictionary views consists of joins of dictionary base tables and user-definedtables.C. The usernames of all the users including the database administrators are stored in the datadictionary.D. The USER_CONS_COLUMNS view should be queried to find the names of the columns towhich a constraint applies.E. Both USER_OBJECTS and CAT views provide the same information about all the objectsthat are owned by the user.F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.Correct Answer: CDF Section: (none)Explanation（解析：我们看到的数据字典的名字都是基于数据字典基表创建的，所有的内容 是来源于基表，所有数据库的对象都可以在数据字典上 得到）QUESTION 71View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.Evaluate thefollowing SQL statement:SQL> SELECT prod_id FROM costsWHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_
