学生——成绩表2.2

一、表说明同“学生——成绩表2.1”

二、目录

选课情况

1.       查询学过"张三"老师授课的同学的信息

2.       查询没学过"张三"老师授课的同学的信息

3.       查询选修了全部课程的学生信息

4.       查询没有学全所有课程的同学的信息

5.       查询出只选有两门课程的全部学生的学号和姓名

6.       检索至少选修两门课程的学生学号

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

8.       查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

9.       查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

10.    查询学过编号为"01"并且也学过编号为"02"的但是没有学过课程"03"的同学的信息

11.    查询至少有一门课与学号为"01"的同学所学相同的同学的信息

12.    查询和"01"号的同学学习的课程完全相同的其他同学的信息

三、查询

1.查询学过"张三"老师授课的同学的信息

select distinct Student.* 
from Student , SCore , Course , Teacher
where Student.s_id= SCore.s_id and SCore.C_id = Course.C_id and Course.T_id = Teacher.T_id and Teacher.Tname = '张三'
order by Student.S_id

2.查询没学过"张三"老师授课的同学的信息

select student.* 
from student 
where student.S_id not in
(select distinct score.S_id from score , course , teacher where score.C_id = course.C_id and course.T_id = teacher.T_id and teacher.tname = N'张三')
order by student.S_id

分析：not in用于查询某表（或结果表）中没有的数据。

3.查询选修了全部课程的学生信息（即参加了所有课程的考试）

方法1

select student.* from student where S_id in
(select S_id from score group by S_id having count(1) = (select count(1) from course))

分析：选修了全部课程，即参加了所有课程的考试，也就是说某位同学在score表中有三条记录（因为有三门课程），因此将score表按s_id进行分组，统计其条数，只要此条数与course表中的条数相等，即表明该组的s_id对应的同学参加了所有的考试。此SQL考察了group by与聚合函数的运用。

注：此查询可以不用嵌套，如下SQL：

select student.* from student
left join score on student.s_id=score.s_id
group by student.s_id
having count(1) =(select count(1) from course)

方法2 使用双重否定来完成 （注：里面一层的嵌套的where not exists 可换成 where m.s_id not in)

select t.* from student t where t.S_id not in(
  select distinct m.S_id from
  ( select S_id , C_id from student , course) m where not exists (select 1 from score n where n.S_id = m.S_id and n.C_id = m.C_id)
)

分析：此SQL的查询逻辑是先查出有缺考的同学的s_id，再根据not in 查询没有缺考的（即参加了所有课程考试）同学信息，这样就可以从student表中查出指定结果了。

现在来具体看一下是如何把有缺考的同学的s_id(m.s_id)查出来的——其实也很简单，先得到s_id与c_id的所有情况，即下面的SQL：

select m.* from
  ( select S_id , C_id from student , course) m

结果：

然后与score表进行比对，利用not exists就可以查出上面结果中有的而在score表不存在的记录（上面结果中的蓝色框框中的记录）——即缺考记录，用 select distinct m.S_id 就查出了缺考记录中同学的s_id(5,6,7,8)，最后再查一下student表，用not in即可查出参加了所有课程考试同学信息——即选修了所有课程的同学信息。

方法3 使用双重否定来完成

select t.* from student t where not exists(select 1 from(
select distinct m.S_id from(select S_id , C_id from student , course) m where not exists (select 1 from score n where n.S_id = m.S_id and n.C_id = m.C_id)
) k where k.S_id = t.S_id)

4.  查询没有学全所有课程的同学的信息

4.1不包括什么课都没选的同学（注：可把group by 中的 Student.Sname , Student.Sage , Student.Ssex 去掉）

select Student.*
from Student , SCore
where Student.S_id = SCore.S_id
group by Student.S_id , Student.Sname , Student.Sage , Student.Ssex having count(C_id) < (select count(C_id) from Course)

4.2包括什么课都没选的同学

select Student.*
from Student left join SCore
on Student.S_id = SCore.S_id
group by Student.S_id , Student.Sname , Student.Sage , Student.Ssex having count(C_id) < (select count(C_id) from Course)

5.查询出只选有两门课程的全部学生的学号和姓名

select Student.S_id, Student.Sname
from Student , SCore
where Student.S_id = SCore.S_id
group by Student.S_id , Student.Sname
having count(SCore.S_id) = 2
order by Student.S_id

6. 检索至少选修两门课程的学生学号

select student.S_id, student.Sname
from student , SCore
where student.S_id = SCore.S_id
group by student.S_id , student.Sname
having count(1) >= 2
order by student.S_id

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

7.1 只在score表中查询

select c_id , count(S_id) 学生数 from score group by c_id

7.2在score表、Course表中查询

select Course.C_id , Course.Cname , count(*) 学生人数
from Course , SCore
where Course.C_id = SCore.C_id
group by  Course.C_id , Course.Cname
order by Course.C_id , Course.Cname

7.3 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select Course.C_id, Course.Cname , count(*) 学生人数
from Course , SCore
where Course.C_id = SCore.C_id
group by  Course.C_id , Course.Cname
having count(*) >= 5
order by 学生人数 desc , Course.C_id

8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

方法1(注：下面SQL可以将exists用in代替，'01' and exists 换成and SCore.S_id in（或and Student.S_id in），Select 1 换成Select SC_2.S_id。)

