ORACLE OCM 12c认证 CUUG最近3期考试中，7位同学参加了OCMM 12c的考试，这7位同学全部通过了考试(2022)

2021年1月9日，cuug一批学员考了19c的ocp认证考试，082 083考试成绩新鲜出炉

CUUG内部整理的OCP 19c相关考试资料， 有想报考OCP 19c的同学可以联系cuug的课程老师 http://www.cuug.com 于老师电话:156-1101-1377 于老师QQ:266-709-8383 闻老师电话:135-5231-1914 闻老师QQ:203-610-5272

OCP认证是oracle一个中级认证，从事oracle相关行业的人行很多都考虑过这个认证，但OCP考试还是有一定的难度，它会有三门考试，只有三个科目全部通过才能拿到证书，而且考试题的数量和内容不定期的改变，甚至有过几次考试题目全部更新，考的全是新内容，这对考试的学员影响很大，尤其是考试和补考的费用不低，补考的话不少人难以接受。 针对OCP题库经常变的情况，CUUG有一些办法解决，每次OCP考试之后，CUUG都会专门把考试中出现的新题收集起来

Choose two. Which two statements are true about the results of using the INTERSECT operator in compound queres? A) Reversing the order of the intersected tables can sometimes affect the output. B) Column names in each SELECT in the compound query can be dfferent. C) INTERSECT returns rows common to both sides of the compound query. D) The number of columns in each SELECT in the compound query can be dfferent. E) INTERSECT ignores NULLs Answer：：BC (解析：intersect 会以第一个查询的第一个列的值进行排序输出，由于求的是交集，所以表的查询顺序可以不分

Choose three. Which three statements are true about a self join? A) It must be an inner join. B) It can be an outer join. C) The ON clause must be used. D) It must be an equijoin. E) The query must use twodifferent aliases for the table. F) The ON clause can be used. Answer：：BCD (解析：左连接其实就是左外连接，是不同的称呼而已，其结果都是一样的，ANSI 语法有 左连接的语法，而 oracle 语法中用(+)来表示。) select emp.empno,emp.deptno,dept.deptno,dept.dname from emp left join dept on emp.deptno=dept.deptno; select emp.empno,emp.deptno,dept.deptn

choose three Examine this SQL statement: SELECT cust_id, cus_ last_name "Last Name" FROM customers WHERE country_id = 10 UNION SELECT cust_id CUST_NO, cust_last_name FROM customers WHERE country_id = 30 Identify three ORDER BY clauses, any one of which can complete the query successfully. A) ORDERBY 2, 1 B) ORDER BY "CUST_NO" C) ORDER BY 2,cust_id D) ORDER BY CUST_NO E) ORDER BY "Last Name" Answer：ACE (解析：此题的解析跟下面的解析一样，答案就是按照解析的总结规律找出来的。) 071-140 题目： SELECT department_id "DEPT_ID&qu

Choose two Examine the data in the CUST NAME column of the CUSTOMERS table: CUST_NAME ------------------------------ Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni Julia Nayer You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two WHERE clauses give the required result? A) WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,' ') +1 ) ) IN ('MC%', 'Mc%) B) WHERE UPPER (SUBSTR(cust_nane, INSTR(cust_name, ' ') +1 ) ) LIKE UPPER('MC%') C) WHERE INITCAP(SUBSTR(cust_nane, INSTR(cust_name,' ') +1 )

choose two Evalute these conmands which execate sucestully CREATE SEQUENCE ord_seq INCREMENT BY 1 START WITH 1 MAXVALUE 100000 CYCLE CACHE 5000; Which two statements are true atout the ORD_ITEMS table and the ORD_SEQ sequence? A) Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ. B) Colunn ORD_NO gets the next number from squence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explict value is given for ORD_NO. C) Sepuence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times D) IF sequence ORD_SEQ is dropped then the def

