当前位置：Gxlcms > 数据库问题 > 网上流行的学生选课相关的50个常用sql语句

网上流行的学生选课相关的50个常用sql语句

时间：2021-07-01 10:21:17 帮助过：12人阅读

a.s# FROM ( SELECT * FROM dbo.SC WHERE c# = ‘001‘ ) a INNER JOIN ( SELECT * FROM dbo.SC WHERE c# = ‘002‘ ) b ON a.s# = b.s# WHERE a.score > b.score

 

2、查询平均成绩大于60分的同学的学号和平均成绩;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  s# ,
  2.         </span><span style="color: #ff00ff;">AVG</span><span style="color: #000000;">(score)
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    dbo.SC
  4. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> s#
  5. </span><span style="color: #0000ff;">HAVING</span>  <span style="color: #ff00ff;">AVG</span>(score) <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">60</span>

 

3、查询所有同学的学号、姓名、选课数、总成绩;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  a.s# ,
  2.         a.sname ,
  3.         </span><span style="color: #ff00ff;">COUNT</span><span style="color: #000000;">(b.c#) ,
  4.         </span><span style="color: #ff00ff;">SUM</span><span style="color: #000000;">(b.score)
  5. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    dbo.Student a
  6.         </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">OUTER</span> <span style="color: #808080;">JOIN</span> dbo.SC b <span style="color: #0000ff;">ON</span> a.s# <span style="color: #808080;">=</span><span style="color: #000000;"> b.s#
  7. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> a.s# ,
  8.         a.sname</span>

 

4、查询姓“李”的老师的个数;

  1. <span style="color: #0000ff;">SELECT</span>  <span style="color: #ff00ff;">COUNT</span>(<span style="color: #0000ff;">DISTINCT</span><span style="color: #000000;"> tname)
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    dbo.Teacher
  3. </span><span style="color: #0000ff;">WHERE</span>   tname <span style="color: #808080;">LIKE</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">李%</span><span style="color: #ff0000;">‘</span>

 

5、查询没学过“叶平”老师课的同学的学号、姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  Student.S# ,
  2.         Student.Sname
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student
  4. </span><span style="color: #0000ff;">WHERE</span>   S# <span style="color: #808080;">NOT</span> <span style="color: #808080;">IN</span> ( <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">DISTINCT</span><span style="color: #000000;">
  5.                             ( SC.S# )
  6.                     </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC ,
  7.                             Course ,
  8.                             Teacher
  9.                     </span><span style="color: #0000ff;">WHERE</span>   SC.C# <span style="color: #808080;">=</span><span style="color: #000000;"> Course.C#
  10.                             </span><span style="color: #808080;">AND</span> Teacher.T# <span style="color: #808080;">=</span><span style="color: #000000;"> Course.T#
  11.                             </span><span style="color: #808080;">AND</span> Teacher.Tname <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">叶平</span><span style="color: #ff0000;">‘</span> )

 

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  Student.S# ,
  2.         Student.Sname
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student ,
  4.         SC
  5. </span><span style="color: #0000ff;">WHERE</span>   Student.S# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.S#
  6.         </span><span style="color: #808080;">AND</span> SC.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span>
  7.         <span style="color: #808080;">AND</span> <span style="color: #808080;">EXISTS</span> ( <span style="color: #0000ff;">SELECT</span> <span style="color: #808080;">*</span>
  8.                      <span style="color: #0000ff;">FROM</span>   SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> SC_2
  9.                      </span><span style="color: #0000ff;">WHERE</span>  SC_2.S# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.S#
  10.                             </span><span style="color: #808080;">AND</span> SC_2.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span> )

 

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S# ,
  2.         Sname
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student
  4. </span><span style="color: #0000ff;">WHERE</span>   S# <span style="color: #808080;">IN</span> ( <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S#
  5.                 </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC ,
  6.                         Course ,
  7.                         Teacher
  8.                 </span><span style="color: #0000ff;">WHERE</span>   SC.C# <span style="color: #808080;">=</span><span style="color: #000000;"> Course.C#
  9.                         </span><span style="color: #808080;">AND</span> Teacher.T# <span style="color: #808080;">=</span><span style="color: #000000;"> Course.T#
  10.                         </span><span style="color: #808080;">AND</span> Teacher.Tname <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">叶平</span><span style="color: #ff0000;">‘</span>
  11.                 <span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> S#
  12.                 </span><span style="color: #0000ff;">HAVING</span>  <span style="color: #ff00ff;">COUNT</span>(SC.C#) <span style="color: #808080;">=</span> ( <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">COUNT</span><span style="color: #000000;">(C#)
  13.                                          </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">   Course ,
  14.                                                 Teacher
  15.                                          </span><span style="color: #0000ff;">WHERE</span>  Teacher.T# <span style="color: #808080;">=</span><span style="color: #000000;"> Course.T#
  16.                                                 </span><span style="color: #808080;">AND</span> Tname <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">叶平</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  17.                                        ) )</span>

 

8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S# ,
  2.         Sname
  3. </span><span style="color: #0000ff;">FROM</span>    ( <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">    Student.S# ,
  4.                     Student.Sname ,
  5.                     score ,
  6.                     ( </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">    score
  7.                       </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      SC SC_2
  8.                       </span><span style="color: #0000ff;">WHERE</span>     SC_2.S# <span style="color: #808080;">=</span><span style="color: #000000;"> Student.S#
  9.                                 </span><span style="color: #808080;">AND</span> SC_2.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  10.                     ) score2
  11.           </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      Student ,
  12.                     SC
  13.           </span><span style="color: #0000ff;">WHERE</span>     Student.S# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.S#
  14.                     </span><span style="color: #808080;">AND</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  15.         ) S_2
  16. </span><span style="color: #0000ff;">WHERE</span>   score2 <span style="color: #808080;"><</span> score

 

9、查询所有课程成绩小于60分的同学的学号、姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S# ,
  2.         Sname
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student
  4. </span><span style="color: #0000ff;">WHERE</span>   S# <span style="color: #808080;">NOT</span> <span style="color: #808080;">IN</span> ( <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  Student.S#
  5.                     </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student ,
  6.                             SC
  7.                     </span><span style="color: #0000ff;">WHERE</span>   Student.S# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.S#
  8.                             </span><span style="color: #808080;">AND</span> score <span style="color: #808080;">></span> <span style="color: #800000; font-weight: bold;">60</span> )

 

10、查询没有学全所有课的同学的学号、姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  Student.S# ,
  2.         Student.Sname
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student ,
  4.         SC
  5. </span><span style="color: #0000ff;">WHERE</span>   Student.S# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.S#
  6. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> Student.S# ,
  7.         Student.Sname
  8. </span><span style="color: #0000ff;">HAVING</span>  <span style="color: #ff00ff;">COUNT</span>(C#) <span style="color: #808080;"><</span> ( <span style="color: #0000ff;">SELECT</span>    <span style="color: #ff00ff;">COUNT</span><span style="color: #000000;">(C#)
  9.                       </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      Course
  10.                     )</span>

 

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S# ,
  2.         Sname
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student ,
  4.         SC
  5. </span><span style="color: #0000ff;">WHERE</span>   Student.S# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.S#
  6.         </span><span style="color: #808080;">AND</span> C# <span style="color: #808080;">IN</span> ( <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  C#
  7.                     </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC
  8.                     </span><span style="color: #0000ff;">WHERE</span>   S# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1001</span><span style="color: #ff0000;">‘</span> )

 

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">DISTINCT</span><span style="color: #000000;">
  2.         SC.S# ,
  3.         Sname
  4. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student ,
  5.         SC
  6. </span><span style="color: #0000ff;">WHERE</span>   Student.S# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.S#
  7.         </span><span style="color: #808080;">AND</span> C# <span style="color: #808080;">IN</span> ( <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  C#
  8.                     </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC
  9.                     </span><span style="color: #0000ff;">WHERE</span>   S# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span> )

 

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

  1. <span style="color: #0000ff;">UPDATE</span><span style="color: #000000;">  SC
  2. </span><span style="color: #0000ff;">SET</span>     score <span style="color: #808080;">=</span> ( <span style="color: #0000ff;">SELECT</span>    <span style="color: #ff00ff;">AVG</span><span style="color: #000000;">(SC_2.score)
  3.                   </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      SC SC_2
  4.                   </span><span style="color: #0000ff;">WHERE</span>     SC_2.C# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.C#
  5.                 )
  6. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Course ,
  7.         Teacher
  8. </span><span style="color: #0000ff;">WHERE</span>   Course.C# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.C#
  9.         </span><span style="color: #808080;">AND</span> Course.T# <span style="color: #808080;">=</span><span style="color: #000000;"> Teacher.T#
  10.         </span><span style="color: #808080;">AND</span> Teacher.Tname <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">叶平</span><span style="color: #ff0000;">‘</span>

 

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S#
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC
  3. </span><span style="color: #0000ff;">WHERE</span>   C# <span style="color: #808080;">IN</span> ( <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  C#
  4.                 </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC
  5.                 </span><span style="color: #0000ff;">WHERE</span>   S# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1002</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> )
  6. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> S#
  7. </span><span style="color: #0000ff;">HAVING</span>  <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>) <span style="color: #808080;">=</span> ( <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span><span style="color: #000000;">)
  8.                      </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">   SC
  9.                      </span><span style="color: #0000ff;">WHERE</span>  S# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1002</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  10.                    )</span>

 

15、删除学习“叶平”老师课的SC表记录;

  1. <span style="color: #0000ff;">DELETE</span><span style="color: #000000;">  SC
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    course ,
  3.         Teacher
  4. </span><span style="color: #0000ff;">WHERE</span>   Course.C# <span style="color: #808080;">=</span><span style="color: #000000;"> SC.C#
  5.         </span><span style="color: #808080;">AND</span> Course.T# <span style="color: #808080;">=</span><span style="color: #000000;"> Teacher.T#
  6.         </span><span style="color: #808080;">AND</span> Tname <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">叶平</span><span style="color: #ff0000;">‘</span>

 

16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、 号课的平均成绩;

  1. <span style="color: #0000ff;">INSERT</span><span style="color: #000000;">  SC
  2.         </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S# ,
  3.                 </span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span><span style="color: #000000;"> ,
  4.                 ( </span><span style="color: #0000ff;">SELECT</span>    <span style="color: #ff00ff;">AVG</span><span style="color: #000000;">(score)
  5.                   </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      SC
  6.                   </span><span style="color: #0000ff;">WHERE</span>     C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  7.                 )
  8.         </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    Student
  9.         </span><span style="color: #0000ff;">WHERE</span>   S# <span style="color: #808080;">NOT</span> <span style="color: #808080;">IN</span> ( <span style="color: #0000ff;">SELECT</span><span style="color: #000000;">  S#
  10.                             </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC
  11.                             </span><span style="color: #0000ff;">WHERE</span>   C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span> )

 

