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。*视图和同义词本身不存储数据。视图是一个临时或虚拟表，用于检索存储在底层数据库表中的数据。

*Answer: C, D. *Views and Synonyms do not store data themselves. A view is a temporary or virtual table used to retrieve data stored in underlying database tables.

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

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

*Answer: D. *A sequence can be created to generate a series of integers. The values generated by a sequence can be stored in any table. A sequence is created with the CREATE SEQUENCE command.

3. What is true about views?

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

*Answer: C, D. *DML operations aren’t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.

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

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

*Answer: B, C. *A view is a temporary or virtual table used to retrieve data stored in underlying database tables. The view query must be executed each time the view is used. A view can be used to simplify queries or restrict access to sensitive data.

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

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

*Answer: D. *DML operations aren’t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword.

6. Where can views get their data from?

回答：C。

*Answer: 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 表中的四列，针对该视图的查询中只会显示这四列。

*Answer: D. *Since the view definition is based on four columns from the EMPLOYEES table, a query on a view with all column will show those four columns only.

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 查询中的其他表或视图联接。

*Answer: A. *A view can be joined with other tables or views in a SELECT query.

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。

*Answer: B. *

10. What among the following is true about synonyms?

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

*Answer: A, C. *A synonym can be a private synonym, which users use to reference objects they own,or a public synonym, which users use to access another user’s database objects. Only SYSDBA or a user with DBA privileges can create a public synonym.

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

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

*Answer: C. *A view containing expressions or functions or joining multiple tables is considered a complex view. A complex view can be used to update only one table.

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

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

*Answer: B. *CREATE VIEW privilege is required by a user to create a view in its own schema.

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

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

*Answer: A. *CREATE ANY VIEW privilege is required by a user to create a view in other user’s schema.

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

*回答：D. *

*Answer: D. *

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

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

*Answer: C. *Simple and Complex views are two types of views. Simple views are based on a subquery that references only one table and doesn’t include group functions, expressions, or GROUP BY clauses. Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

16. What is true about a simple view?

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

*Answer: D. *Simple views are based on a subquery that references only one table and doesn’t include group functions, expressions, or GROUP BY clauses.

17.What is true about a complex view?

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

*Answer: D. *Complex views are based on a subquery that retrieves or derives data from one or more tables and can contain functions or grouped data.

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 同名视图可能已存在。如果已存在，则用新命令中定义的视图替换该视图的旧版本。

*Answer: C. *The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view’s previous version should be replaced with the one defined in the new command.

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

*回答：D. *

*Answer: D. *

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

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

*Answer: A, B. *Single-row functions can be used in Inline as well as Simple views.

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

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

*Answer: C. *Complex view can use group function in the query.

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

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

*Answer: A. *View definition can make use of sub-queries.

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 命令的默认模式，这意味着所有表和列都必须有效，否则将不会创建该视图。

*Answer: B. *Ff you include the FORCE keyword in the CREATE clause, Oracle 11g creates the view in spite of the absence of any referenced tables. NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn’t created.

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

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

*Answer: C. *The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it’s important that users can only query data, not make any changes to it.

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

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

*Answer: B, C. *NOFORCE is the default mode for the CREATE VIEW command, which means all tables and columns must be valid, or the view isn’t created.

26.What is true about the OR REPLACE keyword?

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

*Answer: B. *The OR REPLACE option notifies Oracle 11g that a view with the same name might already exist; if it does, the view’s previous version should be replaced with the one defined in the new command.

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。*如果视图定义包含列的别名，则视图必须引用列别名。

*Answer: B, C. *View must refer the column alias if the view definition contains alias for the columns.

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 查询中选择相同数量的列。

*Answer: B. *If the alias are specified in the view header, same number of columns must be selected in the SELECT query.

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;

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

What is true about the column aliases as in the above query?

回答：B。

*Answer: 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。*指定别名名称是为了提高代码和视图查询的可读性。

*Answer: C. *Specifying alias name is good practice to improve the readability of the code and the view queries.

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。*列别名的顺序没有多大关系，因为它们没有任何行为属性。

*Answer: B. *The sequence of the column alias not matters much as they don’t carry any behavioral attribute.

32.Determine the output of the below DELETE statement.

DELETE FROM dept_sum_vu;

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

*Answer: C. *The view DEPT_SUM_VU is a complex view. DML operations cannot be performed on a complex view.

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

*33.Suppose you modify the query given above to the following: *

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;

修改的影响是什么？

What will be the impact of the modification?

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

*Answer: B. *DML operations cannot be performed on complex views. DEPT_SUM_VU is a complex view as it joined multiple tables. DDL operations are not possible on views.

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 操作。