choose two. ·MANAGER is an existing role with no privileges or roles. ·EMP is an existing role containing the CREATE TABLE privilege. ·EMPLOYEES is an existing table in the HR schema. Which two commands execute successfully? A) GRANT CREATE SEQUENCE TO manager, emp; B) GRANT SELECT, INSERT ON hr.employees TO manager WITH GRANT OPTION: C) GRANT CREATE TABLE, emp TO manager; D) GRANT CREATE TABLE, SELECT ON hr. employees TO manager ; E) GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager; Answer:AC (解析： BAnswer：报错：ORA-01926: cannot GRANT to a role WITH GRANT OPTION DAnswer：

choose two The ORDERS table has a column ORDER_DATE of date type DATE The default display format for a date is DD-MON-RR Which two WHERE conditions demonstrate the correct usage of conversion functions? A) WHERE ordet_date > TO_CRAR(ADD_MONTHS(SYSDATE, 6), 'MON DD YYYY') B) WHERE TO_CHAR(order_date, 'MON DD YYYY') = 'JAN 20 2019' C ) WHERE order_date > TO_DATE('JUL 10 2018', 'MON DD YYYY') D) WHERE order_date IN (TO_DATE ('Oct 21 2018', 'MON DD YYYY'), TO_CHAR('Nov 21 2018','MON DD YYYY')) E) WHERE order_date > TO

Choose two. Which two statements are true about the DUAL table? A) It can display multiple rows and columns. B) It can be accessed only by the SYS user. C) It can be accessed by any user who has the SELECT privilege in any schema D) It can display multiple rows but only a single column. E) It consists of a single row and single column of VARCHAR2 data type. F) It can be used to display only constants or pseudo columns. Answer：AC (解析：dual 这个虚拟的表可以显示多行多列;任何用户都可以访问它。这道题的难点就是对 Answer：C 的理解：在任何模式中拥

Choose three Which three actions can you perfom only with system privileges? A) Truncate a table in another schema. B) Access flat files via a database, which are stored in an operating system directory. C) Log in to a database. D) Query any table in a database. E) Use the WITH GRANT OPTION clause. F) Execute a procedure in another schema. Answer：CDF (解析：这道题考的就是可以授权哪些系统权限去做哪些活儿。 C：create session D：select any table F：EXECUTE ANY PROCEDURE 原来以为 AAnswer：正确，结构查询发现没有 truncate any table 的权限。 )

Choose three. Which three statements are true about defining relations between tables in a relatonal database? A) Foreign key columns allow null values. B) Unique key columns allow null values C) Primary key columns allow null values. D) Every primary or unique key value must refer to a matching foreign key value. E) Every foreign key value must refer to a matching primary or unique key value. Answer：ABE (解析：AB Answer：比较简单明了;D Answer：说反了)

Choose three. Which three statements are true about GLOBAL TEMPORARY TABLES? A) A GLOBAL TEMPORARY TABLE cannot have PUBLIC SYNONYM. B) A GLOBAL TEMPORARY TABLE can have multiple indexes C) A GLOBAL TEMPORARY TABLE can be referenced in the defining query of a view. D) Data Manipulation Language (DML) on GLOBAL TEMPORARY TABLES generates no REDO. E) A GLOBAL TEMPORARY TABLE can have only one index. F) A tigger can be created on a GLOBAL TEMPORARY TABLE Answer：BCD (解析：全局临时表跟普通表一样，可以创建索引、视图、触发器等等，dml 操作同样也会产生 redo。

Choose three Examine this description of the PRODUCTS table: Name NULL? TYPE ------------------------------------------------------------------------ PROD_ID NOT NULL VARCHAR2(6) QUANTITY NUMBER(8,2) PRICE NUMBER(10.2) EXPIRY_DATE DATE Rows exist in this table with data in all the columns. You put the PRODUCTS table in read-only mode. Which three commands execute successfully on PRODUCTS? A) ALTER TAELE products DROP COLUMN expiry_date; B) CREATE INDEX price_idx on products (price); C) ALTER TABLE products SET UNUSED(expiry_date); D) TRUNCATE TABLE products; E) ALTER TABLE products DROP UNUSED

Choose two Which two statements are true about transactions in the Oracle Database serve? A) An uncommitted transaction commits automatically if the user exits SQL*Plus B) Data Manipulation Language (DML) statements always start a new transaction. C) A user can always see uncomitted updates made by the same user in a dfferert session. D) A Data Detiniton Language (DDL) statement does a commit automatially only for the data dictionary updates caused by the DDL E) A session can always see uncomnitted updetes made by itselt. F) If a session has an uncometted transaction,then a DDL statement issue

