内蒙古大学 2017-2018《数据库原理与应用A》实验指导书 作业三

（本文中蓝色字体为个人理解，非标准答案，仅供参考）

 

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

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

 

作业三 数据库中数据的查询-----复杂查询

 

一．实验内容及说明

在实验一和实验二的基础上，进行数据库的查询，充分体会 SQL 语言的强大功能。并将每个实验结果均存储为一张视图表。

方式如下：create view T3_*_*[(..)]as                

                (Select ……);//查询语句

 

二．实验步骤 (Oracle 10g测试通过)

 

1. 复合查询（查询结果存储为 T3_1_题目序号）

（1） 查询每个学生及其选修课程的情况。(与作业二中11题的进阶二方法相同)

方法1：使用两次left join ... on，把学生表、选课表、课程表左外连接，查询学生所有信息+选课信息+课程信息

create view T3_1_1 as 
select student.sno,sname,ssex,sage,sdno,course.cno,cname,ccredit,grade 
from student left join sc on (student.sno=sc.sno) left join course on(sc.cno=course.cno);

方法2：使用两次left join ... using，把学生表、选课表、课程表左外连接，查询学生所有信息+选课信息+课程信息

create view T3_1_1_2 as 
select sno,sname,ssex,sage,sdno,cno,cname,ccredit,grade 
from student left join sc using(sno) left join course using(cno);

方法3：使用where 某属性=某属性(+) and 某属性=某属性(+)，把学生表、选课表、课程表左外连接，查询学生所有信息+选课信息+课程信息

 

create view T3_1_1_3 as 
select student.sno,sname,ssex,sage,sdno,course.cno,cname,ccredit,grade   /*查询sc.sno和查询student.sno是一样的，查询sc.cno和查询course.cno是一样的*/
from student,sc,course 
where student.sno=sc.sno(+) 
and sc.cno=course.cno(+);

查询结果:

 

（2） 查询选修 1001 号课程且分数在 70 分以上的学生学号、姓名及成绩。

方法1：连接学生表和选课表

create view T3_1_2 as 
select sc.sno,sname,grade 
from student,sc 
where student.sno=sc.sno 
and cno='1001' 
and grade>70;

方法2：先将sno，grade查询为派生表t1，再让学生表和派生表t1连接

create view T3_1_2_1 as 
select student.sno,sname,t1.grade 
from student,(select sno,grade from sc where cno='1001' and grade>70) t1 
where student.sno=t1.sno;

查询结果:

 

 

 

（3） 查询‘cs’系所有学生的所有课程的成绩。

方法1：连接学生表、选课表、系别表

create view T3_1_3 as
select student.sno,sname,cno,grade
from student,sc,dept
where student.sno=sc.sno
and student.sdno=dept.dno
and dname='cs';

方法2：连接学生表、选课表、系别表、课程表 （增加课程信息）

create view T3_1_3_1 as 
select student.sdno,dname,student.sno,sname,course.cno,cname,grade 
from student,course,sc,dept 
where student.sno=sc.sno 
and course.cno=sc.cno 
and student.sdno=dept.dno 
and dname='cs';

查询结果:

 

（4） 查询‘cs’系所有教师的所有课程信息。

方法1：连接教师表、课程表、系别表

create view T3_1_4 as
select tno,tname,cno,cname,ccredit
from teacher,course,dept
where teacher.tno=course.ctno
and teacher.tdno=dept.dno
and dname='cs';

方法2：连接教师表、课程表、系别表（增加系别信息）

create view T3_1_4_1 as 
select teacher.tdno,dname,teacher.tno,tname,course.cno,cname,ccredit 
from teacher,course,dept 
where teacher.tno=course.ctno 
and teacher.tdno=dept.dno 
and dname='cs';

查询结果:

 

（5） 查询 “男”教师及其所上的课程信息。

方法1：连接教师表和课程表（缺点：查询不到没有带课的男教师）

create view T3_1_5 as
select tno,tname,tsex,cno,cname,ccredit
from teacher,course
where teacher.tno=course.ctno
and tsex='男';

查询结果:

 

方法2：使用left join ... on，把学生表、选课表左外连接，查询教师信息+课程信息

