sql mark

# 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select  student.*,a.score'C01',b.score'C02'
       from (select score ,SId from sc where sc.CId='01') as a,(select score ,SId from sc where sc.CId='02') as b,student
where a.sid=b.sid and a.sid=student.SId and a.score>b.score;

# 查询同时存在" 01 "课程和" 02 "课程的情况
select * from student
where SId in (select a.sid from (select SId from sc where sc.CId='01') as a,(select SId from sc where sc.CId='02') as b
where a.sid=b.sid);

select a.sid,a.score,b.score
from (select sid,score from sc where sc.cid='01')as a
inner join (select sid,score from sc where sc.cid='02')as b
on a.sid=b.sid;

# 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null ) 以a表为主表
select a.sid,a.score,b.score
from (select sid,score from sc where sc.cid='01')as a
left join (select sid,score from sc where sc.cid='02')as b
on a.sid=b.sid;

# 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from sc
where sc.cid not in(select cid from sc where cid='01')and sc.cid='02';

# 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select sid,avg(score) from sc group by sid;
#GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。group by后面不能接where，having代替了where
select sid,avg(score) from sc group by sid having avg(score)>60;

select b.sid,b.Sname,a.*
from student b,(select sid,avg(score) from sc group by sid having avg(score)>60)as a
where b.SId=a.SId;


select A.Sid,B.Sname,A.dc from(select Sid,AVG(score)dc from SC group by Sid)A
left join Student B on A.Sid=B.Sid where A.dc>=60;

select
s.SId,
st.Sname,
       avg(s.score) as score
from sc s
left join student st
    on s.SId = st.SId
group by s.SId,
st.Sname
having avg(s.score)>=60;

# 查询在 SC 表存在成绩的学生信息
select sc.*,student.*
from sc left join student on sc.SId=student.SId;
#不需要成绩且结果重复

select distinct