用mySQL创建学生选课表_50个常用sql语句 网上流行的学生选课表的例子

50个常用sql语句

建表：

--学生表tblStudent(编号StuId、姓名StuName、年龄StuAge、性别StuSex)

--课程表tblCourse(课程编号CourseId、课程名称CourseName、教师编号TeaId)

--成绩表tblScore(学生编号StuId、课程编号CourseId、成绩Score)

--教师表tblTeacher(教师编号TeaId、姓名TeaName)

问题：

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

select a.StuId from (select StuId,Score from tblScore where CourseId='001') a,(select StuId,Score

from tblScore where CourseId='002') b

where a.Score>b.Score and a.StuId=b.StuId;

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

select StuId,avg(Score)

from tblScore

group by StuId having avg(Score) >60;

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

select tblStudent.StuId,tblStudent.StuName,count(tblScore.CourseId),sum(Score)

from tblStudent left Outer join tblScore on tblStudent.StuId=tblScore.StuId

group by tblStudent.StuId,StuName

4、查询姓“李”的老师的个数;

select count(distinct(TeaName))

from tblTeacher

where TeaName like '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名;

select tblStudent.StuId,tblStudent.StuName

from tblStudent

where StuId not in (select distinct( tblScore.StuId) from tblScore,tblCourse,tblTeacher where tblScore.CourseId=tblCourse.CourseId and tblTeacher.TeaId=tblCourse.TeaId and tblTeacher.TeaName='叶平');

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

select tblStudent.StuId,tblStudent.StuName from tblStudent,tblScore where tblStudent.StuId=tblScore.StuId and tblScore.CourseId='001'and exists( Select * from tblScore as tblScore_2 where tblScore_2.StuId=tblScore.StuId and tblScore_2.CourseId='002');

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

select StuId,StuName

from tblStudent

where StuId in (select StuId from tblScore ,tblCourse ,tblTeacher where tblScore.CourseId=tblCourse.CourseId and tblTeacher.TeaId=tblCourse.TeaId and tblTeacher.TeaName='叶平' group byStuId having count(tblScore.CourseId)=(select count(CourseId) from tblCourse,tblTeacher where tblTeacher.TeaId=tblCourse.TeaId and TeaName='叶平'));

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

Select StuId,StuName from (select tblStudent.StuId,tblStudent.StuName,Score ,(select Score from tblScore tblScore_2 where tblScore_2.StuId=tblStudent.StuId and tblScore_2.CourseId='002') Score2

from tblStudent,tblScore where tblStudent.StuId=tblScore.StuId and CourseId='001') S_2 where Score2

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

select StuId,StuName

from tblStudent

where StuId not in (select tblStudent.StuId from tblStudent,tblScore where S.StuId=tblScore.StuId and Score>60);

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

select tblStudent.StuId,tblStudent.StuName

from tblStudent,tblScore

where tblStudent.StuId=tblScore.StuId group by tblStudent.StuId,tblStudent.StuName having count(CourseId)

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

select StuId,StuName from tblStudent,tblScore where tblStudent.StuId