OCP课程12：SQL之创建其他模式对象

stonebox

课程目标：

• 创建简单和复杂视图
• 从视图获取数据
• 创建、维护及使用序列
• 创建和维护索引
• 创建私有和公有同义词
  

数据库对象：

                               
登录/注册后可看大图

1、视图

视图的优势：

• 屏蔽对敏感信息的访问
• 简化复杂的查询
• 根据不同的用户需求创建不同的视图
• 数据的独立性，通过视图实现接口的功能
  

简单视图和复杂视图

• 简单视图是指视图数据来源于一张表，不包含函数及分组，可以进行DML操作，对简单视图进行操作，实际上就是对基表进行操作，构成视图的表叫基表
• 复杂视图是指视图来源于一张表或者多张表，可能包含分组和函数，不一定能进行DML操作
  

                               
登录/注册后可看大图

创建视图的语法：

                               
登录/注册后可看大图

（1）创建简单视图

例子：使用人员表里面80部门的人员信息创建一个视图

SQL> create view empvu80

  2  as

  3  select employee_id,last_name,salary from employees where department_id=80;

View created.

查询视图结构

SQL> desc empvu80;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID                               NOT NULL NUMBER(6)

LAST_NAME                                 NOT NULL VARCHAR2(25)

SALARY                                             NUMBER(8,2)

例子：在子查询中使用列别名创建视图

SQL> create view salvu50

  2  as

  3  select employee_id id_number,last_name name,salary*12 ann_salary

  4  from employees where department_id=50;

View created.

SQL> desc salvu50;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

ID_NUMBER                                 NOT NULL NUMBER(6)

NAME                                      NOT NULL VARCHAR2(25)

ANN_SALARY                                         NUMBER

与使用子查询创建表类似，如果子查询里面有表达式或者函数，必须要用别名，不然要报错，或者在视图后面带上字段。

查询视图与查询表一样，都是使用select语句，而且性能没有影响

例子：查看查询视图的执行计划

SQL> conn / as sysdba

Connected.

SQL> set autotrace on

SQL> select * from hr.salvu50;

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |    45 |   855 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    45 |   855 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

执行计划与单独执行子查询一样

SQL> select employee_id id_number,last_name name,salary*12 ann_salary

  2  from hr.employees where department_id=50;

Execution Plan

----------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |    45 |   855 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |    45 |   855 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

使用create or replace view修改视图，修改后源视图的权限保留，不需要再进行授权，而删除重建需要重新进行授权。

例子：修改前面创建的视图empvu80

SQL> create or replace view empvu80(id_number,name,sal,department_id)

  2  as

  3  select employee_id,first_name||' '||last_name,salary,department_id

  4  from employees where department_id=80;

View created.

（2）创建复杂视图

例子：创建一个包含组函数，数据来自于employees表和departments表的复杂视图，显示每个部门的部门名称，最小薪水，最大薪水和平均薪水

SQL> create or replace view dept_sum_vu(name,minsal,maxsal,avgsal)

  2  as

  3  select d.department_name,min(e.salary),max(e.salary),avg(e.salary)

  4  from employees e join departments d

  5  on e.department_id=d.department_id

  6  group by d.department_name;

View created.

（3）视图上面进行DML操作的规则

• 在简单视图上面可以进行DML操作
• 视图如果包含组函数，group by子句，distinct关键字，rownum伪列，不能删除视图里面的记录
• 视图如果包含组函数，group by子句，distinct关键字，rownum伪列，表达式定义的列，不能修改视图里面的数据
• 视图如果包含组函数，group by子句，distinct关键字，rownum伪列，表达式定义的列以及存在于基表（没有定义默认值）但是不在视图中的非空列，不能通过视图增加数据
  

例子：通过简单视图删除数据

SQL> delete from empvu80 where id_number=177;

1 row deleted.

例子：通过包含组函数的复杂视图删除数据会报错

SQL> delete from dept_sum_vu where name='IT';

delete from dept_sum_vu where name='IT'

            *

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

例子：通过包含表达式的视图修改相关数据会报错

SQL> update empvu80 set name='aaa' where id_number=179;

update empvu80 set name='aaa' where id_number=179

                   *