create view T3_1_5_1_1 as 
select tno,tname,tsex,cno,cname,ccredit 
from teacher left join course on (teacher.tno=course.ctno) 
where tsex='男';

方法2.1：使用where  某属性=某属性(+)，把学生表、选课表左外连接，查询教师信息+课程信息

create view T3_1_5_1_2 as 
select tno,tname,tsex,cno,cname,ccredit
from teacher,course 
where teacher.tno=course.ctno(+) 
and tsex='男';

查询结果：(可以看到教师"任白"没有带课，而普通连接查询不到，所以必须使用左外连接查询)

 

（6） 查询和“李勇”同性别的所有同学的 Sname。

方法1：不相关子查询，查询性别相同的姓名（不包括'李勇'）

create view T3_1_6 as 
select sname 
from student 
where sname!='李勇'
and ssex=( select ssex 
	   from student 
	   where sname='李勇'
	 );

 

方法2：自身连接，查询性别相同的姓名（不包括'李勇'）

create view T3_1_6_1 as 
select s1.sname 
from student s1,student s2 
where s1.ssex=s2.ssex 
and s2.sname='李勇' 
and s1.sname!='李勇';

查询结果:

 

（7） 查询和“李勇”同性别并同系的同学 Sname。

方法1：不相关子查询，查询性别相同，系别相同的姓名（不包括'李勇'）

create view T3_1_7 as 
select sname 
from student 
where sname!='李勇'
and ssex=( select ssex 
	   from student 
	   where sname='李勇'
	 ) 
and sdno=( select sdno 
	   from student 
	   where sname='李勇'
	 );

方法2：自身连接，查询性别相同，系别相同的姓名（不包括'李勇'）

create view T3_1_7_1 as 
select s1.sname 
from student s1,student s2 
where s1.ssex=s2.ssex 
and s1.sdno=s2.sdno 
and s2.sname='李勇' 
and s1.sname!='李勇';		

查询结果:

 

（8） 查询选修了“数据库”课程且成绩在 80 分以上的所有学生的学号和姓名。

方法1：连接学生表、选课表、课程表

create view T3_1_8 as 
select student.sno,sname 
from student,course,sc 
where student.sno=sc.sno 
and course.cno=sc.cno 
and cname='数据库' 
and grade>80;

方法2：不相关子查询，父查询从学生表查，子查询选课表和课程表连接

create view T3_1_8_1 as
select sno,sname
from student
where sno in( select sno 
	      from sc,course 
	      where course.cno=sc.cno 
	      and cname='数据库' 
	      and grade>80
	    );

方法3：两层不相关子查询，父查询从学生表查，第一层子查询从选课表查，第二层子查询从课程表查

 

create view T3_1_8_2 as
select sno,sname
from student 
where sno in( select sno 
	      from sc 
	      where grade>80 
	      and cno in( select cno 
			  from course 
			  where cname='数据库'
			)
	    );

查询结果:

 

（9） 查询每个学生的学号、姓名、课程及成绩（包括没选修课程的学生情况）。(类似作业二中11题的进阶二方法)

方法1：使用两次left join ... on，把学生表、选课表、课程表左外连接，查询学生信息+课程信息+成绩

create view T3_1_9 as 
select student.sno,sname,course.cno,cname,grade 
from student left join sc on (student.sno=sc.sno) left join course on(sc.cno=course.cno);

方法2：使用两次left join ... using，把学生表、选课表、课程表左外连接，查询学生信息+课程信息+成绩

create view T3_1_9_2 as 
select sno,sname,cno,cname,grade 
from student left join sc using(sno) left join course using(cno);

方法3：使用where 某属性=某属性(+) and 某属性=某属性(+)，把学生表、选课表、课程表左外连接，查询学生信息+课程信息+成绩

create view T3_1_9_3 as 
select student.sno,sname,course.cno,cname,grade 
from student,sc,course 
where student.sno=sc.sno(+) 
and sc.cno=course.cno(+);

查询结果:

 

延伸问题：查询每个学生的学号、姓名、课程及成绩（包括没选课的学生 与 没有被选的课程）

方法1：使用full join ... on，把学生表、选课表、课程表全外连接，查询学生信息+课程信息+成绩