*Answer: C. *The view DEPT_SUM_VU is still a complex view as it uses DISTINCT keyword. Hence, DML operations are not possible on it.

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

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

*Answer: B. *DML operations are possible only on simple views.

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

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

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

*Answer: D. *UPDATE is not possible on a view containing group functions, pseudocolumns or DISTINCT keyword.

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 同义词。

*Answer: B. *Only SYSDBA or a user with DBA privileges can create public synonyms.

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

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 子句中的条件而无法访问行。

*Answer: C. *The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don’t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause.

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 错误。

*Answer: B. *The WITH CHECK OPTION constraint ensures that any DML operations performed on the view (such as adding rows or changing data) don’t prevent the view from accessing the row because it no longer meets the condition in the WHERE clause. An ORA error will be thrown if an INSERT or UPDATE will be executed on any row with a department_id other than 100.

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

UPDATE empvu100
Set department_id = 200
Where employee_id  = 121;

此语句的结果是什么？

What will be the outcome of this statement?

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

*Answer: C. *If the view with CHECK OPTION is updated and new record’s value violates the scope of the view, ORA exception "ORA-01402: view WITH CHECK OPTION where-clause violation" is raised.

41.What is true about the WITH CHECK CONSTRAINT?

答案：A。

*Answer: A. *

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

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

*Answer: B. *The WITH READ ONLY option prevents performing any DML operations on the view. This option is used often when it’s important that users can only query data, not make any changes to it.

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 操作。

*Answer: B, C. *DML operations are not permitted on view which are created with READ ONLY option.

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

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

*Answer: C. *DML operations are not permitted on view which are created with READ ONLY option.

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

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

*Answer: C. *Read only view can be dropped using the DROP VIEW command.

46.What is true about dropping a View?

答案：B、C。

*Answer: B, C. *

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

回答：C。

*Answer: C. *

48.What is true about sequences?

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

*Answer: D. *A sequence speeds up the efficiency of accessing sequence values when cached in memory

49.What is true about a sequence?

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

*Answer: B, C. *CREATE SEQUENCE system privilege is required by a user to create a sequence in its own schema which cannot be shared by other users.

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

*回答：D. *

*Answer: 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 开始每个序列。

*Answer: C. *The START WITH clause establishes the starting value for the sequence. Oracle 11g begins each sequence at 1 unless another value is specified in the START WITH clause.

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

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

*Answer: D. *The MINVALUE and MAXVALUE clauses establish a minimum or maximum value for the sequence.

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

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

*Answer: A. *The INCREMENT BY clause specifies the interval between two sequential values. If the sequence is incremented by a positive value, the values the sequence generates are in ascending order. However, if a negative value is specified, the values the sequence generates are in descending order. If the INCREMENT BY clause isn’t included when the sequence is created, the default setting is used, which increases the sequence by one for each integer generated.

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

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

*Answer: B. *The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value.

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

答案：A。

*Answer: A. *

56.You execute the below query:

SELECT dept_depid_seq.NEXTVAL from dual;

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

*Answer: D. *The NEXTVAL pseudocolumn will generate the next unique integer of the sequence.

57.You execute the below query:

SELECT dept_depid_seq.CURRVAL from dual;

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

*Answer: A. *The CURRVAL pseudocolumn will generate the current unique integer already generated by the sequence.

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：无法更改起始序列号”。

*Answer: B. *Starting number of a sequence cannot be modified. Oracle raises the exception "ORA-02283: cannot alter starting sequence number".

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;

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

What will be the outcome of this alteration?

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

*Answer: A. *The MAXVALUE cannot be less than the START WITH value while altering a sequence.

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

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

*Answer: C. *The CYCLE and NOCYCLE options determine whether Oracle 11g should begin reissuing values from the sequence after reaching the minimum or maximum value. If the CYCLE option is specified and Oracle 11g reaches the maximum value for an ascending sequence or the minimum value for a descending sequence, the CYCLE option initiates the cycle of numbers again.

61.What is true about NEXTVAL pseudo column?

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

*Answer: B. *The pseudocolumn NEXTVAL (NEXT VALUE) is used to actually generate the sequence value. In other words, it calls the sequence object and requests the value of the next number in the sequence. After a value is generated, it’s stored in the CURRVAL (CURRENT VALUE) pseudocolumn so that you can reference it again.

62.What is true about CURRVAL pseudo column?

回答：B。

*Answer: B. *

63.When can NEXTVAL and CURRVAL be used?

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

*Answer: C, D. *The sequence can be used in SELECT query, PL/SQL cursor or in IAS (INSERT-AS-SELECT)direct operations.

64.When can NEXTVAL and CURRVAL not be used?