17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

  1. <span style="color: #0000ff;">SELECT</span>  S# <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 学生ID ,
  2.         ( </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">    score
  3.           </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      SC
  4.           </span><span style="color: #0000ff;">WHERE</span>     SC.S# <span style="color: #808080;">=</span><span style="color: #000000;"> t.S#
  5.                     </span><span style="color: #808080;">AND</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">004</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  6.         ) </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 数据库 ,
  7.         ( </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">    score
  8.           </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      SC
  9.           </span><span style="color: #0000ff;">WHERE</span>     SC.S# <span style="color: #808080;">=</span><span style="color: #000000;"> t.S#
  10.                     </span><span style="color: #808080;">AND</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  11.         ) </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 企业管理 ,
  12.         ( </span><span style="color: #0000ff;">SELECT</span><span style="color: #000000;">    score
  13.           </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">      SC
  14.           </span><span style="color: #0000ff;">WHERE</span>     SC.S# <span style="color: #808080;">=</span><span style="color: #000000;"> t.S#
  15.                     </span><span style="color: #808080;">AND</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">006</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">
  16.         ) </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 英语 ,
  17.         </span><span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 有效课程数 ,
  18.         </span><span style="color: #ff00ff;">AVG</span>(t.score) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 平均成绩
  19. </span><span style="color: #0000ff;">FROM</span>    SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> t
  20. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> S#
  21. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> <span style="color: #ff00ff;">AVG</span>(t.score)

 

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

  1. <span style="color: #0000ff;">SELECT</span>  L.C# <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 课程ID ,
  2.         L.score </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 最高分 ,
  3.         R.score </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 最低分
  4. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC L ,
  5.         SC </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> R
  6. </span><span style="color: #0000ff;">WHERE</span>   L.C# <span style="color: #808080;">=</span><span style="color: #000000;"> R.C#
  7.         </span><span style="color: #808080;">AND</span> L.score <span style="color: #808080;">=</span> ( <span style="color: #0000ff;">SELECT</span>  <span style="color: #ff00ff;">MAX</span><span style="color: #000000;">(IL.score)
  8.                         </span><span style="color: #0000ff;">FROM</span>    SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> IL ,
  9.                                 Student </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> IM
  10.                         </span><span style="color: #0000ff;">WHERE</span>   L.C# <span style="color: #808080;">=</span><span style="color: #000000;"> IL.C#
  11.                                 </span><span style="color: #808080;">AND</span> IM.S# <span style="color: #808080;">=</span><span style="color: #000000;"> IL.S#
  12.                         </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> IL.C#
  13.                       )
  14.         </span><span style="color: #808080;">AND</span> R.Score <span style="color: #808080;">=</span> ( <span style="color: #0000ff;">SELECT</span>  <span style="color: #ff00ff;">MIN</span><span style="color: #000000;">(IR.score)
  15.                         </span><span style="color: #0000ff;">FROM</span>    SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> IR
  16.                         </span><span style="color: #0000ff;">WHERE</span>   R.C# <span style="color: #808080;">=</span><span style="color: #000000;"> IR.C#
  17.                         </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> IR.C#
  18.                       )</span>

 

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

  1. <span style="color: #0000ff;">SELECT</span>  t.C# <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 课程号 ,
  2.         </span><span style="color: #ff00ff;">MAX</span>(course.Cname) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 课程名 ,
  3.         </span><span style="color: #ff00ff;">ISNULL</span>(<span style="color: #ff00ff;">AVG</span>(score), <span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 平均成绩 ,
  4.         </span><span style="color: #800000; font-weight: bold;">100</span> <span style="color: #808080;">*</span> <span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> <span style="color: #ff00ff;">ISNULL</span>(score, <span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">>=</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span>
  5.                        <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span>
  6.                   <span style="color: #0000ff;">END</span>) <span style="color: #808080;">/</span> <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 及格百分数
  7. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;">    SC T ,
  8.         Course
  9. </span><span style="color: #0000ff;">WHERE</span>   t.C# <span style="color: #808080;">=</span><span style="color: #000000;"> course.C#
  10. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> t.C#
  11. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> <span style="color: #800000; font-weight: bold;">100</span> <span style="color: #808080;">*</span> <span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> <span style="color: #ff00ff;">ISNULL</span>(score, <span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">>=</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span>
  12.                         <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span>
  13.                    <span style="color: #0000ff;">END</span>) <span style="color: #808080;">/</span> <span style="color: #ff00ff;">COUNT</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">DESC</span>

 

20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> score <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> C# <span style="color: #0000ff;">WHEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 企业管理平均分 
  2. ,</span><span style="color: #800000; font-weight: bold;">100</span> <span style="color: #808080;">*</span> <span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">AND</span> score <span style="color: #808080;">>=</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 企业管理及格百分数 
  3. ,</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> score <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> C# <span style="color: #0000ff;">WHEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 马克思平均分 
  4. ,</span><span style="color: #800000; font-weight: bold;">100</span> <span style="color: #808080;">*</span> <span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">AND</span> score <span style="color: #808080;">>=</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 马克思及格百分数 
  5. ,</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">003</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> score <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> C# <span style="color: #0000ff;">WHEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">003</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> UML平均分 
  6. ,</span><span style="color: #800000; font-weight: bold;">100</span> <span style="color: #808080;">*</span> <span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">003</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">AND</span> score <span style="color: #808080;">>=</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">003</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> UML及格百分数 
  7. ,</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">004</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> score <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> C# <span style="color: #0000ff;">WHEN</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">004</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 数据库平均分 
  8. ,</span><span style="color: #800000; font-weight: bold;">100</span> <span style="color: #808080;">*</span> <span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">004</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">AND</span> score <span style="color: #808080;">>=</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>)<span style="color: #808080;">/</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">004</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 数据库及格百分数 
  9. </span><span style="color: #0000ff;">FROM</span> SC

 

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

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">max</span>(Z.T#) <span style="color: #0000ff;">AS</span> 教师ID,<span style="color: #ff00ff;">MAX</span>(Z.Tname) <span style="color: #0000ff;">AS</span> 教师姓名,C.C# <span style="color: #0000ff;">AS</span> 课程ＩＤ,<span style="color: #ff00ff;">MAX</span>(C.Cname) <span style="color: #0000ff;">AS</span> 课程名称,<span style="color: #ff00ff;">AVG</span>(Score) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 平均成绩 
  2. </span><span style="color: #0000ff;">FROM</span> SC <span style="color: #0000ff;">AS</span> T,Course <span style="color: #0000ff;">AS</span> C ,Teacher <span style="color: #0000ff;">AS</span><span style="color: #000000;"> Z 
  3. </span><span style="color: #0000ff;">where</span> T.C#<span style="color: #808080;">=</span>C.C# <span style="color: #808080;">and</span> C.T#<span style="color: #808080;">=</span><span style="color: #000000;">Z.T# 
  4. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> C.C# 
  5. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> <span style="color: #ff00ff;">AVG</span>(Score) <span style="color: #0000ff;">DESC</span>

 

22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004） [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">DISTINCT</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">3</span><span style="color: #000000;"> 
  2. SC.S# </span><span style="color: #0000ff;">As</span><span style="color: #000000;"> 学生学号, 
  3. Student.Sname </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 学生姓名 , 
  4. T1.score </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 企业管理, 
  5. T2.score </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 马克思, 
  6. T3.score </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> UML, 
  7. T4.score </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> 数据库, 
  8. </span><span style="color: #ff00ff;">ISNULL</span>(T1.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T2.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T3.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T4.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #0000ff;">as</span><span style="color: #000000;"> 总分 
  9. </span><span style="color: #0000ff;">FROM</span> Student,SC <span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T1 
  10. </span><span style="color: #0000ff;">ON</span> SC.S# <span style="color: #808080;">=</span> T1.S# <span style="color: #808080;">AND</span> T1.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">001</span><span style="color: #ff0000;">‘</span> 
  11. <span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T2 
  12. </span><span style="color: #0000ff;">ON</span> SC.S# <span style="color: #808080;">=</span> T2.S# <span style="color: #808080;">AND</span> T2.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">002</span><span style="color: #ff0000;">‘</span> 
  13. <span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T3 
  14. </span><span style="color: #0000ff;">ON</span> SC.S# <span style="color: #808080;">=</span> T3.S# <span style="color: #808080;">AND</span> T3.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">003</span><span style="color: #ff0000;">‘</span> 
  15. <span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> SC <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T4 
  16. </span><span style="color: #0000ff;">ON</span> SC.S# <span style="color: #808080;">=</span> T4.S# <span style="color: #808080;">AND</span> T4.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">004</span><span style="color: #ff0000;">‘</span> 
  17. <span style="color: #0000ff;">WHERE</span> student.S#<span style="color: #808080;">=</span>SC.S# <span style="color: #808080;">and</span> 
  18. <span style="color: #ff00ff;">ISNULL</span>(T1.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T2.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T3.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T4.score,<span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">) 
  19. </span><span style="color: #808080;">NOT</span> <span style="color: #808080;">IN</span><span style="color: #000000;"> 
  20. (</span><span style="color: #0000ff;">SELECT</span> 
  21. <span style="color: #0000ff;">DISTINCT</span> 
  22. <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">15</span> <span style="color: #0000ff;">WITH</span><span style="color: #000000;"> TIES 
  23. </span><span style="color: #ff00ff;">ISNULL</span>(T1.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T2.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T3.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T4.score,<span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">) 
  24. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> sc 
  25. </span><span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> sc <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T1 
  26. </span><span style="color: #0000ff;">ON</span> sc.S# <span style="color: #808080;">=</span> T1.S# <span style="color: #808080;">AND</span> T1.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">k1</span><span style="color: #ff0000;">‘</span> 
  27. <span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> sc <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T2 
  28. </span><span style="color: #0000ff;">ON</span> sc.S# <span style="color: #808080;">=</span> T2.S# <span style="color: #808080;">AND</span> T2.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">k2</span><span style="color: #ff0000;">‘</span> 
  29. <span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> sc <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T3 
  30. </span><span style="color: #0000ff;">ON</span> sc.S# <span style="color: #808080;">=</span> T3.S# <span style="color: #808080;">AND</span> T3.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">k3</span><span style="color: #ff0000;">‘</span> 
  31. <span style="color: #808080;">LEFT</span> <span style="color: #808080;">JOIN</span> sc <span style="color: #0000ff;">AS</span><span style="color: #000000;"> T4 
  32. </span><span style="color: #0000ff;">ON</span> sc.S# <span style="color: #808080;">=</span> T4.S# <span style="color: #808080;">AND</span> T4.C# <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">k4</span><span style="color: #ff0000;">‘</span> 
  33. <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> <span style="color: #ff00ff;">ISNULL</span>(T1.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T2.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T3.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #808080;">+</span> <span style="color: #ff00ff;">ISNULL</span>(T4.score,<span style="color: #800000; font-weight: bold;">0</span>) <span style="color: #0000ff;">DESC</span>)

 

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

  1. <span style="color: #0000ff;">SELECT</span> SC.C# <span style="color: #0000ff;">as</span> 课程ID, Cname <span style="color: #0000ff;">as</span><span style="color: #000000;"> 课程名称 
  2. ,</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> score <span style="color: #808080;">BETWEEN</span> <span style="color: #800000; font-weight: bold;">85</span> <span style="color: #808080;">AND</span> <span style="color: #800000; font-weight: bold;">100</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">100 - 85</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> 
  3. ,</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> score <span style="color: #808080;">BETWEEN</span> <span style="color: #800000; font-weight: bold;">70</span> <span style="color: #808080;">AND</span> <span style="color: #800000; font-weight: bold;">85</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">85 - 70</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> 
  4. ,</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> score <span style="color: #808080;">BETWEEN</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #808080;">AND</span> <span style="color: #800000; font-weight: bold;">70</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">70 - 60</span><span style="color: #ff0000;">]</span><span style="color: #000000;"> 
  5. ,</span><span style="color: #ff00ff;">SUM</span>(<span style="color: #ff00ff;">CASE</span> <span style="color: #0000ff;">WHEN</span> score <span style="color: #808080;"><</span> <span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">THEN</span> <span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">ELSE</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #0000ff;">END</span>) <span style="color: #0000ff;">AS</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">60 -</span><span style="color: #ff0000;">]</span> 
  6. <span style="color: #0000ff;">FROM</span><span style="color: #000000;"> SC,Course 
  7. </span><span style="color: #0000ff;">where</span> SC.C#<span style="color: #808080;">=</span><span style="color: #000000;">Course.C# 
  8. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span> SC.C#,Cname

 

24、查询学生平均成绩及其名次

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #808080;">+</span>(<span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">COUNT</span>( <span style="color: #0000ff;">distinct</span><span style="color: #000000;"> 平均成绩) 
  2. </span><span style="color: #0000ff;">FROM</span> (<span style="color: #0000ff;">SELECT</span> S#,<span style="color: #ff00ff;">AVG</span>(score) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 平均成绩 
  3. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> SC 
  4. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> S# 
  5. ) </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> T1 
  6. </span><span style="color: #0000ff;">WHERE</span> 平均成绩 <span style="color: #808080;">></span> T2.平均成绩) <span style="color: #0000ff;">as</span><span style="color: #000000;"> 名次, 
  7. S# </span><span style="color: #0000ff;">as</span><span style="color: #000000;"> 学生学号,平均成绩 
  8. </span><span style="color: #0000ff;">FROM</span> (<span style="color: #0000ff;">SELECT</span> S#,<span style="color: #ff00ff;">AVG</span><span style="color: #000000;">(score) 平均成绩 
  9. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> SC 
  10. </span><span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span><span style="color: #000000;"> S# 
  11. ) </span><span style="color: #0000ff;">AS</span><span style="color: #000000;"> T2 
  12. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> 平均成绩 <span style="color: #0000ff;">desc</span>

 

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

  1. <span style="color: #0000ff;">SELECT</span> t1.S# <span style="color: #0000ff;">as</span> 学生ID,t1.C# <span style="color: #0000ff;">as</span> 课程ID,Score <span style="color: #0000ff;">as</span><span style="color: #000000;"> 分数 
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> SC t1 
  3. </span><span style="color: #0000ff;">WHERE</span> score <span style="color: #808080;">IN</span> (<span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">TOP</span> <span style="color: #800000; font-weight: bold;">3</span><span style="color: #000000;"> score 
  4. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> SC 
  5. </span><span style="color: #0000ff;">WHERE</span> t1.C#<span style="color: #808080;">=</span><span style="color: #000000;"> C# 
  6. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> score <span style="color: #0000ff;">DESC</span><span style="color: #000000;"> 
  7. ) 
  8. </span><span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> t1.C#;

 

26、查询每门课程被选修的学生数

  1. <span style="color: #0000ff;">select</span> c#,<span style="color: #ff00ff;">count</span>(S#) <span style="color: #0000ff;">from</span> sc <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> C#;

 

27、查询出只选修了一门课程的全部学生的学号和姓名

  1. <span style="color: #0000ff;">select</span> SC.S#,Student.Sname,<span style="color: #ff00ff;">count</span>(C#) <span style="color: #0000ff;">AS</span><span style="color: #000000;"> 选课数 
  2. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> SC ,Student 
  3. </span><span style="color: #0000ff;">where</span> SC.S#<span style="color: #808080;">=</span>Student.S# <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> SC.S# ,Student.Sname <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(C#)<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">1</span>;

 

28、查询男生、女生人数

  1. <span style="color: #0000ff;">Select</span> <span style="color: #ff00ff;">count</span>(Ssex) <span style="color: #0000ff;">as</span> 男生人数 <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> Ssex <span style="color: #0000ff;">having</span> Ssex<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">男</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">; 
  2. </span><span style="color: #0000ff;">Select</span> <span style="color: #ff00ff;">count</span>(Ssex) <span style="color: #0000ff;">as</span> 女生人数 <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> Ssex <span style="color: #0000ff;">having</span> Ssex<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">女</span><span style="color: #ff0000;">‘</span>;

 

29、查询姓“张”的学生名单

  1. <span style="color: #0000ff;">SELECT</span> Sname <span style="color: #0000ff;">FROM</span> Student <span style="color: #0000ff;">WHERE</span> Sname <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">张%</span><span style="color: #ff0000;">‘</span>;

 

30、查询同名同性学生名单，并统计同名人数

  1. <span style="color: #0000ff;">select</span> Sname,<span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">from</span> Student <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> Sname <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">1</span>;;

 

31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

  1. <span style="color: #0000ff;">select</span> Sname, <span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">char</span> (<span style="color: #800000; font-weight: bold;">11</span>),<span style="color: #ff00ff;">DATEPART</span>(<span style="color: #ff00ff;">year</span>,Sage)) <span style="color: #0000ff;">as</span><span style="color: #000000;"> age 
  2. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> student 
  3. </span><span style="color: #0000ff;">where</span> <span style="color: #ff00ff;">CONVERT</span>(<span style="color: #0000ff;">char</span>(<span style="color: #800000; font-weight: bold;">11</span>),<span style="color: #ff00ff;">DATEPART</span>(<span style="color: #ff00ff;">year</span>,Sage))<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1981</span><span style="color: #ff0000;">‘</span>;

 

32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

  1. <span style="color: #0000ff;">Select</span> C#,<span style="color: #ff00ff;">Avg</span>(score) <span style="color: #0000ff;">from</span> SC <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> C# <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> <span style="color: #ff00ff;">Avg</span>(score),C# <span style="color: #0000ff;">DESC</span> ;

 

33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

  1. <span style="color: #0000ff;">select</span> Sname,SC.S# ,<span style="color: #ff00ff;">avg</span><span style="color: #000000;">(score) 
  2. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> Student,SC 
  3. </span><span style="color: #0000ff;">where</span> Student.S#<span style="color: #808080;">=</span>SC.S# <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> SC.S#,Sname <span style="color: #0000ff;">having</span> <span style="color: #ff00ff;">avg</span>(score)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">85</span>;

 

34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数  

  1. <span style="color: #0000ff;">Select</span> Sname,<span style="color: #ff00ff;">isnull</span>(score,<span style="color: #800000; font-weight: bold;">0</span><span style="color: #000000;">) 
  2. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> Student,SC,Course 
  3. </span><span style="color: #0000ff;">where</span> SC.S#<span style="color: #808080;">=</span>Student.S# <span style="color: #808080;">and</span> SC.C#<span style="color: #808080;">=</span>Course.C# <span style="color: #808080;">and</span> Course.Cname<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">数据库</span><span style="color: #ff0000;">‘</span><span style="color: #808080;">and</span> score <span style="color: #808080;"><</span><span style="color: #800000; font-weight: bold;">60</span>;

 

35、查询所有学生的选课情况;

  1. <span style="color: #0000ff;">SELECT</span><span style="color: #000000;"> SC.S#,SC.C#,Sname,Cname 
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> SC,Student,Course 
  3. </span><span style="color: #0000ff;">where</span> SC.S#<span style="color: #808080;">=</span>Student.S# <span style="color: #808080;">and</span> SC.C#<span style="color: #808080;">=</span>Course.C# ;

 

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;

  1. <span style="color: #0000ff;">SELECT</span> <span style="color: #0000ff;">distinct</span><span style="color: #000000;"> student.S#,student.Sname,SC.C#,SC.score 
  2. </span><span style="color: #0000ff;">FROM</span><span style="color: #000000;"> student,Sc 
  3. </span><span style="color: #0000ff;">WHERE</span> SC.score<span style="color: #808080;">>=</span><span style="color: #800000; font-weight: bold;">70</span> <span style="color: #808080;">AND</span> SC.S#<span style="color: #808080;">=</span>student.S#;

 

37、查询不及格的课程，并按课程号从大到小排列

  1. <span style="color: #0000ff;">select</span> c# <span style="color: #0000ff;">from</span> sc <span style="color: #0000ff;">where</span> score <span style="color: #808080;"><</span><span style="color: #800000; font-weight: bold;">60</span> <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span> C# ;

 

38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;

  1. <span style="color: #0000ff;">select</span> SC.S#,Student.Sname <span style="color: #0000ff;">from</span> SC,Student <span style="color: #0000ff;">where</span> SC.S#<span style="color: #808080;">=</span>Student.S# <span style="color: #808080;">and</span> Score<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">80</span> <span style="color: #808080;">and</span> C#<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">003</span><span style="color: #ff0000;">‘</span>;

 

39、求选了课程的学生人数

  1. <span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">count</span>(<span style="color: #808080;">*</span>) <span style="color: #0000ff;">from</span> sc;

 

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

  1. <span style="color: #0000ff;">select</span><span style="color: #000000;"> Student.Sname,score 
  2. </span><span style="color: #0000ff;">from</span><span style="color: #000000;"> Student,SC,Course C,Teacher 
  3. </span><span style="color: #0000ff;">where</span> Student.S#<span style="color: #808080;">=</span>SC.S# <span style="color: #808080;">and</span> SC.C#<span style="color: #808080;">=</span>C.C# <span style="color: #808080;">and</span> C.T#<span style="color: #808080;">=</span>Teacher.T# <span style="color: #808080;">and</span> Teacher.Tname<span style="color: #808080;">=</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">叶平</span><span style="color: #ff0000;">‘</span> <span style="color: #808080;">and</span> SC.score<span style="color: #808080;">=</span>(<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">max</span>(score)<span style="color: #0000ff;">from</span> SC <span styl="" <="" div="">
  4.                   
  5.  
  6.                     <div class="">
  7.                         <ul class="m-news-opt fix">
  8.                             <li class="opt-item">
  9.                                 <a href="/sql_question-438545.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">PLSQLDeveloper中文显示乱码的解决方法</p></a>
  10.                             </li>
  11.                             <li class="opt-item ta-r">
  12.                                  <a href="/sql_question-438547.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">mysql之存储过程和触发器的应用</p></a>
  13.                             </li>
  14.                         </ul>
  15.                     </div>
  16.                     
  17.                     
  18.                     
  19.                     
  20.                 </span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！