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

CUUG内部OCP题库解析-071-文档3题库新特点：oracle 11g版本的ocp认证考试题库，来自cuug整理，考试题大部分都在这里能找到，如果想考ocp认证可以参考这些文档。（071-sql部分考试）下面的题是（071考试-第3个文档）里的一些考题。其他题找相关文档，或者在群里讨论101-5267-481Administrator CUUG内部OCP题库解析-052-文档2 7/17/1918、（8-7） choose twoWhich two statements are true regarding views? (Choose two.)A) A simple view in which column aliases have been used cannot be updated.B) The OR REPLACE option is used to change the definition of an existing view without droppingand re-creating it.C) The WITH CHECK OPTION constraint can be used in a view definition to restrict the columnsdisplayed through the view.D) Rows added through a view are deleted from the table automatically when the view is dropped.E) Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.F) A subquery used in a complex view definition cannot contain group functions or joins.Answer：BE（解析：只要是简单视图，使用别名的列是可以使用 UPDATE命令更新的。WITH CHECK OPTION用于限制通过视图更改基表的条件，禁止更改不包含在子查询条件里的行。）19、（8-14） choose twoView the Exhibit and examine the structure of the ORDERS table.The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively.Which two INSERT statements are valid? (Choose two.)A) INSERT INTO(SELECT order_id,order_date,customer_id FROM orders)VALUES (1,'09-mar-2007', 101);B) INSERT INTO orders(order_id,order ate,order_mode,customer_id,order_total)VALUES(1,TO_DATE(NULL), 'online', 101, NULL);C) INSERT INTO ordersVALUES (1, '09-mar-2007', 'online','',1000);D) INSERT INTO orders(order_id,order_date,order_mode,order_total)VALUES (1,'10-mar-2007','online',1000);E) INSERT INTO ordersVALUES (1,'09-mar-2007', DEFAULT, 101, DEFAULT);Answer:AE(解析：验证过，考点是考如何插入默认值的列，实际插入过程中可以忽略列，或者用 default关键字显示插入。D语法没有错误，但是 customer_id是非空约束，所以无法插入)原来 047的考题。20、（9-1）choose the best answer:Which statement is true about the Oracle SQL, DELETE and TRUNCATE statements?A) DELETE but not TRUNCATE statement can be used to remove data from selective columns and rowsof a table.B) DELETE and TRUNCATE statements can have a rollback done to restore data into a table.C) DELETE and TRUNCATE statements remove all indexes for the tables on which they are performed.D) DELETE but not TRUNCATE statement can be used to selectively remove rows from a table.Answer：D（解析：Truncate是删除整张表的数据，delete不能删除某个列的数据，删除的是行的数据）21、（9-2）choose threeWhich three tasks can be performed by DDL statements?A) preventing data retrieval from a table outside of office hoursB) modifying a table to prevent data that violate certain co ditions from being entered in a columnC) preventing any data modification to a tableD) creating multiple savepoints to enable partial rollback of a transactionE) providing an alternative name for a tableAnser：BCE（解析：B 答案可以用添加约束来解决；C 答案可以用 alter 命令把表变成只读模式；E 答案可以通过创建同义词来实现，这道题比较灵活，表达的比较隐晦。22、（9-12）choose all that applyView the Exhibit and examine the details of the ORDER_ITEM table.Evaluate the following S statements:Statement 1:SELECT MAX(unit_price*quantity) "Maximum Order"FROM order_items;Statement 2:SELECT MAX(unit_price*quantit ) "Maximum Order"ROM order_itemsGROUP BY order_id;Which statements are true regarding the output of these SQL statements? (Choose all that apply.)A) Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns.B) Statement 1 wou d return only one row of output.C) Both the statements would give the same output.D) Statement 2 would return multiple rows of output.E) Statement 1 would not return any row because the GROUP BY clause is missing.Anser：ABD（解析：对于 null值，Oracle计算的时候结果为空，但是在求最大值的时候，会忽略）SQL> select sal*comm from emp;SAL*COMM-4800006250001750000SQL> select max(sal*comm) from emp;MAX(SAL*COMM)-175000023、（9-16）choose the best answer:View the Exhibit and examine the data in the EMPLOYEES table.You want to generate a report showing the tota compensation paid to each employee to date（迄今为止支付给每位员工的总补偿）.You issue the following query:SQL>S LECT ename |' joined on '| h redate |', the total compensation paid is '|TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365) * sal + comm)"COMPENSATION UNTIL DATE"FROM employeesWhat is the outcome?A) It executes successfully and gives the correct output.B) It generates an error because the usage of the ROUND function in the expression is not valid.C) It generates an error because the concatenation operator can be used to combine only two items.D) It generates an error because the alias is not valid.E) It executes successfully but does not give the correct output.Answer：E（解析：因为 comm列上有空值，所以会导致某些计算的结果为空）24、（10-4） choose two:Examine the data in the CUST_NAME column of the CUSTOMERS table.CUST_NAME-Lex De HaanRenske LadwigJose Manuel UrmanJason MallinYou want to extract only those customer names that have t ee names and display