嵌套查询

嵌套查询
select snamefrom student where sno in( select sno
from sc
where cno=‘002’);

--查询与'刘晨'在同一个系学习的学生select*from `+student`where sdept in(select  sdeptfrom `student`where sname='刘晨')

查询选修课程名为’信息系统‘的学生学号和姓名
select sno,snameFROM `+student`where sno in (select snofrom sc where cno in(select cnofrom coursewhere cname='信息系统'));

查询所有被学生选修过的课程信息select *from course where cno in(select distinct cnofrom sc);

查询所有成绩优秀的学生姓名（所有成绩不小于90分）select `student`.sno,snamefrom `student`,scwhere `student`.sno=sc.sno and `+student`.sno not IN(select sno from sc where grade is null)group by `+student`.snohaving min(grade)>=90;
若一个学生只可能在一个系学习，且必须属于一个系select sno,sname,sdeptfrom `student`where Sdept=(select sdept from `student` where sname='刘晨');
找出每个学生超过他自己选修课程平均成绩的课程号select x.sno,x.cnofrom sc xwhere grade>=(select Avg(Grade)from sc ywhere y.sno=x.sno);
查询其他系中比计算机  某一   学生年龄小的学生select sname ,sage from `student`where sage< any (select sagefrom `student`where sdept='CS')AND sdept <>'CS';

select sname ,sagefrom studentwhere sage<(select max(sage)from studentwhere sdept=‘CS’)and sdept<>‘CS’;

所有学生

select sname,sagefrom studentwhere sage<ALL(select sagefrom studentwhere sdept=‘CS’)AND sdept<>‘CS’;