oracle课后习题答案

练习6

2.实训题

(2)

Create table exer_class(

CNO number(2) primary key,

CNAME varchar2(20),

NUM number(3)

);

Create table exer_student(

SNO number(4) primary key,

SNAME varchar2(10) unique,

SAGE number,

SEX char(2),

CNO number(2)

);

(3)

Alter table exer_student add constraint ck_sage check (sage>0 and sage<=100);

(4)

Alter table exer_student add constraint ck_stu check

(sex='M' or sex='F') modify sex default 'M';

(5)

Create unique index ind_cname on exer_class(cname);

(6)

Create view stu_class_view (e_sno,e_sname,e_cno,e_cname) AS

select sno,sname,cno,cname

From exer_student;

(7)

Create sequence exer_student_seq

start with 100000001

nocycle

nocache;

(8)

Create table exer_student_range

(

sno number(4) primary key,

sname varchar2(10),sage number,

sex char(2),cno number(2)

)

partition by range(sage)

(

partition part1 values less than(20) tablespace example,

partition part2 values less than(30) tablespace orcltbs1,

partition part3 values less than(maxvalue) tablespace orcltbs2

);

(9)

Create table exer_student_list

(

sno number(4) primary key,

sname varchar2(10),

sage number,

sex char(2),

cno number(2)

)

partition by list(sex)

(

partition man values('M') tablespace orcltbs1,

partition woman values('F') tablespace orcltbs2

);

(10)题目修改为“为exer_student_range表的SAGE列上创建本地分区索引。”Create index exer_student_range_local ON exer_student_range(sage) Local;

练习7

P106 实训题

(1)略参见教材P97,98两种方法。

(2)(分两步：建表、插入数据)

以system用户登录某数据库，创建一个用户bs，并给该用户授权：

SQL>create user bs identified by bs default tablespace users;

SQL>grant resource,connect,create view to bs;

然后再创建三个表：

Create table book(

no number(10) primary key,

title varchar2(40) not null,

author varchar2(16) not null,

publish varchar2(20),

pub_date date,

price number(4)

)

tablespace users;

Create table reader(

rno number(10) primary key,

rname varchar2(10) not null

)

tablespace users;

Create table borrow(

no number(10) primary key,

rno number(10) references reader(rno),

borrow_date date

)

tablespace users;

book表：

Insert into book values (100001,'Oracle 9i 数据库系统管理','李代平','冶金工业出版社',to_date('2003-01-01','yyyy-mm-dd'),38);

... ...

Insert into book values (100006,'Oracle 8 实用教程','翁正科等','电子工业出版社',to_date('2003-07-08','yyyy-mm-dd'),38);

reader表：

Insert into reader values (200001,'张三');

... ...

Insert into reader values (200005,'刘英');

borrow表：

Insert into borrow values (100001,200001,to_date('2004-08-10..10:06:14','yyyy-mm-dd..hh24:mi:ss'));

... ...

Insert into borrow values (100005,200005,to_date('2004-08-10..10:06:58','yyyy-mm-dd..hh24:mi:ss'));

(3)Insert into book values(100007,'Java网络编程','李程等','电子工业出版社',to_date('2000-08-01','yyyy-mm-dd'),35)；