hive初级练习50题（详细过程）

一.建库和建表

1.表信息

（1）课程表（course.txt）

01	语文	02
02	数学	01
03	英语	03

（2）成绩表（score.txt）

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

（3）学生表（student.txt）

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	女

（4）教师表（teacher.txt）

01	张三
02	李四
03	王五

将四张表拉到linux中的/opt/data/目录下

2.建库建表

脚本内容（setup_tables.sql）

--建库homework
create database if not exists homework;
use homework;
--创建课程表
drop table if exists course;
create table course(
c_id int,
c_name string,
t_id int
)
row format delimited
fields terminated by "\t"
stored as textfile;
--上传数据
load data local inpath'/opt/data/course.txt' overwrite into table course;
--创建成绩表
drop table if exists score;
create table score(
s_id int,
c_id int,
scores int
)
row format delimited
fields terminated by "\t"
stored as textfile;

load data local inpath'/opt/data/score.txt'  overwrite into table score;
--创建学生表
drop table if exists student;
create table student(
s_id int,
s_name string,
birth string,
gender string
)
row format delimited
fields terminated by "\t"
stored as textfile;

load data local inpath'/opt/data/student.txt'  overwrite into table student;
--创建教师表
drop table if exists teacher;
create table teacher(
t_id int,
t_name string
)
row format delimited
fields terminated by "\t"
stored as textfile;

load data local inpath'/opt/data/teacher.txt'  overwrite into table teacher;

执行以上脚本内容

hive -f setup_tables.sql

二.练习50题(详细过程)

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

select s.s_id,s.s_name,t3.sc1,t3.sc2 from student s
join
(select t1.s_id,t1.scores sc1,t2.scores sc2
from
(select s_id,c_id,scores from score where c_id=01) t1
join
(select s_id,c_id,scores from score where c_id=02) t2
on t1.s_id=t2.s_id
where t1.scores>t2.scores)t3
on s.s_id=t3.s_id

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

select s.s_id,s.s_name,t3.sc1,t3.sc2 from student s
join
(select t1.s_id,t1.scores sc1,t2.scores sc2
from
(select s_id,c_id,scores from score where c_id=01) t1
join
(select s_id,c_id,scores from score where c_id=02) t2
on t1.s_id=t2.s_id
where t1.scores<t2.scores)t3
on s.s_id=t3.s_id

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

select
s.s_id,
s.s_name,
avg(scores) avgs
from student s
join
score sc
on s.s_id=sc.s_id
group by s.s_id,s.s_name
having avgs>60

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

--左联找出所有人的id和平均分
select 
s.s_id,
avg(scores) avgs
from student s
left join score sc
on s.s_id= sc.s_id
group by s.s_id
--再在上表中找出成绩小于60分和无成绩的
select
ss.s_id,
ss.s_name,
avgs
from student ss
join
(select 
s.s_id,
avg(scores) avgs
from student s
left join score sc
on s.s_id= sc.s_id
group by s.s_id) t1
on t1.s_id=ss.s_id
where avgs<60 or avgs is null

5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select 
s.s_id,s_name,count(c_id),sum(scores)
from student s
left join
score sc
on s.s_id=sc.s_id
group by s.s_id,s_name

6.查询"李"姓老师的数量

select
count(t_id)
from teacher
where t_name like '李%'

7.查询学过"张三"老师授课的同学的信息

--先查张三老师授课的id
(select
c.c_id
from course c
join teacher t
on c.t_id=t.t_id
where t_name="张三") t1
 --查学过上表中课程id的学生id
(select
s_id
from score sc
join
(select
c.c_id
from course c
join teacher t
on c.t_id=t.t_id
where t_name="张三") t1
on t1.c_id =sc.c_id) t2
--通过学生id找到学生信息
select * from student st
join (select
s_id
from score sc
join
(select
c.c_id
from course c
join teacher t
on c.t_id=t.t_id
where t_name="张三") t1
on t1.c_id =sc.c_id) t2 on st.s_id=t2.s_id

8.查询没学过"张三"老师授课的同学的信息

select * from student st
 left join (select
s_id
from score sc
join
(select
c.c_id
from course c
join teacher t
on c.t_id=t.t_id
where t_name="张三") t1
on t1.c_id =sc.c_id) t2 on st.s_id=t2.s_id 
where  t2.s_id  is null 

9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select * from student s
join
(select t1.s_id from
(select s_id from score where c_id =1) t1
join
(select s_id from score where c_id =2) t2
on t1.s_id=t2.s_id) t3
on  s.s_id=t3.s_id