Hive综合应用案例

1、第1关：计算每个班的语文总成绩和数学总成绩禁止修改drop database if exists mydb cascade;禁止修改begin 创建mydb数据库create database if not exists mydb;使用mydb数据库use mydb;创建表scorecreate table if not exists score (name string comment 姓名，Chinese string comment 语文成绩,maths string comment 数学成绩)row format delimited fields terminated bystored

2、 as textfile;导入数据：/root/data/stepl_flies/load data local inpath J/root/data/stepl_filesZ， into table score;创建表classcreate table if not exists class (stuname string comment 姓需 classname string comment 所在班级)row format delimited fields terminated bystored as textfile;导入数据：/root/data/stepl_flies/load da

3、ta local inpath /root/d3ta/stepl_files/ into table class;-计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计 算。select ,from(select classname, sum Chinesefrom class c,score swhere = and =60group by tl,(select classname, sum mathsfrom class c,score swhere = and =60group by t2where =;end 第2关：査询选修了 3门以上的课程的学生姓名 禁止

4、修改drop database if exists mydb cascade;禁止修改begin 创建mydb数据库create database if not exists mydb;一-使用mydb数据库use mydb;创建表my_stucreate table if not exists my_stu(id string comment 学生 id，,name string comment 学生姓洛，sex string comment 性别,age string comment 年龄,col string comment 所选的系)row format delimited field

5、s terminated bystored as textfile;导入数据:/root/data/step2_files/load data local inpath J/root/data/step2_filesZ， into table my_stu;创建表my_scorecreate table if not exists my_score(id string comment 学生 id，,courseid string comment 课程 id,score string comment 成绩)row format delimited fields terminated by ,st

6、ored as textfile;导入数据:/root/data/step2_files/load data local inpath /root/data/step2_files/ into table my_score; 创建表 my_coursecreate table if not exists my_course(courseid string comment 课程 id,coursename string comment 课程名称)row format delimited fields terminated bystored as textfile;导入数据：/root/data/

7、step2_flies/load data local inpath 1 /root/data/step2_filesZ， into table my_course; 一-査询选修了 3门以上的课程的学生姓名。select ,from(select id, count (courseid) coursenumfrom my_score group by id) t, my_stu stuwhere =3 and 二；end第3关：课程选修人数禁止修改drop database if exists mydb cascade;禁止修改begin 创建mydb数据库create database i

8、f not exists mydb;一-使用mydb数据库use mydb;创建表my_stucreate table if not exists my_stu( id string comment 学生 id, name string comment 学生姓洛，sex string comment 性别,age string comment 年龄,col string comment 所选的系)row format delimited fields terminated by ,stored as textfile;导入数据:/root/data/step2_files/load data

9、local inpath J/root/data/step2_filesZ， into table my_stu; 创建表my_scorecreate table if not exists my_score(id string comment 学生 id,courseid string comment 课程 id,score string comment 成绩)row format delimited fields terminated bystored as textfile;导入数据：/root/data/step2_flies/load data local inpath /root/

10、data/step2_files/ into table my_score; 创建表 my_course create table if not exists my_course(courseid string comment 课程 id, coursename string comment 课程轲称)row format delimited fields terminated bystored as textfile;导入数据：/root/data/step2_flies/load data local inpath 1 /root/data/step2_filesZ， into table

11、 my_course; 一-査询每个课程有多少人选修。select ,count(*) from( select name, coursenamefrom(select name, courseidfrom my_score score, my_stu stu where = tl, my_course course where 二 t2group by ; 第4关：shujuku课程的平均成绩禁止修改drop database if exists mydb cascade;禁止修改begin 创建mydb数据库create database if not exists mydb;一-使用my

12、db数据库use mydb;创建表my_stucreate table if not exists my_stu( id string comment 学生 id，, name string comment 学生姓洛， sex string comment 性别, age string comment 年龄, col string comment 所选的系)row formatdelimited fields terminated by , stored as textfile;导入数据:/root/data/step2_files/load data local inpath J/root/

13、data/step2_filesZ， into table 创建表my_scorecreate table if not exists my_score(id string comment 学生 id，,courseid string comment 课程 id,score string comment 成绩) row format delimited fields terminated by ,stored as textfile;导入数据:/root/data/step2_files/load data local inpath /root/data/step2_files/ into table 创建表 my_coursecreate table if not exists my_course(courseid string comment 课程 id, coursename string comment 课程名称)row format delimited fields terminated bystored as textfile;导入数据：/root/data/step2_files/load data local inpath J/root/data/step2_fi