老男孩mysql习题答案汇总

题目：http://www.cnblogs.com/wupeiqi/articles/5729934.html

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；

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

　　select a.sid,avg(number) from student a,score b where a.sid=b.student_id group by a.sid having avg(number) > 60;

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

　　select a.sid,sname,count(b.sid) as course_num,sum(b.number) as scores from student a left join score b on a.sid=b.student_id group by a.sid;

5、查询姓“李”的老师的个数；

　　select count(tid) from teacher where tname like '李%';

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

select student.sid,sname from student where student.sid not in (select student_id from score where course_id in (select cid from course,teacher where teacher_id=tid and tname='叶平'));

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

select student_id,sname from score,student where student.sid=student_id and course_id in (select cid from course,teacher where teacher_id=tid and tname='叶平') group by student_id;

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

　　select s1.sid from student s1 where (select count(*) from score s2 where s2.student_id=s1.sid and s2.course_id=1)>0 and (select count(*) from score s3 where s3.student_id=s1.sid 　　   and s3.course_id=2)>0;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；（若在表score上建立 student_id 和 course_id 的唯一索引）

select student_id,sname from score,student where student_id=student.sidnd course_id in (select cid from course,teacher where teacher_id=tid and tname=波多') group by student_id having count(*)>=2;

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

select a.student_id,sname,a.course_id,a.number,b.course_id,b.number from score a, score b,student where a.student_id=b.student_id and a.student_id=student.sid and a.course_id=1 and b.course_id=2 and a.number>b.number;

10、查询有课程成绩小于60分的同学的学号、姓名；

select student_id,sname from score,student where student_id=student.sid and number<60 group by student_id;

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

select student.sid,sname from student left join score on student.sid=student_id group by student.sid having count(*)<(select count(*) from course);

12、

转载于:https://www.cnblogs.com/wkk2/p/9547229.html