SQL进阶练习题1-5
大背景和建表、插入语句就不啰嗦了，参考第一篇。
四张表概要：
- 学生表
student(sid,sname,sage,ssex) --sid 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别 - 课程表
course(cid,cname,tid) --cid 课程编号,cname 课程名称,tid 教师编号 - 教师表
teacher(tid,tname) --tid 教师编号,tname 教师姓名 -
成绩表
sc(sid,cid,score) --sid 学生编号,cid 课程编号,score 分数
为了方便查看，我把四个表截了图：
student
course
teacher
sc
题目：
- 查询" 01 "课程比" 02 "课程成绩高的所有学生的学号
- 查询同时存在" 01 "课程和" 02 "课程的情况
- 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
- 查询不存在" 01 "课程但存在" 02 "课程的情况
- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
sql
查询" 01 "课程比" 02 "课程成绩高的所有学生的学号
--比较成绩的要素有三个：成绩、科目、同一人。
--成绩和科目在一张表里，所以需要把sc表别名成两张表进行比较
select a.sid from
(select * from sc where cid='01') a,(select * from sc where cid='02') b
where a.score>b.score and a.sid=b.sid;
--也可以用join语句。from a,b where a.sid=b.sid与join语句区别在于：join语句有一个主体表，join后辅助表可能有空值，而另一方只显示都有的
select a.sid from
(select * from sc where cid='01') a join (select * from sc where cid='02') b
on a.sid=b.sid
where a.score>b.score;
查询同时存在" 01 "课程和" 02 "课程的情况
--两个答案，第一个用a做主体表，第二个从一个联合表查询
select a.*,b.* from sc a left join sc b
on a.sid=b.sid
where a.cid='01' and b.cid='02'and
b.sid is not null;
select * from (select * from sc where cid='01')A
left join (select * from sc where cid='02')B on A.sid=B.sid
where B.sid is not null
查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select a.*,b.* from sc a left join (select * from sc where cid='02') b
on a.sid=b.sid
where a.cid='01';
查询不存在" 01 "课程但存在" 02 "课程的情况
select * from sc where cid='02' and sid not in
(select sid from sc where cid='01');
查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select sid,avg(score) avg from sc
group by sid
having avg>60;