*回答：D. *

*Answer: 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 子句中不能指定伪列。

*Answer: D. *Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

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 子句中不能指定伪列。

*Answer: D. *Pseudocolumns cannot be specified in DEFAULT clause of a column definition.

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 值。

*Answer: A.*The option C will cause a 'Unique constraint violation' as it will try to insert current value of department id which aleady exists in the DEPARTMENTS table.

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 仅包含所生成的最后一个值。

*Answer: B. *When a user logs in to Oracle 11g, no value is initially stored in the CURRVAL pseudocolumn; the current value is NULL. After a NEXTVAL call has been issued to generate a sequence value, CURRVAL stores that value until the next value is generated. CURRVAL contains only the last value generated.

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

*回答：D. *

*Answer: D. *

70.What is true about caching sequence values?

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

*Answer: C. *If the NOCACHE option is specified when the sequence is created, each number is generated when the request is received. However, if an organization’s transactions require large amounts of sequential numbers throughout a session, the CACHE option can be used to have Oracle 11g generate a set of values ahead of time and store them in the server’s memory. Then, when a user requests a sequence value, the next available value is assigned-without Oracle 11g having to generate the number. On the other hand, if the CACHE option isn’t specified, Oracle 11g assumes a default option of CACHE 20 and stores 20 sequential values in memory automatically for users to access.

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;

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

Suppose the employee transaction rolled back. What will be the result of the above query?

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

*Answer: C. *Sequence values are unaffected by commit or rollback. If a transaction which uses sequence generator is rolled back, the sequence values are wasted and cannot be recovered.

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

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

*Answer: B. *To alter a sequence, the sequence must be in your own schema, or you must have the ALTER object privilege on the sequence, or you must have the ALTER ANY SEQUENCE system privilege.

73.What happens when a sequence is altered?

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

*Answer: B. *By using the ALTER SEQUENCE command, any changes are applied only to values generated after the modifications are made.

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

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

*Answer: C. *The DROP command is used to drop a sequence

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

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

*Answer: D. *To drop a sequence, either the sequence must be in your own schema or you must have the DROP ANY SEQUENCE system privilege.

76.What is true about Indexes?

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

*Answer: D. *Indexes can be created manually as well as automatically following certain actions like creating a primary key or unqiue constraint.

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

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

*Answer: B. *An Oracle 11g index is a database object that stores a map of column values and the ROWIDs of matching table rows. A ROWID is the physical address of a table row.

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

回答：B。

*Answer: B. *

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

*回答：D. *

*Answer: D. *

80.What will happen if an index is dropped?

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

*Answer: D. *Indexes are the objects which are physically stored in schema. Dropping an index doesn’t impacts other objects.

81.What happens when a table is dropped?

回答：B。

*Answer: B. *

82.How are indexes created automatically?

回答：C、D。

*Answer: C, D. *

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

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

*Answer: B, C, D. *The schema object for which you are creating the synonym can be of the following types:Table or object table, View or object view, Sequence, Stored procedure, function, or package, Materialized view, Java class schema object, User-defined object type, Synonym

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

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

*Answer: C. *A synonym is an alternative name or alias for a database object.

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

回答：C。

*Answer: C. *

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

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

*Answer: A, B. *The B-tree (balanced-tree) index is the most common index used in Oracle. You can create this type of index with a basic CREATE INDEX statement. A bitmap index varies in structure and use from a B-tree index. This index is useful for improving queries on columns that have low selectivity (low cardinality, or a small number of distinct values).

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。

*Answer: A. *

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

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

*Answer: A. *A unique index is typically created automatically when a PRIMARY KEY or UNIQUE constraint is defined on a column. Unique indexes can also be explicitly created by including the UNIQUE keyword in the CREATE INDEX statement.

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

回答：C。

*Answer: C. *

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

*90.You have queries written which are expected to retrieve less than 2% to 4% of rows. Which of the following can be applied on the relevant tables to achieve the query performance of such query? (Choose the best answer) *

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

*Answer: A. *Indexes are the best way to achieve query performance. Heavy IO operations can be reduced and simplified using index scans.

91.In what scenarios can Indexes be useful?

回答：C、D。

*Answer: 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 操作性能。

*Answer: A. *Frequent or bulk DML operations on a table with an index add an overhead of maintaining the index segment, which might affect the DML operation performance.

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 万行，现有索引在这种情况下是否有用？

Will the existing indexes help in this case if there are 1 million rows in the table EMPLOYEES?

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

*Answer: B. *Indexes are not used when the query predicates do not contain the columns on which the index is created.

94.Which of the following will remove an Index?

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

*Answer: B. *You must have the DROP ANY INDEX privilege to drop an index.