创建其他模式对象问题

1.以下哪个数据库对象提供了用户和数据之间的抽象层?

  1. 观看次数

  2. 同义词

答案:C,D.视图和同义词本身不存储数据.视图是用于检索存储在基础数据库表中的数据的临时表或虚拟表.

2.以下哪个数据库对象可以生成序列号?

  1. 同义词

  2. 查看

  3. 序列

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

3.关于观点的真实情况是什么?

  1. 它们等于表

  2. 他们存储来自一个或多个表的数据

  3. 我们可以在Simple视图上执行SELECT和其他DML

  4. 视图与表共享相同的命名空间,因此表和视图不能具有相同的名称

答案:C,D.在包含组函数,GROUP BY子句,ROWNUM伪列或DISTINCT关键字的视图上不允许进行DML操作.

4.为什么观点有用? (选择最合适的答案)

  1. 因为他们的名字比表格短

  2. 防止用户访问表格列

  3. 简化用户SQL

  4. 以上所有

答案:B,C.视图是用于检索存储在基础数据库表中的数据的临时表或虚拟表.每次使用视图时都必须执行视图查询.视图可用于简化查询或限制对敏感数据的访问.

5.在下面的哪个场景中,视图上的DML操作是不可能的?

  1. 查看包含GROUP BY子句

  2. 基表包含NOT NULL列但未在视图查询中选择

  3. 查看查询使用ROWNUM伪列

  4. 以上所有

答案:D.包含群组功能的视图不允许进行DML操作,GROUP BY子句,ROWNUM伪列或DISTINCT关键字.

6.视图可以从哪里获取数据?

  1. 来自同一模式的表

  2. 来自不同架构的桌子

  3. A和B

  4. 没有以上

答案:C.

考虑给定的表结构和以下语句,并回答以下问题7到9:

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.您发出以下查询.用户在下面的查询中会看到多少列?

 SELECT * FROM emp_details WHERE department_id = 100;

  1. 0

  2. 1

  3. 2

  4. 4

答案:D.由于视图定义基于EMPLOYEES表中的四列,因此对包含所有列的视图的查询将仅显示这四列.

8.除了以上4列之外,您还需要获取部门名称.以下哪个查询会为您提供所需的结果?

  1.  SELECT E. *,dept_name 
     FROM departments D join emp_details E 
     ON(E.department_id = D.dept_id);

  2.  SELECT hire_date,job,salary,dept_name FROM emp_details

  3. 这是不可能的视图无法加入其他table

  4. 以上都不是

答案:A 视图可以与SELECT查询中的其他表或视图连接.

9.除了在视图中选择的4列之外,您还需要找到最大工资以及部门名称.以下哪个查询会为您提供所需的结果?

  1. Select dept_name, e.salary
    FROM departments D join emp_details E
    On (E.department_id= D.dept_id);

  2. Select dept_name, max(salary)
    FROM departments D join emp_details E
    On (E.department_id= D.dept_id)
    Group by dept_name;

  3. 查看无法在使用群组功能的查询中显示

  4. Select dept_name, max(salary)
    FROM departments D join emp_details E
    On (E.department_id= D.dept_id);

答案:B.

10.关于同义词,以下哪些是正确的?

  1. PUBLIC和PRIVATE同义词可以使用相同的名称对于同一个表

  2. DROP SYNONYM将删除同义词

  3. DROP PUBLIC SYNONYM只能由SYSDBA执行

  4. 以上都不是

答案:A,C .同义词可以是私有同义词,用户可以使用它来引用他们拥有的对象,也可以是用户用来访问其他用户的数据库对象的公共同义词.只有SYSDBA或具有DBA权限的用户才能创建公共同义词.

11.创建视图的真实情况是什么? (选择最合适的答案)

  1. 视图只能从表格中创建

  2. 只能从一个表创建视图

  3. 可以从一个或多个表或视图创建视图

  4. 以上都不是

答案:C.包含表达式或函数或连接多个表的视图被认为是复杂的视图.复杂视图可用于仅更新一个表.

