山东大学数据库实验7-10（part）

7.3 创建"学院班级学分达标情况统计表1"test7_03，依据pub.student， pub.course，pub.student_course统计形成表中各项数据，成绩>=60为及格计入学分，总学分>=10为达标，院系为空值的数据不统计在下表中，表结构：院系名称dname、班级class、学分达标人数p_count1、学分未达标人数p_count2、总人数p_count。
Dname varchar(30) class varchar(10) P_count1 Int P_count2 int P_count int
计算机学院 2006
计算机学院 2007
软件学院 2006
………………

create table test7_031 as (select dname,class,count(sid) p_count1 from(select dname,class,sid,sum(credit)sum_credit from ((select sid,dname,class from pub.student where dname is not null) natural join (select sid,cid,score from pub.student_course where score>=60) natural join pub.course)group by dname,class,sid)
where sum_credit>=10
group by dname,class)

create table test7_032 as select dname,class ,sum(p2)p_count2 from((select dname,class,count(sid)p2 from
(select dname,class ,sid,sum(credit)sum_credit from(( select sid,dname,class from pub.student where dname is not null)natural join(
select sid,cid,score from pub.student_course where score>=60)natural join pub.course) group by dname,class,sid)
where sum_credit<10
group by dname,class)
union all
(select dname ,class, count(sid)p2 from pub.student where dname is not null and sid not in (select sid from pub.student_course) group by dname,class)) group by dname,clas