山东大学数据库系统实验八

实验八

声明：所有SQL语句均在实验平台验证通过，实验细节可能随时间推移老师会进行修改。在此仅提供解答思路，毕竟我的方法肯定不是最优，而且实验平台有查重功能，不要一昧的复制哦！

1.查询各院系（不包括院系名称为空的）的数据结构平均成绩avg_ds_score、操作系统平均成绩avg_os_score，平均成绩四舍五入到个位，创建表test8_01。

create table test8_01 as select ds.dname,ds.avg_ds_score,os.avg_os_score from

(select dname,round(avg(score),0) avg_ds_score from

(select * from

(select sid,max(score) score from pub.student_course where cid in

(select cid from pub.course where name='数据结构') group by sid) sc join

(select dname,sid from pub.student) s on sc.sid=s.sid where dname is not null)

group by dname) ds


join


(select dname,round(avg(score),0) avg_os_score from

(select * from

(select sid,max(score) score from pub.student_course where cid in

(select cid from pub.course where name='操作系统') group by sid) sc join

(select dname,sid from pub.student) s on