本文来自百度空间：http://hi.baidu.com/yht8890/blog/item/bf5253222e9542188b82a11e.html
--答案2010-10-19 16:22--1、查询“001”课程比“002”课程成绩高的所有学生的学号；SELECT A.S# FROM (SELECT a.s#,a.score FROM t_wolf_sc a WHERE a.c#='001') A,(SELECT b.s#,b.score FROM t_wolf_sc b WHERE b.c#='002') B WHERE A.Score>B.Score AND A.S#=B.S#;--2、查询平均成绩大于60分的同学的学号和平均成绩；SELECT a.s#,AVG(a.score) FROM t_wolf_sc a GROUP BY a.s# HAVING AVG(a.score)>60;--3、查询所有同学的学号、姓名、选课数、总成绩； (join on 后不能group by)SELECT A.*,B.sname FROM(SELECT a.s#,COUNT(a.C#),SUM(a.score) FROM t_wolf_sc a GROUP BY a.s#) A RIGHT JOIN t_wolf_student B ON A.S#=B.S#;--SELECT a.s#,b.sname,COUNT(a.C#),SUM(a.score) FROM t_wolf_sc a LEFT JOIN t_wolf_student b ON a.s#=b.s# GROUP BY a.s#;--4、查询姓“李”的老师的个数； SELECT COUNT(a.tname) FROM t_wolf_teacher a WHERE a.tname LIKE('李%');--5、查询没学过“叶平”老师课的同学的学号、姓名； SELECT c.s#,c.sname FROM t_wolf_student c WHERE c.s# NOT IN(SELECT distinct(a.s#) FROM t_wolf_sc a JOIN t_wolf_course b ON a.c#=b.c# JOIN t_wolf_teacher c ON b.t#=c.t# WHERE c.tname='叶平');--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； --法一：SELECT c.s#,c.sname FROM t_wolf_student c WHERE c.s# IN(SELECT a.s# FROM t_wolf_sc a WHERE a.c#='001'INTERSECTSELECT b.s# FROM t_wolf_sc b WHERE b.c#='002');--法二：SELECT a.s#,a.sname FROM t_wolf_student a,t_wolf_sc b WHERE a.s#=b.s# AND b.c#='001' AND EXISTS (SELECT * FROM t_wolf_sc c WHERE c.c#='002' AND c.s#=b.s#);--7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； /*SELECT a.s#,a.sname FROM t_wolf_student a,t_wolf_sc b WHERE a.s#=b.s# GROUP BY a.s#,a.sname,b.c# HAVING b.c# IN(SELECT d.c# FROM t_wolf_teacher c,t_wolf_course d WHERE c.tname='叶平' AND c.t#=d.t#);--答案错误*/SELECT t.s#,t.sname FROM t_wolf_student t WHERE t.s# IN(SELECT a.s# FROM t_wolf_sc a JOIN t_wolf_course b ON a.c#=b.c# JOIN t_wolf_teacher c ON b.t#=c.t# AND c.tname='叶平' GROUP BY a.s#HAVING COUNT(a.c#)=(SELECT COUNT(s.c#) FROM t_wolf_course s,t_wolf_teacher l WHERE s.t#=l.t# AND l.tname='叶平'))--8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； SELECT T.S# FROM (SELECT a.score,a.s# FROM t_wolf_sc a WHERE a.c#='001') T,(SELECT b.score,b.s# FROM t_wolf_sc b WHERE b.c#='002') FWHERE T.SCORE>F.SCORE AND T.S#=F.S#--9、查询所有课程成绩小于60分的同学的学号、姓名； SELECT b.s#,b.sname FROM t_wolf_student b WHERE b.s# IN(SELECT c.s# FROM t_wolf_sc c WHERE c.score<60);--10、查询没有学全所有课的同学的学号、姓名；(select 中的字段group 中需要分组)SELECT a.s#,a.sname FROM t_wolf_student a ,t_wolf_sc b WHERE a.s#=b.s# GROUP BY a.s#,a.sname HAVING COUNT(b.c#)<(SELECT COUNT(c.c#) FROM t_wolf_course c);