select Student.* from Student , SCore 
where Student.S_id = SCore.S_id and SCore.C_id= '01' and exists (
Select 1 from SCore SC_2 where SC_2.S_id = SCore.S_id and SC_2.C_id= '02') 
order by Student.S_id

分析：1.该SQL的查询逻辑是首先查出符合学过编号为"01"课程这个条件的同学信息，然后对于此结果再加一条限定条件，即该结果中的s_id对应的同学也学过编号为"02"的课程，关键在于如何联结这两个条件呢？——exists。2.现在通过分析此SQL的执行过程来说明一下exists是什么意思。见下图：

在查询的过程中，分析器会将外查询的结果的每一行代入内查询验证，为了反映内查询的结果，我们现在看下“Sql A”的执行结果（“结果A”），如分析器在分析“外查询结果”的第一行记录（赵雷那一行）时，代入内查询验证，内查询会查询score表，分析后面的where条件后，发现有这一行（“结果A”中s_id=1，c_id=2的那一行），返回true，那么分析器会将这一行作为外查询的结果行，重复此动作，一直到“外查询结果”的最后一行（吴兰那一行），分析器在分析内查询的where条件后并不能从“Sql A”中找到符合条件的记录，返回false，那么分析器就不会将该行作为外查询的结果行，所以最后的查询记录有5条。

注意：exsits的意思并不是说将外查询的结果的每一条记录都在内查询中存在，而是以内查询与外查询的联结条件及其他条件（如果有的话）分析在内查询的表中是否存在该记录，如果存在，返回true，否则返回false，如果返回true，分析器就会将外查询的当前记录作为查询结果。

方法2（注：下面SQL中的distinct 可去掉）

select m.* from Student m where S_id in(
  select S_id from(
     select distinct S_id from SCore where C_id = '01'
     union all
     select distinct S_id from SCore where C_id = '02'
  ) t group by S_id having count(1) = 2
) order by m.S_id

分析：此SQL的查询逻辑是先得到参加课程01考试同学的s_id和参加课程02同学是s_id，然后在此结果集中以s_id分组，统计条数等于2的s_id——即既参加课程01又参加了课程02的同学的s_id，最后查下student表，用in即可查出指定结果。需注意的是，其中必须用union all而不是union，因为需要重复的s_id以进行分组统计。

结果：

9.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

方法1

select Student.* from Student , SCore 
where Student.S_id = SCore.S_id and SCore.C_id = '01' and not exists (
Select 1 from SCore SC_2 where SC_2.S_id = SCore.S_id and SC_2.C_id = '02') 
order by Student.S_id

方法2

select Student.* from Student , SCore 
where Student.S_id = SCore.S_id and SCore.C_id = '01' and Student.S_id not in (
Select SC_2.S_id from SCore SC_2 where SC_2.S_id = SCore.S_id and SC_2.C_id = '02') 
order by Student.S_id

10.查询学过编号为"01"并且也学过编号为"02"的但是没有学过课程"03"的同学的信息

select Student.* from Student , SCore 
where Student.S_id = SCore.S_id and SCore.C_id= '01' and exists (
Select 1 from SCore SC_2 where SC_2.S_id = SCore.S_id and SC_2.C_id= '02') and not exists(
Select 1 from SCore SC_3 where SC_3.S_id = SCore.S_id and SC_3.C_id= '03')
order by Student.S_id

11.查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct Student.* from Student , SCore 
where Student.S_id = SCore.S_id and SCore.C_id in (select C_id from SCore where S_id = '01') and Student.S_id <> '01'

分析：此SQL的查询逻辑在于先得到s_id=01同学的所有课程，再利用in进行查询。需注意的是，查询的结果需进行取重（因为有重复的结果）。

结果：

12.查询和"01"号的同学学习的课程完全相同的其他同学的信息（注：应该此SQL运用了group by ，所以可以去掉distinct）

select Student.* from Student where S_id in
(select distinct SCore.S_id from SCore where S_id <> '01' and SCore.C_id in (select distinct C_id from SCore where S_id = '01')
group by SCore.S_id having count(1) = (select count(1) from SCore where S_id='01'))

分析：此SQL的查询逻辑在于先得到至少有一门课程与01号同学相同的信息，再查询所有课程与01号同学相同的信息。

select distinct SCore.S_id from SCore where S_id <> '01' and SCore.C_id in (select distinct C_id from SCore where S_id = '01') 即是得到至少有一门课程与01号同学相同的信息（s_id）;

select distinct SCore.S_id from SCore where S_id <> '01' and SCore.C_id in (select distinct C_id from SCore where S_id = '01')
group by SCore.S_id having count(1) = (select count(1) from SCore where S_id='01') 即得到所有课程均与01号同学相同的信息（s_id）——01号同学选了三门课程，在score表中有三条记录，那么与他所选课程相同的同学也应该在socre表中有三条记录。

注意：实际上，此SQL可以直接查询所有课程与01号同学相同的信息，没有必要“先查询至少有一门课程与01号同学相同的信息，然后再以此结果查询所有课程与01号同学相同的信息”。SQL如下：

select * from student where s_id in
(select score.s_id from score where s_id<>1
group by score.s_id having count(1) =(select count(1) from score where s_id=1))