项目中常用的sql整理和详细02

--还是四张表
--1.学生表
--Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
--Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表
--Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表
--SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数

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

--查询李姓老师的数量
   0.1 select COUNT(*) from Teacher a where a.Tname like '李%'
--LEFT(a.Tname,1)的意思的Tname列中左边第一个字符的值
   0.2 select COUNT(*) from Teacher a where LEFT(a.Tname,1) like'李'

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

--查询学过张三老师课程的学生信息
   select Student.*from Student where student.Sname not in( select a.Sname from Student a,SC b,Teacher c ,Course d
   where c.Tname='张三' and d.T#=c.T# and d.C#=b.C# and a.S#=b.S# and b.C#=d.C#)

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

--查询与学生编号为01所学课程一样的学生信息
--常规方法:依次使用exists检查学生所学课程是否与01学生的一致,假设01号学生学了编号为'01','02','03'课程
--exists将外查询表的每一行,代入内查询作为检验,如果内查询返回的结果取非空值,则EXISTS子句返回TRUE
  01.select Student.* from Student  where Student.S#<>'01'
       group by Student.S#,Student.Sage,Student.Sname,Student.Ssex
       having exists(select Student.S# from SC where Student.S#=SC.S# and SC.C#='01' )
       and exists(select Student.S# from SC where Student.S#=SC.S# and SC.C#='02' )
       and exists(select Student.S# from SC where Student.S#=SC.S# and SC.C#='03' )
--特殊情况(01号学生学完了所有课程)可以在having中使用count函数，结果与上面一样
--在having子句中,count函数将外查询的每一行,代入内查询中查询并返回结果
  02.select Student.* from Student where Student.S# in
      (select  SC.S# from SC where SC.S#<>'01' and SC.C# in(select  SC.C# from SC where SC.S#='01') 
      group by SC.S#
      having COUNT(*)=(select COUNT(*)from SC where SC.S#='01'))

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

--查询没学过"张三"老师讲授的任一门课程的学生信息

  01.select Student.* from Student where Student.S# not in
      (select distinct SC.S# from SC,Course  
      where SC.C#=Course.C#
      and Course.C#=(select Course.C# from Course where Course.T#=(select Teacher.T# from Teacher where Teacher.Tname='张三')))

 02.select student.* from student where student.S# not in
     (select distinct sc.S# from sc , course , teacher where sc.C# = course.C# and course.T# = teacher.T# and teacher.tname = N'张三')
     order by student.S#

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

--查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
   01.select Student.S#,Student.Sname,avg(sc.score) [平均成绩] from Student,SC
       where Student.S#=SC.S#
       group by Student.S#,Student.Sname
       having 1<(select COUNT(SC.C#) from SC where score<60 and SC.S#=Student.S#)
  02.select student.S# , student.sname , cast(avg(score) as decimal(18,2)) [平均成绩] from student , sc
      where student.S# = SC.S# and student.S# in (select S# from SC where score < 60 group by S# having count(1) >= 2)
      group by student.S# , student.sname