left join Student B on A.S#=B.S#
--4.1 查有成绩的学生信息
select COUNT(*)李姓老师数量 from Teacher where Tname like '李%'
--5.查询「李」姓老师的数量
select * from Student
where S# in(select distinct S# from SC
where C#=(select C# from Course
where T#=(select T# from Teacher where Tname='张三')))
--6.查询学过「张三」老师授课的同学的信息
select * from Student where S# in(select S# from SC group by S# having COUNT(C#)<3)
--7.查询没有学全所有课程的同学的信息
select * from Student
where S# in(select distinct S# from SC where C# in(select C# from SC where S#='01')
)
--8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from Student
where S# in(select S# from SC where C# in(select distinct C# from SC where S#='01') and S#<>'01'
group by S#
having COUNT(C#)>=3)
--9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select Sname from Student
where S# not in(select S# from SC
where C# in(select C# from Course where T# in(select T# from Teacher where Tname='张三')
)
)--10. 查询没学过「张三」老师讲授的任一门课程的学生姓名
select A.S#,A.Sname,B.平均成绩 from Student A right join
(select S#,AVG(score)平均成绩 from SC where score<60 group by S# having COUNT(score)>=2)B
on A.S#=B.S#--11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select S#,score from SC where C#='01' and score<60 order by score desc
--12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select S#,max(case C# when '01' then score else 0 end)'01',
max(case C# when '02' then score else 0 end)'02',
MAX(case C# when '03' then score else 0 end)'03',AVG(score)平均分 from SC
group by S# order by 平均分 desc
--13. （静态写法）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select distinct A.C#,Cname,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 from SC A
left join Course on A.C#=Course.C#
left join (select C#,MAX(score)最高分,MIN(score)最低分,AVG(score)平均分 from SC group by C#)B on A.C#=B.C#
left join (select C#,(convert(decimal(5,2),(sum(case when score>=60 then 1 else 0 end)*1.00)/COUNT(*))*100)及格率 from SC group by C#)C on A.C#=C.C#
left join (select C#,(convert(decimal(5,2),(sum(case when score >=70 and score<80 then 1 else 0 end)*1.00)/COUNT(*))*100)中等率 from SC group by C#)D on A.C#=D.C#
left join (select C#,(convert(decimal(5,2),(sum(case when score >=80 and score<90 then 1 else 0 end)*1.00)/COUNT(*))*100)优良率 from SC group by C#)E on A.C#=E.C#
left join (select C#,(convert(decimal(5,2),(sum(case when score >=90 then 1 else 0 end)*1.00)/COUNT(*))*100)优秀率
from SC group by C#)F on A.C#=F.C#
--14.查询各科成绩最高分、最低分和平均分：
--以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select *,RANK()over(order by score desc)排名 from SC
--15. 按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺
select *,DENSE_RANK()over(order by score desc)排名 from SC
--15.1 按各科成绩进行排序，并显示排名，Score 重复时合并名次
select *,RANK()over(order by 总成绩 desc)排名 from(
select S#,SUM(score)总成绩 from SC group by S#)A
--16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select *,dense_rank()over(order by 总成绩 desc)排名 from(
select S#,SUM(score)总成绩 from SC group by S#)A
--16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select distinct A.C#,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.C#=B.C#
left join (select C#,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 C#)C on A.C#=C.C#
left join (select C#,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 C#)D on A.C#=D.C#
left join (select C#,sum(case when score>60 and score<=70 then 1 else null end)[70-60],
推荐阅读
- 如何修改MySQL数据库数据存储盘？
- MySQL数据库如何安装
- 分享一个mysql主从监控脚本
- 懂这10道JS经典算法题，你就是前端大神
- Python 操作 MySQL 的5种方式
- 专家提醒：秋茶农药残留多 泡茶勿超两小时
- LV 经典款围巾大合集
- 史上最经典的10部战争影片
- 摄像头是拍照的，为何能测出汽车超速？答案可能跟你想的不一样
- 敦煌自由行攻略，超详细2日游攻略带你重走丝绸之路