(本文中蓝色字体为个人理解,非标准答案,仅供参考)
作业一 作业二 作业三 作业四 作业五
=======================================
作业五 子查询
一.实验内容及说明
在作业一的基础上,完成如下题目。并将每个实验结果均存储为一张视图表。
方式如下: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
)
);