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

数据库实验（一）

熟悉环境、建立/删除表、插入数据

Drop table 表名

update dbtest set test=1 select * from dbscore

1． 教师信息(教师编号、姓名、性别、年龄、院系名称)

test1_teacher：tid char 6 not null、name varchar 10 not null、sex char 2、age int、dname varchar 10。 根据教师名称建立一个索引。

教师编号 100101 100102 100103 教师姓名 张老师 李老师 马老师 性别 男 女 男 年龄 44 45 46 院系名称 计算机学院 软件学院 计算机学院

1、create table test1_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)。 根据姓名建立一个索引。 学号 200800020101 200800020102 200800020103 姓名 王欣 李华 赵岩 性别 女 女 男 年龄 １９ ２０ １８ 出生日期 院系名称 班级 2010 2009 2009 1994-2-2 计算机学院 1995-3-3 1996-4-4 软件学院 软件学院 2、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． 课程信息（课程编号、课程名称、先行课编号、学分）

test1_course：cid char 6 not null、name varchar 10 not null、fcid char 6、 credit numeric 2，1（其中2代表总长度，1代表小数点后面长度）。 根据课程名建立一个索引。

课程号 300001 300002 300003 课程名 数据结构 数据库 操作系统 先行课程号 300001 300001 学分 2 2.5 4

3、create table test1_course(

cid char(6) primary key, name varchar(10) not null, fcid char(6),

credit numeric(2,1) )

4． 学生选课信息（学号、课程号、成绩、教师编号）

test1_student_course：sid char 12 not null、cid char 6 not null、 score numeric 5，1（其中5代表总长度，1代表小数点后面长度）、tid char 6。

学号 200800020101 200800020101 200800020101

