查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student where s_id in (select s_id from score where s_id not in (select s_id from score where s_id not in (select c_id from score where s_id='01')) group by s_id having count(*)=(select count(*) from score where s_id='01') and s_id != '01');查询没学过"张三"老师讲授的任一门课程的学生姓名
SELECT s.s_name FROM student s WHERE s.s_id not in (SELECT sc.s_id FROM score sc WHERE c_id in (SELECT c.c_id from course c LEFT JOIN teacher t on t.t_id=c.t_id WHERE t.t_name='张三'))查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.s_id,s.s_name,a.平均数 FROM student s right JOIN ( SELECT sc.s_id,COUNT(*) 不及格门数 ,round(avg(sc.s_score),1) "平均数" from score sc WHERE sc.s_score<60 GROUP BY sc.s_id ) a ON a.s_id=s.s_id检索"01"课程分数小于60，按分数降序排列的学生信息
select s.*,sc.s_score from student s inner join score sc on sc.s_id=s.s_id where s.s_id in(select s_id from score where s_score<60 and c_id=01) and c_id=01 order by sc.s_score desc按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc1.s_id,a.`平均分` ,sum(case sc1.c_id when 01 then sc1.s_score end) "语文" ,sum(case sc1.c_id when 02 then sc1.s_score end) "数学",sum(case sc1.c_id when 03 then sc1.s_score end) "英语" FROM score sc1 right JOIN (SELECT s_id,AVG(s_score) 平均分 FROM score GROUP BY s_id ORDER BY 平均分 DESC) a ON a.s_id=sc1.s_id GROUP BY sc1.s_id查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 – 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select c.c_id "课程ID",c.c_name "课程名称",max(s.s_score) "最高分",min(s.s_score) "最低分", round(avg(s.s_score)) "平均分", round(( select count(1) from score where s_score>=60 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "及格率", round(( select count(1) from score where s_score between 70 and 80 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "中等", round(( select count(1) from score where s_score between 80 and 90 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "优良", round(( select count(1) from score where s_score>=90 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "优秀" from course c left join score s on s.c_id=c.c_id group by c.c_id,c.c_name select c.c_id "课程ID",c.c_name "课程名称",max(s.s_score) "最高分",min(s.s_score) "最低分", round(avg(s.s_score)) "平均分", round(( select count(1) from score where s_score>=60 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "及格率", round(( select count(1) from score where s_score between 70 and 80 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "中等", round(( select count(1) from score where s_score between 80 and 90 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "优良", round(( select count(1) from score where s_score>=90 and c_id=c.c_id )/( select count(1) from score where c_id=c.c_id ),2) "优秀" from course c left join score s on s.c_id=c.c_id group by c.c_id,c.c_name按各科成绩进行排序，并显示排名(实现不完全)
select s1.c_id,s1.s_id,s1.s_score,count(s2.s_score)+1 "rank" from score s1 left join score s2 on s1.s_score<s2.s_score and s1.c_id=s2.c_id group by s1.c_id,s1.s_id,s1.s_score order by s1.c_id asc,s1.s_score desc查询学生的总成绩并进行排名
select t.s_id,t.总成绩,@rank:=@rank+1 "排名" from (SELECT sc.s_id,SUM(sc.s_score) 总成绩 from score sc GROUP BY sc.s_id ORDER BY SUM(sc.s_score) DESC) t,(select @rank:=0) r查询不同老师所教不同课程平均分从高到低显示
SELECT c_id,ROUND(AVG(s_score),2) 平均分 FROM score GROUP BY c_id ORDER BY AVG(s_score)查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT b.t_id,b.t_name,a.c_name,a.c_id,AVG(c.s_score)'mean' FROM course as a INNER JOIN teacher as b ON a.t_id=b.t_id INNER JOIN score as c ON c.c_id=a.c_id GROUP BY c.c_id ORDER BY mean DESC