choose two In the PROMOTIONS table, the PROMO_BEGTN_DATE columm is of datatype DATE and the default date format is DD-MON-RR. Which two statements are true about expressions using PROMO_BEGIN_DATE contained in a queny? A) TO_NUMBER(PROMO_ BEGIN_DATE) - 5 will return number B) TO_DATE(PROMO_BEGIN_DATE * 5) will return a date C) PROMO_BEGIN_DATE - SYSDATE will return a number. D) PROMO_BEGIN_DATE - 5 will return a date. E) PROMO_BEGIN_DATE - SYSDATE will return an error. Answer：CD (解析：这道题考的还比较有意义，AAnswer：错是因为日期型的不能转换为 number;日期不

Choose two The PRODUCT_INFORMATION table has a UNIT_PRICE column of data type NUMBER(8, 2). Evaluate this SQL statement: SELECT TO_CHAR(unit_price,'$9,999') FROM Product_intormation; Which two statements are true about the output? A) A row whose UNIT_PRICE column contains the value 1023.99 will be displayed as $1,024. B) A row whose UNIT_PRICE column contains the value 1023.99 will be displayed a5 $1,023. C) A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,0236. D) A row whose UNIT_PRICE column contains the value 10235.99 will be displayed as $1,023. E)

Choose two The INVOICE table has a QTY_SOLD column of data type NUMBER and an INVOTCE_DATE column of data type DATE NLS_DATE_FORMAT is set to DD-MON-RR. Which two are true about data type conversions involving these columns in query expressions? A) invoice_date > '01-02-2019' : uses implicit conversion B) qty_sold =' 05549821 ' : requires explicit conversion C) CONCAT(qty_sold, invoice_date) : reuires explicit conversion D) qty_sold BETWEEN '101' AND '110' : uses implicit convesion E) invoice_date = '15-march-2019' : uses implicit conversion Answer：

Choose two Which two statements are true about the WHERE and HAVING clouses in a SELECT statement? A) The WHERE clause can be used to exclude rows after divding them into groups B) WHERE and HAVING clauses can be used In the same statement only if applied to dfferent table columns. C) The HAVING clause can be used with aggregating functions in subqueries. D) Aggregating functions and columns used in HAVING clauses must be spectfied in thes SELECT list of a query. E) The WHERE clause can be used to exclude rows before dviding them into goups. Answer：CE (解析：having 子句是可以跟子

choose two Examine this business rule: Each studert can work on mutiple projects and eath project can have multiple students You must desion an Entity Relationsep (ER) model for optional data storage and allow generating reports in this format: STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK Which two statements are true? A) An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID,which is the foreign key linked to the STUDENTS and PROJECTS entities. B) The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities tha

Choose two. Examine this partial command: CREATE TABLE cust ( cust_id NUMBER(2), credit_ limit NUMBER(10) ) ORGANIZATION EXTERNAL Which two clauses are required for this command to execute successfully? A) the DEFAULT DIRECTORY clause B) the REJECT LIMIT clause C) the LOCATION clause D) the ACCESS PARAMETERS clause E) the access driver TYPE clause Answer：AD (解析：外部表建表语句，上面 Answer：看起来都对。但是通过测试，发现 E Answer：可以不需要指定，但是 directory 必须要制定，还有访问参数也必须要指定，访问参数里面位置参数必

Choone three Examine the description of the PRODUCTS table: Name Null? Type ----------------------------------------------------------------- PRODUCT_ID NOT NULL NUMBER(2) PRODUCT_NANE VARCHAR2(10) UNIT_PRICE NUMBER(3) PURCHARGE VARCHAR2(2) EXPIRY_DATE DATE DELIVERY_DATE DATE Which three queres use valid expressions? A) SELECT produet_id, unit_pricer, 5 "Discount",unit_price+purcharge-discount FROM products; B) SELECT product_id, (unit_price * 0.15 / (4.75 + 552.25)) FROM products; C) SELECT ptoduct_id, (expiry_date - delivery_date) * 2 FROM products; D) SPLECT product_id, expiry_dat

Choose two. Examine this SQL statement: UPDATE orders o SET customer_name = (SELECT cust_last_name FROM customers WHERE customer_id=o.customer_id); Which two are true? A) The subquery is executed before the UPDATE statement is executed. B) All existing rows in the ORDERS table are updated. C) The subquery is executed for every updated row in the ORDERS table. D) The UPDATE statement executes successfully even if the subquery selects multiple rows. E) The subquery is not a correlated subquery. Answer：BD (解析：这道题考的就是关联子查询，类似题目以前有考过。实验证

Choose the best answer. Examine this query: SELECT TRUNC (ROUND(156.00,-2),-1) FROM DUAL; What is the result? A) 16 B) 160 C) 150 D) 200 E) 100 Answer：D (解析：关键就是 round 函数，0 是小数点左边第一位，以此类推。)

