山东大学数据库实验八

1.create table test81 as
(select pub.STUDENT_course.SID,pub.STUDENT.DNAME,max(score) as score
from pub.STUDENT,pub.STUDENT_COURSE,pub.COURSE
where pub.STUDENT_COURSE.SID=pub.student.sid
and pub.STUDENT_COURSE.CID=pub.course.cid
and pub.COURSE.NAME='操作系统'
and dname is not null
group by pub.STUDENT_COURSE.SID,pub.STUDENT.DNAME)
create table test811 as
(select dname,round(avg(score),0) as avg_os_score
from test81
group by dname)
create table test812 as(
select dname,pub.STUDENT_COURSE.SID,max(score) as score
from pub.STUDENT,pub.STUDENT_COURSE,pub.COURSE
where pub.STUDENT_COURSE.SID=pub.student.sid
and pub.STUDENT_COURSE.cid=pub.course.cid
and pub.COURSE.NAME='数据结构'
and dname is not null
group by dname,pub.STUDENT_COURSE.SID)
create table test813 as
(select dname,round(avg(score),0) as avg_ds_score
from test812
group by dname)
create table test8_01 as
(select *
from test811 natural left join test813)
2。create table te