12.在一个人自己的架构中创建视图需要以下哪些特权?

  1. CREATE TABLE系统特权

  2. CREATE VIEW系统特权

  3. ALTER VIEW系统特权

  4. 创建任何视图系统特权

答案:B.用户需要CREATE VIEW权限才能在自己的架构中创建视图.

13.在其他人的架构中创建视图需要以下哪些权限?

  1. 创建任何视图

  2. 创建视图

  3. A和B

  4. 以上都不是

答案:A.用户需要CREATE ANY VIEW权限才能在其他用户的架构中创建视图.

14.对象视图或关系视图是否支持以下哪些?

  1. LOBs

  2. 对象类型

  3. REF数据类型

  4. 以上所有

答案:D.

15.以下是不同类型的视图?

  1. 简单视图

  2. 复杂观点

  3. A和B

  4. 以上都不是

答案:C.简单和复杂视图是两种类型的视图.简单视图基于仅引用一个表的子查询,不包括组函数,表达式或GROUP BY子句.复杂视图基于子查询,该子查询从一个或多个表中检索或派生数据,并且可以包含函数或分组数据.

16.简单视图的真实情况是什么?

  1. DML语句大部分时间都可以发布简单的观点

  2. 只有一个源基表

  3. 没有使用组函数

  4. 以上所有

答案:D.简单的观点是基于仅引用一个表但不包含组函数,表达式或GROUP BY子句的子查询.

17.复杂视图的确是什么?

  1. 无法针对复杂视图发布DML语句

  2. 包含多个基表

  3. 无法进行聚合

  4. 以上所有

答案:D.复杂视图基于子查询,该子查询从一个或多个表中检索或派生数据,并且可以包含函数或分组数据.

18.应该使用哪个关键字组合来实现隐式删除视图(如果存在)并创建一个具有相同名称的新视图?

  1. 创建视图

  2. 更换视图

  3. 创建或替换视图

  4. 以上都不是

答案:C. OR REPLACE选项通知Oracle 11g可能已经存在同名视图;如果是,则视图的先前版本应替换为新命令中定义的版本.

19.如何存储在数据字典中的视图?

  1. 作为WHERE子句

  2. 作为CREATE语句

  3. 作为UPDATE语句

  4. 作为SELECT语句

答案:D.

20.以下哪项可以包含单行函数?

  1. 内联视图

  2. 简单视图

  3. 复杂视图

  4. 复合视图

答案:A,B.单行函数也可以在Inline中使用作为简单视图.

21.以下哪项可以包含一组数据?

  1. 复合视图

  2. 简单视图

  3. 复杂视图

  4. 以上所有

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

22.关于视图,以下哪些是真的?

  1. 子查询可嵌入CREATE VIEW语句

  2. CREATE VIEW语句中使用的子查询必须具有简单的SELECT语法

  3. 在子查询中使用WHERE子句时,不能使用WHERE子句CREATE VIEW语句

  4. 以上所有

答案: A. 视图定义可以使用子查询.

23.即使基表没有,以下任何一个都可以创建视图存在吗?

  1. NOFORCE

  2. FORCE

  3. 或更换

  4. 创建视图

答案:B.如果在CREATE子句中包含FORCE关键字,Oracle 11g会创建视图,尽管没有任何引用的表. NOFORCE是CREATE VIEW命令的默认模式,这意味着所有表和列必须有效,否则视图不会创建.

24.以下命令中的哪一项确保不能对视图执行DML操作?

  1. NOFORCE

  2. FORCE

  3. 仅供阅读

  4. 或更换

答案:C. WITH READ ONLY选项可防止对视图执行任何DML操作.当用户只能查询数据而不对其进行任何更改时,通常会使用此选项.

25. CREATE VIEW语句中的NOFORCE选项有什么用?

  1. 即使基表不存在,它也会创建一个视图.

  2. 仅当基表存在时才创建视图.

  3. 这是创建视图时的默认值.

  4. 以上都不是

答案:B,C. NOFORCE是CREATE VIEW命令的默认模式,这意味着所有表和列必须有效,否则视图不会创建.

