【头歌】Hive基本查询操作（一） 答案

本专栏已收集头歌大数据所有答案 以供参考

第1关：where操作

答案 复制点击评测

----------禁止修改----------
create database if not exists db1;
use db1;

create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/aaa.txt' into table table1;
----------禁止修改----------

----------Begin----------
select workingExp,company_name from table1 where responsibility like '%hive%' and salary > '8000';
----------End----------

第2关：group by操作

答案复制点击评测

----------禁止修改----------
create database if not exists db1;
use db1;

create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/t1.txt' into table table1;
----------禁止修改----------

----------Begin----------
select avg(salary) as avgsalary,workingExp from table1 group by workingExp having avgsalary > 10000;
----------End----------

第3关：join操作

答案 复制点击测评

----------禁止修改----------
create database if not exists db1;
use db1;

create table if not exists table1(
eduLevel_name string comment '学历',
company_name string comment '公司名',
jobName string comment '职位名称',
salary int comment '薪资',
city_code int comment '城市编码',
responsibility string comment '岗位职责',
workingExp string comment '工作经验'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table1;
load data local inpath '/root/t2.txt' into table table1;

create table if not exists table2(
city_code int comment '城市编码',
city_name string comment '城市名'
)
row format delimited fields terminated by ','
lines terminated by '\n'
stored as textfile;
truncate table table2;
load data local inpath '/root/t22.txt' into table table2;
----------禁止修改----------

----------Begin----------
select avg(table1.salary),table2.city_name from table1 right outer join table2 on table1.city_code=table2.city_code group by table2.city_name ;
----------End----------