高级sql训练题

本文来自百度空间：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'

INTERSECT

SELECT 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') F

WHERE 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);