2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
思路：
获取所有有生物课程的人（学号，成绩） – 临时表
获取所有有物理课程的人（学号，成绩） – 临时表
根据【学号】连接两个临时表：
学号 物理成绩 生物成绩
然后再进行筛选
select A.student_id, a, b
from
(select score.student_id, number as a from score left join course on course.cid = score.corse_id where cname=‘生物‘) as A
left join
(select score.student_id, number as b from score left join course on course.cid = score.corse_id where cname=‘物理‘) as B
on
A.student_id = B.student_id where a > if(isnull(b),0,b);
3、查询平均成绩大于60分的同学的学号和平均成绩；
思路：
根据学生分组，使用avg获取平均值，通过having对avg进行筛选
select student_id, avg(number) from score group by student_id having avg(number)>60;
4、查询所有同学的学号、姓名、选课数、总成绩；
select score.student_id, student.sname, count(score.corse_id), sum(score.number)
from
score
left join
student on student.sid = student_id
group by
student_id; 5、查询所姓李的老师个数
select count(tid) from teacher where tname like ‘波%‘; 6、查询没学过“叶平”老师课的同学的学号、姓名；
思路：
先查到“李平老师”老师教的所有课ID
获取选过课的所有学生ID
学生表中筛选
select * from student where sid not in (
select DISTINCT student_id from score where score.corse_id in (
select cid from course left join teacher on course.teacher_id = teacher.tid where tname=‘波多‘)
); 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名
思路：
先查到既选择001又选择002课程的所有同学
根据学生进行分组，如果学生数量等于2表示，两门均已选择
法一：
select student.sid, student.sname from
(select student_id, corse_id from score where corse_id=1 or corse_id=2) as B
left join student on B.student_id = student.sid group by student_id having count(student_id)>1;
法二：
select student.sid, student.sname from score left join student on student.sid= student_id
where corse_id= 1 or corse_id=2 group by student_id having count(corse_id)>1;
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名
思路：
同上,只不过将1和2变成in（老师课程）
select student_id, student.sname from score left join student on student.sid=student_id where corse_id in
(select course.cid from course left join teacher on course.teacher_id=teacher.tid where tname=‘波多‘)
group by student_id having count(corse_id) = (select count(course.cid) from course left join teacher on
course.teacher_id=teacher.tid where tname=‘波多‘); 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
思路：
先找出分别找出课程1 和2 的成绩
在连表 （id ，A.成绩，B.成绩） 比较成绩大小
找出学生id 然后用 in
select student.sid, student.sname from student where sid in (
select A.student_id as c from (select student_id,number from score where corse_id=1) as A
left join (select student_id, number from score where corse_id=2) as B on A.student_id=B.student_id
where A.number> if(isnull(B.number),0,B.number)
); 10、查询有课程成绩小于60分的同学的学号、姓名；
思路：
先在score表找到成绩小于60的学生学号
再用 sid in
select student.sid, student.sname from student where sid in (
select student_id from score where number<60
); 11、查询没有学全所有课的同学的学号、姓名；
思路：
在分数表中根据学生进行分组，获取每一个学生选课数量
如果数量==总课程数量，表示已经选择了所有课程
select student.sid from student where sid not in (
select student.sid from score
left join student on student_id=student.sid group by student_id having
count(corse_id)=(select count(1) from course));
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
思路：
法一：
获取 001 同学选择的所有课程
获取课程在其中的所有人以及所有课程
根据学生筛选，获取所有学生信息
再与学生表连接，获取姓名
student.sid, student.sname from score left join student on score.student_id=student.sid
where student_id !=1 and corse_id in (select corse_id from score where student_id=1);
法二：
select student.sid, student.sname from
(select * from score where corse_id in (select corse_id from score where student_id=1)) as A
left join student on student.sid = A.student_id where student_id !=1; 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
思路：
法一：
同12
先找到学过001的所有人
然后个数=001所有的学科个数
select student.sid, student.sname from
(select * from score where corse_id in (select corse_id from score where student_id=1)) as A
left join student on student.sid= A.student_id where A.student_id !=1 group by student_id having
count(corse_id)=(select count(corse_id) from score where student_id=1) ;
法二：
select student_id,sname, count(course_id)
from score left join student on score.student_id=student.sid
where stduent_id !=1 and score_id in (select corse_id from score where student_id=1) group by student_id having
count(corse_id)=(select count(corse_id) from score where student_id=1);
14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
思路：
先找到002学过的课程号
在用 in 找到学过所有相同的课程号的人
在通过连表，分组，计数 把相同的人找出来
select student.sid,student.sname from (select * from score where corse_id in (select corse_id from score where student_id=1)) as A
left join student on student.sid = A.student_id where A.student_id!=2 group by A.student_id having
count(A.student_id)= (select count(corse_id) from score where student_id=2); 15、删除学习“叶平”老师课的SC表记录；
思路：
先连表查姓名为叶平的老师 然后在删除
delete from score where corse_id in (
select cid from course left join teacher on course.teacher_id=teacher.tid where teacher.tname=‘饭岛‘);
16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
思路：
由于insert 支持
inset into tb1(xx,xx) select x1,x2 from tb2;
所有，获取所有没上过002课的所有人，获取002的平均成绩
insert into score(student_id, course_id, number) select sid,2,(select avg(number) from score where score_id=2)
from student where sid not in (select stduent_id from score where score_id=2);
17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
思路：
连表查学生id,有效课程数,平均分
通过已查询到的学生id为约束条件，再分别连表查询课程号们
select student.sid,
(select avg(number) from score left join course on course.cid = score.corse_id where course.cname=”语文” and student_id = student.sid) as 语文,
(select avg(number) from score left join course on course.cid = score.corse_id where course.cname=”数学” and student_id = student.sid) as 数学,
(select avg(number) from score left join course on course.cid = score.corse_id where course.cname=”英语” and student_id = student.sid) as 英语,
count(student_id)as 有效课程数,
avg(number)as 平均成绩
from score
left join student on student.sid = student_id
group by student_id order by avg(number) desc; 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
思路：
法一：
通过联表把课程表和成绩表关联
再通过corse_id分组 找出最大最小成绩
select course.cid,max(number),min(number) from score left join course on course.cid = corse_id
group by corse_id;
法二：
select corse_id,max(number),min(number) from score group by corse_id;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
思路：
case when .. then
select
corse_id,
avg(number) as avgnum,
sum(case when score.number > 60 then 1 else 0 END)/count(1)*100 as percent,
from score group by corse_id order by avgnum asc, percent desc;
20、课程平均分从高到低显示（现实任课老师）；
思路：
联表 分组
select
avg(if (isnull(score.number),0,score.number)),
teacher.tname
from course
left join score on course.cid = score.corse_id
left join teacher on course.teacher_id = teacher.tid
group by score.corse_id;