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

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
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))
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
update test4_10
set name= replace(name,' ')
update test4_10
set dname= replace(dname,' ')
update test4_10
set sez= replace(sex,' ')
update test4_10
update test4_08
set class= case
when class is NULL then NULL
when length(class)>4 then substr(class,1,4)
else class
end
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 规范班级
when test4_05.dname in (select dname
from pub.DEPARTMENT_41) then (select did
from pub.DEPARTMENT_41
create table test4_06 as selst4_06
set name= replace(name,' ')
7) 4 - 7 规范性别
update test4_07
set sex= replace(sex,' ')
when class is NULL then NULL
when length(class)>4 then substr(class,1,4)
else class
end
update test4_10
set age= case
group by sid
)
4) 4 - 4 设置院系编号
update test4_04
set dname= case
when dname in (select dname
from pub.DEPARTMENT) then (select did
where test4_05.DNAME=department_41.dname)
else '00'
end
6) 4 - 6 剔除姓名中的空格
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
from pub.DEPARTMENT) then (select did
from pub.DEPARTMENT
where test4_05.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
where test4_04.DNAME=department.dname)
实验四
1) 4 - 1 统计总成绩
create table test4_01 as select * from pub.STUDENT_41
alter table test4_01 add sum_score int
update test4_01
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 几项内容综合
when age is NULL then (2012-extract(year from birthday))
else age
end
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