26. OR REPLACE关键字的确是什么?

  1. 使用此关键字创建视图时,对象权限将丢失

  2. 无需重新授予之前授予的对象权限

  3. A和B

  4. 以上都不是

答案:B. OR REPLACE选项通知Oracle 11g可能已经存在同名视图;如果是,则视图的先前版本应替换为新命令中定义的版本.

27.访问以下视图时是什么情况? (假设给出了表结构)

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;

  1. 视图必须由基础中定义的原始列名访问table

  2. 视图必须通过视图查询中给出的别名来访问

  3. 视图是一个简单的视图

  4. 以上都不是

答案:B,C .如果视图定义包含列的别名,则View必须引用列别名.

28.访问以下视图时是什么情况? (假设给出了表结构)

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;

  1. 别名的数量与no匹配并不是强制性的.子查询中的表达式

  2. 必须使用no.列出的别名必须与否匹配.在子查询中选择的表达式

  3. 创建视图时必须提供别名

  4. 没有以上

答案:B.如果在视图标题中指定了别名,则列数相同必须在SELECT查询中选择.

29.请考虑以下语句和给定的表结构:

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	

 SQL> DESC部门
名称空?输入
 ----------------------- -------- -------------- -  
 DEPARTMENT_ID NOT NULL NUMBER(4)
 DEPARTMENT_NAME NOT NULL VARCHAR2(30)
 MANAGER_ID NUMBER(6)
 LOCATION_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)

上述查询中的列别名有什么用?

  1. 列别名以随机顺序列出,因为子查询中的列

  2. 列别名的列出顺序与子查询中的列

  3. 使用CREATE OR REPLACE关键字时,列别名是必需的

  4. 我们无法使用当我们使用CREATE OR REPLACE时连接

答案:B.

考虑以下陈述并回答以下问题30至34:

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.关于上述陈述可以说什么?

  1. 已经为视图提供了替代名称

  2. 如果任何列来自一个列,则必须提供替代名称函数或表达式

  3. A和B

  4. 以上都不是

答案:C.指定别名是提高代码和视图查询可读性的好方法.

31.如果上述声明修改如下,会发生什么?

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;

  1. 它与原始声明$ b没有什么不同$ b

  2. 它将成功执行,给出相同的结果,但更改别名.

  3. 它会抛出一个ORA错误

  4. 以上都不是

答案:B.列别名的顺序并不重要,因为它们没有任何行为属性.

32.确定以下DELETE语句的输出.

 DELETE FROM dept_sum_vu;

  1. 它将删除视图

  2. 它将从视图中删除所有行,但视图的结构将保持不变

  3. 它将抛出ORA错误

  4. 以上都不是

答案: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;

修改的影响是什么?

  1. 可以更新视图以更新EMPLOYEES和DEPARTMENTS表中的值

  2. 无法通过查看

  3. 可以通过视图插入EMPLOYEES和DEPARTMENTS表中的数据

  4. 可以通过视图$ b $将列添加到EMPLOYEES表中b

答案:B.无法在复杂视图上执行DML操作. DEPT_SUM_VU是一个复杂的视图,因为它连接了多个表.视图无法进行DDL操作.

34.假设您在上述问题中使用的查看查询中选择了DISTINCT部门和员工工资.如果您尝试从视图dept_sum_vu中删除行,结果会是什么?

  1. 行将被删除而不会出现任何错误

  2. 只会删除前10行

  3. 无法删除行.

  4. 以上都不是

答案:C.视图DEPT_SUM_VU仍然是一个复杂的视图,因为它使用DISTINCT关键字.因此,DML操作是不可能的.

35.什么时候可以删除视图中的行?

  1. 无法通过视图删除行

  2. 这应该是一个简单的视图

  3. 这应该是一个复杂的视图

  4. 以上所有


  5. 都不是

答案:B. DML操作只适用于简单视图.

36.什么时候可以视图中的数据不被修改?

  1. 当有组表达式时

  2. 当有GROUP BY子句时

  3. 当在视图查询中使用ROWNUM时

  4. 所有上述

答案:D.视图无法更新包含组函数,伪列或DISTINCT关键字.