课程号 300001 300002 300003 成绩 91.5 92.6 93.7 教师编号 100101 100102 100103 4、 create table test1_student_course( sid char(12) , cid char(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。

教师编号 100101 100102 100103 课程号 300001 300002 300003

5、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 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','王欣','女

',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_course 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,'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 ，name from pub.student where sid not in (select sid

from pub.student_course)

2、找出至少选修了学号为“200900130417”的学生所选修的一门课的学生的学

号、姓名。

create table test2_02 as select distinct student.sid，name from pub.student, pub.student_course

where student_course.sid = student.sid and student_course.cid in (select cid

from pub.student_course where sid='200900130417') 3、找出至少选修了一门其先行课程号为“300002”号课程的学生的学号、姓名。 create table test2_03 as select distinct student.sid，name from pub.student, pub.student_course

where student_course.sid = student.sid and student_course.cid in (select cid

from pub.course

where fcid='300002')

4、找出选修了“操作系统”并且也选修了“数据结构”的学生的学号、姓名。 create table test2_04 as select sid,name from pub.student where sid in (select sid

from pub.student_course,pub.course where student_course.cid=course.cid and name ='操作系统') and sid in (select sid

from pub.student_course,pub.course where student_course.cid=course.cid and name ='数据结构')

5、查询20岁的所有有选课的学生的学号、姓名、平均成绩(avg_score，此为列名，下同)（平均成绩四舍五入到个位）、总成绩(sum_score)

create table test2_05 as select student.sid,name，cast(avg(score) as numeric(5,0)) avg_score,sum（score） sum_score from pub.student,pub.student_course

where student.sid = student_course.sidand age ='20' group by student.sid，name

6、查询所有课以及这门课的最高成绩，test2_06有两个列：课程号cid、最高

成绩max_score

create table test2_06 as select sid，max(score） max_score from pub.student_course group by cid

7、查询所有不姓张、不姓李、也不姓王的学生的学号sid、姓名name create table test2_07 as select sid，name from pub.student where name not in （select name from pub.student

where name like '张%' or name like '李%' or name like '王%')

8、查询学生表中每一个姓氏及其人数（不考虑复姓），test2_08有两个列：second_name、p_count

create table test2_08 as select substr(name,1,1) second_name,count(*) p_count

from pub.student

group by substr(name,1,1)

9、查询选修了300003号课程的学生的sid、name、score

create table test2_09 as select student.sid，student.name，score from pub.student,pub.student_course where student.sid = student_course.sid and cid ='300003'

10、查所有有成绩记录的学生sid和cid create table test2_10 as select sid，cid from pub.student_course where score is not null

数据库实验（三） 复制表、删除数据

1.将pub用户下的Student_31及数据复制到主用户的表test3_01，删除表中的学号不是12位数字的错误数据。

create table test3_01 as select * from pub.Student_31

delete from test3_01 where length(translate(sid,'\\0123456789','\\'))>0

2.将pub用户下的Student_31及数据复制到主用户的表test3_02，删除表中的出生日期和年龄不一致 (年龄=2012-出生年份) 的 错误数据。

create table test3_02 as select * from pub.Student_31

delete from test3_02 where age < 2012 - extract(year from birthday) delete from test3_02 where age > 2012 - extract(year from birthday)

3.将pub用户下的Student_31及数据复制到主用户的表test3_03，删除表中的性别有错误的数据（性别只能够是“男”、“女”或者空值）。

create table test3_03 as select * from pub.Student_31 delete 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_31

delete from test3_04 where dname is null or length(dname)<3 or dname like '% %'

5.将pub用户下的Student_31及数据复制到主用户的表test3_05，删除表中的班级不规范的数据，不规范是指和大多数不一致。

create table test3_05 as select * from pub.student_31 delete from test3_05 where length(class)>4

6.将pub用户下的Student_31及数据复制到主用户的表test3_06，删除表中的错误数据，不规范的数据也被认为是错误的数据。

create table test3_06 as select * from pub.student_31

delete from test3_06 where length(translate(sid,'/0123456789','/'))<12

delete from test3_06 where age>2012-extract(year from birthday) or age<2012-extract(year from birthday)

delete from test3_06 where name is null 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)<3 or name like

'% %'

delete from test3_06 where length(class)>4

7.将pub用户下的Student_course_32及数据复制到主用户的表test3_07，删除其中的错误数据，错误指如下情况：学号在学生信息pub.student中不存在的；

create table test3_07 as select * from pub.Student_course_32

delete 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_32

delete from test3_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 * from pub.student_course_32

delete from test3_09 where score<0 or score>100

10.将pub用户下的Student_course_32及数据复制到主用户的表test3_10，删除其中的错误数据。

create table test3_10 as select * from pub.student_course_32

delete from test3_10 where score<0 or score>100

delete from test3_10 where sid not in (select sid from pub.student)

delete from test3_10 where 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 test3_10.tid=pub.teacher_course.tid)

and

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_41 alter table test4_01 add sum_score int

alter table test4_01 add avg_score numeric(5,1) alter table test4_01 add sum_credit int alter table test4_01 add did varchar(2) select *from test4_01

create table test01 as select sid,sum(score) sum_score from pub.student_course group by sid update test4_01

