题目:
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