select count(Ssex) as 女生人数 from Student where Ssex = N'女'
select sum(case when Ssex = N'男' then 1 else 0 end) [男生人数],sum(case when Ssex = N'女' then 1 else 0 end) [女生人数] from student
select case when Ssex = N'男' then N'男生人数' else N'女生人数' end [男女情况] , count(1) [人数] from student group by case when Ssex = N'男' then N'男生人数' else N'女生人数' end
--29、查询名字中含有\风\字的学生信息 select * from student where sname like N'%风%' select * from student where charindex(N'风' , sname) > 0
--30、查询同名同性学生名单，并统计同名人数
select Sname [学生姓名], count(*) [人数] from Student group by Sname having count(*) > 1
--31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime) select * from Student where year(sage) = 1990
select * from Student where datediff(yy,sage,'1990-01-01') = 0 select * from Student where datepart(yy,sage) = 1990
select * from Student where convert(varchar(4),sage,120) = '1990'
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select m.C# , m.Cname , cast(avg(n.score) as decimal(18,2)) avg_score from Course m, SC n where m.C# = n.C# group by m.C# , m.Cname order by avg_score desc, m.C# asc
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 select a.S# , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score from Student a , sc b where a.S# = b.S# group by a.S# , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 85 order by a.S#
--34、查询课程名称为\数学\，且分数低于60的学生姓名和分数 select sname , score from Student , SC , Course
where SC.S# = Student.S# and SC.C# = Course.C# and Course.Cname = N'数学' and score < 60
--35、查询所有学生的课程及分数情况； select Student.* , Course.Cname , SC.C# , SC.score from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# order by Student.S# , SC.C#
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； select Student.* , Course.Cname , SC.C# , SC.score from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.score >= 70 order by Student.S# , SC.C#
--37、查询不及格的课程
select Student.* , Course.Cname , SC.C# , SC.score from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.score < 60 order by Student.S# , SC.C#
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名； select Student.* , Course.Cname , SC.C# , SC.score from Student, SC , Course
where Student.S# = SC.S# and SC.C# = Course.C# and SC.C# = '01' and SC.score >= 80 order by Student.S# , SC.C#
--39、求每门课程的学生人数
select Course.C# , Course.Cname , count(*) [学生人数] from Course , SC where Course.C# = SC.C#
group by Course.C# , Course.Cname order by Course.C# , Course.Cname
--40、查询选修\张三\老师所授课程的学生中，成绩最高的学生信息及其成绩 --40.1 当最高分只有一个时
select top 1 Student.* , Course.Cname , SC.C# , SC.score from Student, SC , Course , Teacher
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三' order by SC.score desc --40.2 当最高分出现多个时
select Student.* , Course.Cname , SC.C# , SC.score from Student, SC , Course , Teacher
where Student.S# = SC.S# and SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三' and
SC.score = (select max(SC.score) from SC , Course , Teacher where SC.C# = Course.C# and Course.T# = Teacher.T# and Teacher.Tname = N'张三')
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 --方法1
select m.* from SC m ,(select C# , score from SC group by C# , score having count(1) > 1) n where m.C#= n.C# and m.score = n.score order by m.C# , m.score , m.S# --方法2
select m.* from SC m where exists (select 1 from (select C# , score from SC group by C# , score having count(1) > 1) n
where m.C#= n.C# and m.score = n.score) order by m.C# , m.score , m.S#
--42、查询每门功成绩最好的前两名
select t.* from sc t where score in (select top 2 score from sc where C# = T.C# order by score desc) order by t.C# , t.score desc
--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 select Course.C# , Course.Cname , count(*) [学生人数] from Course , SC where Course.C# = SC.C#
group by Course.C# , Course.Cname having count(*) >= 5
order by [学生人数] desc , Course.C#
--44、检索至少选修两门课程的学生学号 select student.S# , student.Sname from student , SC where student.S# = SC.S#
group by student.S# , student.Sname having count(1) >= 2 order by student.S#
--45、查询选修了全部课程的学生信息 --方法1 根据数量来完成
select student.* from student where S# in
(select S# from sc group by S# having count(1) = (select count(1) from course)) --方法2 使用双重否定来完成 select t.* from student t where t.S# not in (
select distinct m.S# from (
select S# , C# from student , course
) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#) )
--方法3 使用双重否定来完成
select t.* from student t where not exists(select 1 from (
select distinct m.S# from (
select S# , C# from student , course
) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#) ) k where k.S# = t.S# )
--46、查询各学生的年龄 --46.1 只按照年份来算
select * , datediff(yy , sage , getdate()) [年龄] from student
--46.2 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一 select * , case when right(convert(varchar(10),getdate(),120),5) <
right(convert(varchar(10),sage,120),5) then datediff(yy , sage , getdate()) - 1 else datediff(yy , sage , getdate()) end [年龄] from student
--47、查询本周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--48、查询下周过生日的学生
select * from student where datediff(week,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
--49、查询本月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = 0
--50、查询下月过生日的学生
select * from student where datediff(mm,datename(yy,getdate()) + right(convert(varchar(10),sage,120),6),getdate()) = -1
drop table Student,Course,Teacher,SC