山东大学《数据库系统》上机实验答案详细整理2013最新版

1、数据库实验（一）熟悉环境、建立/删除表、插入数据Drop table 表名update dbtest set test=1select * from dbscore1 教师信息(教师编号、姓名、性别、年龄、院系名称)test1_teacher：tid char 6 not null、name varchar 10 not null、sex char 2、age int、dname varchar 10。根据教师名称建立一个索引。教师编号教师姓名性别年龄院系名称100101100102100103张老师李老师马老师男女男444546计算机学院软件学院计算机学院1、create table tes

2、t1_teacher( tid char(6) primary key, name varchar(10) not null, sex char(2), age int, dname varchar(10) ) 2 学生信息（学生编号、姓名、性别、年龄、出生日期、院系名称、班级）test1_student：sid char 12 not null、name varchar 10 not null、sex char 2、age int、birthday date（oracle的date类型是包含时间信息的，时间信息全部为零）、dname varchar 10、class varchar(10)。

3、根据姓名建立一个索引。学号姓名性别年龄出生日期院系名称班级200800020101200800020102200800020103王欣李华赵岩女女男1994-2-21995-3-31996-4-4计算机学院软件学院软件学院2010200920092、create table test1_student( sid char(12) primary key, name varchar(10) not null, sex char(2), age int, birthday date, dname varchar(10), class varchar(10) ) 3 课程信息（课程编号、课程名称、先

4、行课编号、学分）test1_course：cid char 6 not null、name varchar 10 not null、fcid char 6、credit numeric 2，1（其中2代表总长度，1代表小数点后面长度）。根据课程名建立一个索引。课程号课程名先行课程号学分300001300002300003数据结构数据库操作系统30000130000122.543、create table test1_course( cid char(6) primary key, name varchar(10) not null, fcid char(6), credit numeric(2

5、,1) ) 4 学生选课信息（学号、课程号、成绩、教师编号）test1_student_course：sid char 12 not null、cid char 6 not null、score numeric 5，1（其中5代表总长度，1代表小数点后面长度）、tid char 6。学号课程号成绩教师编号20080002010120080002010120080002010130000130000230000391.592.693.71001011001021001034、 create table test1_student_course( sid char(12) , cid char(6

6、) , score numeric(5,1), tid char(6), primary key(sid,cid), FOREIGN KEY (sid) REFERENCES test1_student(sid), FOREIGN KEY (cid) REFERENCES test1_course(cid), FOREIGN KEY (tid) REFERENCES test1_teacher(tid) ) 5 教师授课信息（教师编号、课程编号）test1_teacher_course：tid char 6 not null，cid char 6 not null。教师编号课程号1001011

7、001021001033000013000023000035、create table test1_teacher_course( tid char(6) , cid char(6) , primary key(tid,cid), FOREIGN KEY (tid) REFERENCES test1_teacher(tid), FOREIGN KEY (cid) REFERENCES test1_course(cid) ) 二、创建索引 1、create index index_table1 on test1_teacher(name); 2、create index index_table2

8、 on test1_student(name); 3、create index index_table3 on test1_course(name);三、插入数据1、insert into test1_teacher values(100101,张老师,男,44,计算机学院);insert into test1_teacher values(100102,李老师,女,45,软件学院);insert into test1_teacher values(100103,马老师,男,46,计算机学院);2、insert into test1_student values(200800020101,王欣

9、,女,19,to_date(19940202,yyyymmdd),计算机学院,2010);insert into test1_student values(200800020102,李华,女,20,to_date(19950303,yyyymmdd),软件学院,2009);insert into test1_student values(200800020103,赵岩,男,18,to_date(19960404,yyyymmdd),软件学院,2009);3、insert into test1_course values(300001,数据结构,2);insert into test1_cour

10、se values(300002,数据库,300001,2.5);insert into test1_course values(300003,操作系统,300001,4);4、Insert into test1_student_course values(200800020101,300001,91.5,100101);insert into test1_student_course values(200800020101,300002,92.6,100102);insert into test1_student_course values(200800020101,300003,93.7,

11、100103);5、insert into test1_teacher_course values(100101,300001);insert into test1_teacher_course values(100102,300002);insert into test1_teacher_course values(100103,300003);数据库实验（二） 检索查询1、找出没有选修任何课程的学生的学号、姓名。create table test2_01 as select sid ，namefrom pub.studentwhere sid not in(select sidfrom p

12、ub.student_course)2、找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学号、姓名。create table test2_02 as select distinct student.sid，namefrom pub.student, pub.student_coursewhere student_course.sid = student.sid and student_course.cid in(select cidfrom pub.student_coursewhere sid=200900130417)3、找出至少选修了一门其先行课程号为“300

