题目:
Table:student
学号 | 班级 | 分数 |
1 | Class1 | 70 |
2 | Class1 | 50 |
3 | Class2 | 67 |
4 | Class3 | 36 |
5 | Class1 | 45 |
6 | Class2 | 66 |
查出 高于class1 平均分数的班级 和 学生数量
答案:
create table student(
xuehao varchar(20),
banji varchar(20),
fenshu number(3)
)
insert into student values('1','class1',90);
/
insert into student values('2','class1',30);
/
insert into student values('3','class1',69);
/
insert into student values('4','class2',69);
/
insert into student values('5','class2',76);
/
insert into student values('6','class3',85);
select banji,count(xuehao) as num1 from student
where fenshu > (select avg(fenshu) from student where banji='class1')
group by banji