ERROR at line 1:

ORA-01733: virtual column not allowed here

例子：创建的视图不包含基表的非空字段，且非空字段没有默认值，则通过视图进行插入数据会报错

先创建一个基表，包含非空字段

SQL> create table emp as select employee_id,last_name,salary,job_id from employees where 1=0;

Table created.

SQL> desc emp;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID                                        NUMBER(6)

LAST_NAME                                 NOT NULL VARCHAR2(25)

SALARY                                             NUMBER(8,2)

JOB_ID                                    NOT NULL VARCHAR2(10)

再创建一个视图，不包含所有非空字段

SQL> create view v_emp as

  2  select employee_id,salary from emp;

View created.

SQL> desc v_emp;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID                                        NUMBER(6)

SALARY                                             NUMBER(8,2)

插入数据报错

SQL> insert into v_emp values(100,1000);

insert into v_emp values(100,1000)

*

ERROR at line 1:

ORA-01400: cannot insert NULL into ("HR"."EMP"."LAST_NAME")

修改表定义，为非空字段增加默认值

SQL> alter table emp modify(last_name default 'stone',job_id default 'IT');

Table altered.

再次插入数据

SQL> insert into v_emp values(100,1000);

1 row created.

查询基表确认，已正确插入了

SQL> select * from emp;

EMPLOYEE_ID LAST_NAME                     SALARY JOB_ID

----------- ------------------------- ---------- ----------

        100 stone                           1000 IT

（4）使用with check option语句

使用with check option语句增加约束，可以确保用户的DML操作只能在视图的范围内进行（就是不能超出where条件）。不然，用户修改了数据导致该数据不满足创建视图子查询的where条件，则下次查询就看不到了。

例子：不使用with check option语句和使用with check option语句的区别

先创建一个视图不使用with check option语句

SQL> create or replace view empvu20 as

  2  select employee_id,last_name,salary,department_id from employees where department_id=20;

View created.

SQL> select * from empvu20;

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID

----------- ------------------------- ---------- -------------

        201 Hartstein                      13000            20

        202 Fay                             6000            20

对其中一条记录进行修改，将部门编号修改为10

SQL> update empvu20 set department_id=10 where employee_id=202;

1 row updated.

再次查询，刚才修改的记录看不到了

SQL> select * from empvu20;

EMPLOYEE_ID LAST_NAME                     SALARY DEPARTMENT_ID

----------- ------------------------- ---------- -------------

        201 Hartstein                      13000            20

为了避免这种情况，使用with check option语句创建视图

SQL> create or replace view empvu20 as

  2  select employee_id,last_name,salary,department_id from employees where department_id=20

  3  with check option constraint empvu20_ck;

View created.

再来进行修改就会报错

SQL> update empvu20 set department_id=10 where employee_id=201;

update empvu20 set department_id=10 where employee_id=201

       *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

（5）使用with read only语句

使用with check option语句将视图设置为只读，执行DML操作会报错

例子：创建一个只读的视图

SQL> create or replace view empvu10(employee_number,employee_name,job_title) as

  2  select employee_id,last_name,job_id from employees

  3  where department_id=10

  4  with read only;

View created.

执行删除操作报错

SQL> delete from empvu10;

delete from empvu10

            *

ERROR at line 1:

ORA-42399: cannot perform a DML operation on a read-only view

（6）删除视图

删除视图的语法：

                               
登录/注册后可看大图

删除视图不会删除基表的数据

SQL> drop view empvu80;

View dropped.

2、序列

• 自动产生唯一数字
• 是一个共享的对象
• 可以用于生成主键的值
• 替换程序代码
• 使用缓存提高效率
  

（1）创建序列

创建序列的语法：

                               
登录/注册后可看大图

例子：创建一个序列，增长10，从120开始，最大达到9999，没有cache，没有循环

SQL> create sequence dept_deptid_seq

  2  increment by 10

  3  start with 120

  4  maxvalue 9999

  5  nocache

  6  nocycle;

Sequence created.

（2）使用序列

使用序列需要使用nextval和currval伪列

• nextval返回下一个可用的序列值
• currval返回序列的当前值
• 在当前会话中，如果要使用currval，需要先使用nextval
  

