实验四 复杂查询
教材使用的是雷景生版本的《数据库原理及应用（第2版）》
一、 实验目的：
掌握两个表以上的连接查询的应用，包括嵌套查询。
二、 实验内容：
初始表格三张：
Student表、Course表和SC表
1、 同一数据库中的多表查询
（1） 查询比“林红”年纪大的男学生的信息。
select *
from Student
where Sex ='男'
and Birth < any
(select Birth
from Student
where Sname='林红')
//这里的思路是先提取（或者叫做查询）林红的生日再使用比较运算符结果：
为空，因为没有比林红年龄大的男同学
·
·
·
（2） 查询所有学生的选课信息，包括学号、姓名、课号、课程名、成绩。
select Student.Sno,Sname,Course.Cno,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno结果：
这里要注意的是，如果代码的where后没有用自然连接的话会出现如下的数据冗杂情况（会变成三张表的笛卡尔乘积）
·
·
·
（3） 查询已选课学生的学号、姓名、课程名、成绩。
select SC.Sno,Student.Sname,Cname,Grade
from SC left outer join Student on(student.sno=sc.sno),Course
where Course.Cno=SC.Cno结果：
·
·
·
（4） 查询选修了“C语言程序设计”的学生的学号和姓名。
select Student.Sno,Sname
from Student,SC
where Student.Sno=SC.Sno and SC.Cno in
(select Cno
from Course
where Cname='C语言程序设计')结果：
·
·
·
（5） 查询与“张虹”在同一个班级的学生的学号、姓名、家庭住址。
select Sno,Sname,Home_addr
from Student
where Classno in
(select Classno
from Student
where Sname='张虹')结果：
·
·
·
（6） 查询其他班级中比“051”班所有学生年龄大的学生的学号、姓名。
select Sno,Sname
from Student
where Classno <>'051'
and Birth<all(select Birth
from Student
where Classno='051')结果：
·
·
·
（7） (选做)查询选修了全部课程的学生的姓名。
方法一：
select Sname
from Student
where Sno in
(select Sno from SC
group by Sno
having count(*) = (select count(*) from Course ))结果：
方法二：
select Sname
from Student
where not exists
(select *
from Course
where not exists
(select *
from SC
where Student.Sno=SC.Sno
and Course.Cno=SC.Cno))结果：
·
·
·
(8) (选做)查询至少选修了学生“20110002”选修的全部课程的学生的学号、姓名。
方法一：
select Student.Sno,Sname
from Student,SC
where Student.Sno=SC.Sno
and Cno =
(select Cno from SC
where Sno='20110002')结果：
方法二：
select distinct sx.Sno,Sname
from SC sx left outer join Student on(sx.Sno =Student.Sno )
where not exists
(select *
from SC sy
where sy.Sno ='20110002'and
not exists
(select * from SC sz
where sz.Cno=sy.Cno
and sz.Sno=sx.Sno) )结果：
·
·
·
（9） 查询学生的学号、姓名、学习课程名及课程成绩。
select Student.Sno,Sname,Cname,Grade
from Student,Course,SC
where Student.Sno=SC.Sno and Course.Cno=SC.Cno结果：
·
·
·
（10） 查询选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩,并按成绩从高到低排列。
select Sno,Cno,Grade
from SC
where Cno in(select Cno from Course
where Cname='高数' )
and Grade >all(select Grade from SC
where Cno='002')
order by Grade desc结果：
·
·
·
（11） 查询选修3门以上课程的学生的学号、总成绩(不统计不及格的课程)，并要求按总成绩的降序排列出来。
select Sno,sum(case when Grade>=60 then grade else 0 end)as'allg'
from SC
where Sno in
(select Sno from SC group by Sno having count(*)>3)
group by Sno
order by allg desc结果：
·
·
·
（12） 查询多于3名学生选修的并以3结尾的课程号的平均成绩。
select Cno,AVG(Grade)as 'pingjun'
from SC
where Cno in
(select Cno from SC group by Cno having COUNT(*)>3)
and Cno='%3'
group by Cno结果：
·
·
·
（13） 查询最高分与最低分之差大于5分的学生的学号、姓名、最高分、最低分。
select SC.Sno,Sname,MAX(Grade),MIN(Grade)
from SC,Student
where SC.Sno in
(select Sno
from SC
group by Sno
having COUNT(*)>2
and MAX(Grade)-MIN(Grade)>5)
and SC.Sno=Student.Sno
group by SC.Sno,Sname结果：
·
·
·
（14） 创建表Studen_other，结构同Student,输人若干记录，部分记录和Student表中的相同。
创建Student_other表如下：
①查询同时出现在Student表和Student_other表中的记录。
select * from Student
intersect
select * from Student_other结果：
②查询Student表和Student_other表中的全部记录。
select * from Student
select * from Student_other结果：
2、多个数据库间的多表查询
(选做)创建一个数据库Student_info_other,参数自定。
(1)当前数据库为Student_info,将Student_info数据库中的表Student_other复制到Student_info_other 中。
(2)查询同时出现在Student表和Student_info__other 数据库Student_other表中的记录。
3、外连接查询
（1）查询所有课程信息及其选课信息，包含未被学生选修的课程。
select *
from Course left outer join SC on(Course.Cno=SC.Cno )结果：
（2）查询所有学生信息，所有课程信息及其选课信息，包含未选修课程的学生及未被学生选修的课程。
select *
from Student
left join SC on (Student.Sno=SC.Sno)
left join Course on(SC.Cno=Course.Cno)结果：
