摘自：http://www.iteye.com/topic/194446
设有学生选取修课程数据库：S（S#, SNAME, AGE, SEX, DEPARTMENT, ADDRESS, BIRTHPLACE）SC(S#, C#, GRADE)C(C#, CNAME, TEACHER)(1) 李老师所教的课程号、课程名称；
1 SELECT C#, CNAME; 2 FROM C; 3 WHERE TEACHER=”李”
(2) 年龄大于23岁的女学生的学号和姓名；
1 SELECT S#, SNAME; 2 FROM S; 3 WHERE AGE>23 AND SEX=”女”
(3) “李小波”所选修的全部课程名称；
1 SELECT CNAME; 2 FROM S, SC; 3 WHERE S.S#=SC.S# AND SNAME=’李小波’
(4) 所有成绩都在80分以上的学生姓名及所在系；
1 SELECT SNAME, DEPARTMENT; 2 FROM S, SC; 3 WHERE S.S#=SC.S# AND SC.S# ; 4 not in ( SELECT S#; 5 FROM SC; 6 WHERE GRADE<80 )
(5) 没有选修“操作系统”课的学生的姓名；
1 SELECT SNAME ; 2 FROM S ; 3 WHERE S# NOT IN 4 (SELECT S# ; 5 FROM SC, C ; 6 WHERE SC.C#=C.C# AND CNAME=’操作系统’ )
(6) 与“李小波”同乡的男生姓名及所在系；
1 Select cname from c where c# in (select C# from sc where s# in 2 (select s# from s where sname=’李小波’))
(7) 英语成绩比数学成绩好的学生；
1 Select sname from S 2 Where s# in (select X.s# from SC X inner join SC Y on X.S#=Y.S# 3 Where X.grade > Y.grade and X.C#=(select c# from c where cname=’英语’) 4 And Y.c#=(select c# from c where cname=’数学’));
(8) 选修同一门课程时，女生比男生成绩好的学生名单；
1 Select sname from S 2 Where s# in (select X.s# from SC X inner join SC Y on X.S#=Y.S# 3 Where (X.C#=Y.C#) and (X.grade > Y.grade) and X.s#=(select s# from s where sex=’女’) 4 And Y.s#=(select s# from s where sex=’男’));
(9) 至少选修两门以上课程的学生姓名、性别；
Select sname,sex from s where s# in (select s# from sc group by s# having count(*)>=2);
(10) 选修了李老师所讲课程的学生人数；
Select count(*) from sc where c# in (select c# from c where teacher=’李老师’);
(11) 没有选修李老师所讲课程的学生；
1 Select sname from s where s# in (select s# from sc 2 where c# not in (select c# from c where teacher=’李老师’));
(12) “操作系统”课程得最高分的学生姓名、性别、所在系；
1 Select sname,sex,deptment from s where s# in (select s# from sc 2 where (c# in (select c# from c Where cname=’操作系统’)) 3 and grade in (select max(grade) from sc group by c#));
转载于:https://www.cnblogs.com/baiyixianzi/archive/2012/08/19/sql3.html
相关资源：formSelects.zip