50个SQL语句(二)

on a.Sid = b.Sid and b.Cid ='01'leftjoin SC c

on a.Sid = c.Sid and c.Cid ='02'

where b.score >isnull(c.score,0)

--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

select * fromstudent s inner joinsc a

on s.sid=a.sid anda.cid='01' inner join sc b

on s.sid=b.sid andb.cid='02' where a.score

--2.1、查询同时存在"01"课程和"02"课程的情况

select a.* , b.score [课程'01'的分数],c.score [课程'02'的分数]from Student a , SC b , SC c

where a.Sid = b.Sid and a.Sid = c.Sid and b.Cid ='01'and c.Cid ='02'and b.score < c.score

--2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况

select * fromstudent s left joinsc a

on s.sid=a.sid and(a.cid='01' or a.cid is null) inner join sc b

on s.sid=b.sid andb.cid='02'

select * fromstudent s inner join

(select * from sc where cid='02') a on s.sid=a.sid left join

(select * from sc where (cid='01' or cid is null)) b on s.sid=b.sid

select a.* , b.score [课程"01"的分数],c.score [课程"02"的分数]from Student a

leftjoin SC b on a.Sid = b.Sid and b.Cid ='01'

leftjoin SC c on a.Sid = c.Sid and c.Cid ='02'

whereisnull(b.score,0) < c.score

--3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

思路：平均成绩大于等于60分（一个记录集合），STUDENT表（一个记录集合）

select s.sid,s.sname,b.[平均成绩] fromstudent s inner join

(select sid,convert(decimal(18,2),avg(score)) as '平均成绩' from sc group by sid having avg(score)>=60) b

on s.sid=b.sid

select * fromstudent s inner join

(select sid,avg(score) as avgscore from sc group by sid having avg(score)>=60) a

on s.sid=a.sid?

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)) >=60

orderby a.Sid?

--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

思路：平均成绩小于60分（一个记录集合），STUDENT（一个记录集合）

select s.sid,s.sname,b.[平均成绩] fromstudent s inner join

(select sid,convert(decimal(18,2),avg(score)) as '平均成绩' from sc group by sid having avg(score)>60) b

on s.sid=b.sid?

--4.1、查询在sc表存在成绩的学生信息的SQL语句。

思路：STUDENT表（一个记录集合）是否有记录包含在SC表（一个记录集合）

select * fromstudent where sid in(select sid from sc)?

select * fromstudent s where exists(select 1 from sc a where s.sid=a.sid)

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)) <60

orderby a.Sid

--4.2、查询在sc表中不存在成绩的学生信息的SQL语句。

select * from student where sid not in (select distinct sid from sc)

select * fromstudent s where notexists (select 1 from sc a where s.sid=a.sid)

select a.Sid , a.Sname , isnull(cast(avg(b.score) asdecimal(18,2)),0) avg_score

from Student a leftjoin sc b

on a.Sid = b.Sid

groupby a.Sid , a.Sname

havingisnull(cast(avg(b.score) asdecimal(18,2)),0) <60

orderby a.Sid

--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

思路：SC表的选课总数、总成绩（一个记录集合），STUDENT表（一个记录集合）

select s.sid,s.sname,a.[选课总数],a.[总成绩] fromstudent s inner join

(select sid,count(*) as '选课总数',sum(score) as '总成绩' from sc group by sid) a

on s.sid=a.sid

select * fromstudent s inner join

(select sid,count(cid) as '课程总数',sum(score) as '课程总成绩' from sc group by sid) a

on s.sid=a.sid

select s.sid,s.sname,count(a.cid) as '课程总数',sum(a.score) as '课程总成绩' from student s inner join sc a

on s.sid=a.sid groupby s.sid,s.sname

--5.1、查询所有有成绩的SQL。

select s.sid,s.sname,a.[选课总数],a.[总成绩] fromstudent s inner join

(select sid,count(*) as '选课总数',sum(score) as '总成绩' from sc group by sid) a

on s.sid=a.sid

select a.Sid [学生编号], a.Sname [学生姓名], co