用SQL语言进行复杂查询：对各表中的数据进行不同条件的连接查询和嵌套查询： １）查询每个学生及其选课情况； ２）查询每门课的间接先修课

对各表中的数据进行不同条件的连接查询和嵌套查询：
１）查询每个学生及其选课情况；
２）查询每门课的间接先修课
３）将STUDENT,SC进行右连接
４）查询有不及格的学生姓名和所在系
５）查询所有成绩为优秀（大于90分）的学生姓名
６）查询既选修了2号课程又选修了3号课程的学生姓名、学号；
７）查询和刘晨同一年龄的学生
８）选修了课程名为“数据库”的学生姓名和年龄
９）查询其他系比IS系任一学生年龄小的学生名单
１０）查询其他系中比IS系所有学生年龄都小的学生名单
１１）查询选修了全部课程的学生姓名
１２）查询计算机系学生及其性别是男的学生
１３）查询选修课程1的学生集合和选修2号课程学生集合的差
１４）查询李丽同学不学的课程的课程号
１５）查询选修了3号课程的学生平均年龄
１６）求每门课程学生的平均成绩
１７）统计每门课程的学生选修人数（超过3人的才统计）。要求输出课程号和选修人数，结果按人数降序排列，若人数相同，按课程号升序排列
１８）查询学号比刘晨大，而年龄比他小的学生姓名。
１９）求年龄大于女同学平均年龄的男同学姓名和年龄
２０）求年龄大于所有女同学年龄的男同学姓名和年龄
２１）查询至少选修了95002选修的全部课程的学生号码

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

1.
SELECT  student.*,cno,grade
from student,sc
where student.sno=sc.sno 
;
2.
SELECT course1.cno,course2.cpno
from course course1,course course2
where course1.cpno=course2.cno
;
3.
SELECT student.*,sc.*
from student right join sc
on student.sno=sc.sno
;
4.
SELECT sname,sdept
from student,sc
where student.sno=sc.sno and 
grade<60 
;
5.
SELECT sname
from student,sc
where student.sno=sc.sno
group by sname
having min(grade)>=90
;
6.
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')
;
7.
SELECT student.*
from student
where sage=(select sage from student where sname='刘晨') and sname<>'刘晨' 
;
8.
SELECT sname,sage
from student,sc,course
where student.sno=sc.sno and course.cno=sc.cno and cname='数据库'
;
9.
SELECT student.*
from student
where sage<any(SELECT sage from student where sdept ='is') and sdept <>'is'
;
10.
SELECT student.*
from student
where sage<all(SELECT sage from student where sdept ='is') and sdept <>'is'
;
11.
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
)
)
12.
SELECT *
from student 
where sdept='is' and ssex='男'
;
13.
SELECT sno
from sc 
where cno='001'and sno in
(SELECT sno
from sc 
where cno<>'002')
;
14.
SELECT distinct cno
from sc
where cno not in
(select cno 
from student,sc
where student.sno=sc.sno and sname='李丽' )
;
15.
SELECT avg(sage)
from student,sc
where student.sno=sc.sno and cno='003'
;
16.
SELECT cno,avg(grade)
from sc
group by cno
;
17.
SELECT cno,count(sno)
from sc 
group by cno
having count(sno)>3
order by count(sno) asc,cno desc
;
18.
SELECT *
from student
where 
sno>(select sno from student where sname='刘晨') 
and sage<(select sage from student where sname='刘晨') 
;
19.
SELECT sname,sage
from student
where 
sage>(select avg(sage) from student where ssex='女') 
and ssex='男'
;
20.
SELECT sname,sage
from student
where 
sage>all(select sage from student where ssex='女') 
and ssex='男'
;
21.
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")
;
22.
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")
;