【Hive】Hive练习题50道

数据展示

student表

score表

teacher表

course表

在hive中建表导入数据

首先要先在hdfs上为每个数据建一个文件名相同的文件夹，以上的4张表都是txt格式的，放入hdfs相对应的文件夹后，使用以下语句建表(因为数据量不大，就直接建内部表)

create table if not exists student(
id int,
name string,
birthday string,
sex string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/data/myschool/student';

create table if not exists teacher(
tid int,
tname string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/data/myschool/teacher';

create table if not exists score(
sid int,
cid int,
scores int
)
row format delimited fields terminated by '\t'
stored as textfile
location '/data/myschool/score';

create table if not exists course(
cid int,
cname string,
tid int
)
row format delimited fields terminated by '\t'
stored as textfile
location '/data/myschool/course';

题目

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

select stu.*,sco1.scores 01scores,sco2.scores 02scores from 
student stu join score sco1 
on stu.id=sco1.sid and sco1.cid=1
left join score sco2 
on stu.id=sco2.sid and sco2.cid=2
where sco1.scores>sco2.scores;

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

select stu.*,sco1.scores 01scores,sco2.scores 02scores from 
student stu join score sco1 
on stu.id=sco1.sid and sco1.cid=1
left join score sco2 
on stu.id=sco2.sid and sco2.cid=2
where sco1.scores<sco2.scores;

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

select stu.id,stu.name,avg(sco.scores)
from student stu join score sco
on stu.id=sco.sid
group by stu.id,stu.name
having avg(sco.scores)>60;

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

select stu.id,stu.name,round(avg(sco.scores),2) as avg_scores
from student stu join score sco
on stu.id=sco.sid
group by stu.id,stu.name
having avg(sco.scores)<60
union all
select stu1.id,stu1.name,0 as avg_scores
from student stu1
where stu1.id not in
(select distinct sid from score);

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

select stu.id,stu.name,count(cid),sum(scores)
from student stu left join score sco
on stu.id=sco.sid
group by stu.id,stu.name;

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

select count(tid) as num,'姓李的老师' as teal
from teacher 
where tname like '李%';

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

select stu.* 
from student stu join score sco on stu.id=sco.sid
join course cor on sco.cid=cor.cid
join teacher tea on tea.tid=cor.tid
where tea.tname='张三';

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

select s.* from student s 
where s.id not in
(select stu.id 
from student stu join score sco on stu.id=sco.sid
join course cor on sco.cid=cor.cid
join teacher tea on tea.tid=cor.tid
where tea.tname='张三');

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

select stu.*
from student stu
join 
(select sid as</