set sum_score=(select test01.sum_score 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_41 alter table test4_02 add sum_score int

alter table test4_02 add avg_score numeric(5,1) alter table test4_02 add sum_credit int alter table test4_02 add did varchar(2) select *from test4_02

create table test02 as select sid,avg(score) avg_score from pub.student_course group by sid update test4_02

set avg_score=(select test02.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_03

create table test4_03 as select* from pub.student_41 alter table test4_03 add sum_score int

alter table test4_03 add avg_score numeric(5,1) alter table test4_03 add sum_credit int alter table test4_03 add did varchar(2) select *from pub.course drop table test03

create table test031 as select sid,cid,score from pub.student_course alter table test031 add credit int

update test031

set credit=(select credit from pub.course

where test031.cid=pub.course.cid and score>=60) update test031 set credit=0 where score<60

create table test03 as select sid,sum(credit) sum_credit from test031 group by sid update test4_03

set sum_credit=(select test03.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_04 drop table test04

create table test4_04 as select* from pub.student_41 alter table test4_04 add sum_score int

alter table test4_04 add avg_score numeric(5,1) alter table test4_04 add sum_credit int alter table test4_04 add did varchar(2) select *from pub.department

create table test04 as select* from pub.department

insert into test04 select*from pub.department_41 update test4_04

set did=(select test04.did from test04

where test4_04.dname=test04.dname) where dname in(select dname from test04) update test4_04 set did='00'

where dname not in(select dname from test04) or dname is null

update dbtest set test=4 select * from dbscore

5、将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） 根据院系名称到pub.department或者pub.department_41中，找到

对应编号，填写到院系编号中，如果都没有对应的院系，则填写为00。

create table test4_05 as select* from pub.student_41

alter table test4_05 add sum_score int

alter table test4_05 add avg_score numeric(5,1) alter table test4_05 add sum_credit int alter table test4_05 add did varchar(2) update test4_05

set sum_score=(select test4_01.sum_score from test4_01

where test4_01.sid=test4_05.sid) update test4_05

set avg_score=(select test4_02.avg_score from test4_02

where test4_02.sid=test4_05.sid) update test4_05

set sum_credit=(select test4_03.sum_credit from test4_03

where test4_03.sid=test4_05.sid) update test4_05

set did=(select test04.did from test04

where test04.dname=test4_05.dname) where dname in (select dname from test04) update test4_05 set did='00'

where dname not in (select dname

from test04) or dname is null update dbtest set test=4 select * from dbscore

6、将pub用户下的Student_42及数据复制到主用户的表test4_06中,对表中的数据进行整理，修复那些不规范的数据： 剔除姓名列中的所有空格；

select *from pub.student_42 drop table test4_06

create table test4_06 as select* from pub.student_42

update test4_06

set name=replace(name,' ','')

7、将pub用户下的Student_42及数据复制到主用户的表test4_07中,对表中的数据进行整理，修复那些不规范的数据：

对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些和大多数不一样的就是不规范的）；

create table test4_07 as select* from pub.student_42 update test4_07

set sex=replace(sex,'性','') update test4_07

set sex=replace(sex,' ','')

8、将pub用户下的Student_42及数据复制到主用户的表test4_08中,对表中的数据进行整理，修复那些不规范的数据：

对班级列进行规范（需要先确定哪些班级不规范）。

create table test4_08 as select* from pub.student_42 update test4_08

set class=replace(class,'级','') update test4_08

set class=replace(class,' ','')

9、将pub用户下的Student_42及数据复制到主用户的表test4_09中,对表中的数据进行整理，修复那些不规范的数据：

年龄为空值的根据出生日期设置学生年龄（年龄=2012-出生年份），年龄不为空值的不要改变。

create table test4_09 as select* from pub.student_42 update test4_09

set age=2012-extract(year from birthday) where age is null

10、 将pub用户下的Student_42及数据复制到主用户的表test4_10中,对表

中的数据进行整理，修复那些不规范的数据： （1） 剔除姓名列中的所有空格； （2） 剔除院系名称列中的所有空格；

（3） 对性别列进行规范（需要先确定哪些性别数据不规范，也就是那些

和大多数不一样的就是不规范的）；

（4） 对班级列进行规范（需要先确定哪些班级不规范）。

（5） 年龄为空值的根据出生日期设置学生年龄（年龄=2012-出生年份），

年龄不为空值的不要改变。

select *from pub.student_42 drop table test4_06

create table test4_10 as select* from pub.student_42 update test4_10

set name=replace(name,' ','')

update test4_10

set dname=replace(dname,' ','')

update test4_10

set sex=replace(sex,'性','') update test4_10

set sex=replace(sex,' ','')

update test4_10

set age=2012-extract(year from birthday) where age is null

update test4_10

set class=replace(class,'级','') update test4_10

set class=replace(class,' ','')

update dbtest set test=4 select * from dbscore

update test4_10

set age=2012-extract(year from birthday) where age is null

update test4_10

set class=replace(class,'级','') update test4_10

set class=replace(class,' ','')

update dbtest set test=4 select * from dbscore

本文来源：https://www.bwwdw.com/article/k2eo.html