2018.12.27作业

–查询平均成绩大于60分的同学的学号和平均成绩；

select sid,avg(score)
from sc
group by sid
having avg(score)>60

–查询"001"课程比"002"课程成绩高的所有学生的学号；

select sc1.sid from sc sc1 join sc sc2 on sc1.sid=sc2.sid
where sc1.cid = ‘001’ and sc2.cid = ‘002’ and sc1.score > sc2.score

–查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select cid,max(score),min(score)
from sc
group by cid

–查询所有课程成绩小于60分的同学的学号、姓名

select a.sid,sname from sc a natural join student b
where score < 60
group by a.sid
having count() = (select count() from course)

–查询所有同学的学号、姓名、选课数、总成绩；
select s.sid ,sname ,count(*),sum(score)
from sc s join student t
on s.sid = t.sid
group by s.sid ,sname