可以在以下情况使用nextval和currval

• 在select statement的select list中（不能在子查询的select list）
• 在insert statement的子查询select list中
• 在insert statement的value语句中
• 在update statement的set语句中
  

不可以在以下情况使用nextval和currval

• 视图的select list
• 带有distinct关键字的select statement
• 带有group by、having、order by语句的select statement
• 在select、delete、update的子查询
• 在create table或者alter table语句的default expression中
  

例子：序列的使用

先使用currval报错

SQL> select dept_deptid_seq.currval from dual;

select dept_deptid_seq.currval from dual

                                    *

ERROR at line 1:

ORA-08002: sequence DEPT_DEPTID_SEQ.CURRVAL is not yet defined in this session

先使用nextval，获取下一个可用的序列值

SQL> select dept_deptid_seq.nextval from dual;

   NEXTVAL

----------

       120

再使用currval，取上一次nextval取的值

SQL> select dept_deptid_seq.currval from dual;

   CURRVAL

----------

       120

新开一个会话窗口，使用currval报错

SQL> conn hr/hr

SQL> select dept_deptid_seq.currval from dual;

select dept_deptid_seq.currval from dual

*

ERROR at line 1:

ORA-08002: sequence DEPT_DEPTID_SEQ.CURRVAL is not yet defined in this session

还是需要先使用nextval才能使用currval

SQL> select dept_deptid_seq.nextval from dual;

   NEXTVAL

----------

       130

SQL> select dept_deptid_seq.currval from dual;

   CURRVAL

----------

       130

再在此会话中执行两次nextval

SQL> select dept_deptid_seq.nextval from dual;

   NEXTVAL

----------

       140

SQL> select dept_deptid_seq.nextval from dual;

   NEXTVAL

----------

       150

SQL> select dept_deptid_seq.currval from dual;

   CURRVAL

----------

       150

而先前会话中的currval保持不变

SQL> select dept_deptid_seq.currval from dual;

   CURRVAL

----------

       120

在先前会话再次执行nextval，就应该是160了

SQL> select dept_deptid_seq.nextval from dual;

   NEXTVAL

----------

       160

例子：使用序列生成的值作为表的主键

SQL> select dept_deptid_seq.nextval from dual;

   NEXTVAL

----------

       270

SQL> insert into departments(department_id,department_name,location_id)

  2  values(dept_deptid_seq.nextval,'Support',2500);

1 row created.

SQL> select dept_deptid_seq.currval from dual;

   CURRVAL

----------

       280

（3）缓存序列值

为了提高访问速度，在创建序列的时候使用cache关键字指定缓存序列的个数，但是这有可能会导致序列出现gap，因为在系统崩溃后，缓存的序列都会被清除。

序列不能回滚，在实际当中，如果使用序列进行了插入，最后没有提交，而是回滚，那么中间这些序列值就没有了，也会出现了gap。

SQL> rollback;

Rollback complete.

SQL> select dept_deptid_seq.currval from dual;

   CURRVAL

----------

       280

如果一个序列多个表都在使用，对单个表来说，也会出现gap，所以在实际当中，我们一般为一个表创建一个序列。

（4）修改序列

可以修改序列的增长值，最大值，最小值，循环选项，缓存选项，但是不能修改起始值，如果不设置最小值，默认为1。

修改注意事项：

• 要有修改的权限
• 只影响后续的序列
• 修改起始值只能删除重建
  

例子：修改序列

SQL> alter sequence dept_deptid_seq

  2  increment by 20

  3  maxvalue 300

  4  nocache

  5  nocycle;

Sequence altered.

超过最大值报错

SQL> select dept_deptid_seq.currval from dual;

   CURRVAL

----------

       290

SQL> select dept_deptid_seq.nextval from dual;

select dept_deptid_seq.nextval from dual

                                    *

ERROR at line 1:

ORA-08004: sequence DEPT_DEPTID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be

instantiated

修改序列为可以循环

SQL> alter sequence dept_deptid_seq

  2  cycle;

Sequence altered.

超过最大值后从起始值开始，默认为1。

SQL> select dept_deptid_seq.nextval from dual;

   NEXTVAL

----------

         1

（5）删除序列

