from Student a , SC b
where a.Sid = b.Sid
groupby a.Sid,a.Sname
orderby a.Sid
--5.2、查询所有(包括有成绩和无成绩)的SQL。
select s.sid,s.sname,a.[选课总数],a.[总成绩] fromstudent s left join
(select sid,count(*) as '选课总数',sum(score) as '总成绩' from sc group by sid) a
on s.sid=a.sid
select * fromstudent s left join
(select sid,count(cid) as '课程总数',sum(score) as '课程总成绩' from sc group by sid) a
on s.sid=a.sid orderby s.sid
select s.sid,s.sname,count(a.cid) as '课程总数',sum(a.score) as '课程总成绩' from student s left join sc a
on s.sid=a.sid groupby s.sid,s.sname order by s.sid?
select a.Sid [学生编号], a.Sname [学生姓名], count(b.Cid) 选课总数, sum(score) [所有课程的总成绩]
from Student a leftjoin SC b
on a.Sid = b.Sid
groupby a.Sid,a.Sname
orderby a.Sid
--6、查询"李"姓老师的数量
select count(*) as '数量' fromteacher where left(tname,1)='李'
--方法1
selectcount(Tname) ["李"姓老师的数量]from Teacher where Tname like N'李%'
--方法2
selectcount(Tname) ["李"姓老师的数量]from Teacher whereleft(Tname,1) = N'李'
--7、查询学过"张三"老师授课的同学的信息
思路： STUDENT（一个记录集合），张三老师（一个记录集合），张三老师上的课（一个记录集合），张三老师上的课的成绩（一个记录集合）
select * fromstudent s inner joinsc a
on s.sid=a.sid innerjoin course c
on a.cid=c.cid innerjoin teacher t
on c.tid=t.tid wheret.tname='张三'
思路：从全部学生中（一个记录集合）提取上过张三老师课的学生（一个记录集合）
select * fromstudent where sid in(
select sid from sc a inner join course b
on a.cid=b.cid innerjoin teacher c
on b.tid=c.tid andc.tname='张三')
selectdistinct Student.*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 Student.Sid
--8☆、查询没学过"张三"老师授课的同学的信息
思路：从全部学生中（一个记录集合）删除上过张三老师课的学生（一个记录集合）。
select * fromstudent where sid notin ( select distinct sid from sc a inner join course c
on a.cid=c.cid innerjoin teacher t
on c.tid=t.tid wheret.tname='张三')
select m.*from Student m where Sid notin (selectdistinct SC.Sid from SC , Course , Teacher where SC.Cid = Course.Cid and Course.Tid = Teacher.Tid and Teacher.Tname = N'张三') orderby m.Sid
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
思路：上过课程01（一个记录集合），上过课程02（一个记录集合），STUDENT表（一个记录集合）
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'
思路：上过课程01的学生（一个记录集合）并且存在上过课程02的学生（一个记录集合）
select * fromstudent s inner joinsc a
on s.sid=a.sid anda.cid='01' and exists (select 1 from sc b where s.sid=b.sid and b.cid='02')
--方法1
select Student.*from Student , SC where Student.Sid = SC.Sid and SC.Cid ='01'andexists (Select1from SC SC_2 where SC_2.Sid = SC.Sid and SC_2.Cid ='02') orderby Student.Sid
--方法2
select Student.*from Student , SC where Student.Sid = SC.Sid and SC.Cid ='02'andexists (Select1from SC SC_2 where SC_2.Sid = SC.Sid and SC_2.Cid ='01') orderby Student.Sid
--方法3
select m.*from Student m where Sid in
(
select Sid from
(
selectdistinctSid from SC where Cid ='01'
unionall
selectdistinctSid from SC where Cid ='02'
) t groupby Sid havingcount(1) =2
)
orderby m.Sid
--10☆、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
思路：上过课程01的学生（一个记录集合）并且不存在上过课程02的学生（一个记录集合）
select * fromstudent s inner joinsc a
on s.sid=a.sid anda.cid='01' and not exists (select 1 from sc b where s.sid=b.sid and b.cid='02')?
思路：从全部学生中（一个记录集合）先提取上过课程01的学生记录（一个记录集合）再排除没上过课程02的学生记录（一个记录集合）
select * fromstudent where sid in
(select sid from sc where cid='01') and sid not in (
select sid from sc where cid='02')?
select * fromstudent s inner joinsc a
on