CUUG内部OCP题库解析-071-文档5
CUUG内部OCP题库解析-071-文档5题库新特点：oracle 11g版本的ocp认证考试题库，来自cuug整理，考试题大部分都在这里能找到，如果想考ocp认证可以参考这些文档。（071-sql部分考试）下面的题是（071考试-第5个文档）里的一些考题。其他题找相关文档，或者在群里讨论101-5267-481Administrator CUUG内部OCP题库解析-052-文档2 7/17/19QUESTION 51View the exhibit for the structure of the STUDENTand FACULTYtables. STUDENTName Null? Type- - -STUDENT_ID NOT NULL NUMBER(2) STUDENT_NAME VARCHAR2(20) FACULTY_ID VARCHAR2(2)LOCATION_ID NUMBER(2)FACULTYName Null? Type- - -FACULTY_ID NOT NULL NUMBER(2) FACULTY_NAME VARCHAR2(20)LOCATION_ID NUMBER(2)You need to display the faculty name followed by the number of students handled by the faculty at the base location.Examine the following two SQL statements:Statement 1SQL>SELECT faculty_name, COUNT(student_id) FROM student JOIN facultyUSING (faculty_id, location_id) GROUP BY faculty_name; Statement 2SQL>SELECT faculty_name, COUNT(student_id) FROM student NATURAL JOIN facultyGROUP BY faculty_name;Which statement is true regarding the outcome?A. Only statement 2 executes successfully and gives the required result.B. Only statement 1 executes successfully and gives the required result.C. Both statements 1 and 2 execute successfully and give different results.D. Both statements 1 and 2 execute successfully and give the same required result.Correct Answer: A Section: (none) ExplanationSELECT DNAME,COUNT(EMPNO) FROM DEPT2 NATURAL JOIN EMPGROUP BY DNAME; QUESTION 52Which statement correctly grants a system privilege? A. GRANT CREATE VIEWON table1 TO user1;B. GRANT ALTER TABLE TO PUBLIC;C. GRANT CREATE TABLE TO user1, user2;D. GRANT CREATE SESSION TO ALL;Correct Answer: C Section: (none)Explanation 解析：无 ALTER TABLE 系统权限 Explanation/Reference: QUESTION 53View the exhibit and examine the structure of ORDERSand CUSTOMERS tables.ORDERS ORDERS Name Null? TypeORDER_ID NOT NULL NUMBER(4)ORDER_DATE NOT NULL DATEORDER_MODE VARCHAR2(8)CUSTOMER_ID NOT NULL NUMBER(6)ORDER_TOTAL NUMBER(8, 2)CUSTOMERSName Null? Type CUSTOMER_IDNOT NULL NUMBER(6)CUST_FIRST_NAME NOT NULL VARCHAR2(20)CUST_LAST_NAME NOT NULL VARCHAR2(20)CREDIT_LIMIT NUMBER(9,2)CUST_ADDRESS VARCHAR2(40)Which INSERT statement should be used to add a row into the ORDERStable for the customer whose CUST_LAST_NAMEis Robertsand CREDIT_LIMITis600? Assume there exists only one row with CUST_LAST_NAME as Roberts and CREDIT_LIMIT as 600.A. INSERT INTO (SELECT o.order_id, o.order_date, o.order_mode, c.customer_id, o.order_totalFROM orders o, customers cWHERE o.customer_id = c.customer_id AND c.cust_last_name='Roberts' AND c.credit_limit=600)VALUES (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);B. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer id FROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), order_total)VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);C. INSERT INTO ordersVALUES (1,'10-mar-2007', 'dire t', (SELECT customer_idFROM customersWHERE cust_last_name='Roberts' AND credit_limit=600), 1000);D. INSERT INTO orders (order_id, order_date, order_mode,(SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), order_total);VALUES (1,'10-mar-2007', 'direct', &customer_id, 1000);Correct Answer: C Section: (none) Explanation 相关的语句，注意子查询查出来的是某个列的值：INSERT INTO emp (empno,ename,job,deptno,sal)VALUES (1,'cuug', 'direct',(SELECT deptno FROM deptWHERE dname='RESEARCH' ), 1000);QUESTION 54Which statements are correct regarding indexes? (Choose all that apply.)A. A non-deferrable PRIMARY KEYor UNIQUE KEYconstraint in a table automatically attempts to creates a unique index.B. Indexes should be created on columns that are frequently referenced as part of any expression.C. When a table is dropped, the corresponding indexes are automatically dropped.D. For each DML operation performed, the corresponding indexes are automatically updated.Correct Answer: ACD Section: (none)Explanation：解析，有点数据库管理的意思 Explanation/Reference: References:http:/viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/ QUESTION 55View the exhibit and examine the description of the DEPARTMENTSan EMPLOYEEStables.The retrieve data for all the employees for their EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT NAME, the following SQL statement was written:SELECT employee_id, first_name, department_nameFROM employeesNATURAL JOIN departments;The desired output is not obtained after executing the above SQL statement. Whatcould be the reason for this?A. The table prefix is missing for the column names in the SELECTclause.B. The NATURAL JOINclause is missing the USINGclause.C. The DEPARTMENTStable is not used before the EMPLOYEEStable in theFROMclause.D. The EMPLOYEESand DEPARTMENTStables have more than one col
