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

AN_drew

已于 2024-05-20 13:06:17 修改

阅读量732

点赞数

文章标签：数据库

于 2018-07-10 22:57:03 首次发布

本文链接：https://blog.csdn.net/an_drew/article/details/80991014

版权

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

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

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

作业五子查询

一．实验内容及说明

在作业一的基础上，完成如下题目。并将每个实验结果均存储为一张视图表。

方式如下：create view T5_*_ [(..)]AS

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

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

1. 嵌套查询（查询结果存储为 T5_1_题目序号，要求 SQL 脚本必须按嵌套式的方式实现）

(1) 查询与“陈伟”在同一系的学生。

方法：不相关子查询得到陈伟的所在系sdno，查询同一系的学生（不包括'陈伟'）

create view T5_1_1 as 
select * 
from student 
where sname!='陈伟'
and sdno=( select sdno 
       from student 
       where sname='陈伟'
     );

//等价的连接查询

create view T5_1_1_1 as 
select s1.* 
from student s1,student s2 
where s1.sdno=s2.sdno 
and s2.sname='陈伟' 
and s1.sname!='陈伟';

查询结果:

(2) 查询选修了课程名为“数据库”的学生学号和姓名。

方法：不相关子查询得到数据库的课程号，查询选修数据库的学生

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

//等价的连接查询

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

查询结果:

(3) 查询每个学生超过他选修课程平均成绩的学号、课程号和成绩。

（有两种理解，第一种：本课程成绩>所有课程的平均成绩；第二种：本课程成绩>本课程的平均成绩）

第一种：本课程成绩>所有课程的平均成绩

方法1.1：相关子查询从选课表c2查出学生所有课程的平均成绩，c1和c2的学号相等为同一个学生，父查询选成绩>所有课程平均成绩的信息

create view T5_1_3_1_1 as 
select sno,cno,grade 
from sc c1 
where grade>( select avg(grade) avg 
	      from sc c2 
	      where c1.sno=c2.sno 
	      group by c2.sno
	    );

方法1.2：将学号sno、所有课程平均成绩avg查询为派生表t1，选课表sc和派生表t1连接，选择成绩grade>所有课程平均成绩avg的信息

create view T5_1_3_1_2 as 
select sc.sno,cno,grade 
from sc,( select sno,avg(grade) avg from sc group by sno ) t1 
where sc.sno=t1.sno 
and grade>t1.avg;

查询结果:

第二种：本课程成绩>本课程的平均成绩（类似作业四第7题）

方法2.1：相关子查询，查询本课程的平均成绩avg，选择成绩grade>avg的信息

create view T5_1_3_2_1 as 
select sno,cno,grade 
from sc c1 
where grade>( select avg(grade) avg 
	      from sc c2 
	      where c1.cno=c2.cno 
	      group by c2.cno 
	    );

方法2.2：将学号sno，本课程平均成绩avg查询为派生表t1，选课表sc和派生表t1连接，选择成绩grade>本课程平均成绩avg的信息

create view T5_1_3_2_2 as 
select sno,sc.cno,grade 
from sc,( select cno,avg(grade) avg from sc group by cno ) t1 
where sc.cno=t1.cno 
and sc.grade>t1.avg 
order by sno,sc.cno;

查询结果:

(4) 查询没有选修课程名为“数据库”的学生学号和姓名。

方法1：不相关子查询，连接选课表、课程表，找到选数据库的学生，sno not in查出没有选的

create view T5_1_4 as 
select distinct student.sno,sname 
from student 
where student.sno not in (select sc.sno 
                          from sc,course 
                          where sc.cno=course.cno 
                          and cname='数据库'
                          );

方法2：用集合作差，减去选数据库的学生

create view T5_1_4_1 as 
select student.sno,sname 
from student 
minus select student.sno,sname 
      from student,sc,course
      where student.sno=sc.sno
      and sc.cno=course.cno
      and cname='数据库';

查询结果:

(5) 列出所有成绩都在 70 分以上的学生姓名。

