亲测可用

1.
create table test8_01(Dname varchar(30),Avg_ds_score int,Avg_os_score int)
insert into test8_01 select dname,round(avg(score),0),0 from pub.student,pub.course,pub.student_course where pub.student.sid=pub.student_course.sid and pub.student_course.cid= pub.course.cid and dname is not null and pub.course.name='数据结构' group by dname
update test8_01 set Avg_os_score=(select avg(score) from pub.student,pub.course,pub.student_course where pub.student.sid=pub.student_course.sid and pub.student_course.cid= pub.course.cid and dname is not null and test8_01.dname=pub.student.dname and pub.course.name='操作系统' group by dname)
2.
select * from pub.student
create table test8_02(sid varchar(12),name varchar(10),dname varchar(30),ds_score int,os_score int)
insert into test8_02 select pub.student.sid,pub.student.name,dname,0,0 from pub.student where dname='计算机科学与技术学院' and sid in(select sid from pub.student_course where cid=(select cid from pub.course where name='数据结构') intersect(select sid from pub.student_course where cid=(select cid from pub.course where name='操作系统')))

update test8_02 set ds_score=(select score from pub.student_course where cid=(select cid from pub.course where name='数据结构') and test8_02.sid=pub.student_course.sid)

update test8_02 set os_score=(select score from pub.student_course where cid=(select cid from pub.course where name='操作系统') and test8_02.sid=pub.student_course.sid)
3.
create table test8_03(sid varchar (12),name varchar(10),dname varchar(30),ds_score int,os_score int)

insert into test8_03 select pub.student.sid,pub.student.name,dname,null,null from pub.student where dname='计算机科学与技术学院' and sid in(select sid from pub.student_course where cid=(select cid from pub.course where name='数据结构') union(select sid from pub.student_course where cid=(select cid from pub.course where name='操作系统')))

update test8_03 set ds_score=(select score from pub.student_course where cid=(select cid from pub.course where name='数据结构') and
test8_03.sid=pub.student_course.sid)

update test8_03 set os_score=(select score from pub.student_course where cid=(select cid from pub.course where name='操作系统') and
test8_03.sid=pub.student_course.sid)
4.
create table test8_04(sid varchar(12),name varchar(10),dname varchar(30),ds_score int,os_score int)

insert into test8_04 select pub.student.sid,pub.student.name,dname,null,null from pub.student where dname='计算机科学与技术学院'

update test8_04 set ds_score=(select score from pub.student_course where cid=(select cid from pub.course where name='数据结构') and
test8_04.sid=pub.student_course.sid)

update test8_04 set os_score=(select score from pub.student_course where cid=(select cid

from pub.course where name='操作系统') and
test8_04.sid=pub.student_course.sid)

update dbtest set test=8

select * from dbscore where 实验号=8