Oracle作业

1.根据表格创建数据表

学生表(School_Students)

列名 数据类型 长度 是否允许为空 说明
stu_id nvarchar2 20 账号、主键约束
stu_name nvarchar2 20 姓名
stu_class_id nvarchar2 20 班级编号
--创建学生表school_students
create 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)

列名 数据类型 长度 是否允许为空 说明
class_id nvarchar2 20 班级编号、主键约束
class_name nvarchar2 20 班级名称
--创建班级表(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.添加数据

班级编号 班级名称
20163001 16播音与主持艺术1班
20163002 16播音与主持艺术2班
20163003 16传播学
20163004 16地理科学
--往班级表(school_class)插入数据
insert into school_class
values ('20163001','16播音与主持艺术1班');
insert into school_class
values ('20163002','16播音与主持艺术2班');
insert into school_class
values ('20163003','16传播学');
insert into school_class
values ('20163004','16地理科学');
学号 姓名 班级
201632218002 曹浒琳 20163001
201632218003 曹睿 20163001
201632218004 陈浩 20163001
201632218028 李松鸿 20163002
201632218029 李唯郡 20163002
201632218030 李文博 20163002
201632218031 李昕原 20163003
201632218041 潘睿 20163003
201632218042 戚婉秋 20163003
201632218051 王进凤 20163004
21632218052 王连超 20163004
201632218054 王祎 20163004
--往学生表(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_students
add stu_sex nvarchar2(2) default '男';

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

alter table school_students
add stu_credits number(2) default 0;

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

alter table school_class
modify class_id default '2016';

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

update school_students
set stu_sex='女',stu_credits=12
where stu_id='201632218002';

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

update school_students
set stu_sex='女',stu_credits=15
where stu_id='201632218042';

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

update school_students
set stu_sex='女',stu_credits=13
where stu_id='201632218051';

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

update school_students
set stu_credits=16
where stu_class_id='20163003';

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

create table school_students2
as select * from school_students;

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

delete from school_students2
where stu_class_id='20163004';

12.创建表School_Students3

列名 数据类型 长度 是否允许为空 说明
stu_id nvarchar2 20 账号、主键约束
stu_name nvarchar2 20 姓名
stu_sex nvarchar2 20 默认值男
stu_credits number 2 默认值12
stu_class_id nvarchar2 20 班级编号
--创建表School_Students3
create 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(20not 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)
select
stu_id,stu_name,stu_sex,stu_credits,stu_class_id
from school_students2;

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

merge into school_students3
using school_students on(school_students3.stu_id=school_students.stu_id) 
when not matched then
insert
(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_students
set stu_sex='女',stu_credits=20
where stu_class_id=20163002;

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

merge into school_students3
using school_students 
on(school_students3.stu_id=school_students.stu_id)
when matched then
update set 
school_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_id
when not matched then
insert
(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_test
on school_students(stu_name)   
initrans 2    //设置事务初始条目为2
maxtrans 200;  //设置最大并发数为200

18.修改索引名为“index_School_Students_stu_name”

//修改索引名
alter index index_test
rename to index_school_students_stu_name;

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

create index index_sex
on school_students(stu_sex)
initrans 2
maxtrans 200;

20.删除性别索引

drop index index_sex;
发布了6 篇原创文章 · 获赞 1 · 访问量 1579
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览