方法1：不相关子查询，找出成绩<=70的学生学号，然后sno not in，查出所有成绩>70的学生

create view T5_1_5 as 
select sname 
from student 
where sno not in( select sno 
		  from sc 
		  where grade<=70 
		  group by sno
		);

方法2：不相关子查询，找出最低成绩>70的学生学号，然后sno in，查出所有成绩>70的学生

create view T5_1_5_1 as 
select sname 
from student 
where sno in( select sno 
	      from sc 
	      group by sno 
	      having min(grade)>70
	    );

方法3：与方法2相同，只是把子查询变为派生表，再与学生表连接

create view T5_1_5_2 as 
select sname 
from student,(select sno from sc group by sno having min(grade)>70) t1 
where student.sno=t1.sno;

查询结果:

(6) 列出选修“数据库”课程得最高分的学生的姓名、性别和所在系。

方法1：最内层相关子查询，查出数据库课程的最高分。外层相关子查询，查出成绩grade=最高分max的学生学号。父查询sno in，查出学生信息

create view T5_1_6 as 
select sname,ssex,sdno
from student
where sno=( select sno 
	    from sc c1 
	    where grade=( select max(grade) max 
	                  from sc c2,course 
			  where c2.cno=course.cno 
			  and c1.cno=c2.cno 
			  and cname='数据库'
			 )
	  );

方法2：最内层相关子查询，查出数据库课程的最高分。外层相关子查询，查出成绩grade=最高分max的学生学号。父查询sno in，连接学生表，系别表，查出学生信息+系名称

create view T5_1_6_1 as 
select sname,ssex,sdno,dname 
from student,dept 
where student.sdno=dept.dno 
and sno=( select sno 
	  from sc c1 
	  where grade=( select max(grade) max 
	                from sc c2,course 
	                where c2.cno=course.cno 
	                and c1.cno=c2.cno 
	                and cname='数据库'
	               )
	);

方法2：相关子查询，查出数据库课程的最高分。父查询查出成绩grade=最高分max的学生信息+系名称

create view T5_1_6_2 as 
select sname,ssex,sdno,dname 
from student,sc c1,dept 
where student.sdno=dept.dno 
and student.sno=c1.sno 
and grade=( select max(grade) max 
	    from sc c2,course 
	    where c2.cno=course.cno 
	    and c1.cno=c2.cno 
	    and cname='数据库'
	  );

查询结果:

(7) 列出每门课程成绩最高的学生的姓名。

方法1：最内层相关子查询，查出本课程的最高分。外层相关子查询，本课程成绩grade=本课程最高分max。父查询sno in查出学生姓名

create view T5_1_7 as 
select sname 
from student 
where sno in( select sno 
	      from sc c1 
	      where grade=( select max(grade) max 
			    from sc c2 
			    where c1.cno=c2.cno 
			    group by c2.cno
			  )
	    );

查询结果:

进阶问题：列出每门课程成绩的课程号，课程名，最高分学生的学号,姓名及其成绩

方法2：最内层相关子查询，查出本课程的最高分。外层相关子查询，本课程成绩grade=本课程最高分max。父查询sno=查出学生姓名

create view T5_1_7_1 as 
select course.cno,cname,student.sno,sname,grade 
from student,course,sc c1 
where student.sno=c1.sno 
and c1.cno=course.cno 
and student.sno=( select sno 
		  from sc c2 
		  where c1.cno=c2.cno 
		  and grade=( select max(grade) max 
			      from sc c3 
			      where c2.cno=c3.cno 
			      group by cno
			    )
		) 
order by c1.cno;

方法3：连接学生表、课程表、选课表，内层相关子查询查出最高分，本课程成绩grade=本课程最高分max

create view T5_1_7_2 as 
select course.cno,cname,student.sno,sname,grade 
from student,course,sc c1 
where student.sno=c1.sno 
and c1.cno=course.cno 
and grade=( select max(grade) max 
	    from sc c2 
	    where c1.cno=c2.cno 
	    group by cno
	  ) 
order by c1.cno;

(8) 列出和“李勇”同学选修了相同课程的学生的姓名。

