SQL 题1

最新推荐文章于 2023-05-10 21:23:39 发布

lonelinecat

最新推荐文章于 2023-05-10 21:23:39 发布

阅读量414

点赞数

分类专栏：面试题-SQL 文章标签： sql c# 数据库 join uml insert

本文链接：https://blog.csdn.net/lonelinecat/article/details/6446990

版权

本文通过一系列SQL查询实例，详细讲解了如何处理学生成绩、课程、教师等数据，包括成绩对比、平均分计算、多条件筛选、成绩排名等复杂操作，旨在帮助读者深入理解SQL在实际中的应用。

摘要由CSDN通过智能技术生成

    
    
    
     
     
     Student(S#,Sname,Sage,Ssex) 学生表 
Course(C#,Cname,T#) 课程表 
SC(S#,C#,score) 成绩表 
Teacher(T#,Tname) 教师表 

问题： 

     
     1
     
     、查询“
     
     001
     
     ”课程比“
     
     002
     
     ”课程成绩高的所有学生的学号； 
  
     
     select
     
      a.S# 
     
     from
     
      (
     
     select
     
      s#,score 
     
     from
     
      SC 
     
     where
     
      C#
     
     =
     
     '
     
     001
     
     '
     
     ) a,(
     
     select
     
      s#,score 
  
     
     from
     
      SC 
     
     where
     
      C#
     
     =
     
     '
     
     002
     
     '
     
     ) b 
  
     
     where
     
      a.score
     
     >
     
     b.score 
     
     and
     
      a.s#
     
     =
     
     b.s#; 

     
     2
     
     、查询平均成绩大于60分的同学的学号和平均成绩； 
    
     
     select
     
      S#,
     
     avg
     
     (score) 
    
     
     from
     
      sc 
    
     
     group
     
      
     
     by
     
      S# 
     
     having
     
      
     
     avg
     
     (score) 
     
     >
     
     60
     
     ; 

     
     3
     
     、查询所有同学的学号、姓名、选课数、总成绩； 
  
     
     select
     
      Student.S#,Student.Sname,
     
     count
     
     (SC.C#),
     
     sum
     
     (score) 
  
     
     from
     
      Student 
     
     left
     
      
     
     Outer
     
      
     
     join
     
      SC 
     
     on
     
      Student.S#
     
     =
     
     SC.S# 
  
     
     group
     
      
     
     by
     
      Student.S#,Sname 

     
     4
     
     、查询姓“李”的老师的个数； 
  
     
     select
     
      
     
     count
     
     (
     
     distinct
     
     (Tname)) 
  
     
     from
     
      Teacher 
  
     
     where
     
      Tname 
     
     like
     
      
     
     '
     
     李%
     
     '
     
     ; 

     
     5
     
     、查询没学过“叶平”老师课的同学的学号、姓名； 
    
     
     select
     
      Student.S#,Student.Sname 
    
     
     from
     
      Student  
    
     
     where
     
      S# 
     
     not
     
      
     
     in
     
      (
     
     select
     
      
     
     distinct
     
     ( SC.S#) 
     
     from
     
      SC,Course,Teacher 
     
     where
     
       SC.C#
     
     =
     
     Course.C# 
     
     and
     
      Teacher.T#
     
     =
     
     Course.T# 
     
     and
     
      Teacher.Tname
     
     =
     
     '
     
     叶平
     
     '
     
     ); 

     
     6
     
     、查询学过“
     
     001
     
     ”并且也学过编号“
     
     002
     
     ”课程的同学的学号、姓名； 
  
     
     select
     
      Student.S#,Student.Sname 
     
     from
     
      Student,SC 
     
     where
     
      Student.S#
     
     =
     
     SC.S# 
     
     and
     
      SC.C#
     
     =
     
     '
     
     001
     
     '
     
     and
     
      
     
     exists
     
     ( 
     
     Select
     
      
     
     *
     
      
     
     from
     
      SC 
     
     as
     
      SC_2 
     
     where
     
      SC_2.S#
     
     =
     
     SC.S# 
     
     and
     
      SC_2.C#
     
     =
     
     '
     
     002
     
     '
     
     ); 

     
     7
     
     、查询学过“叶平”老师所教的所有课的同学的学号、姓名； 
  
     
     select
     
      S#,Sname 
  
     
     from
     
      Student 
  
     
     where
     
      S# 
     
     in
     
      (
     
     select
     
      S# 
     
     from
     
      SC ,Course ,Teacher 
     
     where
     
      SC.C#
     
     =
     
     Course.C# 
     
     and
     
      Teacher.T#
     
     =
     
     Course.T# 
     
     and
     
      Teacher.Tname
     
     =
     
     '
     
     叶平
     
     '
     
      
     
     group
     
      
     
     by
     
      S# 
     
     having
     
      
     
     count
     
     (SC.C#)
     
     =
     
     (
     
     select
     
      
     
     count
     
     (C#) 
     
     from
     
      Course,Teacher  
     
     where
     
      Teacher.T#
     
     =
     
     Course.T# 
     
     and
     
      Tname
     
     =
     
     '
     
     叶平
     
     '
     
     )); 

     
     8
     
     、查询课程编号“
     
     002
     
     ”的成绩比课程编号“
     
     001
     
     ”课程低的所有同学的学号、姓名； 
  
     
     Select
     
      S#,Sname 
     
     from
     
      (
     
     select
     
      Student.S#,Student.Sname,score ,(
     
     select
     
      score 
     
     from
     
      SC SC_2 
     
     where
     
      SC_2.S#
     
     =
     
     Student.S# 
     
     and
     
      SC_2.C#
     
     =
     
     '
     
     002
     
     '
     
     ) score2 
  
     
     from
     
      Student,SC 
     
     where
     
      Student.S#
     
     =
     
     SC.S# 
     
     and
     
      C#
     
     =
     
     '
     
     001
     
     '
     
     ) S_2 
     
     where
     
      score2 
     
     <
     
     score; 

     
     9
     
     、查询所有课程成绩小于60分的同学的学号、姓名； 
  
     
     select
     
      S#,Sname 
  
     
     from
     
      Student 
  
     
     where
     
      S# 
     
     not
     
      
     
     in
     
      (
     
     select
     
      Stu