SQL进行复杂查询

题目：

对各表中的数据进行不同条件的连接查询和嵌套查询；

l 查询每个学生及其选课情况；

l 查询每门课的间接先修课

l 将STUDENT,SC进行右连接

l 查询有不及格的学生姓名和所在系

l 查询所有成绩为优秀（大于90分）的学生姓名

l 查询既选修了2号课程又选修了3号课程的学生姓名、学号；

l 查询和刘晨同一年龄的学生

l 选修了课程名为“数据库”的学生姓名和年龄

l 查询其他系比IS系任一学生年龄小的学生名单

l 查询其他系中比IS系所有学生年龄都小的学生名单

l 查询选修了全部课程的学生姓名

l 查询计算机系学生及其性别是男的学生

l 查询选修课程1的学生集合和选修2号课程学生集合的差集

l 查询李丽同学不学的课程的课程号

l 查询选修了3号课程的学生平均年龄

l 求每门课程学生的平均成绩

l 统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列

l 查询学号比刘晨大，而年龄比他小的学生姓名。

l 求年龄大于女同学平均年龄的男同学姓名和年龄

l 求年龄大于所有女同学年龄的男同学姓名和年龄

l 查询至少选修了95002选修的全部课程的学生号码

l 查询95001和95002两个学生都选修的课程的信息

答案：

a 查询每个学生及其选课情况；

Select student.*,cno,grade

From student,sc

Where student.sno = sc.sno;

l 查询每门课的间接先修课

SELECT course1.cno,course2.cpno
from course course1,course course2
where course1.cpno=course2.cno;

l 将STUDENT,SC进行右连接

SELECT student.*,sc.*
from student right join sc
on student.sno=sc.sno;

l 查询有不及格的学生姓名和所在系

SELECT sname,sdept
from student,sc
where student.sno=sc.sno and 
grade<60 ;

l 查询所有成绩为优秀（大于90分）的学生姓名

SELECT sname
from student,sc
where student.sno=sc.sno
group by sname
having min(grade)>=90;

l 查询既选修了2号课程又选修了3号课程的学生姓名、学号；

SELECT student.sno,student.sname
from student,sc
where student.sno=sc.sno 
and cno='002'and sc.sno in(SELECT sno
from sc where cno='003');

l 查询和刘晨同一年龄的学生

SELECT student.*
from student
where sage=(select sage from student where sname='刘晨') and sname<>'刘晨' ;

l 选修了课程名为“数据库”的学生姓名和年龄

SELECT sname,sage
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and cname='数据库';

l 查询其他系比IS系任一学生年龄小的学生名单

SELECT student.*
from student
where sage<any(SELECT sage from student where sdept ='is') and sdept <>'is';

☆查询其他系中比IS系所有学生年龄都小的学生名单

SELECT student.*
from student
where sage<all(SELECT sage from student where sdept ='is') and sdept <>'is';

l 查询选修了全部课程的学生姓名

select sname,sdept
from student
where not exists
(
select *from course
where not exists
(
select *
from sc
where sno=student.sno and cno=course.cno
)
);

l 查询计算机系学生及其性别是男的学生

SELECT *
from student 
where sdept='is' and ssex='男';

l 查询选修课程1的学生集合和选修2号课程学生集合的差集

SELECT sno
from sc 
where cno='001'and sno in
(SELECT sno
from sc 
where cno<>'002');

l 查询李丽同学不学的课程的课程号

SELECT distinct cno
from sc
where cno not in
(select cno 
from student,sc
where student.sno=sc.sno and sname='李丽' );

l 查询选修了3号课程的学生平均年龄

SELECT avg(sage)
from student,sc
where student.sno=sc.sno and cno='003';

l 求每门课程学生的平均成绩

SELECT cno,avg(grade)
from sc
group by cno;

l 统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列

SELECT cno,count(sno)
from sc 
group by cno
having count(sno)>3
order by count(sno) asc,cno desc;

l 查询学号比刘晨大，而年龄比他小的学生姓名 .

SELECT *
from student
where 
sno>(select sno from student where sname='刘晨') 
and sage<(select sage from student where sname='刘晨') ;

l 求年龄大于女同学平均年龄的男同学姓名和年龄

SELECT sname,sage
from student
where 
sage>(select avg(sage) from student where ssex='女') 
and ssex='男';

l 求年龄大于所有女同学年龄的男同学姓名和年龄

SELECT sname,sage
from student
where 
sage>all(select sage from student where ssex='女') 
and ssex='男';

l 查询至少选修了95002选修的全部课程的学生号码

select sno
from sc
where 
cno in (SELECT cno from sc where sno='08002') 
and sno <>'08002'
group by sno
having count(cno)=(select count(cno) from sc where sno="08002");

l 查询95001和95002两个学生都选修的课程的信息

SELECT course.cno,cname,cpno,credit
from sc,course 
where sc.cno=course.cno and
sno='08001' and
sc.cno in (select cno from sc where sno="08002");