数据库原理实验作业第2题2015答案

作业2
1、统计各系人数（3）
select sdept,count(*) as num from student group by sdept
2、统计各系男女生人数(6)
select sdept,ssex,count(*) as num from student group by sdept,ssex
3、统计各门课程选修的人数（7）
select cname,count(*) as num from course,sc where o=o group by cname
4、统计各门课程男女生选修的人数（14）
select cname,ssex,count(*) as num from all_data group by cname,ssex
5、统计各系各门课程选修的人数（21）
select sdept,cname,count(*) as num from all_data group by sdept,cname
6、统计课程不及格人次数（7）
select cname,count(*) as num from course,sc where o=o and grade<60 group by cname
7、统计各系课程都及格人数（3）
select sdept,count(*) from student where not exists (
select * from sc where sc.sno=student.sno and grade<60)
group by sdept
8、统计各系课程成绩在55分以下的人次数（3）
select sdept,count(*) as num from all_data
where grade<55
group by sdept
9、统计各系各门课程成绩在55分以下的人次数（21）
select sdept,cname,count(*) as num from all_data
where grade<55
group by sdept,cname
order by sdept,cname
10、统计各系课程不及格人次数（3）
select sdept,count(*) as num from all_data
where grade<60
group by sdept
11、统计各系各门课程课程不及格人次数(21)
select sdept,cname,count(*) as num from all_data
where grade<60
group by sdept，cname
12、统计各系有1门课程及以上课程不及格人数(3)
select sdept,count(*) from student where exists (
select * from sc where sc.sno=student.sno and grade<60)
group by sdept
13、统计各系每门课程平均分（21）
select sdept,cname,avg(grade) as avg_grade from all_data group by sdept,cname
14、统计各系学生课程平均分在60到70之间的人数(70)
select sdept,sname , avg(grade) asavg_grade from all_data
group by sdept,sname
having avg(grade)>=60 and avg(grade)<70
15、统计各系每门功课都及格的学生人数(3)
select sdept,count(*) from student where not exists (
select * from sc where sc.sno=student.sno and grade<60)
group by sdept
16、统计各系3门及以上课程不及格的人数(3)
select sdept,count(*) as num from all_data group by sdept having count(*)>=3
17、统计各系在各门课程的平均分之上的人数*(21)
select sdept,person_ame,count(*)as num from
(select sdept,sno, sname,cname,avg(grade) as person_avg_grade
from all_data group by sdept,sno, sname,cname)
as person_avg(sdept,sno, sname,cname,person_avg_grade),
(select cname,avg(grade) as course_avg_grade
from all_data group by cname)
as course_avg(cname,course_avg_grade)
where person_ame = course_ame and
person_avg.person_avg_grade>course_avg.course_avg_grade
group by sdept,person_ame
18、分男女统计各系每门功课不及格人数(37)
select sdept,cname,ssex,count(*) as num
from all_data
where grade <60
group by sdept,cname,ssex
order by sdept,cname,ssex
19、分男女统计各系各年龄数人数(43)
select sdept,sage,ssex,count(*) as num
from student
group by sdept,ssex,sage
order by sdept,ssex,sage
20、分男女统计各系平均年龄(6)
select sdept, ssex, avg(sage) as avg_age
from student
group by sdept,ssex
order by sdept,ssex
21、统计各系每门功课最高分(21)
select sdept,cname,max(grade) asmax_grade
from all_data
group by sdept,cname
order by sdept,cname
22、分男女统计各系每门功课最低分(42)
select ssex, sdept,cname,min(grade) asmin_grade
from all_data
group by ssex,sdept,cname
order by sdept,ssex,cname
23、分系分男女统计学生总分最高分(6)
select sdept,ssex,max(sum_grade) as max_sum
from ( select sdept,ssex,sname,sum(grade) as sum_grade from all_data
group by sdept,ssex,sname) as grade_sum(sdept,ssex,sname,sum_grade)
group by sdept,ssex
order by sdept,ssex
24、分系统计总分最低分(3)
select sdept,min(sum_grade) as min_sum
from ( select sdept,sname,sum(grade) as sum_grade
from all_data
group by sdept,sname) as grade_sum(sdept,sname,sum_grade)
group by sdept
order by sdept
25、统计各门课程各分数段人数，每10分为一个分数段，比如成绩65分划为60这个分数段。

（提示用计算列ROUND(grade-5,-1计算每个成绩的分数段）(42)
select cname,grade_rank,count(*) as num from (
select cname,round(grade-5,-1) as grade_rank
from all_data ) as grade1(cname,grade_rank)
group by cname,grade_rank
order by cname,grade_rank
26、统计每个学生总学分（注：成绩及格才计学分）(254)
select sno,sum(ccredit) as sum_cedit
from sc,course
where o=o and grade >0
group by sno
order by sno。