create view T3_1_9_X as 
select student.sno,sname,course.cno,cname,grade 
from student full join sc on(student.sno=sc.sno) full join course on(course.cno=sc.cno);

方法2：使用full join ... using，把学生表、选课表、课程表全外连接，查询学生信息+课程信息+成绩

create view T3_1_9_Y as 
select sno,sname,cno,cname,grade
from student full join sc using(sno) full join course using(cno);

查询结果:(可以看到课程"生物信息学"没有学生选择)

 

（10） 查询每门课程的详细授课情况，列课程出号、课名、授课教师姓名及学分。

方法1：连接课程表、教师表（缺点：查询不到没有教师授课的课程）

create view T3_1_10 as 
select cno,cname,tname,ccredit 
from teacher,course 
where teacher.tno=course.ctno;

方法2.1：使用eft join ... on，把课程表、教师表左外连接，查询教师信息+课程信息

create view T3_1_10_2_1 as 
select cno,cname,tname,ccredit
from course left join teacher on(course.ctno=teacher.tno);

方法2.2：使用where 某属性=某属性(+)，把课程表、教师表左外连接，查询教师信息+课程信息

create view T3_1_10_2_2 as 
select cno,cname,tname,ccredit
from course,teacher
where course.ctno=teacher.tno(+);

查询结果:

 

2. 组合查询和统计查询（查询结果存储为 T3_2_题目序号）

（1） 查询各个系学生选修 1001 课程的平均成绩。   

方法1：连接学生表、选课表，按所在系sdno分组，聚集函数avg()统计平均成绩

create view T3_2_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 T3_2_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） 查询各个系学生选修课程的总门数。

方法：连接学生表、选课表，按所在系sdno分组，然后聚集函数avg()在组内统计不重复的cno个数

create view T3_2_2 as 
select sdno,count(distinct cno) count 
from student,sc 
where student.sno=sc.sno 
group by sdno;

查询结果:

 

（3） 查询’cs’系且选修课程的最低成绩大于等于 60 分的学生信息。

方法1：连接学生表、选课表、系别表，找到'cs'系，并且最低成绩min(grade)>=60的学生

create view T3_2_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 T3_2_3_2 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 T3_2_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） 查询学校开设的课程总数。

方法：使用聚集函数count()统计课程个数

create view T3_2_4 as 
select count(cno) count 
from course;

查询结果:

 

（5） 查询所有学生所选课程的总成绩。（查询每个学生的总成绩）

方法：按学号sno分组，使用聚集函数sum()统计总成绩

create view T3_2_5 as 
select sno,sum(grade) sum 
from sc 
group by sno;

查询结果:

 

（6） 查询每门课程的平均分。

方法1：按课程号cno分组，使用聚集函数avg()统计平均成绩

create view T3_2_6 as 
select cno,avg(grade) avg 
from sc 
group by cno 
order by cno;

方法2：按课程号cno分组，使用聚集函数avg()统计平均成绩，并且按cno升序排序

create view T3_2_6_1 as 
select cno,avg(grade) avg 
from sc 
group by cno 
order by cno;

查询结果:

 

 3.视图查询（查询结果存储为 T3_3_题目序号）

（1） 利用视图 View1 中统计各门课程的选课人数，结果显示课程号、课程名及选课人数。

方法：view1（sname,cname,grade）中没有cno，所以需要连接课程表，然后按cno和cname分组，count()统计选课人数，以选课人数count降序排序

create view T3_3_1 as 
select cno,view1.cname,count(view1.sname) count 
from view1,course 
where view1.cname=course.cname 
group by cno,view1.cname 
order by count desc;

查询结果:

 

（2） 在视图 View2 中查询年龄小于 20 岁学生的学号。

方法：view2（sno,ssex,sage,birthday）中有sno，可以直接用sage<20查询

create view T3_3_2 as 
select sno from view2 
where sage<20;

查询结果:

 

（3） 在视图 JSGV 中查询选课多于 2 门的学生学号。

方法：JSGV（sno,cno,grade）中有sno，按学号sno分组，使用count()统计选课数

create view T3_3_3 as 
select sno 
from JSGV 
group by sno 
having count(cno)>2;

查询结果:

 

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

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