例子：删除序列

SQL> drop sequence dept_deptid_seq;

Sequence dropped

3、索引

• 是一个模式对象
• 用于提高查询的速度
• 快速定位数据，减少物理I/O
• 和表独立的，是针对某个字段来建索引，创建和删除索引对基表无影响，但是删除表后，表的索引也会被删除
• 由Oracle server自动维护
  

（1）创建索引

创建索引的2种方式

• 自动创建，当创建主键约束或者唯一约束的时候自动创建一个唯一索引
• 手工创建，创建非唯一索引提高访问速度
  

创建索引的语法：

                               
登录/注册后可看大图

例子：在人员表的last_name字段创建一个索引

SQL> create index emp_last_name_idx on employees(last_name);

Index created.

创建索引的注意事项：

                               
登录/注册后可看大图

例子：通过查看执行计划，看是否使用了索引

SQL> explain plan for

  2  select * from employees where last_name='Abel';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 350933353

--------------------------------------------------------------------------------

-----------------

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (

%CPU)| Time     |

--------------------------------------------------------------------------------

-----------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |     1 |    69 |     2

  (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |    69 |     2

  (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | EMP_LAST_NAME_IDX |     1 |       |     1

  (0)| 00:00:01 |

--------------------------------------------------------------------------------

-----------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("LAST_NAME"='Abel')

14 rows selected.

如果在查询的时候，使用了表达式，则不会用到索引

SQL> explain plan for

  2  select * from employees where last_name||''='Abel';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1445457117

-------------------------------------------------------------------------------

| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |           |     1 |    69 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    69 |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

   1 - filter("LAST_NAME"||''='Abel')

13 rows selected.

这是屏蔽索引的一种方法，创建索引后不想用，又不想删除，如果是数字类型，可以加一个0，如果是字符类型，拼接一个空，那么就可以屏蔽这个字段对索引的使用。

（3）监控索引的使用

创建索引后，可以通过monitoring usage监控索引的使用，然后通过v$object_usage视图查看在监控的这一段时间内，是否有用到该索引，如果没有用到，就可以将其删除，提高更新插入的性能。

监控索引

SQL> alter index emp_last_name_idx monitoring usage;

Index altered.

已经在监控，但是还没有使用

SQL> select index_name,monitoring,used from v$object_usage;

INDEX_NAME                     MON USE

------------------------------ --- ---

EMP_LAST_NAME_IDX              YES NO

查询一次

SQL> select last_name from employees where last_name='Abel';

LAST_NAME

-------------------------

Abel

已经使用索引了

SQL> select index_name,monitoring,used from v$object_usage;

INDEX_NAME                     MON USE

------------------------------ --- ---

EMP_LAST_NAME_IDX              YES YES

SQL> alter index emp_last_name_idx nomonitoring usage;

Index altered.

停止监控

SQL> select index_name,monitoring,used from v$object_usage;

INDEX_NAME                     MON USE

------------------------------ --- ---

EMP_LAST_NAME_IDX              NO  YES

（4）删除索引

索引不能修改，只能删除重建，删除索引需要是索引的所有者或者有DROP ANY INDEX权限。

删除索引的语法：

                               
登录/注册后可看大图

例子：删除索引

SQL> drop index emp_last_name_idx;

Index dropped.

4、同义词

同义词是数据库模式对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。在使用同义词时，Oracle数据库将它翻译成对应模式对象的名字。与视图类似，同义词并不占用实际存储空间，只有在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程、包等等，都可以根据实际情况为他们定义同义词。

同义词的分类

• 公用Oracle同义词：由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。
• 私有Oracle同义词：它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。
  

同义词作用

• 如果没有同义词，当操作其他用户的表时，必须通过user名.object名的形式，采用了同义词之后就可以不加所有者名称，还要注意用户是否有这个权限。
• 如果创建的表的名字很长，可以为这个表创建一个同义词来简化引用。
  

创建同义词的语法：

                               
登录/注册后可看大图

例子：创建及删除一个同义词

SQL> create synonym d_sum for dept_sum_vu;

Synonym created.

SQL> drop synonym d_sum;

Synonym dropped.

5、相关习题

（1） Evaluate the CREATE TABLE statement: CREATE TABLE products (product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, product_name VARCHAR2(15))? Which statement is true regarding the PROD_ID_PK constraint?

A.It would be created only if a unique index is manually created first.

B.It would be created and would use an automatically created unique index.

C.It would be created and would use an automatically created nonunique index.

D.It would be created and remains in a disabled state because no index is specified in the command.

答案：B

（2）Evaluate the following CREATE SEQUENCE statement:CREATE SEQUENCE seq1 START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE  The sequence SEQ1 has generated numbers up to the maximum limit of 200.You issue the following SQL statement: SELECT seq1.nextval FROM dual . What is displayed by the SELECT statement?

A.1
B.10
C.100
D.an error

答案：A

（3）View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. You need to  create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order.  Which CREATE VIEW statement would create the view successfully?

A.CREATE OR REPLACE VIEW ord_vu (order_id,order_date) AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) "NO OF ITEMS" FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date?

B.CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)"NO OF ITEMS"  FROM  orders o  JOIN order_items i  ON (o.order_id  =  i.order_id)  GROUP  BY o.order_id,o.order_date?

