sql50题查询大全.docVIP

首先给出表结构，表结构需要大家去分析一下他们之间的逻辑关系。 tblStudent(SId,Sname,Sage,Ssex) 学生表 tblCourse(CId,Cname,TId) 课程表 tblScore(SId,CId,Score) 成绩表 tblTeacher(TId,Tname) 教师表 tblStudent(SID,Sname,Sage,Ssex) 学生表 tblCourse(CID,Cname,TID) 课程表 tblScore(SID,CID,Score) 成绩表 tblTeacher(TID,Tname) 教师表 问题： 1、查询“”课程比“”课程成绩高的所有学生的学号； SELECT a.SID FROM (SELECT SId,Score FROM tblScore WHERE CID=001) a, (SELECT SId,Score FROM tblScore WHERE CID=002) b WHERE a.Scoreb.Score AND a.SId=b.SId; 2、查询平均成绩大于分的同学的学号和平均成绩； SELECT SID,avg(Score) FROM sc GROUP BY SID having avg(Score) 60; 3、查询所有同学的学号、姓名、选课数、总成绩； SELECT tblStudent.SID,tblStudent.Sname,count(tblScore.CID),sum(Score) FROM tblStudent left Outer JOIN tblScore on tblStudent.SID=tblScore.SID GROUP BY tblStudent.SID,Sname 4、查询姓“李”的老师的个数； SELECT count(distinct(Tname)) FROM tblTeacher WHERE Tname like 李%; 5、查询没学过“叶平”老师课的同学的学号、姓名； SELECT tblStudent.SID,tblStudent.Sname FROM tblStudent WHERE SID not in (SELECT distinct( tblScore.SID) FROM tblScore,tblCourse,tblTeacher WHERE tblScore.CID=tblCourse.CID AND tblTeacher.TID=tblCourse.TID AND tblTeacher.Tname=叶平); 6、查询学过“”并且也学过编号“”课程的同学的学号、姓名； SELECT tblStudent.SID,tblStudent.Sname FROM tblStudent,tblScore WHERE tblStudent.SID=tblScore.SID AND tblScore.CID=001and exists( SELECT * FROM tblScore as tblScore_2 WHERE tblScore_2.SID=tblScore.SID AND tblScore_2.CID=002); 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名； SELECT SID,Sname FROM tblStudent WHERE SID in (SELECT SID FROM tblScore ,tblCourse ,tblTeacher WHERE tblScore.CID=tblCourse.CID AND tblTeacher.TID=tblCourse.TID AND tblTeacher.Tname=叶平 GROUP BY SID having count(tblScore.CID)=(SELECT count(CID) FROM tblCourse,tblTeacher WHERE tblTeacher.TID=tblCourse.TID AND Tname=叶平)); 8、查询课程编号“c001”的成绩比课程编号“”课程低的所有同学的学号、姓名； SELECT SID,Sname FROM (SELECT tblStudent.SID,tblStudent.Sname,Score ,(SELECT Score FROM tblScore tblScore_2 WHERE tblScore_2.SID=tblStudent.SID AND tblScore_2.CID=002) Sco