SQL练习题

1-10

1. 查询" 01 “课程⽐” 02 "课程成绩⾼的学⽣的信息及课程分数select
思路：先得到01课程比02课程分数高的学生id+分数组成的表，再将该表与学生信息表联结

select stu.Sid,stu.Sname,stu.Ssex,s3.score
from student as stu
join (select s1.Sid,s1.score from (select Sid,score from sc where Cid=1) as s1 join (select Sid,score from sc where Cid=2) as s2 on s1.Sid = s2.Sid and s1.score>s2.score) as s3
on stu.SId=s3.Sid;
2. 查询同时存在" 01 “课程和” 02 "课程的情况
思路：先后得到学习01/02课程的学生表，再进行两表之间学生id匹配的条件判断可以得到符合题意的新表。

select s1.Sid,s1.score
from (select * from sc where sc.CId=01) as s1
JOIN (SELECT * from sc where sc.CId=02) as s2
on s1.SId=s2.Sid;
3. 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
思路：先得到拥有02课程的学生，用right join与拥有01课程的学生匹配得出符合题意的表。

select s2.,s1.
from (select * from sc where Cid=02) as s1
right join (select * from sc where Cid=01) as s2
on s1.sId=s2.sid;
4. 查询不存在" 01 “课程但存在” 02 "课程的情况
思路：先排除学习过01课程的学生，再确认学过02课程的学生

select *
from sc
where sid not in(select sid from sc where cid=01)
and cid=02;
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
思路：先得到平均表，再将学生表与平均表进行学生id的匹配和分数的判断

select s1.sid,s1.sname,s2.avg from student as s1
join (select sid,avg(score) as avg from sc GROUP BY sid) as s2
on s1.sid=s2.sid and s2.avg>=60;
6.查询在 SC 表存在成绩的学⽣信息
思路:通过分组sid将sc表的重复sid整合，其次让学生表与整合后的sc表匹配sid可以得到符合题意的表

select s1.*
from student as s1
join (select sc.SId from sc group by sid) as s2
on s1.SId=s2.SId;
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
思路：通过分组sid将sc表的重复sid整合，用聚集函数得到选课总数，课程总成绩，其次让学生表left join整合后的sc表匹配sid得到符合题意的表
select s1.SId,s1.Sname,s2.cids,s2.scores
from student as s1 left
join (select SId,count(cid) as cids,sum(score) as scores from sc group by sid) as s2
on s1.SId=s2.SId;
8.查询「李」姓⽼师的数量
思路：查询以李开头的老师名字即可
select count(*)
from teacher
where tname like"李%";
9.查询学过「张三」⽼师授课的同学的信息
思路：从teacher表得到名字是张三的老师id，让老师id与课程表的id相匹配得到课程id，再将课程id与成绩表的id相匹配得到学习过张三老师课程的学生id，最后将学生id与学生信息表匹配得到学生信息

select s1.*
from student as s1
join (select sid from sc join (select cid from course join (select tid from teacher where tname=“张三”)as t1 on course.tid=t1.tid) as c1 on sc.cid=c1.cid) as s2
on s1.SId=s2.sid;
10.查询没有学全所有课程的同学的信息
思路：想分组然后获取sc表中同学学习的课程数，然后将student表与新获得的表匹配，排除课程数在3的同学。

select s1.*
from student as s1
join (select sid,count(cid) as cids from sc group by sid) as s2
on s1.sid =s2.sid
and cids<3;

11-20

11.查询⾄少有⼀⻔课与学号为" 01 "的同学所学相同的同学的信息
思路：1获取到01同学学习课程的id；2获取sc表中与01同学有过相同课程id的同学id，这里需要分组；3获取student表中与得到的同学id相同的同学信息

select s1.*
from student as s1 join(select sc.sid from sc join(select cid from sc where sid=01) as s1 on sc.cid = s1.cid and sc.sid !=01 GROUP BY sid) as s2
on s1.sid=s2.sid;
12.查询和"