C.CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) FROM orders o JOIN order_items i ON (o.order_id = i.order_id) GROUP BY o.order_id,o.order_date?

D.CREATE OR REPLACE VIEW ord_vu AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id||' NO OF ITEMS'  FROM orders o  JOIN order_items i  ON (o.order_id  =  i.order_id)  GROUP  BY o.order_id,o.order_date WITH CHECK OPTION?

答案：B

（4）View the Exhibit and examine the structure of the ORDERS table: The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint. Evaluate the following statement: INSERT INTO (SELECT order_id,order_date,customer_id FROM ORDERS WHERE order_total = 1000 WITH CHECK OPTION) VALUES (13, SYSDATE, 101)? What would be the outcome of the above INSERT statement?

                               
登录/注册后可看大图

A.It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.

B.It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.

C.It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.

D.It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.

答案：C

（5）Which two statements are true regarding views  ?(Choose two.)

A.A simple view in which column aliases have been used cannot be updated.

B.A subquery used in a complex view definition cannot contain group functions or joins.

C.Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

D.Rows added through a view are deleted from the table automatically when the view is dropped.

E.The OR REPLACE option is used to change the definition of an existing view without dropping and re--creating it.

F.The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.

答案：CE

（6）Which statements are correct regarding indexes? (Choose all that apply.)

A.When a table is dropped, the corresponding indexes are automatically dropped.
B.For each DML operation performed, the corresponding indexes are automatically updated.
C.Indexes should be created on columns that are frequently referenced as part of an expression.
D.A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique
index.

答案：ABD

（7）View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO). Which two CREATE INDEX statements are valid? (Choose two.)

                               
登录/注册后可看大图

A.CREATE INDEX ord_idx ON ord(ord_no);

B.CREATE INDEX ord_idx ON ord_items(ord_no);

C.CREATE INDEX ord_idx ON ord_items(item_no);

D.CREATE INDEX ord_idx ON ord,ord_items(ord_no, ord_date,qty);

答案：BC

（8）Which two statements  are  true  about  sequences created in a single instance database? (Choose two.)

A.The numbers generated by a sequence can be used only for one table.

B.DELETE would remove a sequence from the database.

C.CURRVAL is used to refer to the last sequence number that has been generated.

D.When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.

E.When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted.

答案：ＣＤ

（9）The ORDERS table belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully? SELECT * FROM ord；

A.CREATE SYNONYM ord FOR orders；This command is issued by OE.
B.CREATE PUBLIC SYNONYM ord FOR orders； This command is issued by OE.
C.CREATE SYNONYM ord FOR oe.orders；This command is issued by the database administrator.
D.CREATE  PUBLIC  SYNONYM  ord  FOR oe.orders；This  command  is  issued  by  the  database administrator.

答案：D

（10）Which statement is true regarding synonyms?
A.Synonyms can be created for tables but not views.
B.Synonyms are used to reference only those tables that are owned by another user.
C.A public synonym and a private synonym can exist with the same name for the same table.
D.The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.

答案：C

