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

CUUG内部OCP题库解析-071-文档4题库新特点：oracle 11g版本的ocp认证考试题库，来自cuug整理，考试题大部分都在这里能找到，如果想考ocp认证可以参考这些文档。（071-sql部分考试）下面的题是（071考试-第4个文档）里的一些考题。其他题找相关文档，或者在群里讨论101-5267-481Administrator CUUG内部OCP题库解析-052-文档2 7/17/1931、（12-11） choose the best answer:Examine the structure and data in the PRICE_LIST table:You plan to give a discount of 25% on the product price and need to display the discount amountin the same format as the PROD_PRICE.Which SQL statement would give the required result?A) SELECT TO_CHAR (TO_NUMBER (prod_price)*.25, '$99,999.00')FROM PRICE_LIST;B) SELECT TO_CHAR(prod_price*.25, '$99,999.99')FROM PRICE_LIST;C) SELECT TO_CHAR(TO_NUMBER(prod_price,'$99,999.99')*.25,'$99,999.00')FROM PRICE LIST;D) SELECT TO_NUMBER (TO_NUMBER(prod_price, '$99,999.99')*.25, '$99,999.00')FROM PRICE_LIST;CUUGAnswe :A(解析：其实 B答案也可以实现要求，但是它不是最好的，这里需要 to_number转换函数，因为 prod_price本身是 varchar2类型，可以用如下语句实验：SELECT TO_CHAR(to_number al)*.25, '$99,999.99')FROM emp; 原来 051的考题培训领航专家）2018-12-1532、（12-14） choose the best answer:Exami e the command to create the BO KS table.SQL>CREATE TABLE booksOracle(book_id CHAR(6) PRIMARY KEY,title VARCHAR2(100) NOT NULL,publisher_id VARCHAR2(4), author_id VARCHAR2(50);The BOOK_ID value 101 does not exist in the table.Examine the SQL statement:SQL> INSERT INTO books(BOOK_ID, TITLE, AUTHOR_ID)VALUES ('101', 'LEARNING SQL', 'Tim Jones');Which statement is true?A) It executes successfully only if the PUBLISHER_ID column name is added to the columns listand NULL is explicitly specified in the INSERT statement.B) It executes successfully only if NULL is explicity specified in the INSERT statement.C) It executes successfully only if the PUBLISHER_ID column name is added to the columns listin the INSERT statement.D) It executes successfully and the row is inserted with a null PUBLISHER_ID.Answer：D33、（12-15） choose the best answer:View the Exhibit and examine the structure of the ORDER_ITEMS and ORDERS tables.You are asked to retrieve the ORDER_ID, PRODUCT_ID, and total price (UNIT_PRICE multiplied byQUANTITY), where the total price is greater than 50,000.You executed the following SQL statement:SELECT order_id, product_id, unit_price*quantity "Total PricFROM order_itemsWHERE unit_price*quantity > 50000NATURAL JOIN orders;Which statement is true regarding the execution of the statement?A) The statement would not execute because the WHERE clause is before the NATURAL JOIN clause.B) The statement would not execute because the USING keyword is missing in the NATURAL JOIN clause.C) The statement would execute and provide the desired result.D) The statement would not execute because the ON keyword is missing the NATURAL JOIN clause.CUUGAnswer：A（解析：注意语法，where 子句应该出现在各种连接的语句后面，比如 right outer join 等等。）34、（13-3） choose the best answer:培训领航专家Which statement is true regarding the SESSION RIVS dictionary view?A) It contains the object privileges granted to other users by the current user session.B) It contains the system privileges granted to other users by the current user session. C) It contains the current system pr vileges available in the user session. D) It contains the current object privileges available in the user session.OracleAnswer：C（解析：SESSION_PRIVS查看的是该当前会话所有的系统权限。 session_r les查看当前会话所激活的所有角色）35、（14-14） choose the best answer:You need to create a table with the following column specifications:1. Employee ID (numeric data type) for each employee2. Employee Name (character data type) that stores the employee name3. Hire date, which stores the date of joining the organization for each employee4. Status (character data type), that contains the value 'ACTIVE' if no data is entered5. Resume (character large object CLOG data type), which contains the resume submitted by theemployeeWhich is the correct syntax to create this table?A) CREATE TABLE 1_EMP(emp_id NUMBER (4) ,emp_name VARCHAR2(25),start_date DATE,emp_tatus VARCHAR2(10) DEFAULT 'ACTIVE',resume CLOB);B) CREATE TABLE EMP_1(emp_id NUMBER,emp_name VARCHAR2(25),start_date DATE,emp_status VARCHAR2(10) DEFAULT 'ACTIVE',resume CLOB);C) CREATE TABLE EMP_1(emp_id NUMBER (4) ,emp_name VARCHAR2(25),start_date DATE,e_status VARCHAR2(10) DEFAULT 'ACTIVE',resume CLOB(200);D) CREATE TABLE EMP_1(emp_id NUMBER (4) ,CUUGe p_name VARCHAR2(25),start_date DATE,emp_status VARCHAR2(10) DEFAULT "ACTIVE",resume CLOB);Answer：B(解析：A答案错误是因为表名字第一个必培须是字符，不训能是数字；D答领案错误是因为航字符要用单引专号；C 家答案错误是大对象类型不能指定长度)36、（14-17） choose two:Exami e the structure of the DEPARTM NTS tableOracleYou execute the following command:SQL> ALTER TABLE departmentsSET UNUSED (country);Which two statements are true?A) A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.B) Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.