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='张三'
--10.查询没有学全所有课程的同学的信息
select * from Student st
where st.SId not in(
select s.SId
from SC s
group by s.SId
having COUNT(s.CId)=(select
COUNT(*)
from Course)
)

--11.查询和" 01 "号的同学学习的课程 至少一门的其他同学的信息
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.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

select s2.sid,student.sname
from sc as s1
join sc as s2
on s1.cid = s2.cid
and s1.sid='01'
and s2.sid !='01'
join student
on s2.sid = student.sid
GROUP BY s2.sid,student.sname
having count(s2.cid)=(select count(*) from sc where sid = '01');

 
--13.查询没学过"张三"⽼师讲授的任⼀门课程的学⽣姓名
select * from Student where SId not in( select 
st.SId
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='张三')

 
--14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select st.Sname,s.SId,AVG(s.score) as 平均成绩
from Student st
inner join SC s on st.SId=s.SId
group by s.SId,st.Sname
having avg(s.score)<=90 and COUNT(s.CId)>=2
 

--15.检索" 01 "课程分数⼩于 60，按分数降序排列的学⽣信息
select  st.* from SC s inner join Student st on s.SId=st.SId  where s.score<60 order by s.score desc

--#用两个表join一下，并限制cid和score，最后再排序
select  st.* from SC s inner join Student st on s.SId=st.SId  where s.score<60 and s.CId='01'

 order by s.score desc

 
--16.按平均成绩从⾼到低显示所有学⽣的所有课程的成绩以及平均成绩
select s1.sname,
c1.score as '01',
c2.score as '02',
c3.score as '03',
avg(c4.score) as '平均成绩'
from student as s1
left join sc as c1 on s1.sid=c1.sid AND c1.cid=01
left join sc as c2 on s1.sid=c2.sid AND c2.cid=02
left join sc as c3 on s1.sid=c3.sid AND c3.cid=03
left join sc as c4 on s1.sid=c4.sid
group by s1.sname,c1.score,c2.score,c3.score
order by 平均成绩 desc;

--17.查询各科成绩最⾼分、最低分和平均分：
select
s.CId,MAX(s.score) 最高分,MIN(score) 最低分,AVG(score) 平均分
from
SC s
group by s.CId
 
--18.按各科平均成绩进⾏排序，并显示排名， Score 重复时保留名次空缺
SELECT s.CId,AVG(s.score),RANK()over(order by avg(score) desc) 排名 
FROM SC s
group by s.CId
 
--19.按各科平均成绩进⾏排序，并显示排名， Score 重复时不保留名次空缺
 SELECT  s.CId,AVG(s.score),DENSE_RANK()over(order by avg(score) desc) 排名 
FROM SC s
group by s.CId
--20.查询学⽣的总成绩，并进⾏排名，总分重复时保留名次空缺
 SELECT  s.SId,sum(s.score),RANK()over(order by sum(score) desc) 排名 
FROM SC s
group by s.SId
--21.查询学⽣的总成绩，并进⾏排名，总分重复时不保留名次空缺
   SELECT  s.SId,sum(s.score),DENSE_RANK()over(order by sum(score) desc) 排名 
FROM SC s
group by s.SId
--22.统计各科成绩各分数段⼈数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分⽐
 SELECT DISTINCT A.CID,B.Cname,C.[100-85],C.所占百分比,D.[85-70],D.所占百分比,E.[70-60],E.所占百分比,F.[60-0],F.所占百分比
FROM SC A
LEFT JOIN Course B ON A.CID=B.CID
LEFT JOIN (SELECT CID,sum(case when score>85 and score<=100 then 1 else null end) [100-85],
convert(decimal(5,2),(sum(case when score>85 and score<100 then 1 else null end))*1.00/COUNT(*))*100 所占百分比 FROM SC GROUP BY CID)C on A.CID=C.CID
LEFT JOIN (SELECT CID,sum(case when score>70 and score<=85 then 1 else null end)[85-70],
convert(decimal(5,2),(sum(case when score>70 and score<=85 then 1 else null end))*1.00/COUNT(*))*100 所占百分比 FROM SC GROUP BY CID)D on A.CID=D.CID
LEFT JOIN (SELECT CID,sum(case when score>60 and score<=70 then 1 else null end)[70-60],
convert(decimal(5,2),(sum(case when score>60 and score<=70 then 1 else null end))*1.00/COUNT(*))*100 所占百分比 FROM SC GROUP BY CID)E on A.CID=E.CID
LEFT JOIN (SELECT CID,sum(case when score>0 and score<=60 then 1 else null end)[60-0],
convert(decimal(5,2),(sum(case when score>0 and score<=60 then 1 else null end))*1.00/COUNT(*))*100 所占百分比 FROM SC GROUP BY CID)F on A.CID=F.CID
--23.查询各科成绩前三名的记录
 SELECT * FROM 
(SELECT *,rank()over (partition by CID order by score desc) A
FROM SC)B
WHERE B.A<=3
--24.查询每门课程被选修的学⽣数
 SELECT CID,COUNT(SID)学生数 FROM SC GROUP BY CID
--25.查询出只选修两⻔课程的学⽣学号和姓名
 select st.SId,st.Sname
 from Student st
 join SC s on st.SId=s.SId
 group by st.SId,st.Sname
 having COUNT(*)=2
