一、表说明同“学生——成绩表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 , Teacherwhere 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 studentleft join score on student.s_id=score.s_idgroup by student.s_idhaving 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 , SCorewhere Student.S_id = SCore.S_idgroup 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 SCoreon Student.S_id = SCore.S_idgroup 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.Snamefrom Student , SCorewhere Student.S_id = SCore.S_idgroup by Student.S_id , Student.Snamehaving count(SCore.S_id) = 2order by Student.S_id
6. 检索至少选修两门课程的学生学号
select student.S_id, student.Snamefrom student , SCorewhere student.S_id = SCore.S_idgroup by student.S_id , student.Snamehaving count(1) >= 2order 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 , SCorewhere Course.C_id = SCore.C_idgroup by Course.C_id , Course.Cnameorder by Course.C_id , Course.Cname
7.3 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select Course.C_id, Course.Cname , count(*) 学生人数from Course , SCorewhere Course.C_id = SCore.C_idgroup by Course.C_id , Course.Cnamehaving count(*) >= 5order 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<>1group by score.s_id having count(1) =(select count(1) from score where s_id=1))