（11）Evaluate  the  following  command:  CREATE  TABLE  employees  (employee_id  NUMBER(2) PRIMARY  KEY,  last_name  VARCHAR2(25)  NOT NULL, department_id  NUMBER(2),  job_id VARCHAR2(8), salary NUMBER(10,2))；You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization: CREATE OR REPLACE VIEW sales_staff_vu AS SELECT employee_id, last_name,job_id FROM employees WHERE job_id LIKE 'SA_%' WITH CHECK OPTION；Which statements are true regarding the above view?  (Choose all that apply.)

A.It allows you to insert details of all new staff into the EMPLOYEES table.
B.It allows you to delete the details of the existing sales staff from the EMPLOYEES table.
C.It allows you to update the job ids of the existing sales staff to any other job id in the EMPLOYEES table.
D.It allows you to insert the IDs, last names and job ids of the sales staff from the view if it is used in multitable INSERT statements.

答案：BD

（12）Evaluate  the  following  SQL  statement:  CREATE  INDEX  upper_name_idx  ON product_information(UPPER(product_name))；  Which query  would  use  the  UPPER_NAME_IDX index ？
A.SELECT UPPER(product_name) FROM product_information WHERE product_id = 2254；
B.SELECT UPPER(product_name) FROM product_information ；
C.SELECT product_id FROM product_information WHERE UPPER(product_name) IN ('LASERPRO', 'Cable') ；
D.SELECT  product_id,  UPPER(product_name)  FROM  product_information  WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000 ；

答案：C

（13）View the Exhibit and examine the structure of the ORD table. Evaluate the following SQL

statements that are executed in a user session in the specified order:

CREATE SEQUENCE ord_seq;

SELECT ord_seq.nextval

FROM dual;

INSERT INTO ord

VALUES (ord_seq.CURRVAL, '25-jan-2007',101);

UPDATE ord

SET ord_no= ord_seq.NEXTVAL

WHERE cust_id =101;

What would be the outcome of the above statements?

A. All the statements would execute successfully and the ORD_NO column would contain the value 2 for the CUST_ID 101.

B. The CREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.

C. The CREATE SEQUENCE command would not execute because the starting value of the sequence and the increment value have not been specified.

D. All the statements would execute successfully and the ORD_NO column would have the value 20 for the CUST_ID 101 because the default CACHE value is 20.

答案：A

（14）ORD is a private synonym for the OE.ORDERS table. The user OE issues the following command: DROP SYNONYM ord；Which statement is true regarding the above SQL statement？

A.Only the synonym would be dropped.
B.The synonym would be dropped and the corresponding table would become invalid.
C.The synonym would be dropped and the packages referring to the synonym would be dropped.
D.The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

答案：A

（15）View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables. In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key. Which view can have all the DML operations performed on it？

                               
登录/注册后可看大图

A.CREATE VIEW V1 AS SELECT order_id, product_id FROM order_items；  
B.CREATE VIEW V4(or_no, or_date, cust_id) AS SELECT order_id, order_date, customer_id FROM orders WHERE order_date < '30-mar-2007' WITH CHECK OPTION  ；
C.CREATE VIEW V3 AS SELECT o.order_id, o.customer_id, i.product_id FROM orders o, order_items i WHERE o.order_id=i.order_id；
D.CREATE VIEW V2 AS SELECT order_id, line_item_id, unit_price*quantity total FROM order_items  ；

答案：B

（16）ORD is a private synonym for the OE.ORDERS table. The user OE issues the following command: DROP SYNONYM ord；Which statement is true regarding the above SQL statement？

A.Only the synonym would be dropped.
B.The synonym would be dropped and the corresponding table would become invalid.
C.The synonym would be dropped and the packages referring to the synonym would be dropped.
D.The synonym would be dropped and any PUBLIC synonym with the same name becomes invalid.

答案：A

（17）Which two statements are true regarding views? (Choose two.)

A. A simple view in which column aliases have been used cannot be updated.

B. A subquery used in a complex view definition cannot contain group functions or joins.

C. Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

D. Rows added through a view are deleted from the table automatically when the view is dropped.

E. The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.

F. The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view

答案：CE

bikong123

详细，用心！赞！！！
学SQL者，多多阅读，并提出自己的建议！