来啦，今天分享的是两个表以上的连接查询。
三个表分别存着以下信息：
1.同一个数据库中的多表查询
（1）查询比“林红”年纪大的男学生的信息
select *
from student
where sex = '男' and birth <
(select birth from student
where sname = '林红');
（2）查询所有学生的课程信息，包括学号、姓名、课程号、成绩
select student.sno,sname,course.cno,cname,grade
from student,course ,sc2
where student.sno = sc2.sno and sc2.cno = course.cno;
（3）查询已选学生的学号、姓名、课程号、成绩
select student.sno,sname,cname,grade
from student,course ,sc2
where sc2.sno = student .sno and sc2.cno = course.cno;
（4）查询选修了“c语言程序设计”的学生的学号和姓名
select student.sno,sname
from student,course ,sc2
where sc2.sno = student .sno and sc2.cno = course.cno and cname = 'C语言程序设计';
（5）查询与“张虹”在同一个班级的学生的学号、姓名、家庭住址
select a.sno,a.sname,a.home_addr
from student a ,student b
where a.sname != '张虹' and a.classno = b.classno and b.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 not exists (
select * from course
where not exists (
select * from sc2
where student.sno= sc2.sno and sc2.cno = course.cno ));
（8）（选做）查询至少选修了学生“20110002”选修的全部课程的学生的学号、姓名
select sno,sname from student
where sno in (select distinct sno from sc2 x
where not exists (
select * from sc2 y
where y.sno = '20110002' and not exists (
select * from sc2 z
where z.sno = y.sno and z.cno = x.cno )));
（9）查询每个学生的学号、姓名、平均成绩
select a.sno,sname ,avg(grade)平均分
from student a left outer join sc2 b
on a.sno = b.sno
group by a.sno,sname ;
（10）查询选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学好、课程号、成绩、并按成绩从高到低排序
select sno,a.cno,grade from sc2 a ,course
where course.cno = a.cno and cname = '高数'
and a.grade > (
select MAX(grade) from sc2
where cno = '002')
order by grade desc;
（11）查询选修3门课以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列。
select sno,SUM(grade) 总成绩
from sc2 a
where grade >= 60 and sno in (
select sno
from sc2 group by sno
having COUNT (*) >= 3 )
group by sno
order by SUM(grade) desc;
（12）查询多于3名学生选修的并以3结尾的课程号的平均成绩
select cno ,AVG(grade) 平均成绩
from sc2
where cno in (
select cno
from sc2 group by cno
having COUNT(*) >3)
and cno like '%3'
group by cno ;
（13）查询最高分与最低分之差大于5分的学生的学号、姓名、最高分、最低分。
select a.sno,a.sname,MAX(grade)最高分,MIN(grade)最低分 from
student a ,sc2 b
where a.sno in (select sno
from sc2
group by sno
having (MAX(grade)-MIN(grade)) > 5)
and a.sno = b.sno
group by a.sno,sname
以上，全部。
文章转载自统计神谕，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