37. JOB_HISTORY表由用户"Andy"拥有. Andy将JOB_HISTORY表的SELECT权限授予另一个用户"HR".哪个语句会创建一个同义词EMP_JOBS,以便"HR"可以成功执行以下查询?(假设表的结构为给定)

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;

  1. Andy问题 -  

    CREATE SYNONYM EMP_JOBS for JOB_HISTORY

  2. HR问题 -  

    CREATE SYNONYM EMP_JOBS for andy.JOB_HISTORY

  3. 人力资源问题 -  

    CREATE PUBLIC SYNONYM EMP_JOBS FOR andy.JOB_HISTORY

  4. 以上都不是

答案:B.只有SYSDBA或具有DBA权限的用户才能创建公共同义词.

38.哪个关键字可以确保在视图上执行的DML操作保留在视图的域中?

  1. OR REPLACE

  2. CREATE

  3. WITH CHECK OPTION

  4. 以上都不是

答案:C. WITH CHECK OPTION约束确保对视图执行的任何DML操作(例如添加行或更改数据)都不会阻止视图访问该行,因为它不再满足WHERE子句中的条件.

考虑以下表格结构和给定的陈述,并回答以下问题39和40:

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.以上陈述会做什么?

  1. 它允许用户对所有部门执行INSERT或UPDATE

  2. 它将允许用户执行INSERT或UPDATE任何具有部门100
    的行

  3. 用户可以更新employees表中的任何行

  4. 用户可以INSERT employees表中没有任何限制的行

答案:B. WITH CHECK OPTION约束确保任何在视图上执行的DML操作(例如添加行或更改数据)不会阻止视图访问该行,因为它不再满足WHERE子句中的条件.如果在department_id不是100的任何行上执行INSERT或UPDATE,将抛出ORA错误.

40.假设您触发UPDATE语句,如下所示:

UPDATE empvu100
Set department_id = 200
Where employee_id  = 121;

这句话的结果是什么?

  1. 没有行更新

  2. 抛出ORA错误

  3. A和B

  4. 以上都不是

答案:C.如果更新了带有CHECK OPTION的视图并且新记录的值违反了视图的范围,则会引发ORA异常"ORA-01402:查看WITH CHECK OPTION where-clause违规".

41. WITH CHECK CONSTRAINT是什么?

  1. 执行INSERT或UPDATE通过视图无法创建视图无法选择的行

  2. 只有通过视图执行的INSERT才能创建视图无法选择的行

  3. 只有通过视图执行的UPDATE才能创建视图无法选择的行

  4. 以上都不是

答案:A.

42.H你可以阻止视图上的DML操作吗?

  1. 通过定义WITH CHECK OPTION约束

  2. 通过定义WITH READ ONLY选项

  3. 既不是A也不是B

  4. 以上都不是

答案:B. WITH READ ONLY选项可防止在视图上执行任何DML操作.当用户只能查询数据而不对其进行任何更改时,通常会使用此选项.

考虑表结构和给定语句并回答问题43,以下44和45:

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.上述声明有什么用?

  1. 视图不会被创建

  2. 对此视图的INSERT操作将抛出ORA错误

  3. 在更新部门100中所有员工的行时,将抛出ORA错误

  4. 以上都不是

答案:B,C.在视图上不允许使用READ ONLY选项创建DML操作.

44.如上图所示,可以从视图中删除多少行?

  1. 视图的所有行

  2. 仅部门的所有行100

  3. 没有行

  4. 以上都不是

答案:C.在视图上不允许使用READ ONLY选项创建DML操作.

45.以下哪些陈述会删除上面创建的视图?

  1.  DROP READ ONLY VIEW empvu100;

  2.  DROP NOFORCE empvu100;

  3.  DROP VIEW empvu100;

  4. 以上都不是

答案:C.只读视图可以删除使用DROP VIEW命令.

46.删除视图有什么用?

  1. 基表中视图中的列也被删除

  2. 视图的定义将从数据库中删除

  3. 删除视图对底层基表没有影响

  4. 以上都不是

答案:B,C.

