15-成绩2

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)
from
class c,score s,
(select c.cname
from class c,score s,
(select max(math) max from score)t
where s.math = t.max and s.name = c.name)t1
where t1.cname=c.cname and c.name = s.name


1.数学和语文平均成绩均大于60分的班级和平均成绩

数学成绩大于60分的班级
select t1.math,t2.chinese,t1.cname
from
(select avg(math) math,cname
from score s join class c on s.name = c.name
group by cname having avg(math)>60) t1
join
(select avg(chinese) chinese,cname
from score s join class c on s.name = c.name
group by cname having avg(chinese)>60)t2
on 
t1.cname = t2.cname
语文大于60分的班级

select c.cname,avg(s.math),avg(s.chinese)
from
class c,score1 s
where c.name = s.name
group by c.cname
having avg(s.math)>60 and avg(s.chinese)>60


2.
查询语文成绩大于60
数学成绩大于60

select t1.cname,t1.math,t2.chinese from
(select avg(s.math) as math,c.cname
from class c,score1 s
where c.name = s.name
group by c.cname
having avg(s.math)>60)t1
join
(select avg(s.chinese) as chinese,c.cname
from class c,score1 s
where c.name = s.name
group by c.cname
having avg(s.chinese)>60)t2
where t1.cname = t2.cname;

数据

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

xiaoming,c1
xiaohei,c2
banzhang,c3
tuanzhishu,c2
xiaohong,c1
xiaolong,c2
xiaoliang,c3