select c_name ,avg(grade) as grade from class a join chosen_class b on a.c_id=b.c_id group by c_name order by 2;select s_name,count(1) as '选课数' from student a join chosen_class b on a.s_id=b.s_id group by a.s_name having count(1)>1
1) 没有选修课程编号为“C1”的学生姓名 select s_name from student s where not exists (select 1 from chosen_class cc,class c where cc.c_id=c.c_id and c.c_name='C1' and s_id=s.s_id)
2) 列出每门课程名称和平均成绩,并按照程序排序 select c_name,avg(grade) from chosen_class cc,class c where cc.c_id=c.c_id group by c_name
3) 选了2门课以上的学生姓名 select s_name from student s where 2<=(select count(*) from chosen_class where s_id=s.s_id)
from class a join chosen_class b
on a.c_id=b.c_id
group by c_name
order by 2;select s_name,count(1) as '选课数'
from student a join chosen_class b
on a.s_id=b.s_id
group by a.s_name
having count(1)>1
select s_name
from student s
where not exists (select 1 from chosen_class cc,class c where cc.c_id=c.c_id and c.c_name='C1' and s_id=s.s_id)
select c_name,avg(grade)
from chosen_class cc,class c where cc.c_id=c.c_id
group by c_name
select s_name
from student s
where 2<=(select count(*) from chosen_class where s_id=s.s_id)