13、002”号课程的学生的学号、姓名。create table test2_03 as select distinct student.sid，namefrom pub.student, pub.student_coursewhere student_course.sid = student.sid and student_course.cid in(select cidfrom pub.coursewhere fcid=300002)4、找出选修了“操作系统”并且也选修了“数据结构”的学生的学号、姓名。create table test2_04 as select sid,namefrom pu

14、b.studentwhere sid in(select sidfrom pub.student_course,pub.coursewhere student_course.cid=course.cidand name =操作系统)and sid in(select sidfrom pub.student_course,pub.coursewhere student_course.cid=course.cidand name =数据结构)5、 查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)crea

15、te table test2_05 as select student.sid,name，cast(avg(score) as numeric(5,0) avg_score,sum（score） sum_scorefrom pub.student,pub.student_coursewhere student.sid = student_course.sid and age =20group by student.sid，name6、查询所有课以及这门课的最高成绩，test2_06有两个列：课程号cid、最高成绩max_scorecreate table test2_06 as select

16、sid，max(score） max_scorefrom pub.student_coursegroup by cid7、 查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名namecreate table test2_07 as select sid，namefrom pub.studentwhere name not in（select namefrom pub.studentwhere name like 张% or name like 李% or name like 王%)8、查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08有两个列：second_name、p_co

17、untcreate table test2_08 as select substr(name,1,1) second_name,count(*) p_countfrom pub.studentgroup by substr(name,1,1)9、查询选修了300003号课程的学生的sid、name、scorecreate table test2_09 as select student.sid，，scorefrom pub.student,pub.student_coursewhere student.sid = student_course.sidand cid =3

18、0000310、查所有有成绩记录的学生sid和cidcreate table test2_10 as select sid，cidfrom pub.student_coursewhere score is not null数据库实验（三） 复制表、删除数据1.将pub用户下的Student_31及数据复制到主用户的表test3_01，删除表中的学号不是12位数字的错误数据。create table test3_01 as select * from pub.Student_31delete from test3_01 where length(translate(sid,0123456789,

19、)02.将pub用户下的Student_31及数据复制到主用户的表test3_02，删除表中的出生日期和年龄不一致 (年龄=2012-出生年份) 的 错误数据。create table test3_02 as select * from pub.Student_31delete from test3_02 where age 2012 - extract(year from birthday)3.将pub用户下的Student_31及数据复制到主用户的表test3_03，删除表中的性别有错误的数据（性别只能够是“男”、“女”或者空值）。create table test3_03 as sele

20、ct * from pub.Student_31delete from test3_03 where sex not in(select sex from test3_03 where sex=男 or sex=女 or sex=null)4.将pub用户下的Student_31及数据复制到主用户的表test3_04，删除表中的院系名称有空格的、院系名称为空值的或者院系名称小于3个字的错误数据。create table test3_04 as select * from pub.student_31delete from test3_04 where dname is null or leng

21、th(dname)46.将pub用户下的Student_31及数据复制到主用户的表test3_06，删除表中的错误数据，不规范的数据也被认为是错误的数据。create table test3_06 as select * from pub.student_31delete from test3_06 where length(translate(sid,/0123456789,/)2012-extract(year from birthday) or age2012-extract(year from birthday)delete from test3_06 where name is nu

22、ll or length(name)2 or name like % %delete from test3_06 where sex not in (select sex from test3_06 where sex =男 or sex=女 or sex=null)delete from test3_06 where dname is null or length(dname)47.将pub用户下的Student_course_32及数据复制到主用户的表test3_07，删除其中的错误数据，错误指如下情况：学号在学生信息pub.student中不存在的；create table test3_

23、07 as select * from pub.Student_course_32delete from test3_07 where sid not in (select sid from pub.student)8.将pub用户下的Student_course_32及数据复制到主用户的表test3_08，删除其中的错误数据，错误指如下情况：课程号和教师编号在教师授课表pub.teacher_course中不同时存在的，即没有该教师教该课程；create table test3_08 as select * from pub.student_course_32delete from test

24、3_08 where (cid,tid) not in(select test3_08.cid,test3_08.tid from test3_08,pub.teacher_course where test3_08.cid=pub.teacher_course.cid and test3_08.tid=pub.teacher_course.tid)9.将pub用户下的Student_course_32及数据复制到主用户的表test3_09，删除其中的错误数据，错误指如下情况：成绩数据有错误（需要先找到成绩里面的错误）。create table test3_09 as select * fro

25、m pub.student_course_32delete from test3_09 where score10010.将pub用户下的Student_course_32及数据复制到主用户的表test3_10，删除其中的错误数据。create table test3_10 as select * from pub.student_course_32delete from test3_10 where score100delete from test3_10 where sid not in (select sid from pub.student)delete from test3_10 w

26、here cid not in (select cid from pub.course)delete from test3_10 where tid not in (select tid from pub.teacher)delete from test3_10 where (cid,tid) not in(select test3_10.cid,test3_10.tid from test3_10,pub.teacher_course where test3_10.cid=pub.teacher_course.cid and test3_10.tid=pub.teacher_course.t

27、id)Test4 复制表、修改表结构、修改数据1、 将pub用户下表student_41及数据复制到主用户的表test4_01中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。使用update语句,利用pub.student_course、pub.course，统计 “总成绩”；create table test4_01 as select* from pub.student_41alter table test4_01 add

28、sum_score intalter table test4_01 add avg_score numeric(5,1)alter table test4_01 add sum_credit intalter table test4_01 add did varchar(2) select *from test4_01create table test01 as select sid,sum(score) sum_score from pub.student_coursegroup by sidupdate test4_01set sum_score=(select test01.sum_sc

29、ore from test01 where test01.sid=test4_01.sid)2、 将pub用户下表student_41及数据复制到主用户的表test4_02中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。利用pub.student_course、pub.course，统计“平均成绩”；create table test4_02 as select* from pub.student_41alter table t

30、est4_02 add sum_score intalter table test4_02 add avg_score numeric(5,1)alter table test4_02 add sum_credit intalter table test4_02 add did varchar(2)select *from test4_02create table test02 as select sid,avg(score) avg_score from pub.student_course group by sidupdate test4_02set avg_score=(select t

31、est02.avg_score from test02 where test02.sid=test4_02.sid)3、 将pub用户下表student_41及数据复制到主用户的表test4_03中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。使用update语句,利用pub.student_course、pub.course，统计 “总学分”；drop table test4_03create table test4_03 a

32、s select* from pub.student_41alter table test4_03 add sum_score intalter table test4_03 add avg_score numeric(5,1)alter table test4_03 add sum_credit intalter table test4_03 add did varchar(2)select *from pub.coursedrop table test03create table test031 as select sid,cid,score from pub.student_course

33、alter table test031 add credit intupdate test031set credit=(select credit from pub.course where test031.cid=pub.course.cid and score=60) update test031set credit=0where score60create table test03 as select sid,sum(credit) sum_credit from test031 group by sidupdate test4_03set sum_credit=(select test

34、03.sum_credit from test03 where test03.sid=test4_03.sid)4、 将pub用户下表student_41及数据复制到主用户的表test4_04中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。根据院系名称到pub.department或者pub.department_41中，找到对应编号，填写到院系编号中，如果都没有对应的院系，则填写为00。drop table test4_04d

35、rop table test04create table test4_04 as select* from pub.student_41alter table test4_04 add sum_score intalter table test4_04 add avg_score numeric(5,1)alter table test4_04 add sum_credit intalter table test4_04 add did varchar(2)select *from pub.departmentcreate table test04 as select* from pub.de

36、partmentinsert into test04 select*from pub.department_41update test4_04set did=(select test04.did from test04 where test4_04.dname=test04.dname)where dname in(select dname from test04)update test4_04set did=00where dname not in(select dname from test04) or dname is nullupdate dbtest set test=4select

37、 * from dbscore5、 将pub用户下表student_41及数据复制到主用户的表test4_05中，使用alter table语句为表增加五个列：“总成绩:sum_score”、 “平均成绩:avg_score”（四舍五入到个位）、“总学分:sum_credit”、“院系编号:did varchar(2) ”。（1） 利用pub.student_course、pub.course，统计 “总成绩”；（2） 利用pub.student_course、pub.course，统计“平均成绩”；（3） 利用pub.student_course、pub.course，统计 “总学分”；（4

38、） 根据院系名称到pub.department或者pub.department_41中，找到对应编号，填写到院系编号中，如果都没有对应的院系，则填写为00。 create table test4_05 as select* from pub.student_41alter table test4_05 add sum_score intalter table test4_05 add avg_score numeric(5,1)alter table test4_05 add sum_credit intalter table test4_05 add did varchar(2)update

39、 test4_05set sum_score=(select test4_01.sum_score from test4_01 where test4_01.sid=test4_05.sid)update test4_05set avg_score=(select test4_02.avg_score from test4_02 where test4_02.sid=test4_05.sid)update test4_05set sum_credit=(select test4_03.sum_credit from test4_03 where test4_03.sid=test4_05.si

40、d)update test4_05set did=(select test04.did from test04 where test04.dname=test4_05.dname)where dname in (select dname from test04) update test4_05set did=00where dname not in (select dname from test04) or dname is nullupdate dbtest set test=4select * from dbscore6、 将pub用户下的Student_42及数据复制到主用户的表test4_06中,对表中的数据进行整理，修复那些不规范的数据：剔除姓名列中的所有空格；select *from pub.student_42drop table test4_06create table test4_06 as select* from pub.student_42 update test4_06 set name=replace(name, ,)7、 将pub用户下的Student_42及数据复制到主用户