内蒙古大学 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
	      );

查询结果:(因为只选1门课的成绩都>60，所以查询不到，提示未选定行)

 

 

 

=======================================

作业一         作业二          作业三           作业四           作业五