create table score(name string,chinese string,math string)row format delimited fields terminated by ','lines terminated by '\n'stored as textfile;load data local inpath '/score.txt' into table score;create table class(name string,cname string)row format delimited fields terminated by ','lines terminated by '\n'stored as textfile;load data local inpath '/class.txt' into table class;数学最高分的学生所在班级的平均成绩？1.求出数学的最高分2.根据最高分求出这个学生3.根据学生查询班级4.根据班级查询学生5.根据学生查询出成绩6.根据成绩查询平均值select avg(s.math)fromclass c,score s,(select c.cnamefrom class c,score s,(select max(math) max from score)twhere s.math = t.max and s.name = c.name)t1where t1.cname=c.cname and c.name = s.name1.数学和语文平均成绩均大于60分的班级和平均成绩数学成绩大于60分的班级select t1.math,t2.chinese,t1.cnamefrom(select avg(math) math,cnamefrom score s join class c on s.name = c.namegroup by cname having avg(math)>60) t1join(select avg(chinese) chinese,cnamefrom score s join class c on s.name = c.namegroup by cname having avg(chinese)>60)t2on t1.cname = t2.cname语文大于60分的班级select c.cname,avg(s.math),avg(s.chinese)fromclass c,score1 swhere c.name = s.namegroup by c.cnamehaving avg(s.math)>60 and avg(s.chinese)>602.查询语文成绩大于60数学成绩大于60select t1.cname,t1.math,t2.chinese from(select avg(s.math) as math,c.cnamefrom class c,score1 swhere c.name = s.namegroup by c.cnamehaving avg(s.math)>60)t1join(select avg(s.chinese) as chinese,c.cnamefrom class c,score1 swhere c.name = s.namegroup by c.cnamehaving avg(s.chinese)>60)t2where t1.cname = t2.cname;

数据

banzhang,89,98tuanzhishu,99,89xiaoming,55,66xiaohong,88,66xiaolong,55,66xiaoliang,88,99

xiaoming,c1xiaohei,c2banzhang,c3tuanzhishu,c2xiaohong,c1xiaolong,c2xiaoliang,c3