暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / SQL 经典50题练习之11-20题

SQL 经典50题练习之11-20题

SQL数据库入门学习 2019-08-17
3459


本公众号使用SQL SERVER2014测试。SQL语句可能因数据库系统不同而存在少许差异。


测试数据请参照上一章节，各表查询结果如下图：

练习题目

11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
14. 查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
16.  查询学生的总成绩，并进行排名，总分重复时保留名次空缺
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
18. 查询各科成绩前三名的记录
19. 查询每门课程被选修的学生数
20. 查询出只选修两门课程的学生学号和姓名


参考答案

--------------------------------------------------------

--11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
    select a.SId,a.Sname,avg(b.score) as 平均成绩
    from Student a, sc b 
    where a.SId=b.SId and b.score < 60
    group by a.sid,a.Sname having count(*)>=2

    输出结果


    --12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息


      select a.*
      from Student a, sc b 
      where a.SId=b.SId and b.score < 60 
      and b.CId=01 order by b.score desc

      输出结果


      --13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩


        select sc.SId,sc.CId,sc.score,t.avgs from sc,
        (select sc.SId,avg(score) as avgs 
        from SC group by sc.SId)t
        where sc.SId=t.SId
        order by t.avgs desc

        输出结果


        --14. 查询各科成绩最高分、最低分和平均分：

         --   以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

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

         --   要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列


          select CId,count(*) as 选修人数,
          max(score) as 最高分,min(score) as 最低分,
          cast(avg(score) as decimal(5,2))  as 平均分,
          cast(cast(cast(sum(case when sc.score>=60 then 1 else 0 end )as decimal(5,2)) count(*)*100 as decimal(5,2)) as varchar(20))+'%' as 及格率,
          cast(cast(cast(sum( case when sc.score>=70 and sc.score<80 then 1 else 0 end )as decimal(5,2)) count(*)*100 as decimal(5,2)) as varchar(20))+'%' as 中等率,
          cast(cast(cast(sum( case when sc.score>=80 and sc.score<90 then 1 else 0 end )as decimal(5,2)) count(*)*100 as decimal(5,2)) as varchar(20))+'%' as 优良率,
          cast(cast(cast(sum( case when sc.score>=90 then 1 else 0 end )as decimal(5,2)) count(*)*100 as decimal(5,2)) as varchar(20))+'%' as 优秀率
          from SC  group by cid
          order by count(*) desc,cid

          输出结果


          --15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺


            select sc.CId,sc.score ,
            rank() over (partition by cid order by sc.score desc) as num 
            from  sc

            输出结果


            --15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

              select cid,sc.score ,
              dense_RANK() over (partition by cid order by sc.score desc) as num 
              from SC

               输出结果


              --16.  查询学生的总成绩，并进行排名，总分重复时保留名次空缺

                select sid,sum(score) as score,
                rank() over (order by  sum(score) desc) as num 
                from sc group by SId

                输出结果


                --16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

                  select sid,sum(score) as score,
                  row_number() over (order by  sum(score) desc) as num 
                  from sc group by SId

                  输出结果


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

                    select t1.Cname,t2.* from Course t1,(
                    select SC.cid,
                    sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end) as [100-85],
                    cast(cast(cast(sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%' as '[100-85]所占百分比' ,
                    sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end) as [85-70],
                    cast(cast(cast(sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%' as '[85-70]所占百分比' ,
                    sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end) as [70-60],
                    cast(cast(cast(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%' as '[70-60]所占百分比' ,
                    sum(case when sc.score>0 and sc.score<60 then 1 else 0 end) as [60-0],
                    cast(cast(cast(sum(case when sc.score>0 and sc.score<60 then 1 else 0 end) as decimal(5,2))/count(*)*100 as decimal(5,2))as varchar(20))+'%' as '[60-0]所占百分比'
                    from SC group by SC.cid) t2
                    where t1.CId=t2.CId

                    输出结果


                    --18. 查询各科成绩前三名的记录


                      select * from sc where (select count(*) 
                      from sc t1 where t1.CId =sc.CId and t1.score>sc.score)<3
                      order by cid , score desc

                      输出结果


                      --19. 查询每门课程被选修的学生数 


                        select cid,count(*) as 选修人数 
                        from SC
                        group by cid

                        输出结果


                        --20. 查询出只选修两门课程的学生学号和姓名 

                          select a.sid ,a.Sname 
                          from Student a,SC b 
                          where a.SId=b.SId
                          group by a.sid ,a.Sname having count(*)=2

                          输出结果

                          如果觉得对您有一丢丢帮助，请点击右下角【在看】，让更多人看到该文章。

                          长按识别二维码

                          关注获取视频资料





                          本文分享自微信公众号 - SQL数据库入门学习，如有侵权，请联系 service001@enmotech.com 删除。
                          文章转载自SQL数据库入门学习，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

                          评论