47.其中following privileges should a user have to dr op a view?

  1. CREATE ANY VIEW

  2. CREATE VIEW

  3. DROP ANY VIEW

  4. DROP VIEW

Answer: C.

48.What is true about sequences?

  1. It generates integers

  2. It is a shareable object

  3. Can be used to create a PRIMARY KEY value

  4. All of the above

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

49.What is true about a sequence?

  1. It is created when the Oracle Database is installed

  2. It is created by a user who has CREATE SEQUENCE system privilege

  3. It cannot be shared by more than two users

  4. It drops automatically when the database is disconnected.

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?

  1. The integers generated through a sequence for one table cannot be used by other tables

  2. A sequence can only be incremented

  3. A sequence becomes invalid if another sequence generates the same integers

  4. A sequence can be used by many tables and they can be incremented or decremented

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?

  1. 1

  2. 100

  3. 101

  4. 9999

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?

  1. 0

  2. 100

  3. 101

  4. 9999

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?

  1. 102

  2. 100

  3. 99

  4. 9999

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?

  1. 101

  2. No value

  3. It will throw an ORA error

  4. None of the above

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?

  1. 20

  2. 0

  3. 100

  4. 9999

Answer: A.

56.You execute the below query:

SELECT dept_depid_seq.NEXTVAL from dual;


Assuming that the last value the sequence generated was 200, what will be the outcome of this query?

  1. 200

  2. 101

  3. 9999

  4. 201

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;


Assuming that the last value the sequence generated was 200, what will be the outcome of this query?

  1. 200

  2. 101

  3. 9999

  4. 201

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?

  1. ALTER dept_deptid_seq 
     INCREMENT BY 100 
     START WITH 1000 
     MAXVALUE 9999 
     NOCACHE 
     NOCYCLE;
  2. The sequence has to be dropped and re-created to start the sequence from 1000.

  3. ALTER SEQUENCE dept_deptid_seq 
     START WITH 101
  4. ALTER SEQUENCE dept_deptid_seq 
     INCREMENT BY 100 
     START WITH 101 
     CYCLE;

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?

  1. ORA error

  2. The maximum value for the altered sequence will now be 99

  3. Neither of A nor B

  4. None of the above

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?

  1. If we want to purge the old rows faster

  2. If we do not want to use the sequence to generate PRIMARY KEY values

  3. Both A and B

  4. None of the above

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?

  1. It re-generates the CURRVAL of a sequence

  2. It returns the next available sequence value

  3. It can return duplicate values

  4. It generates the same values for different users

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?

  1. CURRVAL can be used before NEXTVAL with respect to a sequence

  2. CURRVAL gives the current value of a sequence

  3. CURRVAL can give duplicate values

  4. None of the above

Answer: B.

63.When can NEXTVAL and CURRVAL be used?

  1. SET clause of an INSERT statement

  2. VALUES clause of an UPDATE statement

  3. The SELECT list of a SELECT statement that is not part of a sub-query

  4. The SELECT list of an INSERT statement

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?

  1. The SELECT list of a view

  2. The SELECT statement with the DISTINCT keyword

  3. A sub-query in SELECT, DELETE or UPDATE statement

  4. All of the above

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)

  1. Table will be created

  2. The department_id column will have the values from the sequence generated for the employee ID

  3. The department_id column will have a DEFAULT value

  4. ORA error

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)

  1. Table will be created

  2. The department_id column will have the values from the sequence generated for the employee ID

  3. The department_id column will have a DEFAULT value

  4. ORA error

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?

  1. INSERT INTO departments (dept_id, dept_name, location_id) 
     VALUES (dept_deptid_seq.NEXTVAL, ’HR’, 1000);
  2. INSERT INTO departments (dept_id, dept_name, location_id) 
     VALUES (dept_deptid_seq.NEXTVAL, "HR", 1000);
  3. INSERT INTO departments (dept_id, dept_name, location_id) 
     VALUES (dept_deptid_seq.CURRVAL, ’HR’, 1000);
  4. None of the above

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;

  1. ORA error

  2. It will give the current value of the sequence

  3. Neither of A nor B

  4. None of the above

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?

  1. When a rollback occurs

  2. The system crashes

  3. A sequence is used in another table

  4. All of the above

