题30:
统计学生总人数。
select count(sname) as cou
from student;
题31:
统计选修了课程的学生的人数。
select count(distinct sno) as cou
from sc;
题32 :
计算学号为9512101的学生的考试总成绩之和。
select sum(grade) as sumgrade
from sc
where sno='9512101';
题33:
计算课程“c01”的学生的考试平均成绩。
select avg(grade) as avggrade
from sc
where cno='c01';
题34:
查询选修了课程“c01”的学生的最高分和最低分。
select min(grade) as mingrade,max(grade) as maxgrade
from sc
where cno='c01';
题35:
统计每门课程的选课人数,列出课程号和人数。
select cno,count(cno) as cou
from sc
group by cno;
题36:
查询每名学生的选课们数和平均成绩。
select sno,count(cno) ascou,avg(grade) as avggrade
from sc
group by sno;
题37:
查询选修了3门以上课程的学生的学号。
select sno from (
select sno,count(cno) ascou,avg(grade) as avggrade from sc group by sno) a
where a.ascou>3;
题38:
查询选课门数等于或大于4门的学生的平均成绩和选课门数。
select sno,avggrade,cou
from (
select sno,count(cno) as cou,avg(grade) as avggrade
from sc
group by sno) a
where a.cou>=4;
题39:
查询每个学生的情况及其选课的情况。
select student.*,sc.cno
from student,sc
where student.sno=sc.sno;
题40:
去掉例38中的重复列。
????哪来的重复列
题49:
查询选修了课程“c02”且成绩高于次课程的平均成绩的学生的学号和成绩。
select sno,grade from (select sno,grade,avg(grade) avggrade
from sc
where cno='c02') a;