内蒙古大学 2017-2018《数据库原理与应用A》实验指导书 作业四
（本文中蓝色字体为个人理解，非标准答案，仅供参考）
=================================================================================
作业四 数据库中数据的查询-----分组查询和统计查询
一．实验内容及说明
在作业一的基础上，完成如下题目。并将每个实验结果均存储为一张视图表。
方式如下：create view T4_* [(..)]AS
(Select ……);//查询语句
注：个别题目的排序结果在视图中没办法实现，请再排序的结果无误的情况下，将未排序的结果生成视图提交到服务器中。
二．实验步骤 (Oracle 10g测试通过)
1. 查询各个系学生选修 1001 课程的平均成绩。(同作业三 T3_2_1)
方法1：连接学生表、选课表，按所在系sdno分组，聚集函数avg()统计平均成绩
create view T4_1 as
select sdno,avg(grade) avg
from student,sc
where student.sno=sc.sno
and cno='1001'
group by sdno 方法2：连接学生表、选课表，按所在系sdno分组，聚集函数avg()统计平均成绩，按sdno升序排序
create view T4_1_1 as
select sdno,avg(grade) avg
from student,sc
where student.sno=sc.sno
and cno='1001'
group by sdno
order by sdno;查询结果:
2. 查询各个系学生选修课程的总门数。(同作业三 T3_2_2)
方法：连接学生表、选课表，按所在系sdno分组，然后聚集函数avg()在组内统计不重复的cno个数
create view T4_2 as
select sdno,count(distinct cno) count
from student,sc
where student.sno=sc.sno
group by sdno;查询结果:
3. 查询‘cs’系且选修课程的最低成绩大于等于 60 分的学生信息。(同作业三 T3_2_3)
方法1：连接学生表、选课表、系别表，找到'cs'系，并且最低成绩min(grade)>=60的学生
create view T4_3 as
select student.sno,sname,ssex,sage,student.sdno
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs'
group by student.sno,sname,ssex,sage,student.sdno
having min(grade)>=60;方法2：父查询连接学生表、系别表，找到'cs'系，子查询最低成绩min(grade)>=60的学生（子查询简化group by子句）
create view T4_3_1 as
select student.sno,sname,ssex,sage,student.sdno
from student,dept
where student.sdno=dept.dno
and dname='cs'
and sno in( select sno
from sc
group by sno
having min(grade)>=60
);方法3：两个不相关子查询，找到'cs'系的系编号dno，并且sno不在grade<60的学生
（grade<60说明最低成绩要比60还要低。那么sno not in，就会选出最低成绩>=60的学生）
create view T4_3_2 as
select sno,sname,ssex,sage,sdno
from student
where sdno=( select dno
from dept
where dname='cs'
)
and sno not in( select sno
from sc
where grade<60
);查询结果:
4. 列出学校开设的课程总数。(同作业三 T3_2_4)
方法：使用聚集函数count()统计课程个数
create view T4_4 as
select count(cno) count
from course;查询结果:
5. 列出每门课程的平均分。(同作业三 T3_2_6)
方法1：按课程号cno分组，使用聚集函数avg()统计平均成绩
create view T4_5 as
select cno,avg(grade) avg
from sc
group by cno
order by cno;方法2：按课程号cno分组，使用聚集函数avg()统计平均成绩，并且按cno升序排序
create view T4_5_1 as
select cno,avg(grade) avg
from sc
group by cno
order by cno;查询结果:
6. 查询各个系学生选修每门课程的平均成绩。
方法：按系编号sdno和课程号cno分组，组内用聚集函数avg()求平均成绩，按sdno、cno升序排序
create view T4_6 as
select sdno,cno,avg(grade) avg
from student,sc
where student.sno=sc.sno
group by sdno,cno
order by sdno,cno;查询结果:
7. 列出成绩高于课程平均成绩的所有学生信息，并列出相关详细信息。
方法1：不相关子查询嵌套相关子查询，父查询从学生表查，不相关子查询从选课表c1查，相关子查询从选课表c2查（c1的成绩大于c2的平均成绩，并且是同一课程）
create view T4_7 as
select student.sno,sname,ssex,sage,sdno
from student
where student.sno in( select c1.sno
from sc c1
where grade>( select avg(grade) avg
from sc c2
where c1.cno=c2.cno
group by cno
)
);方法2：父查询连接学生表、选课表c1，相关子查询从选课表c2查（c1的成绩大于c2的平均成绩，并且是同一课程）
create view T4_7_1 as
select distinct student.sno,sname,ssex,sage,sdno
from student,sc c1
where student.sno=c1.sno
and grade>( select avg(grade) avg
from sc c2
where c1.cno=c2.cno
group by c2.cno
);
查询结果:
进阶方法：将课程号cno和平均成绩avg查询为派生表t1，再把学生表、选课表、课程表、派生表t1连接，查询所有信息
create view T4_7_2 as
select student.sno,sname,ssex,sage,sdno,sc.cno,cname,ccredit,grade,t1.avg
from student,sc,course,( select cno,avg(grade) avg from sc group by cno ) t1
where student.sno=sc.sno
and sc.cno=course.cno
and sc.cno=t1.cno
and grade>t1.avg;查询结果:
8. 列出每个系学生的详细信息。
方法：连接学生表、系别表，按系编号升序排序
create view T4_8 as
select student.sdno,dname,sno,sname,ssex,sage
from student,dept
where student.sdno=dept.dno
order by sdno;查询结果:
9. 列出每门课程的详细授课信息。
方法：连接教师表、课程表
create view T4_9 as
select cno,cname,ccredit,tno,tname,tsex,prof,tdno
from course,teacher
where teacher.tno=course.ctno;查询结果:
10. 列出每门课程的最低成绩和最高成绩。
方法1：按课程号cno分组，查询课程号，最低、最高成绩
create view T4_10 as
select cno,min(grade) min,max(grade) max
from sc
group by cno;方法2：按课程号cno分组，连接课程表、选课表，查询课程号，课程名，最低、最高成绩
create view T4_10_1 as
select sc.cno,cname,min(grade) min,max(grade) max
from course,sc
where course.cno=sc.cno
group by sc.cno,cname
order by sc.cno;查询结果:
11. 列出所有学生选课的详细信息，并按每位学生选课成绩降序显示。
方法1：按学号sno分组，排序按学号sno升序，成绩grade降序排序
create view T4_11 as
select sno,cno,grade
from sc
group by sno,cno,grade
order by sno,grade desc;方法2：把学生表、选课表、课程表左外连接，按学号sno分组，排序按学号sno升序，成绩grade降序排序
create view T4_11_1 as
select student.sno,sname,sc.cno,cname,grade
from student,sc,course
where student.sno=sc.sno(+)
and sc.cno=course.cno(+)
group by student.sno,sname,sc. cno,cname,grade
order by sno,grade desc;查询结果:
12. 列出同一系中选修相同课程的学生的详细信息。
方法1：将学生表、选课表连接当作学生1。派生表将学生表、选课表连接当作学生2。
然后将基表与派生表连接，即选出同系，相同课程的学生1、学生2。（distinct用来去重）
create view T4_12 as
select distinct student.sdno,sc.cno,student.sno,sname,ssex,sage
from student,sc,(select sdno,cno,student.sno from student,sc where student.sno=sc.sno) t1
where student.sno=sc.sno
and student.sdno=t1.sdno
and sc.cno=t1.cno
and student.sno!=t1.sno;方法2.1：学生1的学生表s1、选课表c1。学生2的学生表s2，选课表c2。
先s1与s2的系编号sdno相等，然后c1与c2的课程号cno相等，再让s1,c1连接，s2,c2连接，即选出同系，相同课程的学生1、学生2。
create view T4_12_1 as
select *
from student s1
where exists( select *
from student s2
where s1.sno!=s2.sno
and s1.sdno=s2.sdno
and exists( select *
from sc c1
where s1.sno=c1.sno
and exists( select *
from sc c2
where c1.sno!=c2.sno
and s2.sno=c2.sno
and c1.cno=c2.cno
)
)
);
方法2.2：学生1的学生表s1、选课表c1。学生2的学生表s2，选课表c2。
先s1,c1连接，然后s2,c2连接，再让s1与s2的系编号sdno相等，c1与c2的课程号cno相等即选出同系，相同课程的学生1、学生2。
create view T4_12_2 as
select *
from student s1
where exists( select *
from sc c1
where s1.sno=c1.sno
and exists( select *
from student s2
where s1.sno!=s2.sno
and s1.sdno=s2.sdno
and exists( select *
from sc c2
where s2.sno=c2.sno
and c1.cno=c2.cno
)
)
);
查询结果:
13. 列出学生选修课程人数最多的三门课程。
方法1：将课程cno和人数count查询为派生表并按人数count降序排序，然后查询前三行(rownum<=3)。仅有一张表且为派生表，派生表的别名可有可无
create view T4_13 as
select cno,count
from (select cno,count(sno) count from sc group by cno order by count desc)
where rownum<=3;方法2：将课程cno、课程名cname、人数count查询为派生表并按人数count降序排序，然后查询前三行(rownum<=3)。仅有一张表且为派生表，派生表的别名可有可无，此处别名为t1
create view T4_13_1 as
select t1.cno,t1.cname,t1.count
from ( select sc.cno,cname,count(sno) count
from course,sc
where course.cno=sc.cno
group by sc.cno,cname
order by count desc
)t1
where rownum<=3;查询结果:
14. 统计每个学生的学分，列出学号、姓名、学生。（要求：不及格课程无学分）
方法：连接学生表、选课表、课程表，找出有成绩（成绩非空），且成绩>=60的学生，聚集函数sum()统计总学分
create view T4_14 as
select student.sno,sname,sum(ccredit) sum
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
and grade>=60
and grade is not null
group by student.sno,sname
order by student.sno;查询结果:
15. 某高校学士学位授予条件为：每门课程必须及格且平均成绩达 75 分以上。请查询 Cs 系可授予学士学位的学生名单。
方法1：连接学生表、选课表、系别表，找到'cs'系的学生，按学号sno分组，每门课程必须及格（即最小成绩>=60），平均成绩avg(grade)>75
create view T4_15 as
select student.sdno,dname,student.sno,sname
from dept,student,sc
where dept.dno=student.sdno
and student.sno=sc.sno
and dname='cs'
group by student.sno,sname,student.sdno,dname
having ( min(grade)>60 and avg(grade)>75 );方法2：连接学生表、选课表、系别表，查询学号sno、平均成绩avg作为派生表t1并连接，找到'cs'系的学生，按学号sno分组，每门课程必须及格（即最小成绩>=60），并查询出最小成绩
create view T4_15_1 as
select student.sdno,dname,student.sno,sname,min(grade) min,t1.avg
from student,sc,dept,(select sno,avg(grade) avg from sc group by sno having avg(grade)>75) t1
where student.sno=sc.sno
and student.sdno=dept.dno
and student.sno=t1.sno
and dname='cs'
group by student.sdno,dname,student.sno,sname,t1.avg
having min(grade)>=60
order by student.sdno,student.sno;
查询结果:
16. 建立一张统计信息表 DEPTage(sdept,avage)，要求存放每个系学生的平均年龄。 (注意是建表，而不是建立视图)
方法：按系编号sdno分组，聚集函数avg()统计平均年龄
create table DEPTage(sdept,avage) as
select sdno,avg(sage)
from student
group by sdno
order by sdno;查询结果:
17. 查询只选 1 门课且成绩不及格学生的选课记录。
方法：在不相关子查询中先找出只选1门课的学生学号sno，然后父查询找出成绩<60的不及格学生信息
（如果不用子查询，而是把having count(cno)=1放在where grade<60后面。SQL会先把成绩<60的记录找出，然后再分组统计课程数。则会出现选了多门课，只有1门不及格的情况被当作结果查出）
create view T4_17 as
select sno,cno,grade
from sc
where grade<60
and sno in( select sno
from sc
group by sno
having count(cno)=1
);查询结果:(因为只选1门课的成绩都>60，所以查询不到，提示未选定行)
18. 查询 cs 系只选 1 门课且成绩不及格学生的选课记录。
方法：与17题同理，只不过增加了cs系的条件，只需再连接学生表、系别表，增加系名称，学生名信息
create view T4_18 as
select student.sdno,dname,student.sno,sname,cno,grade
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs'
and grade<60
and sc.sno in ( select sno
from sc
group by sno
having count(cno)=1
);