Sql Certificate 简明教程

SQL - Creating Other Schema Objects Questions

1. Which database object among the following provides a layer of abstraction between the users and the data?

*答案：C、D。*视图和同义词本身不存储数据。视图是一个临时或虚拟表，用于检索存储在底层数据库表中的数据。

2. Which of the following data base objects can generate serial numbers?

*答案：D。*可以创建一个序列来生成一系列整数。由序列生成的值可以存储在任何表中。使用 CREATE SEQUENCE 命令创建序列。

3. What is true about views?

*答案：C、D。*在包含组函数、GROUP BY 子句、ROWNUM 伪列或 DISTINCT 关键字的视图上不允许使用 DML 操作。

4. Why are views useful? (Choose the most appropriate answer)

*答案：B、C。*视图是一个临时或虚拟表，用于检索存储在底层数据库表中的数据。每次使用视图时都必须执行视图查询。可以使用视图来简化查询或限制对敏感数据的访问。

5. In which of the below scenarios, DML operations on a view are not possible?

*答案：D。*在包含组函数、GROUP BY 子句、ROWNUM 伪列或 DISTINCT 关键字的视图上不允许使用 DML 操作。

6. Where can views get their data from?

回答：C。

Consider the given table structure and the following statement and answer the questions 7 to 9 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

CREATE VIEW emp_details AS
SELECT hire_date, job, salary, department_id FROM employees;

7. You issue the below query. How many columns will the user see as a result of the below query?

SELECT * FROM emp_details WHERE department_id= 100;

*答案：D. *因为视图定义基于 EMPLOYEES 表中的四列，针对该视图的查询中只会显示这四列。

8. You need to get the department name in addition to the above 4 columns. Which of the following query will give you the required results?

*答案：A. *视图可以与 SELECT 查询中的其他表或视图联接。

9. You need to find the maximum salary along with the department name in addition to the 4 columns selected in the view. Which of the following query will give you the required results?

回答：B。

10. What among the following is true about synonyms?

*答案：A、C. *同义词可以是私有同义词（用户用于引用其拥有对象）或公共同义词（用户用于访问其他用户的数据库对象）。只有 SYSDBA 或具有 DBA 权限的用户才能创建公共同义词。

11. What is true about creating a view? (Choose the most appropriate answer)

*答案：C. *包含表达式或函数或联接多个表的视图被视为复杂视图。复杂视图只能用于更新一个表。

12. Which of the following privileges are required to create views in one’s own schema?

*答案：B. *CREATE VIEW 权限对于用户在其自己的架构中创建视图是必需的。

13. Which of the following privileges are required to create views in someone else’s schema?

*答案：A. *CREATE ANY VIEW 权限对于用户在其他用户的架构中创建视图是必需的。

14.Which of the following are supported for an object view or relational view?

*回答：D. *

15. What among the following are different types of Views?

*答案：C. *简单视图和复杂视图是两种类型的视图。简单视图基于仅引用一个表且不包含组函数、表达式或 GROUP BY 子句的子查询。复杂视图基于从一个或多个表中检索或导出数据的子查询，且可以包含函数或分组数据。

16. What is true about a simple view?

*答案：D. *简单视图基于仅引用一个表且不包含组函数、表达式或 GROUP BY 子句的子查询。

17.What is true about a complex view?

*答案：D. *复杂视图基于从一个或多个表中检索或导出数据的子查询，可以包含函数或组数据。

18.Which keyword combination should be used to implicitly drop a view (if it exists) and create a new view with the same name?

*答案：C. *OR REPLACE 选项会通知 Oracle 11g 同名视图可能已存在。如果已存在，则用新命令中定义的视图替换该视图的旧版本。

19.How is a view stored in the data dictionary?

*回答：D. *

20.Which of the following can contain single-row functions?

*答案：A、B. *单行函数可在内联视图和简单视图中使用。

21.Which of the following can contain a group of data?

*答案：C. *复杂视图可以在查询中使用组函数。

22.What among the following is true about a View?

*答案：A. *视图定义可以使用子查询。

23.Which of the following can create a view even if the base table(s) does not exist?

