hive语句练习50题

数据：

student表
s_id s_name s_birth s_sex
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
08 王菊 1990-01-20 女

course表
c_id c_course t_id
01    语文    02
02    数学    01
03    英语    03

teacher表
t_id t_name
01    张三
02    李四
03    王五


score表
s_id c_id s_score
01    01    80
01    02    90
01    03    99
02    01    70
02    02    60
02    03    80
03    01    80
03    02    80
03    03    80
04    01    50
04    02    30
04    03    20
05    01    76
05    02    87
06    01    31
06    03    34
07    02    89
07    03    98

题目：

##hive 50题 ##建表、加载数据： ################################################

create table if not exists student(
s_id int,
s_name string,
s_birth string,
s_sex string
)
row format delimited 
fields terminated by ' '
;
load data inpath '/user/yanqingz/data/student' into table student; 


create table if not exists course(
c_id int,
c_course string,
t_id int
)
row format delimited 
fields terminated by '\t'
;
load data inpath '/user/yanqingz/data/course' into table course ;

create table if not exists teacher(
t_id int,
t_name string
)
row format delimited 
fields terminated by '\t'
;
load data inpath '/user/yanqingz/data/teacher' into table teacher;


create table if not exists score(
s_id int,
c_id int,
s_score int
)
row format delimited 
fields terminated by '\t'
;
load data inpath '/user/yanqingz/data/score' into table score;

 

################################################

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:

select student.s_id,student.s_name,s1_score,s2_score from (
select s_id,s_score as s1_score from score where c_id = 01
)s1
join( 
select s_id,s_score as s2_score from score where c_id = 02  
)s2 on s1.s_id = s2.s_id
left join student on s1.s_id = student.s_id
where s1.s1_score > s2.s2_score

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:

select student.s_id,student.s_name,s1_score,s2_score from (
select s_id,s_score as s1_score from score where c_id = 01
)s1
 join( 
select s_id,s_score as s2_score from score where c_id = 02  
)s2 on s1.s_id = s2.s_id
left join student on s1.s_id = student.s_id
where s1.s1_score < s2.s2_score

 

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:

select score.s_id,student.s_name,avg(score.s_score) from score 
left join student on score.s_id = student.s_id
group by score.s_id,student.s_name
having avg(score.s_score) >= 60

 

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩: (包括有成绩的和无成绩的)

select * from (
select student.s_id,student.s_name,
case when avg(score.s_score) is null then 0 
else  avg(score.s_score)
end as avgscore
from student 
left join score on  student.s_id = score.s_id
group by student.s_id,student.