开始复习下sql，常用SQL语句示例（二）

--11，查询和"01"号的同学学习的课程完全相同的其他同学的信息

select s.*
from Student s,SC
where s.S#=SC.S# and SC.S#<>01 and C# in
(select C# from SC where S#=01)
group by s.S#,Sname,Sage,Ssex
having count(C#)=(select count(C#) from SC where S#=01)

 

--12、查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from Student where S# not in 
(select S# from SC, Course c,Teacher t
 where SC.C#=c.C# and c.T#=t.T# and Tname='张三'
)

 

--13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select s.S#,Sname,avg(score) 平均成绩
from Student s,SC
where s.S#=SC.S# and isnull(score,0)<60
group by s.S#,Sname
having count(C#)>=2

 

 --14、检索"01"课程分数小于60，按分数降序排列的学生信息

select s.*,C#,score
from Student s, SC
where s.S#=SC.S# and C#=01 and isnull(score,0)<60
order by score desc

 

 --15、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（用case语句）

select s.S#,Sname,
max(case Cname when '语文' then score else null end) as 语文,
max(case Cname when '数学' then score else null end) as 数学,
min(case Cname when '英语' then score else null end) as 英语,
avg(score) as 平均成绩
from Student s left join SC on s.S#=SC.S#
left join Course c on SC.C#=c.C#
group by s.S#, Sname
order by 平均成绩 desc