数据库原理实验作业第2题2015答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
作业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