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