sql试题分析和总结.docx

Student(S#,Sname,Sage,Ssex)学生表Course(C#,Cname,T#)课程表SC(S#,C#,score)成绩表Teacher(T#,Tname)教师表

Selectsid,sanmeformstudentwheresidin(selectsidfromteacher,sc,coursewhere

)

问题：

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

selecta.S#from(selects#,scorefromSCwhereC#=001)a,(selects#,scorefromSCwhereC#=002)b

wherea.scoreb.scoreanda.s#=b.s#;

2、查询平均成绩大于60分的同学的学号和平均成绩；selectS#,avg(score)

fromsc

groupbyS#havingavg(score)60;

3、查询所有同学的学号、姓名、选课数、总成绩；selectStudent.S#,Student.Sname,count(SC.C#),sum(score)fromStudentleftjoinSConStudent.S#=SC.S#

groupbyStudent.S#,Sname

4、查询姓“李”的老师的个数；selectcount(distinct(Tname))fromTeacher

whereTnamelike李%;

5、查询没学过“叶平”老师课的同学的学号、姓名；selectStudent.S#,Student.Sname

fromStudent

where S# not in (select distinct( SC.S#) from SC,Course,Teacher where

SC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname=叶 平);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#andSC.C#=001andexists(Select*fromSCasSC_2whereSC_2.S#=SC.S#andSC_2.C#=002);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

selectS#,SnamefromStudent

whereS#in(selectS#fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname=叶平groupbyS#havingcount(SC.C#)=(selectcount(C#)fromCourse,TeacherwhereTeacher.T#=Course.T#andTname=叶平));

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、

姓名；

SelectS#,Snamefrom(selectStudent.S#,Student.Sname,score,(selectscorefromSCSC_2whereSC_2.S#=Student.S#andSC_2.C#=002)score2

fromStudent,SCwhereStudent.S#=SC.S#andC#=001)S_2wherescore2score;9、查询所有课程成绩小于60分的同学的学号、姓名；

selectS#,SnamefromStudent

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# andscore60);

10、查询没有学全所有课的同学的学号、姓名；

selectStudent.S#,Student.SnamefromStudent,SC

whereStudent.S#=SC.S#groupby Student.S#,Student.Snamehavingcount(C#)

(selectcount(C#)fromCourse);

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；selectS#,SnamefromStu