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

(本文中蓝色字体为个人理解,非标准答案,仅供参考)

 

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

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

作业五 子查询

 

一.实验内容及说明

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

方式如下: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
				   )
		);

 

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

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

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

AN_drew

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值