--26.查询男⽣、⼥⽣⼈数
select st.Ssex,COUNT(*) 人数
from Student st
group by st.Ssex
--27.查询名字中含有「风」字的学⽣信息
select *
from Student st
WHERE st.Sname like '%风%' 
--28.查询同名同性学⽣名单，并统计同名⼈数
 select st.Sname,COUNT(*)
 from Student st
 inner join Student st2
 on st.SId=st2.SId
 where st.Sname=st2.Sname
 group by st.Sname having COUNT(*)>1
--29.查询 1990 年出⽣的学⽣名单
select * from Student st where DATEPART(YYYY,st.Sage)=1990
 
--30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
 select s.CId ,AVG(score)
 from SC s
 group by s.CId
 order by AVG(score) desc,CId
 
--31.查询平均成绩⼤于等于 85 的所有学⽣的学号、姓名和平均成绩
 select s.SId,max(st.Sname),AVG(score)
 from SC s inner join Student st on s.SId=st.SId
 group by s.SId
 having AVG(score)>=85

 
--32.查询课程名称为「数学」，且分数低于 60 的学⽣姓名和分数
 select
st.Sname,s.score from Course co inner join SC s on co.CId=s.CId inner join Student st on s.SId=st.SId
 where co.Cname='数学' and s.score<60
--33.查询所有学⽣的课程及分数情况（存在学⽣没成绩，没选课的情况）
 select * from Student st left join SC s on st.SId=s.SId
--34.查询任何⼀门课程成绩在 70 分以上的姓名、课程名称和分数
select 
max(st.Sname),max(co.Cname),max(s.score)
from SC s inner join Course co on s.CId=co.CId inner join Student st on s.SId=st.SId
group by s.SId
having MAX(s.score)>70

--35.查询不及格的课程
select 
distinct
s.CId
from SC s
group by s.CId,s.score
having s.score<60
 
--36.查询课程编号为 01 且课程成绩在 80 分以上的学⽣的学号和姓名
 select
 st.SId,st.Sname
 from SC s inner join Student st on s.SId=st.SId
 where s.CId='01' and s.score>80
 
--37.求每门课程的学⽣⼈数
select 
co.CId,max(co.Cname),COUNT(*)
from Course co left join SC s on co.CId=s.CId
group by co.CId
 
--38.成绩不重复，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩
 
 select 
 max(st.SId),max(st.Sage),max(st.Sname),max(s.score)
 from Teacher te inner join Course co on te.TId=co.TId inner join SC s on s.CId=co.TId
 inner join Student st on st.SId=s.SId
 where te.Tname='张三'
--39.成绩有重复的情况下，查询选修「张三」⽼师所授课程的学⽣中，成绩最⾼的学⽣信息及其成绩
 select 
 max(st.SId),max(st.Sage),max(st.Sname),max(s.score)
 from Teacher te inner join Course co on te.TId=co.TId inner join SC s on s.CId=co.TId
 inner join Student st on st.SId=s.SId
 where te.Tname='张三'
--将4张表join起来，然后限制条件教师表的tname=张三，然后做降序排序，找出最高分的分数是多少，然后取出分数为最高分的学生信息。下题也是同样操作。
 
--40.查询 
select 
 top(1)max(st.SId),max(st.Sage),max(st.Sname),max(s.score)
 from Teacher te inner join Course co on te.TId=co.TId inner join SC s on s.CId=co.TId
 inner join Student st on st.SId=s.SId
 where te.Tname='张三' 
 group by s.score
 order by s.score desc
 
-- 41.查询每门课程成绩最好的前两名
 SELECT * FROM 
(SELECT *,rank()over (partition by CID order by score desc) A
FROM SC)B
WHERE B.A<=2

--42.统计每门课程的学⽣选修⼈数（超过 5 ⼈的课程才统计）。
 select 
co.CId,max(co.Cname),COUNT(*)
from Course co left join SC s on co.CId=s.CId
group by co.CId
having COUNT(*)>5
 
--43.检索⾄少选修两门课程的学⽣学号
 select
 s.SId,COUNT(*)
 from Student st inner join SC S ON st.SId=s.SId
 group by s.SId
 having COUNT(*)>2
--44.查询选修了全部课程的学⽣信息
 select st.* from Student st join SC s on st.SId=s.SId
 group by st.SId,st.Sage,st.Sage,st.Sname,st.Ssex
 having COUNT(s.SId)=(select COUNT(*) from Course)
--45.查询各学⽣的年龄，只按年份来算
 select st.Sname,(DATEPART(YYYY,GETDATE())-DATEPART(YYYY,st.Sage)) as 年龄 
 from Student st
--46.按照出⽣⽇期来算，当前⽉⽇ < 出⽣年⽉的⽉⽇则，年龄减⼀
 select datediff(YEAR,Sage,GETDATE()) as 年龄
 from Student
--47.查询本周过生日的学
select 
*
from Student st
where DATEPART(WW,st.Sage)=DATEPART(WW,GETDATE())

--返回⽇期从范围内的数字⽇历星期1到53
 
--48.查询下周过⽣⽇的学⽣
 select 
*
from Student st
where DATEPART(WW,st.Sage)=DATEPART(WW,DATEADD(WW,1,GETDATE()))
--49.查询本⽉过⽣⽇的学⽣
 select 
*
from Student st
where DATEPART(MM,st.Sage)=DATEPART(MM,GETDATE())
--50.查询下⽉过⽣⽇的学⽣
 select 
*
from Student st
where DATEPART(MM,st.Sage)=DATEPART(MM,DATEADD(MM,1,GETDATE()))