*答案：B. *如果在 CREATE 子句中包含 FORCE 关键字，则 Oracle 11g 会创建该视图，而无需任何被引用表。NOFORCE 是 CREATE VIEW 命令的默认模式，这意味着所有表和列都必须有效，否则将不会创建该视图。

24.Which of the following commands ensures that no DML operations can be performed on a view?

*答案：C. *WITH READ ONLY 选项阻止对该视图执行任何 DML 操作。当重要的是用户只能查询数据，而不能对其进行任何更改时，此选项经常使用。

25.What is true about the NOFORCE option in CREATE VIEW statement?

*答案：B、C. *NOFORCE 是 CREATE VIEW 命令的默认模式，这意味着所有表和列都必须有效，否则将不会创建该视图。

26.What is true about the OR REPLACE keyword?

*答案：B. *OR REPLACE 选项会通知 Oracle 11g 同名视图可能已存在。如果已存在，则用新命令中定义的视图替换该视图的旧版本。

27.What is true with respect to accessing the below view? (Assume the table structure given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

CREATE VIEW salVU100
AS SELECT employee_id  ID_NUMBER, last_name NAME, salary*12 ANNUAL_SAL
FROM employees E
WHERE department_id= 100;

*答案：B、C。*如果视图定义包含列的别名，则视图必须引用列别名。

28.What is true with respect to accessing the below view? (Assume the table structure given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

CREATE VIEW salVU100 (ID_NUMBER, NAME, ANNUAL_SAL)
AS SELECT employee_id , last_name, salary*12
FROM employees E
WHERE department_id= 100;

*答案：B。*如果在视图标题中指定了别名，则必须在 SELECT 查询中选择相同数量的列。

29. Consider the following statement and the given table structure:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

CREATE OR REPLACE VIEW empVU100
(ID_NUMBER, NAME, ANNUAL_SAL, DEPT_ID)
AS
SELECT employee_id , first_name ||' '|| last_name, salary, department_id
FROM employees
WHERE department_id= 100;

上面查询中的列别名有什么作用？

回答：B。

Consider the following statement and answer the questions 30 to 34 that follow:

CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name;

30.What can be said about the statement given above?

*答案：C。*指定别名名称是为了提高代码和视图查询的可读性。

31.What will happen if the above statement is modified as below?

CREATE OR REPLACE VIEW dept_sum_vu(name, maxsal, minsal, avgsal)
AS
SELECT d.dept_name, MIN(e.salary), MAX(e.salary), AVG (e.salary)
FROM employees e JOIN departments d
ON (e.department_id= d.dept_id)
GROUP BY d.dept_name;

*答案：B。*列别名的顺序没有多大关系，因为它们没有任何行为属性。

32.Determine the output of the below DELETE statement.

DELETE FROM dept_sum_vu;

*答案：C。*视图 DEPT_SUM_VU 是一个复杂视图。无法对复杂视图执行 DML 操作。

33.假设你将上面给出的查询修改为以下内容：

CREATE OR REPLACE VIEW dept_sum_vu(name, sal)
AS
SELECT d.dept_name, e.salary
FROM employees e JOIN departments d
ON (e.department_id= d.dept_id)
Where rownum < 10;

修改的影响是什么？

*答案：B。*无法对复杂视图执行 DML 操作。DEPT_SUM_VU 是一个复杂视图，因为它连接了多张表。不能对视图执行 DDL 操作。

34.Suppose you select DISTINCT departments and employee salaries in the view query used in above question. What will be the outcome if you try to remove rows from the view dept_sum_vu?

*答案：C。*视图 DEPT_SUM_VU 仍然是一个复杂视图，因为它使用了 DISTINCT 关键字。因此，无法对它执行 DML 操作。

35.When can the rows from a view be removed?

*答案：B。*DML 操作只能在简单视图上进行。

36.When can the data in a view not be modified? ：

答案：D. 包含分组函数、伪列或 DISTINCT 关键字的视图无法 UPDATE。

37. The JOB_HISTORY table is owned by a user "Andy". Andy grants the SELECT privilege on the JOB_HISTORY table to another user "HR". Which statement would create a synonym EMP_JOBS so that "HR" can execute the following query successfully?(Assume the structure of tables as given) ：

SQL> desc job_history
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 START_DATE		 NOT NULL DATE
 END_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 DEPARTMENT_ID			  NUMBER(4)

SELECT * from EMP_JOBS;

答案：B. 只有 SYSDBA 或具有 DBA 特权的用户才能创建 public 同义词。

38.Which keyword can assure that the DML operations performed on the view stay in the domain of the view? ：

答案：C. WITH CHECK OPTION 约束确保对视图执行的任何 DML 操作（例如添加行或更改数据）都不会导致视图因不再满足 WHERE 子句中的条件而无法访问行。

Consider the following table structure and the given statement and answer the questions 39 and 40 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

CREATE OR REPLACE VIEW empvu100
AS
SELECT * FROM employees
WHERE department_id= 100
WITH CHECK OPTION CONSTRAINT empvu100_ck;

39.What will the above statement do?

*答案：B。*WITH CHECK OPTION 约束确保对视图执行的任何 DML 操作（例如添加行或更改数据）都不会阻止视图访问行，因为它不再满足 WHERE 子句中的条件。如果对部门编号不是 100 的任何行执行 INSERT 或 UPDATE，将会引发 ORA 错误。

40.Suppose you fire an UPDATE statement as shown below:

UPDATE empvu100
Set department_id = 200
Where employee_id  = 121;

此语句的结果是什么？

*答案：C。*如果更新了带 CHECK OPTION 的视图，且新记录的值违反了视图的范围，则会引发 ORA 异常“ORA-01402：视图 WITH CHECK OPTION where-clause violation”。

41.What is true about the WITH CHECK CONSTRAINT?

答案：A。

42.How can you prevent DML operations on a View?

*答案：B。*WITH READ ONLY 选项可防止对视图执行任何 DML 操作。经常使用此选项，如果用户只能查询数据，而不能进行任何更改，则这很重要。

Consider the table structure and the given statement and answer the questions 43, 44 and 45 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

CREATE OR REPLACE empvu100(employee_id , first_name, job)
AS
SELECT employee_id , last_name, job
FROM employees
WHERE department_id = 100
WITH READ ONLY;

43.What is true about the above statement?

*答案：B、C。*不允许对使用 READ ONLY 选项创建的视图执行 DML 操作。

44.How many rows can be deleted from the view as shown above?

*答案：C。*不允许对使用 READ ONLY 选项创建的视图执行 DML 操作。

45.Which of the following statements will drop the view created as above?

*答案：C。*可以使用 DROP VIEW 命令删除只读视图。

46.What is true about dropping a View?

答案：B、C。

47.Which of the following privileges should a user have to drop a view?

回答：C。

48.What is true about sequences?

*答案：D。*当在内存中缓存时，序列可以提高访问序列值时的效率

49.What is true about a sequence?

*答案：B、C。*CREATE SEQUENCE 系统权限是用户在其自己无法与其他用户共享的模式中创建序列所需的权限。

50.What among the following options is true about Sequences?

*回答：D. *

Consider the following statement and answer the questions 51 to 59 that follow:

CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 9999
NOCACHE
NOCYCLE;

51.What will be the first value generated by this sequence?

*答案：C。*START WITH 子句建立序列的起始值。除非在 START WITH 子句中指定了其他值，否则 Oracle 11g 以 1 开始每个序列。

52.What can be the last value generated by this sequence?

*答案：D。*MINVALUE 和 MAXVALUE 子句为序列建立最小值或最大值。

53.What will be the 2nd value generated by this sequence?

*答案：A。*INCREMENT BY 子句指定两个顺序值之间的间隔。如果序列以正值递增，则序列生成的值将按升序排列。但是，如果指定了负值，则序列生成的值将按降序排列。如果在创建序列时未包含 INCREMENT BY 子句，则使用默认设置，该设置将序列增加一（对于生成的每个整数）。

54.What will be the next value after the maximum integer 9999 is reached by this sequence?

*答案：B。*CYCLE 和 NOCYCLE 选项决定在达到最小值或最大值后 Oracle 11g 是否应该从序列重新颁发值。

55.How many values will Oracle pre allocate in memory based on the sequence given above?

答案：A。

56.You execute the below query:

SELECT dept_depid_seq.NEXTVAL from dual;

*答案：D。*NEXTVAL 伪列将生成序列的下一个唯一整数。

57.You execute the below query:

SELECT dept_depid_seq.CURRVAL from dual;

*答案：A。*CURRVAL 伪列将生成序列已生成的当前唯一整数。

58.Suppose you need to change the start value of this sequence to 1000. Which of the following statements will help?

*答案：B。*不能修改序列的起始编号。Oracle 引发异常 “ORA-02283：无法更改起始序列号”。

59.Suppose that the above sequence is altered as below:

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 100
START WITH 101
MAXVALUE 99
NOCACHE
NOCYCLE;

进行此修改的结果是什么？

*答案：A。*更改序列时，MAXVALUE 不能小于 START WITH 值。

60.When can we use the CYCLE option in Sequences?

*答案：C。*CYCLE 和 NOCYCLE 选项用于确定在达到最小值或最大值后 Oracle 11g 是否应从序列中重新发布值。如果指定 CYCLE 选项且 Oracle 11g 达到上升序列的最大值或下降序列的最小值，那么 CYCLE 选项会重新开始数值循环。

61.What is true about NEXTVAL pseudo column?

*答案：B。*NEXTVAL（下一个值）伪列用于实际生成序列值。换句话说，它会调用序列对象并请求序列中下一个数字的值。生成值后，将把它存储在 CURRVAL（当前值）伪列中，以便你可以再次引用它。

62.What is true about CURRVAL pseudo column?

回答：B。

63.When can NEXTVAL and CURRVAL be used?

*答案：C、D。*序列可用在 SELECT 查询、PL/SQL 游标中，或直接用于 IAS（INSERT-AS-SELECT）操作。

64.When can NEXTVAL and CURRVAL not be used?

*回答：D. *

Consider the given statement and answer the questions 65 and 66 that follow:

CREATE TABLE employees
(employee_id  NUMBER(4) DEFAULT emp_empid_seq.CURRVAL,
 department_id NUMBER(4));

65.What will be the outcome of this statement? (Assume that emp_empid_seq is sequence used to generate employee ID values)

*答案：D.*在列定义的 DEFAULT 子句中不能指定伪列。

66.What will be the outcome of this statement if the CURRVAL is replaced with NEXTVAL? (Assume that emp_empid_seq is generated to generate employee ID values)

*答案：D.*在列定义的 DEFAULT 子句中不能指定伪列。

Examine the given exhibit giving the structures of the tables Departments and Location. Answer the questions 67 and 68 that follow:

SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

SQL> desc locations
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 LOCATION_ID		 NOT NULL NUMBER(4)
 STREET_ADDRESS 		  VARCHAR2(40)
 POSTAL_CODE			  VARCHAR2(12)
 CITY			 NOT NULL VARCHAR2(30)
 STATE_PROVINCE 		  VARCHAR2(25)
 COUNTRY_ID			  CHAR(2)

67.You need to insert a new department named "HR" in the location ID 1000. Which of the following statements will give you the required results?

*答案：A.*选项 C 会导致“唯一约束违规”，因为它会尝试插入一个已存在于 DEPARTMENTS 表中的当前部门 ID 值。

68.Suppose you execute the below query before inserting the values as shown in the option A in question 67. What will be the outcome of the query?

SELECT dept_deptid_seq.CURRVAL FROM DUAL;

*答案：B.*当用户登录至 Oracle 11g 时，CURRVAL 伪列中最初不存储任何值；当前值为 NULL。在发出 NEXTVAL 调用以生成序列值后，CURRVAL 会存储该值，直至生成下一个值。CURRVAL 仅包含所生成的最后一个值。

69.How can gaps occur in the values of a sequence?

*回答：D. *

70.What is true about caching sequence values?

*答案：C.*如果在创建序列时指定 NOCACHE 选项，则在接收到请求时会生成每个数字。然而，如果组织的事务需要在整个会话中使用大量顺序数字，则可以使用 CACHE 选项，让 Oracle 11g 提前生成一组值并将其存储在服务器的内存中。然后，当用户请求一个序列值时，将会分配下一个可用的值，而无需让 Oracle 11g 生成数字。另一方面，如果未指定 CACHE 选项，则 Oracle 11g 会假定 CACHE 20 的默认选项，并自动在内存中存储 20 个顺序值供用户访问。

71.The following query for the sequence EMP_EMPID_SEQ is executed after a transaction which inserted five employee details.

Select emp_empID_seq.CURRVAL from dual;

假设员工交易已回滚。上述查询的结果将会是什么？

*答案：C.*序列值不受提交或回滚的影响。如果使用序列生成器的交易已回滚，则序列值会被浪费，而且无法恢复。

72.Which of the following privileges are required to modify a sequence?

*答案：B.*要更改序列，序列必须位于您自己的模式中，或者您必须对序列具有 ALTER 对象权限，或者您必须具有 ALTER ANY SEQUENCE 系统权限。

73.What happens when a sequence is altered?

*答案：B.*通过使用 ALTER SEQUENCE 命令，任何更改都只应用于修改后生成的值。

74.Suppose you need to drop a sequence. Which of the following commands will help?

*答案：C。*DROP 命令用于删除序列

75.Which of the following privileges will allow you to drop a sequence? (Choose the most appropriate answer)

*答案：D。*要删除序列，要么序列必须在您自己的模式中，要么您必须具有 DROP ANY SEQUENCE 系统权限。

76.What is true about Indexes?

*答案：D。*索引可以手动创建，也可以在某些操作（例如创建主键或唯一约束）后自动创建。

77.Which of the following is used by an index to locate the data quickly?

*答案：B。*Oracle 11g 索引是存储列值和匹配表行的 ROWID 的映射的数据库对象。ROWID 是表行的物理地址。

78.What happens when there is no index on a column of a table?

回答：B。

79.What among the following is true about an Index?

*回答：D. *

80.What will happen if an index is dropped?

*答案：D。*索引是物理存储在模式中的对象。删除索引不会影响其他对象。

81.What happens when a table is dropped?

回答：B。

82.How are indexes created automatically?

回答：C、D。

83.For which of the following objects, a synonym can be created?

*答案：B、C、D。*您为其创建同义词的模式对象可以是以下类型：表或对象表、视图或对象视图、序列、存储过程、函数或包、物化视图、Java 类模式对象、用户定义对象类型、同义词

84. Which of the following can you use to reference a table owned by another user?

*答案：C。*同义词是数据库对象的别名或替代名称。

85.What among of the following is an example of a Non-unique index?

回答：C。

86.Which of the following is the main and basic type of an Index?

*答案：A、B。*B 树（平衡树）索引是 Oracle 中使用最广泛的索引。您可以使用基本的 CREATE INDEX 语句创建这种类型的索引。位图索引在结构和使用中不同于 B 树索引。此索引对于改进对具有低选择性（低基数或少量不同值）的列的查询非常有用。

87.You need to speed up a query by creating an index on the FIRST_NAME of the EMPLOYEES table. Which of the following statements can you use? (Assume the table structure as shown)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

答案：A。

88.What does the UNIQUE keyword do while creating indexes?

*答案：A。*通常在列上定义主键或唯一约束时，自动创建一个唯一索引。还可以在 CREATE INDEX 语句中包含 UNIQUE 关键字显式创建唯一索引。

89.What will happen when you specify the keyword BITMAP while creating an Index?

回答：C。

90.您编写的查询预期检索少于 2% 到 4% 的行。应用在相关表上的以下哪一项可以实现此类查询的查询性能？（选择最佳答案）

*答案：A。*索引是实现查询性能的最佳方式。可以借助索引扫描来减少和简化繁重的 IO 操作。

91.In what scenarios can Indexes be useful?

回答：C、D。

92.The table EMPLOYEES is updated frequently. When can Indexes be created on this table? (Choose the most appropriate answer)

*答案：A。*在具有索引的表上频繁或批量执行 DML 操作会增加维护索引段的开销，这可能会影响 DML 操作性能。

93.Consider the following query and answer the following query. Assume that the EMPLOYEE_ID , DEPARTMENT_ID and FIRST_NAME columns of EMPLOYEES table are indexed. (Assume the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

SELECT first_name, last_name
FROM employees
WHERE comm IS NULL;

如果 EMPLOYEES 表中有 100 万行，现有索引在这种情况下是否有用？

*答案：B。*当查询谓词不包含创建索引的列时，不会使用索引。

94.Which of the following will remove an Index?

*答案：B。*您必须具有 DROP ANY INDEX 权限才能删除索引。