--方法1
select Student.*from Student , SC where Student.Sid = SC.Sid and SC.Cid ='01'andnotexists (Select1from SC SC_2 where SC_2.Sid = SC.Sid and SC_2.Cid ='02') orderby Student.Sid
--方法2
select Student.*from Student , SC where Student.Sid = SC.Sid and SC.Cid ='01'and Student.Sid notin (Select SC_2.Sid from SC SC_2 where SC_2.Sid = SC.Sid and SC_2.Cid ='02') orderby Student.Sid?
--11、查询没有学全所有课程的同学的信息
思路：从全部学生中（一个记录集合）提取在SC表中课程总数不是全部的学生（一个记录集合）
select * fromstudent where sid in
(select sid from
(select sid,count(*) as abc from sc group by sid having count(*)<(select count(*) from course)) t)
该方法只列出有课程分数的学生，一个课程分数也没有的学生不存在第二个记录集合中。?
思路：从全部学生中（一个记录集合）排除在SC表中有全部课程分数的学生（一个记录集合）
select * fromstudent where sid notin
(select sid from
(select sid,count(*) as abc from sc group by sid having count(*)=(select count(*) from course)) t)
该方法还会列出一个课程分数都没有的学生。?
--11.1、
select Student.*
from Student , SC
where Student.Sid = SC.Sid
groupby Student.Sid , Student.Sname ,Student.Sage , Student.Ssex havingcount(Cid)< (selectcount(Cid) from Course)?
--11.2
select Student.*
from Student leftjoin SC
on Student.Sid = SC.Sid
groupby Student.Sid , Student.Sname ,Student.Sage , Student.Ssex havingcount(Cid)< (selectcount(Cid) from Course)?
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
思路：从全部学生中（一个记录集合）提取所学课程中至少有一门和学生01所学课程相同（一个记录集合）（也就是课程ID至少有一个存在于学生01的课程ID中）并排除学生01
select * fromstudent where sid in
(select distinct sid from sc where cid in
(select cid from sc where sid='01') and sid<>'01')?
selectdistinct Student.*from Student , SC where Student.Sid = SC.Sid and SC.Cid in (select Cid from SC where Sid ='01') and Student.Sid <>'01'?
--13☆、查询和"01"号的同学学习的课程完全相同的其他同学的信息
思路：从全部学生中（一个记录集合）提取所学全部课程ID存在于学生01的课程ID中并且课程总数等于学生01的课程总数（一个记录集合）
select * fromstudent where sid in
(select distinct sid from sc where cid in
(select cid from sc where sid='01') and sid<>'01' group by sid
having count(*)=(select count(*) from sc where sid='01'))?
select Student.*from Student where Sid in
(selectdistinct SC.Sid from SC where Sid <>'01'and SC.Cid in (selectdistinct Cid from SC where Sid ='01')
groupby SC.Sid havingcount(1) = (selectcount(1) from SC where Sid='01'))?
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
思路：从全部学生中（一个记录集合）排除学过老师张三上过的课的学生（一个记录集合）（就是在SC表中有张三老师上过的课的分数）
select * fromstudent where sid notin
(select distinct a.sid from sc a inner join course b
on a.cid=b.cid innerjoin teacher c
on b.tid=c.tid wherec.tname='张三')?
select student.*from student where student.Sid notin
(selectdistinct sc.Sid from sc , course , teacher where sc.Cid = course.Cid and course.Tid = teacher.Tid and teacher.tname = N'张三')
orderby student.Sid?
--15☆、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
思路：全部学生（一个记录集合），两门及以上不及格课程（一个记录集合）
select * fromstudent s inner join
(select sid,count(*) as '不及格课程总数',convert(decimal(18,2),avg(score)) as '平均分数' from sc where score<60 group by sid having count(*)>=2) b
on s.sid=b.sid?
select s.sid,s.sname,convert(decimal(5,2),avg(a.score)) as average fromstudent s inner joinsc a
on s.sid=a.sid groupby s.sid,s.sname having s.sid in
(select sid from
(select sid,count(*) as times from sc where score<60 group by sid having count(*)>=2) t)?
select student.Sid ,student.sname , cast(avg(score) asdecimal(18,2)) avg_score from student , sc
where student.Sid = SC.Sid and student.Sid in (select Sid from SC where score <60groupby Sid havingcount(1) >=2)
groupby student.Sid , student.sname?
--16、检索"01"课程分数小于60，按分数降序排列的学生信息
思路：全部学生（一个记录集合），课程01分数小于60（一个记录集合）
selec