sql语法练习（三）

--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

--方法1

select m.C# [课程编号], m.Cname [课程名称],

  max(n.score) [最高分],

  min(n.score) [最低分],

  cast(avg(n.score) as decimal(18,2)) [平均分],

  cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SCwhere C# = m.C#) as decimal(18,2)) [及格率(%)],

  cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 80 )*100.0 / (selectcount(1) from SC where C# = m.C#) as decimal(18,2)) [中等率(%)],

  cast((select count(1) from SC where C# = m.C# and score >= 80 and score < 90 )*100.0 / (selectcount(1) from SC where C# = m.C#) as decimal(18,2)) [优良率(%)],

  cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SCwhere C# = m.C#) as decimal(18,2)) [优秀率(%)]

from Course m , SC n

where m.C# = n.C#

group by m.C# , m.Cname

order by m.C#

--方法2

select m.C# [课程编号], m.Cname [课程名称],

  (select max(score) from SC where C# = m.C#) [最高分],

  (select min(score) from SC where C# = m.C#) [最低分],

  (select cast(avg(score) as decimal(18,2)) from SC where C# = m.C#) [平均分],

  cast((select count(1) from SC where C# = m.C# and score >= 60)*100.0 / (select count(1) from SCwhere C# = m.C#) as decimal(18,2)) [及格率(%)],

  cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 80 )*100.0 / (selectcount(1) from SC where C# = m.C#) as decimal(18,2)) [中等率(%)],

  cast((select count(1) from SC where C# = m.C# and score >= 80 and score < 90 )*100.0 / (selectcount(1) from SC where C# = m.C#) as decimal(18,2)) [优良率(%)],

  cast((select count(1) from SC where C# = m.C# and score >= 90)*100.0 / (select count(1) from SCwhere C# = m.C#) as decimal(18,2)) [优秀率(%)]

from Course m

order by m.C#

--19、按各科成绩进行排序，并显示排名

--19.1 sql 2000用子查询完成

--Score重复时保留名次空缺

select t.* , px = (select count(1) from SC where C# = t.C# and score > t.score) + 1 from sc torder by t.c# , px

--Score重复时合并名次

select t.* , px = (select count(distinct score) from SC where C# = t.C# and score >= t.score) fromsc t order by t.c# , px

--19.2 sql 2005用rank,DENSE_RANK完成

--Score重复时保留名次空缺(rank完成)

select t.* , px = rank() over(partition by c# order by score desc) from sc t order by t.C# , px

--Score重复时合并名次(DENSE_RANK完成)

select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t order by t.C# , px

--20、查询学生的总成绩并进行排名

--20.1 查询学生的总成绩

select m.S# [学生编号] ,

       m.Sname [学生姓名] ,

       isnull(sum(score),0) [总成绩]

from Student m left join SC n on m.S# = n.S#

group by m.S# , m.Sname

order by [总成绩] desc

--20.2 查询学生的总成绩并进行排名，sql 2000用子查询完成，分总分重复时保留名次空缺和不保留名次空缺两种。

select t1.* , px = (select count(1) from

(

  select m.S# [学生编号] ,

         m.Sname [学生姓名] ,

         isnull(sum(score),0) [总成绩]

  from Student m left join SC n on m.S# = n.S#

  group by m.S# , m.Sname

) t2 where 总成绩 > t1.总成绩) + 1 from

(

  select m.S# [学生编号] ,

         m.Sname [学生姓名] ,

         isnull(sum(score),0) [总成绩]

  from Student m left join SC n on m.S# = n.S#

  group by m.S# , m.Sname

) t1

order by px

select t1.* , px = (select count(distinct 总成绩) from

(

  select m.S# [学生编号] ,

         m.Sname [学生姓名] ,

         isnull(sum(score),0) [总成绩]

  from Student m left join SC n on m.S# = n.S#

  group by m.S# , m.Sname

) t2 where 总成绩 >= t1.总成绩) from

(

  select m.S# [学生编号] ,

         m.Sname [学生姓名] ,

         isnull(sum(score),0) [总成绩]

  from Student m left join SC n on m.S# = n.S#

  group by m.S# , m.Sname

) t1

order by px

--20.3 查询学生的总成绩并进行排名，sql 2005用rank,DENSE_RANK完成，分总分重复时保留名次空缺和不保留名次空缺两种。

select t.* , px = rank() over(order by [总成绩] desc) from

(

  select m.S# [学生编号] ,

         m.Sname [学生姓名] ,

         isnull(sum(score),0) [总成绩]

  from Student m left join SC n on m.S# = n.S#

  group by m.S# , m.Sname

) t

order by px

select t.* , px = DENSE_RANK() over(order by [总成绩] desc) from

(

  select m.S# [学生编号] ,

         m.Sname [学生姓名] ,

         isnull(sum(score),0) [总成绩]

  from Student m left join SC n on m.S# = n.S#

  group by m.S# , m.Sname

) t

order by px

--21、查询不同老师所教不同课程平均分从高到低显示

select m.T# , m.Tname , cast(avg(o.score) as decimal(18,2)) avg_score

from Teacher m , Course n , SC o

where m.T# = n.T# and n.C# = o.C#

group by m.T# , m.Tname

order by avg_score desc

--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

--22.1 sql 2000用子查询完成

--Score重复时保留名次空缺

select * from (select t.* , px = (select count(1) from SC where C# = t.C# and score > t.score) + 1from sc t) m where px between 2 and 3 order by m.c# , m.px

--Score重复时合并名次

select * from (select t.* , px = (select count(distinct score) from SC where C# = t.C# and score>= t.score) from sc t) m where px between 2 and 3 order by m.c# , m.px

--22.2 sql 2005用rank,DENSE_RANK完成

--Score重复时保留名次空缺(rank完成)

select * from (select t.* , px = rank() over(partition by c# order by score desc) from sc t) mwhere px between 2 and 3 order by m.C# , m.px

--Score重复时合并名次(DENSE_RANK完成)

select * from (select t.* , px = DENSE_RANK() over(partition by c# order by score desc) from sc t) m where px between 2 and 3 order by m.C# , m.px

--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

--23.1 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]

--横向显示

select Course.C# [课程编号] , Cname as [课程名称] ,

  sum(case when score >= 85 then 1 else 0 end) [85-100],

  sum(case when score >= 70 and score < 85 then 1 else 0 end) [70-85],

  sum(case when score >= 60 and score < 70 then 1 else 0 end) [60-70],

  sum(case when score < 60 then 1 else 0 end) [0-60]

from sc , Course

where SC.C# = Course.C#

group by Course.C# , Course.Cname

order by Course.C#

--纵向显示1(显示存在的分数段)

select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end) ,

  count(1) 数量

from Course m , sc n

where m.C# = n.C#

group by m.C# , m.Cname , (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end)

order by m.C# , m.Cname , 分数段

--纵向显示2(显示存在的分数段，不存在的分数段用0显示)

select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end) ,

  count(1) 数量

from Course m , sc n

where m.C# = n.C#

group by all m.C# , m.Cname , (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end)

order by m.C# , m.Cname , 分数段

--23.2 统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[<60]及所占百分比

--横向显示

select m.C# 课程编号, m.Cname 课程名称,

  (select count(1) from SC where C# = m.C# and score < 60) [0-60],

  cast((select count(1) from SC where C# = m.C# and score < 60)*100.0 / (select count(1) from SCwhere C# = m.C#) as decimal(18,2)) [百分比(%)],

  (select count(1) from SC where C# = m.C# and score >= 60 and score < 70) [60-70],

  cast((select count(1) from SC where C# = m.C# and score >= 60 and score < 70)*100.0 / (selectcount(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)],

  (select count(1) from SC where C# = m.C# and score >= 70 and score < 85) [70-85],

  cast((select count(1) from SC where C# = m.C# and score >= 70 and score < 85)*100.0 / (selectcount(1) from SC where C# = m.C#) as decimal(18,2)) [百分比(%)],

  (select count(1) from SC where C# = m.C# and score >= 85) [85-100],

  cast((select count(1) from SC where C# = m.C# and score >= 85)*100.0 / (select count(1) from SCwhere C# = m.C#) as decimal(18,2)) [百分比(%)]

from Course m

order by m.C#

--纵向显示1(显示存在的分数段)

select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end) ,

  count(1) 数量 , 

  cast(count(1) * 100.0 / (select count(1) from sc where C# = m.C#) as decimal(18,2)) [百分比(%)]

from Course m , sc n

where m.C# = n.C#

group by m.C# , m.Cname , (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end)

order by m.C# , m.Cname , 分数段

--纵向显示2(显示存在的分数段，不存在的分数段用0显示)

select m.C# [课程编号] , m.Cname [课程名称] , 分数段 = (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end) ,

  count(1) 数量 , 

  cast(count(1) * 100.0 / (select count(1) from sc where C# = m.C#) as decimal(18,2)) [百分比(%)]

from Course m , sc n

where m.C# = n.C#

group by all m.C# , m.Cname , (

  case when n.score >= 85 then '85-100'

       when n.score >= 70 and n.score < 85 then '70-85'

       when n.score >= 60 and n.score < 70 then '60-70'

       else '0-60'

  end)

order by m.C# , m.Cname , 分数段