中科软java笔试题分享：学生信息表分组查询
题目：
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
