必看，经典sql面试题3（学生表_课程表_成绩表_教师表）

点击链接加入QQ群 522720170（免费公开课、视频应有尽有）：https://jq.qq.com/?_wv=1027&k=5C08ATe

40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 
    select Student.Sname,score 
    from Student,SC,Course C,Teacher 
    where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# ); 

41、查询各个课程及相应的选修人数 
    select count(*) from sc group by C#; 

42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 
  select distinct A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ; 

43、查询每门功成绩最好的前两名 
    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数 
      FROM SC t1 
      WHERE score IN (SELECT TOP 2 score 
              FROM SC 
              WHERE t1.C#= C# 
            ORDER BY score DESC              ) 
      ORDER BY t1.C#; 

44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列  
    select C# as 课程号,count(*) as 人数 
    from  sc  
    group by  C# 
    order by count(*) desc,c#   

45、检索至少选修两门课程的学生学号 
    select  S#  
    from  sc  
    group by  s# 
    having count(*) > = 2 

46、查询全部学生都选修的课程的课程号和课程名 
    select  C#,Cname  
    from  Course  
    where C# in (select c# from sc group by  c#) 
   

47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 
    select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平'); 

48、查询两门以上不及格课程的同学的学号及其平均成绩 
    select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#; 

49、检索“004”课程分数小于60，按分数降序排列的同学学号 
    select S# from SC where C#='004'and score <60 order by score desc; 

50、删除“002”同学的“001”课程的成绩 delete from Sc where S#='001'and C#='001';