实验四 复杂查询

实验四 复杂查询

教材使用的是雷景生版本的《数据库原理及应用（第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)

结果：