中科软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