1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数#解题技巧，先写框架select*from sc a left join student don a.sid=d.sid inner join sc b on a.sid=b.sid and a.cid='01'and b.cid='02' wherea.score>b.score;
2.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null)select a.sid,a.sname,count(b.cid) as cons,sum(b.score) as cours from student a left join sc b on a.sid=b.sidgroup by a.sid;
3.查询学过「张三」老师授课的同学的信息select b.* fromsc a left join student b on a.sid=b.sidwhere cid in (select cid from course where tid=(select tid from teacher where tname='张三'))group by sid;
4.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息select distinct b.*fromsc a left join student b on a.sid=b.sidwhere cid in(select cidfrom sc where sid='01');
5.查询没学过"张三"老师讲授的任一门课程的学生姓名select * from student a where a.sid not in (select sid from sc a left join course c on a.cid=c.cid inner join teacher d on c.tid=d.tid and d.tname='张三');
6.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩select*,avg(score) as avg_scofrom sc a left join student bon a.sid=b.sidwhere a.score<60group by a.sid having count(cid)>=2;
7.按各科成绩进行排序，并显示排名， Score 重复时也继续排名select sid,cid,score,@rank:=@rank+1 as rn from sc , (select @rank:=0) as t order by score desc;
8.查询每门课程被选修的学生数select cid,count(1) as cons from sc group by cid;
9.查询出只选修两门课程的学生学号和姓名select student.SId,student.Snamefrom sc,student where student.SId=sc.SId group by sc.SId having count(*)=2;
10.查询男生、女生人数select count (1) as cons,ssex from student groupby ssex;
11.查询名字中含有「风」字的学生信息select * from student where sname like'%风%';
12.查询 1990 年出生的学生名单select * from student where year(sage)='1990';
13.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数select * from sc a left join student b on a.sid=b.sid left join course con a.cid=c.cidwhere a.score>70;
14.求每门课程的学生人数select sc.CId,count(*) as 学生人数 fromsc group by sc.CId;