1、查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
思路：先查出01课程所有学生分数作为t1，再查出02课程所有学生分数作为t2，
将两表进行联结，条件是：学生sid相同，01分数比02分数高。
如下：
(select sc.*
from sc
where sc.cid='01') as t1
(select sc.*
from sc
where sc.cid='02') as t2
select sc.*
from t1
join t2
on t1.sid=t2.sid and t1.score>t2.score
将t1，t2 替换代码如下
select t1.,t2.cid,t2.score
from (select sc.
from sc
where sc.cid='01') as t1
join (select sc.*
from sc
where sc.cid='02') as t2
on t1.sid=t2.sid and t1.score>t2.score #可按具体要求选择要显示的内容,join的写法更清晰,可读性较强
标准答案(此为sql92写法,个人认为可读性不如sql99即join写法,看个人喜好,只是做下对比)
select *
from (select SId ,score from sc where sc.CId='01')as t1 ,
(select SId ,score from sc where sc.CId='02') as t2
where t1.SId=t2.SId
and t1.score>t2.score
2、查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
思路：对学生编号分组，找出每个学生的sid及平均成绩，再用having进行大于60分的筛选，最后进行表关联
select sc.sid,avg(sc.score) as avgscore
from sc
group by sc.sid
having avgscore >60 #分组，再过滤
关联 条件是sid相同，如下：
select sc.sid,s.sname,avg(sc.score) as avgscore
from sc
join student s
on sc.sid=s.sid
group by sc.sid
having avgscore >60
标准答案（个人认为还是写法问题，而且题目要求显示编号姓名及平均成绩，这个是搜的全部，不太严谨）
select student.*,t1.avgscore
from student inner JOIN(
select sc.SId ,AVG(sc.score)as avgscore
from sc
GROUP BY sc.SId
HAVING AVG(sc.score)>=60)as t1 on student.SId=t1.SId
3、查询在 SC 表存在成绩的学生信息
思路：个人理解的第一种解法：根据题目，直接根据sc.sid分组即可（也就是标准答案的去重），再和student表关联，如下：
select sc.sid,s.*
from sc
join student s
on sc.sid=s.sid
group by sc.sid
标准答案 用distinct去重，代码更简洁
select DISTINCT student.*
from student ,sc
where student.SId=sc.SId
第三种写法，也就是4.1的答案 ，exists的用法：
select *
from student
where EXISTS(select * from sc where student.SId=sc.SId)
7、查询没有学全所有课程的同学的信息
个人思路：用count函数先找出一共有多少课，再对sc表sid进行分组，再次用count函数找出等于课程数即学全同学的sid，
用not in 函数，筛选除sid以外的其他同学的信息
1、
select count()
from course #是3
2、
select sc.sid
from sc
group by sid
having count(sc.cid)=3 学完3门的同学的编号
3、筛选 用 not in
select s.
from student s
where s.sid not in (select sc.sid
from sc
group by sid
having count(sc.cid)=3)
4、最终答案 记得替换 3
select s.*
from student s
where s.sid not in (select sc.sid
from sc
group by sid
having count(sc.cid)=(select count(*)
from course))
标准答案解法1
select student.*
from sc ,student
where sc.SId=student.SId
GROUP BY sc.SId
Having count()<(select count() from course)
但这种解法得出来的结果不包括什么课都没选的同学
标准答案解法2 （个人感觉比较难读懂）
select DISTINCT student.*
from
(select student.SId,course.CId
from student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student
where t2.SId is null
and t1.SId=student.SId
8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
思路：先选出01同学所学科目的编号，再用‘in’来选出与01至少相同一门的学生编号（个人认为要排除01），最后做表关联。
select sc.cid
from sc
where sc.sid='01' #选出01同学的课程编号
select distinct sc.sid,s.sname #这里记得去重，筛选的结果一定是有重复的
from sc
join student s
on sc.sid=s.sid
where sc.sid <>'01' and sc.cid in ( select sc.cid
from sc
where sc.sid='01') #排除01同学，在01同学的课程里面选调用上面写的代码
标准答案：（个人认为不严谨，会选出01同学）
select DISTINCT student.*
from sc ,student
where sc.CId in (select CId from sc where sc.SId='01')
and sc.SId=student.SId
