mysql 创建课程表_MySQL全方位练习（学生表 教师表 课程表 分数表）

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

select a.* from

(select * from sc a where a.cno='c001') a,

(select * from sc b where b.cno='c002') b

where a.sno=b.sno and a.score > b.score;

或

select * from sc a

where a.cno='c001'

and exists(select * from sc b where b.cno='c002' and a.score>b.score and a.sno = b.sno)

******************************************************************

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

select sno,avg(score) from sc group by sno having avg(score)>60;

******************************************************************

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

select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno

******************************************************************

4、查询姓“刘”的老师的个数；

select count(*) from teacher where tname like '刘%';

******************************************************************

5、查询没学过“谌燕”老师课的同学的学号、姓名；

select a.sno,a.sname from student a where a.sno not in

(

select distinct s.sno from sc s,

(

select c.* from course c ,

(select tno from teacher t where tname='谌燕') t

where c.tno=t.tno

) b

where s.cno = b.cno

)

或

select * from student st where st.sno not in

(

select distinct sno from sc s

join course c on s.cno=c.cno

join teacher t on c.tno=t.tno where tname='谌燕'

)

******************************************************************

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

select st.* from sc a

join sc b on a.sno=b.sno

join student st on st.sno=a.sno

where a.cno='c001' and b.cno='c002' and st.sno=a.sno;

******************************************************************

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

select st.* from student st join sc s on st.sno=s.sno

join course c on s.cno=c.cno

join teacher t on c.tno=t.tno

where t.tname='谌燕'

******************************************************************

8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

select * from