MySql经典50题（11~20）

11、查询没有学全所有课程的同学的信息

select s.* from student as s where s.sno not in
(select c.sno from sc as c where c.cno =01) or 
s.sno not in (select c.sno from sc as c where c.cno =02) or
s.sno not in (select c.sno from sc as c where c.cno =03)

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select s.* from student as s where s.sno  in
(select distinct c.sno from sc as c where c.cno in
(select c.cno from sc as c where c.sno=01))

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select s.* from Student as s,sc as c
where s.sno = c.sno and 
s.sno !='01'
group by s.sno
having count(cno)=(select count(cno) from sc where sno='01');

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select s.sname from student as s
where s.sno not in
(select sc.sno from sc where sc.cno in
(select cs.cno from course as cs where cs.tno in
(select t.tno from teacher as t where t.tname='张三')))

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.sno,s.sname,avg(c.score) as '平均成绩',sum(score<60) as bjg
from student as s ,sc as c
where s.sno = c.sno
group by s.sname having bjg>=2

16、检索"01"课程分数小于60，按分数降序排列的学生信息

select s.*, c.score from student as s,sc as c
where s.sno = c.sno and c.score<60 and c.cno='01'
order by c.score desc 

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select s.sname,c.score,avg(c.score)as'平均成绩'
from student as s,sc as c
where s.sno = c.sno
group by c.sno
order by avg(c.score) desc

18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

select cs.cno,cs.cname,
	max(score) as '最高分',min(score) as '最低分',
	avg(score) as '平均分' ,
	count(score>=60 or null)/count(c.cno)*100 '及格率',
	count(score>=70 and score<80 or null)/count(c.cno)*100 '中等率',
	count(score>=80 and score<90 or null)/count(c.cno)*100 '优良率',
	count(score>=90 or null)/count(c.cno)*100 '优秀率'
	from course as cs , sc as c
where c.cno = cs.cno group by cs.cno

19、按各科成绩进行排序，并显示排名

select c.sno,c.cno,c.score ,count(sc2.score)+1 as rank
from sc c 
left join sc sc2 on c.cno = sc2.cno and c.score < sc2.score
group by c.cno,c.sno,c.score
order by c.cno,rank asc;