1、Hive綜合應(yīng)用案例-學(xué)生成績(jī)查詢第1關(guān)：計(jì)算每個(gè)班的語(yǔ)文總成績(jī)和數(shù)學(xué)總成績(jī)- 禁止修改 - drop database if exists mydb cascade;- 禁止修改 - begin -創(chuàng)建mydb數(shù)據(jù)庫(kù)create database if not exists mydb;-使用mydb數(shù)據(jù)庫(kù)use mydb;-創(chuàng)建表scorecreate table if not exists score(name string comment '姓名',chinese string comment '語(yǔ)文成績(jī)',maths string comment 

2、9;數(shù)學(xué)成績(jī)')row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step1_files/load data local inpath '/root/data/step1_files/' into table score;-創(chuàng)建表classcreate table if not exists class(stuname string comment '姓名',classname string comment '所在班級(jí)&

3、#39;)row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step1_files/load data local inpath '/root/data/step1_files/' into table class;-計(jì)算每個(gè)班的語(yǔ)文總成績(jī)和數(shù)學(xué)總成績(jī)，要求有哪科低于60分，該名學(xué)生成績(jī)不計(jì)入計(jì)算。select ,from(select classname,sum chinesefrom class c,score swhere = and >=

4、60group by t1,(select classname,sum mathsfrom class c,score swhere = and >=60group by t2where =;- end -第2關(guān)：查詢選修了3門(mén)以上的課程的學(xué)生姓名- 禁止修改 - drop database if exists mydb cascade;- 禁止修改 - begin -創(chuàng)建mydb數(shù)據(jù)庫(kù)create database if not exists mydb;-使用mydb數(shù)據(jù)庫(kù)use mydb;-創(chuàng)建表my_stucreate table if not exists my_stu(id s

5、tring comment '學(xué)生id',name string comment '學(xué)生姓名',sex string comment '性別',age string comment '年齡',col string comment '所選的系')row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load data local inpath '/root/dat

6、a/step2_files/' into table my_stu;-創(chuàng)建表my_scorecreate table if not exists my_score(id string comment '學(xué)生id',courseid string comment '課程id',score string comment '成績(jī)')row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load da

7、ta local inpath '/root/data/step2_files/' into table my_score;-創(chuàng)建表my_coursecreate table if not exists my_course(courseid string comment '課程id',coursename string comment '課程名稱')row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files

8、/load data local inpath '/root/data/step2_files/' into table my_course;-查詢選修了3門(mén)以上的課程的學(xué)生姓名。select ,from(select id,count(courseid) coursenumfrom my_scoregroup by id) t,my_stu stuwhere >=3 and =;- end -第3關(guān)：課程選修人數(shù)- 禁止修改 - drop database if exists mydb cascade;- 禁止修改 - begin -創(chuàng)建mydb數(shù)據(jù)庫(kù)create da

9、tabase if not exists mydb;-使用mydb數(shù)據(jù)庫(kù)use mydb;-創(chuàng)建表my_stucreate table if not exists my_stu(id string comment '學(xué)生id',name string comment '學(xué)生姓名',sex string comment '性別',age string comment '年齡',col string comment '所選的系')row format delimited fields terminated by 

10、9;,'stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load data local inpath '/root/data/step2_files/' into table my_stu;-創(chuàng)建表my_scorecreate table if not exists my_score(id string comment '學(xué)生id',courseid string comment '課程id',score string comment '成績(jī)')row format deli

11、mited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load data local inpath '/root/data/step2_files/' into table my_score;-創(chuàng)建表my_coursecreate table if not exists my_course(courseid string comment '課程id',coursename string comment '課程名稱')row for

12、mat delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load data local inpath '/root/data/step2_files/' into table my_course;-查詢每個(gè)課程有多少人選修。select ,count(*)from(select name, coursenamefrom(select name, courseidfrom my_score score,my_stu stuwhere = t1,my_

13、course coursewhere = t2group by ;- end -第4關(guān)：shujuku課程的平均成績(jī)- 禁止修改 - drop database if exists mydb cascade;- 禁止修改 - begin -創(chuàng)建mydb數(shù)據(jù)庫(kù)create database if not exists mydb;-使用mydb數(shù)據(jù)庫(kù)use mydb;-創(chuàng)建表my_stucreate table if not exists my_stu(id string comment '學(xué)生id',name string comment '學(xué)生姓名',sex s

14、tring comment '性別',age string comment '年齡',col string comment '所選的系')row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load data local inpath '/root/data/step2_files/' into table my_stu;-創(chuàng)建表my_scorecreate table if not

15、 exists my_score(id string comment '學(xué)生id',courseid string comment '課程id',score string comment '成績(jī)')row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load data local inpath '/root/data/step2_files/' into table my_score;-創(chuàng)建表my_coursecreate table if not exists my_course(courseid string comment '課程id',coursename string comment '課程名稱')row format delimited fields terminated by ','stored as textfile;-導(dǎo)入數(shù)據(jù)：/root/data/step2_files/load data loca