selectcasewhen Ssex = N'男'then N'男生人数'else N'女生人数'end[男女情况] , count(1) [人数]from student groupbycasewhen Ssex = N'男'then N'男生人数'else N'女生人数'end?
--29、查询名字中含有"风"字的学生信息
select * fromstudent where sname like'%风%'?
select*from student where sname like N'%风%'
select*from student wherecharindex(N'风' , sname) >0
--30、查询同名同性学生名单，并统计同名人数
思路：按照姓名字段进行GROUP BY，同时计算人数，只要大于1，就是同姓同名。
select sname,count(1) as '人数' from student group by sname having count(1)>1?
select Sname [学生姓名], count(*) [人数]from Student groupby Sname havingcount(*) >1
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select * fromstudent where datepart(year,sage)='1990'?
select*from Student whereyear(sage) =1990
select*from Student wheredatediff(yy,sage,'1990-01-01') =0
select*from Student wheredatepart(yy,sage) =1990
select*from Student whereconvert(varchar(4),sage,120) ='1990'?
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
思路：每门课程的平均成绩（一个记录集合），再使用函数排序，排序时根据平均成绩、课程编号。
select row_number() over(order by convert(decimal(18,2),avg(a.score)) desc,b.cid) as '排名',b.cid,b.cname,convert(decimal(18,2),avg(a.score)) as '平均成绩' from sc a inner join course b
on a.cid=b.cid groupby b.cid,b.cname?
select m.Cid , m.Cname , cast(avg(n.score) asdecimal(18,2)) avg_score
from Course m, SC n
where m.Cid = n.Cid
groupby m.Cid , m.Cname
orderby avg_score desc, m.Cid asc?
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s.sid,s.sname,convert(decimal(18,2),avg(a.score)) as '平均成绩' from student s inner join sc a
on s.sid=a.sid groupby s.sid,s.sname having avg(a.score)>=85?
select a.Sid , a.Sname , cast(avg(b.score) asdecimal(18,2)) avg_score
from Student a , sc b
where a.Sid = b.Sid
groupby a.Sid , a.Sname
havingcast(avg(b.score) asdecimal(18,2)) >=85
orderby a.Sid?
--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.sid,s.sname,b.cname,a.score from student sinner join sc a
on s.sid=a.sid innerjoin course b
on a.cid=b.cid
where b.cname='数学' and a.score<60?
select sname , score
from Student , SC , Course
where SC.Sid = Student.Sid and SC.Cid = Course.Cid and Course.Cname = N'数学'and score <60
--35、查询所有学生的课程及分数情况；
select s.sid,s.sname,b.cid,b.cname,a.score
from student s inner join sc a on s.sid=a.sid inner join course b on a.cid=b.cid?
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course
where Student.Sid = SC.Sid and SC.Cid = Course.Cid
orderby Student.Sid , SC.Cid?
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select s.sid,s.sname,b.cid,b.cname,a.score from student sinner join sc a
on s.sid=a.sid innerjoin course b
on a.cid=b.cid
where a.score>70?
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course
where Student.Sid = SC.Sid and SC.Cid = Course.Cid and SC.score >=70
orderby Student.Sid , SC.Cid?
--37、查询不及格的课程
select s.sid,s.sname,b.cid,b.cname,a.score from student sinner join sc a
on s.sid=a.sid innerjoin course b
on a.cid=b.cid
where a.score<60?
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, SC , Course
where Student.Sid = SC.Sid and SC.Cid = Course.Cid and SC.score <60
orderby Student.Sid , SC.Cid?
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select s.sid,s.sname,b.cid,b.cname,a.score from student sinner join sc a
on s.sid=a.sid innerjoin course b
on a.cid=b.cid
where a.score>=80 and b.cid='01'?
select Student.* , Course.Cname , SC.Cid ,SC.score
from Student, S