Choone two Which three statements are true about dropping and unused columns in an Oracle database? A) A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option. B) A DROP COLUMN command can be rolled back C) An UNUSED column's space is redaimed automatially when the block containing that column is next queried D) An UNUSED column's space is redaimed automatially when the row containing that column is next queried. E) Partition key columns cannot be dropped. F) A column that is set to NNUSED still counts towards the limit of 1000 colu

Choose two. Which two statements are true about a self join? A) The join key column must have an index. B) It can be a left outer join. C) It must be a full outer join. D) It can be an inner join. E) It must be an equie join. Answer：BD (自连接其实跟两张表的连接一样。可以是等连接、左右外连接等等。 不过在进行内连接时为了区别表的名字，需要加别名 select w.ename,m.ename from emp2 w inner join emp2 m on w.mgr=m.empno; )

Choose two Which two statements are true about Oracle synonyms? A) A synonym can have a synonym. B) All private synonym names must be unique in the database. C) Any user can create a PUBLIC synonym. D) A synonym can be created on an object in a package. E) A synonym has an object number. (解析： 实验证明可以为同义词创建同义词; 同义词也是 Oracle 的一个对象; synonym 需要授权才能够创建; )

Choose three Which three statements are true about views in an Orade batabase? A) A SELECT statement cannot contain a where clause when querying a view contaning a WHERE clause in its defining query B) Rows inserted into a table using a view are retained in the table if the view is dopped C) Views can join tables only if they belong to the same schema. D) Views have no segment. E) Views have no object number. F) A view can be created that refers to a non-existent table in its defining query. Answer：BD? (解析： 经过实验，A 不对;C 不对，视图可以和不同模式的表进行连

Choose the best answer The EMPLOYEES table contains columns EMP_ID of data type NUMBER and HIRE_DATE of data type DATE You want to display the date of the first Monday after the completion of six months since hiring The NLS_TERRITORY parameter is set to AMERICA in the session and, therefore, Sunday is the first day ofthe week Which query can be used? A) SELECT emp_id,NEXT_DAY(ADD_MONTHS(hite_date,6), 'MONDAY') FROM employees; B) SELECT emp_id,ADD_MONTHS(hire_date,6), NEXT_DAY('MONDAY') FROM employees; C) SELECT emp_id,NEXT_DAY(MONTHS_BETWEEN(hire_date,SYSDATE),6) FROM employees

Choose three Which three are true about granting object privileges on tables, views, and sequences? A) UPDATE can be granted only on tables and views. B) DELETE can be granted on tables, views, and sequences. C) REFERENCES can be granted only on tables and views. D) INSERT can be granted on tables, vews, and sequences. E) SELECT can be granted only on tables and views. F) ALTER can be granted only on tables and sequences. Answer：ACF (解析：update 和 delete 只能在表和视图上授权，不能在 sequence 上。 只有 SELECT 和 ALTER 权限对序列有效; )

choose three Which three statements are true about indexes and their administration in an Orade database? A) An INVISIBLE index is not maintained when Data Manipulation Language (DML) is performed on its underlying table. B) An index can be created as part of a CREATE TABLE statement. C) A DROP INDEX statement always prevents updates to the table during the drop opeaton D) A UNIQUE and non-unique index can be ceated on the same table colunn E) A descending index is a type of funcion-based index F) If a query filters on an indexed column then it will always be used during execution of the query

