16-成绩3

create table student(
uid string,
name string,
sex string,
age string,
xi string
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
load data local inpath '/my_student.txt' into table student;

create table course(
cid string,
cname string
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
load data local inpath '/my_course.txt' into table course;

create table score1(
uid string,
cid string,
score string
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
load data local inpath '/my_score.txt' into table score1;

表的关联（两张表关联）

查询选修了课程的学生姓名



计算数据库课程的学生平均成绩(谁选择了数据库课程，选择的那个人的平均成绩，并且按照成绩进行排序)
1.根据课程查询课程id
2.课程id查询学生id
3.根据学生们查询出来这些学生的所有成绩
4.按照学生分组求出每个学生的平均分数
5.order by score desc


查询选修操作系统课程的学生最高分数和人
1.根据操作系统查询出来课程id
2.根据课程id查询出来所有的学生和分数
3.查询出来所有分数中的最高分
4.谁的分数和最高分是一样的
5.求出这个人



求各个课程名及相应的选课人数 


查询选修了4门以上的课程的学生姓名

my_course.txt

1,数据库
2,数学
3,信息系统
4,操作系统
5,数据结构
6,数据处理

my_score.txt

95001,1,81
95001,2,85
95001,3,88
95001,4,70
95002,2,90
95002,3,80
95002,4,71
95002,5,60
95003,1,82
95003,3,90
95003,5,100
95004,1,80
95004,2,92
95004,4,91
95004,5,70
95005,1,70
95005,2,92
95005,3,99
95005,6,87
95006,1,72
95006,2,62
95006,3,100
95006,4,59
95006,5,60
95006,6,98
95007,3,68
95007,4,91
95007,5,94
95007,6,78
95008,1,98
95008,3,89
95008,6,91
95009,2,81
95009,4,89
95009,6,100
95010,2,98
95010,5,90
95010,6,80
95011,1,81
95011,2,91
95011,3,81
95011,4,86
95012,1,81
95012,3,78
95012,4,85
95012,6,98
95013,1,98
95013,2,58
95013,4,88
95013,5,93
95014,1,91
95014,2,100
95014,4,98
95015,1,91
95015,3,59
95015,4,100
95015,6,95
95016,1,92
95016,2,99
95016,4,82
95017,4,82
95017,5,100
95017,6,58
95018,1,95
95018,2,100
95018,3,67
95018,4,78
95019,1,77
95019,2,90
95019,3,91
95019,4,67
95019,5,87
95020,1,66
95020,2,99
95020,5,93
95021,2,93
95021,5,91
95021,6,99
95022,3,69
95022,4,93
95022,5,82
95022,6,100

my_student.txt

95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA

 

 

 

 

 

字段是string类型  要改成int 类型

 

问题？
hive> select s.name,round(t2.avg) av
    > from
    > student s,
    > (select avg(t1.score) avg,t1.uid
    > from
    > (select sc.score,t.uid
    > from
    > score1 sc,
    > (select sc.uid
    > from score1 sc,course c
    > where sc.cid=c.cid and c.cname='数据库')t
    > where sc.uid=t.uid)t1
    > group by t1.uid)t2
    > where s.uid=t2.uid order by av desc;
FAILED: ParseException line 11:34 character '据' not supported here
line 11:35 character '库' not supported here
line 11:36 character '库' not supported here
line 14:35 character '<EOF>' not supported here