sql高级

--1.查询" 01 "课程⽐" 02 "课程成绩⾼的学⽣的信息及课程分数

-- 1.先分别查询01和02课程的学员id和分数
 
-- 2.对比两个结果,发现两个结果中有一些sid是不对应的
-- 因此可以对两个结果做jion联结，条件是sid要相等
-- 并且01的成绩要大于02的
 
-- 3.通过以上的sql，得到了符合条件的学员id和分数，再联结学生表，获取学员信息
select * from Student st inner join
(
select t1.SId from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as class2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId AND t1.class1 > t2.class2) r
    
    on st.sid=r.sid
--2.查询同时学习" 01 "课程和" 02 "课程的学生信息情况
select * from Student st inner join
(
select t1.SId from
          (select SId, score as class1 from sc where sc.CId = '01')as t1, 
          (select SId, score as class2 from sc where sc.CId = '02')as t2
    where t1.SId = t2.SId ) r
    
    on st.sid=r.sid
 
     
--3.查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select * from
(select * from SC where SC.CID='01')AS T1
left join
(select * from SC where SC.CID='02')AS T2
on T1.sid=T2.sid

--4.查询不存在" 01 "课程但存在" 02 "课程的情况(存在02但不存在01的课程信息)
select * from SC 
WHERE SID not in(select sid from Sc WHERE SC.CID='01')
and cid='02'

 
--5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
 select  ST.SId,st.Sname,AVG(sc.score) 
 from Sc  INNER JOIN Student ST ON SC.SID=ST.SID
 group by st.SId,st.Sname
 having AVG(sc.score)>=60
 
--6.查询在 SC 表存在成绩的学⽣信息
select 
*
from Student
where SId in(select SId from SC )
--7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select
s.SId,st.Sname,COUNT(*) as 选课总数,SUM(score) as 总成绩
from Student st inner join SC s on st.SId=s.SId
group by s.SId,st.Sname

--8.查询「李」姓⽼师的数量
select 
COUNT(*) as 数量
from Teacher te
where te.Tname like '张%'
 
--9.查询学过「张三」⽼师授课的同学的信息

select 
st.*
from Teacher te inner join Course co on te.TId=co.TId inner join SC s on co.CId=s.CId
inner join Student st on s.SId=st.SId
where te.Tname='张