Hive综合应用案例 — 学生成绩查询

第1关：计算每个班的语文总成绩和数学总成绩 ---------- 禁止修改 ---------- drop database if exists mydb cascade; ---------- 禁止修改 ----------

---------- begin ---------- ---创建mydb数据库

create database if not exists mydb; ---使用mydb数据库 use mydb;

---创建表score

create table if not exists score( name string comment '姓名',

chinese string comment '语文成绩', maths string comment '数学成绩' )

row format delimited fields terminated by ',' stored as textfile;

---导入数据：/root/data/step1_files/score.txt

load data local inpath '/root/data/step1_files/score.txt' into table score; --创建表class

create table if not exists class( stuname string comment '姓名',

classname string comment '所在班级' )

row format delimited fields terminated by ',' stored as textfile;

---导入数据：/root/data/step1_files/class.txt

load data local inpath '/root/data/step1_files/class.txt' into table class;

--计算每个班的语文总成绩和数学总成绩，要求有哪科低于60分，该名学生成绩不计入计算。

select t1.classname,t1.chinese,t2.maths from(

select c.classname classname,sum(s.chinese) chinese from class c,score s

where c.stuname=s.name and s.chinese>=60 group by c.classname) t1,(

select c.classname classname,sum(s.maths) maths from class c,score s

where c.stuname=s.name and s.maths>=60 group by c.classname) t2

where t1.classname=t2.classname; ---------- end ----------

第2关：查询选修了3门以上的课程的学生姓名 ---------- 禁止修改 ---------- drop database if exists mydb cascade; ---------- 禁止修改 ---------- ---------- begin ---------- ---创建mydb数据库

create database if not exists mydb; ---使用mydb数据库 use mydb;

---创建表my_stu

create 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/my_student.txt

load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu; --创建表my_score

create 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/my_score.txt

load data local inpath '/root/data/step2_files/my_score.txt' 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 by ',' stored as textfile;

---导入数据：/root/data/step2_files/my_course.txt

load data local inpath '/root/data/step2_files/my_course.txt' into table my_course; ---查询选修了3门以上的课程的学生姓名。 select stu.name,t.coursenum

from(

select id,count(courseid) coursenum from my_score

group by id) t,my_stu stu

where t.coursenum>=3 and stu.id=t.id; ---------- end ----------

第3关：课程选修人数

---------- 禁止修改 ---------- drop database if exists mydb cascade; ---------- 禁止修改 ---------- ---------- begin ---------- ---创建mydb数据库

create database if not exists mydb; ---使用mydb数据库 use mydb;

---创建表my_stu

create 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/my_student.txt

load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu; --创建表my_score

create 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/my_score.txt

load data local inpath '/root/data/step2_files/my_score.txt' 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 by ',' stored as textfile;

---导入数据：/root/data/step2_files/my_course.txt

load data local inpath '/root/data/step2_files/my_course.txt' into table my_course; ---查询每个课程有多少人选修。 select t2.coursename,count(*) from(

select t1.name name,course.coursename coursename from(

select stu.name name,score.courseid courseid from my_score score,my_stu stu

where score.id=stu.id) t1,my_course course where t1.courseid=course.courseid) t2 group by t2.coursename; ---------- end ----------

第4关：shujuku课程的平均成绩 ---------- 禁止修改 ---------- drop database if exists mydb cascade; ---------- 禁止修改 ---------- ---------- begin ---------- ---创建mydb数据库

create database if not exists mydb; ---使用mydb数据库 use mydb;

---创建表my_stu

create 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/my_student.txt

load data local inpath '/root/data/step2_files/my_student.txt' into table my_stu; --创建表my_score

create 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/my_score.txt

load data local inpath '/root/data/step2_files/my_score.txt' 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 by ',' stored as textfile;

---导入数据：/root/data/step2_files/my_course.txt

load data local inpath '/root/data/step2_files/my_course.txt' into table my_course; ---计算shujuku课程的平均成绩。 select t3.coursename,t2.avg_score from (

select t1.courseid courseid,avg(score.score) avg_score from(

select courseid from my_course

where my_course.coursename='shujuku') t1,my_score score where t1.courseid=score.courseid group by t1.courseid) t2,my_course t3 where t2.courseid=t3.courseid; ---------- end ----------