mysql练习2

14、查询每门课程被选修的次数

　　SELECT course_id,COUNT(sid) FROM score GROUP BY course_id

15、查询之选修了一门课程的学生姓名和学号

　　SELECT sname FROM student WHERE sid in (
　　SELECT student_id FROM score GROUP BY student_id HAVING COUNT(sid)=1)

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

　　SELECT DISTINCT num FROM score ORDER BY num DESC

17、查询平均成绩大于85的学生姓名和平均成绩

　　SELECT sname,AVG(num) FROM score INNER JOIN student ON score.student_id=student.sid
　　GROUP BY student_id HAVING AVG(num)>85

18、查询生物成绩不及格的学生姓名和对应生物分数

　　SELECT sname 姓名,num 生物成绩 FROM student INNER JOIN (
　　SELECT student_id,num FROM score WHERE course_id=(SELECT cid FROM course WHERE cname="生物") AND num<60)AS s
　　ON student.sid=s.student_id

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

　　

　　SELECT sname FROM student INNER JOIN (

　　SELECT MAX(s.a),s.student_id FROM (
 　　SELECT student_id,AVG(num) a FROM score WHERE course_id in (
  　　SELECT cid FROM course WHERE course.teacher_id=(
   　　SELECT tid FROM teacher WHERE tname="李平老师"))
 　　GROUP BY student_id
 　　ORDER BY AVG(num) DESC)AS s

　　)AS s2 ON student.sid=s2.student_id

转载于:https://www.cnblogs.com/guomeina/p/7252176.html