自测题：
1、查询哪些课程没有人选修列出课程号和课程名；
[code]select cno,cname
from course
where cno not in(
select distinct cno
from sc)[/code]
2、用子查询实现如下查询：
(1)查询选修了1号课程的学生姓名和所在系；
[code]select sname,sno
from student
where sno in(
select sno
from sc
where cno=1)[/code]
(2)查询“数据库”成绩在80分以上的学生的学号和姓名；
[code]Select sno,sname
From student
Where sno in(
select sno
from course,sc
where o=o and ame='数据库' and grade>=80)[/code](3)查询计算机系最高成绩。
[code]select top 1 grade
from student,sc
where student.sno=sc.sno and sdept='CS'
order by grade desc[/code]
3、查询同时选修了1号和2号课程的学生学号
[code]select sno
from sc
where cno=1 and sno in(
select sno
from sc
where cno=2)[/code]
4、查询选修了“离散数学”的学生姓名(连接查询)
[code]select sname
from student
where sno in(
select sno
from course,sc
where o=o and ame='离散数学')[/code]
5、查询选修课程名为“数据库”的学生姓名(子查询)
[code]select sname
from student
where sno in(
select sno
from course,sc
where o=o and ame='数据库')[/code]
6、查询与张天和张琪在同一个系的学生
[code]select *
from student
where sdept in(
select sdept
from student
where sname='张天' or sname='张琪')[/code]
查询与张天或张琪不在同一个系的学生
[code]select *
from student
where sdept not in(
select sdept
from student
where sname='张天' or sname='张琪')[/code]
7、查询比信息系所有学生年龄大的学生姓名
[code]select sname
from student s1
where s1.sage>all(
select sage
from student s2
where s2.sdept='CS')[/code]
8、查询比张天平均成绩高的学生姓名
[code]select sname
from student
where student.sno in(
select sno
from sc
group by sno
having avg(grade) >(
select avg(grade) as avg_grade2
from sc sc2,student
where student.sno=sc2.sno and sname='刘晨'
group by sc2.sno)
)[/code]9、查询比学号为200215121学生年龄大的学生
[code]select *
from student s1
where s1.sage>(
select sage
from student s2
where s2.sno='200215121')[/code]
10、查询各系总分最高的学生学号
[code]Select sdept,student.sno
from student,sc
where student.sno=sc.sno
group by sdept,student.sno
having sum(grade)>=all(
select sum(grade)
from student,sc
where student.sno=sc.sno and sdept=student.sdept
group by student.sno)[/code]
11、查询选修了以6号课程为先行课的所有课程的学生学号。
[code]select distinct sno
from sc
where o in(
select cno
from course
where cpno=6)[/code]
