山东大学数据库实验四答案

实验四

1) 4 - 1 统计总成绩
create table test4_01 as select * from pub.STUDENT_41
alter table test4_01 add sum_score int


update test4_01
set sum_score=(
select sum(score)
from pub.STUDENT_COURSE,pub.COURSE
where test4_01.sid=student_course.sid and student_course.cid=COURSE.cid
group by sid
)

2) 4 - 2 统计平均成绩
alter table test4_02 add avg_score float(int不行)

update test4_02
set avg_score=(
select trim(to_char(avg(score),999999.9))
from pub.STUDENT_COURSE
where test4_02.sid=student_course.sid
group by sid
)


3) 4 - 3 统计总学分
alter table test4_03 add sum_credit int

update test4_03
set sum_credit=(
select sum(credit)
from pub.STUDENT_COURSE,pub.COURSE
where test4_03.sid=student_course.sid and student_course.cid=COURSE.cid and score>=60
group by sid
)

4) 4 - 4 设置院系编号

update test4_04
set dname= case
when dname in (select dname
from pub.DEPARTMENT) then (select did
from pub.DEPARTMENT
where test4_04.DNAME=department.dname)
else test4_04.DNAME
end




5) 4 - 5 几项内容综合
前三项参照前面题目
第四项
update test4_05
set did= case
when test4_05.dname in (select dname
from pub.DEPARTMENT) then (select did
from pub.DEPARTMENT
where test4_05.DNAME=department.dname)
when test4_05.dname in (select dname
from pub.DEPARTMENT_41) then (select did
from pub.DEPARTMENT_41
where test4_05.DNAME=department_41.dname)
else '00'
end


6) 4 - 6 剔除姓名中的空格
create table test4_06 as select * from pub.STUDENT_42

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


7) 4 - 7 规范性别
update test4_07
set sex= replace(sex,' ')


update test4_07
set sex= case
when sex is NULL then NULL
when length(sex)>1 then substr(sex,1,1)
else sex
end

8) 4 - 8 规范班级

update test4_08
set class= case
when class is NULL then NULL
when length(class)>4 then substr(class,1,4)
else class
end

9) 4 - 9 计算年龄

update test4_09
set age= case
when age is NULL then (2012-extract(year from birthday))
else age
end

10) 4 - 10 几项内容综合
update test4_10
set

name= replace(name,' ')


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

update test4_10
set sez= replace(sex,' ')
update test4_10
set sex= case
when sex is NULL then NULL
when length(sex)>1 then substr(sex,1,1)
else sex
end


update test4_10
set class= case
when class is NULL then NULL
when length(class)>4 then substr(class,1,4)
else class
end


update test4_10
set age= case
when age is NULL then (2012-extract(year from birthday))
else age
end