where Student.Sid = SC.Sid and SC.Cid = Course.Cid and SC.Cid ='01'and SC.score >=80
orderby Student.Sid , SC.Cid?
--39、求每门课程的学生人数
select b.cid,b.cname,count(1) as '人数' from sc a inner join course b
on a.cid=b.cid groupby b.cid,b.cname?
select Course.Cid , Course.Cname, count(*) [学生人数]
from Course , SC
where Course.Cid = SC.Cid
groupby Course.Cid , Course.Cname
orderby Course.Cid , Course.Cname?
--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
思路：上张三老师课的学生（一个记录集合）
select top 1 * from student s inner join sc a
on s.sid=a.sid innerjoin course b
on a.cid=b.cid innerjoin teacher c
on b.tid=c.tid wherec.tname='张三' order by a.score desc?
--40.1 当最高分只有一个时
selecttop1 Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course ,Teacher
where Student.Sid = SC.Sid and SC.Cid = Course.Cid and Course.Tid = Teacher.Tid and Teacher.Tname = N'张三'
orderby SC.score desc?
--40.2 当最高分出现多个时
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course ,Teacher
where Student.Sid = SC.Sid and SC.Cid = Course.Cid and Course.Tid = Teacher.Tid and Teacher.Tname = N'张三'and
SC.score= (selectmax(SC.score) from SC , Course , Teacher where SC.Cid = Course.Cid and Course.Tid = Teacher.Tid and Teacher.Tname = N'张三')?
--41☆☆☆☆☆、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
思路：?
--方法1
select m.*from SC m ,(select Cid , score from SC groupby Cid , score havingcount(1) >1) n
where m.Cid= n.Cid and m.score = n.score orderby m.Cid , m.score , m.Sid
--方法2
select m.*from SC m whereexists (select1from (select Cid , score from SC groupby Cid , score havingcount(1) >1) n
where m.Cid= n.Cid and m.score = n.score) orderby m.Cid , m.score , m.Sid
--42、查询每门课程成绩最好的前两名
思路：每门课程全部成绩（一个记录集合）。
select * from (selectrow_number() over(partitionby cid order by score desc) as ranking,* from sc) a whereranking in (1,2)?
select t.*from sc t where score in (selecttop2 score from sc where Cid = T.Cid orderby score desc) orderby t.Cid , t.score desc?
--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select b.cid,b.cname,count(1) as '人数' from sc a inner join course b
on a.cid=b.cid groupby b.cid,b.cname having count(1)>5 order by count(1) desc,b.cid?
select Course.Cid , Course.Cname, count(*) [学生人数]
from Course , SC
where Course.Cid = SC.Cid
groupby Course.Cid , Course.Cname
havingcount(*) >=5
orderby[学生人数]desc , Course.Cid?
--44、检索至少选修两门课程的学生学号
select s.sid,s.sname,count(1) as '课程数' from student s inner join sc a
on s.sid=a.sid groupby s.sid,s.sname having count(1)>=2?
select student.Sid ,student.Sname
from student , SC
where student.Sid = SC.Sid
groupby student.Sid , student.Sname
havingcount(1) >=2
orderby student.Sid?
--45、查询选修了全部课程的学生信息
select s.sid,s.sname,count(1) as '课程数' from student s inner join sc a
on s.sid=a.sid groupby s.sid,s.sname having count(1)>=(select count(1) from course)?
--方法1 根据数量来完成
select student.*from student where Sid in
(select Sid from sc groupby Sid havingcount(1) = (selectcount(1) from course))
--方法2 使用双重否定来完成
select t.*from student t where t.Sid notin
(
selectdistinctm.Sid from
(
select Sid , Cid from student , course
) m wherenotexists (select1from sc n where n.Sid = m.Sid and n.Cid = m.Cid)
)
--方法3 使用双重否定来完成
select t.*from student t wherenotexists(select1from
(
selectdistinctm.Sid from
(
select Sid , Cid from student , course
) m wherenotexists (select1from sc n where n.Sid = m.Sid and n.Cid = m.Cid)
) k where k.Sid = t.Sid
)
--46、查询各学生的年龄