java面试题 sql_javaSql面试题（10题）

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

SELECT tblstudent.StuId

from tblstudent

where

(SELECT TC.Score FROM tblscore TC WHERE TC.CourseId='001'and tblstudent.StuId=tc.StuId)

>

(SELECT tb.Score FROM tblscore Tb WHERE Tb.CourseId='002' and tblstudent.StuId=Tb.StuId)

查询平均成绩大于60分的同学的学号和平均成绩

select Student.stuId,avg(Scores.score)

from Student,Scroes

where Studen.stuId=Scores.stuId

Group by Student.stuId

having avg(Scores.score)>60;

聚合函数是不能连接在where子句后面的。

查询所有同学的学号，姓名，选课数，总成绩

Select StuId,StuName,

(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId)SelCourses,

(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId) SumScore

From tblStudent s1

查询姓李的老师的个数

select count(Teacher.TeacherId)

from Teacher

where Teacher.TeacherName like "李%";

查询所有课程成绩小于60分的 同学的学号，姓名

select tblstudent.StuId,tblstudent.StuName

from tblstudent

where tblstudent.StuId NOT IN

(select tblscore.StuId from tblscore where tblstudent.StuId=tblscore.StuId AND tblscore.Score>60)