方法1：两层相关子查询，查出与李勇的课程相同，但姓名不同的学生

create view T5_1_8 as 
select sname 
from student 
where sname!='李勇'
and sno in( select sno 
	    from sc 
	    where cno in( select cno 
			  from sc,student 
			  where student.sno=sc.sno 
			  and sname='李勇'
			 )
	   );

方法2：内层不相关子查询先连接学生表和选课表，选出李勇的课程。父查询连接学生表和选课表，选出课程相同的学生姓名（需去重）

create view T5_1_8_1 as 
select distinct sname 
from student,sc 
where student.sno=sc.sno 
and sname!='李勇'
and cno in( select cno 
	    from student,sc 
	    where student.sno=sc.sno 
	    and sname='李勇'
	  );

方法3：将李勇的课程cno查询为派生表，然后与学生表，选课表连接，选出课程相同的学生姓名（需去重）

create view T5_1_8_2 as 
select distinct sname 
from student,sc,(select cno from student,sc where student.sno=sc.sno and sname='李勇') t1 
where student.sno=sc.sno 
and sc.cno=t1.cno 
and sname!='李勇';

查询结果:

2. EXISTS 查询（查询结果存储为 T5_2_题目序号）

(1) 查询没有选修‘2002’号课程的学生的姓名。

方法1：相关子查询exists找到选'2002'课程的学生学号，父查询sno not in查出没有选'2002'课程的学生

create view T5_2_1 as 
select sname
from student 
where sno not in( select sno 
		  from sc c1 
		  where exists( select * 
				from sc c2 
				where c1.sno=c2.sno 
				and  cno='2002'
			       )
		 );

方法2：相关子查询not exists找到没有选'2002'课程的学生学号，父查询sno in查出选'2002'课程的学生

create view T5_2_1_1 as 
select sname 
from student 
where sno in( select sno 
	      from sc c1 
	      where not exists( select * 
				from sc c2 
				where c1.sno=c2.sno 
				and  cno='2002'
	                      )
	    );

方法3：父查询连接学生表、选课表，相关子查询连接选课表not exists找到没有选'2002'课程的学生学号

create view T5_2_1_2 as 
select distinct sname 
from student,sc c1 
where student.sno=c1.sno 
and not exists( select * 
		from sc c2 
		where c1.sno=c2.sno 
		and c2.cno='2002'
	      );

方法4：不相关子查询，集合作差，减去选'2002'课程的学生，父查询sno in查出没有选'2002'课程的学生

create view T5_2_1_3 as 
select sname 
from student 
where sno in( select sno 
	      from student 
	      minus select student.sno 
		    from student,sc 
		    where student.sno=sc.sno 
		    and cno='2002'
	    );

查询结果:

(2) 查询选修了所有课程的学生的姓名。

方法1：最内层相关子查询从课程表not exists查出没有人选的课程，外层相关子查询从选课表not exists否定没有人选的课程（双重否定，则课程都有人选，即选出所有课程），最终查出姓名

create view T5_2_2 as 
select sname 
from student 
where not exists( select * 
		  from course 
		  where not exists( select * 
				    from sc 
				    where sc.sno=student.sno 
				    and sc.cno=course.cno
				   )
		);

方法2：最内层相关子查询从选课表not exists查出没有人选的课程，外层相关子查询从课程表not exists否定没有人选的课程（双重否定，则课程都有人选，即选出所有课程），最终查出姓名

create view T5_2_2_1 as 
select sname 
from student 
where not exists( select * 
		  from sc 
		  where not exists( select * 
				    from course 
				    where student.sno=sc.sno 
				    and sc.cno=course.cno
				   )
		);

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

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

AN_drew

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
内蒙古大学 2017-2018《数据库原理与应用A》实验指导书作业五

（本文中蓝色字体为个人理解，非标准答案，仅供参考）作业一作业二作业三作业四作业五====================================================作业五子查询一．实验内容及说明在作业一的基础上，完成如下题目。并将每个实验结果均存储为一张视图...
复制链接

扫一扫