Oracle作业

1.根据表格创建数据表

学生表（School_Students）

--创建学生表school_studentscreate table school_Students(stu_id nvarchar2(20) not null,stu_name nvarchar2(20) not null,stu_class_id nvarchar2(20) not null,constraint pk_stu_id primary key(stu_id));

班级表(school_class)

--创建班级表(school_class)create table school_class(class_id nvarchar2(20) not null,class_name nvarchar2(20) not null,constraint pk_class_id primary key(class_id));

2.添加数据

--往班级表(school_class)插入数据insert into school_classvalues (\'20163001\',\'16播音与主持艺术1班\');insert into school_classvalues (\'20163002\',\'16播音与主持艺术2班\');insert into school_classvalues (\'20163003\',\'16传播学\');insert into school_classvalues (\'20163004\',\'16地理科学\');

--往学生表(school_students)添加数据insert into school_students values (\'201632218002\',\'曹浒琳\',\'20163001\');insert into school_students values (\'201632218003\',\'曹睿\',\'20163001\');insert into school_students values (\'201632218004\',\'陈浩\',\'20163001\');insert into school_students values (\'201632218028\',\'李松鸿\',\'20163002\');insert into school_students values (\'201632218029\',\'李唯郡\',\'20163002\');insert into school_students values (\'201632218030\',\'李文博\',\'20163002\');insert into school_students values (\'201632218031\',\'李昕原\',\'20163003\');insert into school_students values (\'201632218041\',\'潘睿\',\'20163003\');insert into school_students values (\'201632218042\',\'戚婉秋\',\'20163003\');insert into school_students values (\'201632218051\',\'王进凤\',\'20163004\');insert into school_students values (\'201632218052\',\'王连超\',\'20163004\');insert into school_students values (\'201632218054\',\'王祎\',\'20163004\');

3.给学生表添加性别（stu_sex），默认值为男

alter table school_studentsadd stu_sex nvarchar2(2) default \'男\';

4.给学生表添加已修学分（stu_credits） 默认为0分

alter table school_studentsadd stu_credits number(2) default 0;

5.给班级表修改班级编号字段的默认值，默认值为“2016”

alter table school_classmodify class_id default \'2016\';

6.修改学号“201632218002”的同学，性别为女，学分为12，

update school_studentsset stu_sex=\'女\',stu_credits=12where stu_id=\'201632218002\';

7.修改学号“201632218042”的同学，性别女 ，学分为15，

update school_studentsset stu_sex=\'女\',stu_credits=15where stu_id=\'201632218042\';

8.修改学号“201632218051”的同学，性别女 ，学分为13，

update school_studentsset stu_sex=\'女\',stu_credits=13where stu_id=\'201632218051\';

9.修改班级编号为“20163003”的同学，学分为16，

update school_studentsset stu_credits=16where stu_class_id=\'20163003\';

10.复制表school_students的结构和数据到表school_students2中

create table school_students2as select * from school_students;

11.删除班级编号为“20163004”的School_Students2表中的数据

delete from school_students2where stu_class_id=\'20163004\';

12.创建表School_Students3

--创建表School_Students3create table school_students3(stu_id nvarchar2(20) not null,stu_name nvarchar2(20) not null,stu_sex nvarchar2(20) default \'男\',stu_credits number(2) default 12,stu_class_id nvarchar2(20） not null,constraint pk_stu_id3 primary key(stu_id));

13.复制School_Students2到School_Students3（注意顺序）

insert into school_students3(stu_id,stu_name,stu_sex,stu_credits,stu_class_id)selectstu_id,stu_name,stu_sex,stu_credits,stu_class_idfrom school_students2;

14.把School_Students的数据合并到School_Students3（注意顺序,school_students3已经存在的数据不合并）

merge into school_students3using school_students on(school_students3.stu_id=school_students.stu_id)when not matched theninsert(stu_id,stu_name,stu_sex,stu_credits,stu_class_id)values(school_students.stu_id,school_students.stu_name,school_students.stu_sex,school_students.stu_credits,school_students.stu_class_id);

15.修改School_Students表中的数据，把班级编号(stu_class_id)为“20163002”的同学，性别修改为“女”，学分修改为20

update school_studentsset stu_sex=\'女\',stu_credits=20where stu_class_id=20163002;

16.把School_Students表中的数据更新到School_Students3

merge into school_students3using school_studentson(school_students3.stu_id=school_students.stu_id)when matched thenupdate setschool_students3.stu_name=school_students.stu_name,school_students3.stu_sex=school_students.stu_sex,school_students3.stu_credits=school_students.stu_credits,school_students3.stu_class_id=school_students.stu_class_idwhen not matched theninsert(stu_id,stu_name,stu_sex,stu_credits,stu_class_id)values(school_students.stu_id,school_students.stu_name,school_students.stu_sex,school_students.stu_credits,school_students.stu_class_id);

17.给School_Students表创建学生姓名索引(index_test)，事务初始条目为2，最大并发数200

create index index_test   //创建索引index_teston school_students(stu_name)initrans 2    //设置事务初始条目为2maxtrans 200;  //设置最大并发数为200

18.修改索引名为“index_School_Students_stu_name”

//修改索引名alter index index_testrename to index_school_students_stu_name;

19.给School_Students表创建学生性别索引(index_sex)，事务初始条目为2，最大并发数200

create index index_sexon school_students(stu_sex)initrans 2maxtrans 200;

20.删除性别索引

drop index index_sex;