choose two The CUSTOMERS table has a CUST_CREDT_LIMIT column of date type number. Which two queries execute successtully? A) SELECT TO_CHAR(NVL(cust_credit_limit * .15,'Not Available')) FROM customers; B) SELECT NVL2(cust_credit_limit * .15,'Not AvailabIe') FROM customers; C) SELECT NVL(cust_credit_limit * .15, 'Not Available') FROM customers; D) SLECT NVL(TO_CHAR(cust_credit_limit * .15),'Not available') from customers; E) SELECT NVL2(cust_credit_limit,TO_CHAR(cust_credit_limit * .15),'NOT Available') FROM customers; Answer:DE (解析：注意 nvl 返

choose the best answer The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2. The table has two rows whose COST_LAST_MANE values are Anderson and Ausson. Which query produces output for CUST_LAST_SAME containing Order for the first row and Aus for the second? A) SELECT REPLACE (REPLACE(cust_last_name,'son',''),'An','O') FROM customers; B) SELECT REPLACE (TRIM(TRALING 'son' FROM cust_last_name), 'An','O') FROM customers; C) SELECT INITCAP (REPLACE(TRIM('son' FROM cust_last_name),'An','O')) FROM custo

choose two Exammine the desciption of the PRODUCT DETALS table: NAME NULL TYPE -------------------------------------------------- PRODUCT_ID NOT NULL NUMBER(2) PRODUCT_NAME NOT NULL VARCHAR2(25) PRODUCT_PRICE NUMBER(8,2) EXPIRY_DATE DATE Which two statements are true? A) PRODUCT_ID can be assigned the PEIMARY KEY constraint. B) EXPIRY_DATE cannot be used in arithmetic expressions. C) EXPIRY_DATE contains the SYSDATE by defalt if no date is assigned to it D) PRODUCT_PRICE can be used in an arithmetic expression even if it has no value stored in it E) PRODUCT_PRICE contains the value zero by def

choose three Which three are true about the CREATE TABLE command? A) It can include the CREATE...INDEX statement for creating an index to enforce the primary key constraint. B) The owner of the table should have space quota available on the tablespace where the table is defined. C) It implicitly executes a commit. D) It implicitly rolls back any pending transactions. E) A user must have the CREATE ANY TABLE privilege to create tables, F) The owner of the table must have the UNLIMITED TABLESPACE system privilege Answer：ABC (解析：建表的要求有两个：1、要有表空间的 quota;2、

Choose the best answer. Exanine the desciption of the EMPLOYEES table: Nane Null Type ---------------------------------------------------------- EMP_ID NOT NUL NUMBER EMP_NAME VARCHAR2 (40) DEPT_ID NUMBER(2) SALARY NUMBER(8,2) JOIN_DATE DATE Which query is valid? A) SELECT dept_id, join_date, SUM(salary) FROM employees GROUP BY dept_id,join_date; B) SELECT depe_id,join_date, SUM(salary) FROM employees GROUP BY dept_id: C) SELECT dept_id,MAX (AVG (salary)) FROM employees GROUP BY dept_id; D) SELECT dept_ id,AVG (MAX (salary)) FROM employees GROUP BY dapt_id; Anser:A (解析：sum 函数如果

Which two are true about Optimizer Statistics? A)They are gathered by the Manageability Monitor(MMON) background process. B)They can be gathered by executing the PL/SQL procedures in the DBMS_STATES package. C)They do not persist across instance restarts. D)They can be used to determine the events on which a database instance waited. E)They provide information to the Optimizer so that it can determine optimal execution plans for SQL statements. F)They are automatically updated when DDL statements execute. Answer: BE

Which three are true about the Automatic Workload Repository (AWR)? A)AWR data is stored in the SYSAUX tablespace. B)Automatic AWR snapshots are created by the Manageability Monitor (MMON) background process. C)It includes statistics that contain the number of distinct values per table column. D)By default, AWR data is retained for eight days. E)it is maintained by executing the pl/sql procedures in the dbms_stats package. F)It includes statistics that contain the number of rows per table. Answer: ABD 赵：C、E、F 说的都是优化器的事，和 AWR 没关系。

Which two are true about Oracle's Optimizer statistics? A)They are automatically updated when ddl statements execute. B)They can be calculated exactly. C)They can be gathered on external tables. D)They are automatically updated when dml statements execute. E)They can be estimated. Answer: BE 赵： estimated adj. 估计的;预计的;估算的;

Which three are true about Optimizer Statistics? A)They can be gathered manually B)They are automatically updated by DML statements C)They contain the execution plan for each executed SQL statement. D)They include the number of rows in a table and the number of distinct values per column. E)They can be gathered automatically by an automatic maintenance job. Answer: ADE 赵：C 错，优化器统计信息和执行计划是两个事，统计信息的最终用途是告诉优化器 optimizer 可以对每个 SQL 去选择最好的执行计划。