Answer: D.

70.What is true about caching sequence values?

  1. Caching sequence values is not possible in Oracle

  2. The cache is populated when the maximum limit of the sequence is reached

  3. Caching starts the first time when the sequence is referred

  4. None of the above

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?

  1. The sequence value at the starting of employee transaction

  2. NULL

  3. The sequence value at the end of employee transaction

  4. None of the above

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?

  1. CREATE OR REPLACE privilege

  2. ALTER privilege for the sequence

  3. ALTER TABLE privilege

  4. UPDATE privilege

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?

  1. The existing integers already generated by the sequence are altered as well

  2. Only the future integers are affected

  3. The sequence stops caching the future integers

  4. None of the above

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?

  1. ALTER SEQUENCE sequence_name START WITH NULL;
  2. DROP sequence_name;
  3. DROP SEQUENCE sequence_name;
  4. None of the above

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)

  1. ALTER SEQUENCE

  2. ALTER TABLE

  3. DROP SEQUENCE

  4. 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?

  1. Indexes are only manually created

  2. Indexes are only automatically created

  3. Both A and B

  4. None of the above

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?

  1. ROWNUM

  2. ROWID

  3. $ b$b

  4. Sequence

  5. None of the above

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?

  1. The data is located quickly

  2. There is a full table scan

  3. The table cannot be created

  4. The table cannot be altered

Answer: B.

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

  1. Index reduces the disk I/O

  2. Index locates data quickly

  3. Indexes are logically and physically independent of the table that they index

  4. All of the above

Answer: D.

80.What will happen if an index is dropped?

  1. The column on which the index is created, is dropped too

  2. The table on which the index is created, is dr opped too

  3. Indexes once created cannot be dropped

  4. As Indexes are logically and physically independent objects, they can be dropped without affecting other objects

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?

  1. The indexes still remain as they are logically and independent objects

  2. The indexes in the table are also dropped

  3. Neither of A nor B

  4. None of the above

Answer: B.

82.How are indexes created automatically?

  1. When we create a table

  2. When a table is altered

  3. When a PRIMARY KEY is defined on a column (or group of columns) of a table

  4. When a UNIQUE KEY constraint is defined in the table definition

Answer: C, D.

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

  1. Tables and views only

  2. Table, vie w and sequence

  3. Stored procedure, function, or package

  4. Synonym

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?

  1. INDEX

  2. TABLE

  3. SYNONYMS

  4. SEQUENCES

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?

  1. PRIMARY KEY

  2. UNIQUE KEY

  3. FOREIGN KEY

  4. None of the above

Answer: C.

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

  1. Bitmap

  2. B-tree

  3. Unique

  4. Non-unique

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)

  1. CREATE INDEX emp_first_name_idx  
     ON employees (first_name);
  2. CREATE INDEX emp_first_name_idx  
     ON employees first_name;
  3. ALTER INDEX emp_first_name_idx  
     ON employees (first_name);
  4. None of the above

Answer: A.

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

  1. It specifies that the value of the column(s) upon which the index is created must be unique

  2. You cannot use the UNIQUE keyword when creating indexes

  3. It specifies that the index that is created can be used only by one table

  4. None of the above

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?

  1. It creates the index with a bitmap for each distinct key.

  2. It does not create the index on each row separately

  3. Both A and B

  4. None of the above

Answer: C.

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)

  1. Indexes

  2. UNION set operator

  3. MINUS set operator

  4. None of the above

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?

  1. If the table(s) is very large

  2. If a column has less values

  3. If a column contains a large number of NULL values

  4. If a column has a wide range of values

Answer: C, D.

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

  1. Indexes should not be created if a table is updated frequently

  2. Indexes should be created at the time when the table is created

  3. Neither of A nor B

  4. None of the above

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;

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

  1. Yes

  2. No

  3. It might help

  4. None of the above

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?

  1. DELETE FROM index_name;
  2. DROP INDEX index_name;
  3. DROP INDEX;
  4. None of the above

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