【Hive】Hive练习题50道

数据展示

student表

score表

teacher表

course表

在hive中建表导入数据

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

createtableifnotexists student(
id int,
name string,
birthday string,
sex string
)row format delimited fieldsterminatedby'\t'
stored as textfile
location '/data/myschool/student';createtableifnotexists teacher(
tid int,
tname string
)row format delimited fieldsterminatedby'\t'
stored as textfile
location '/data/myschool/teacher';createtableifnotexists score(
sid int,
cid int,
scores int)row format delimited fieldsterminatedby'\t'
stored as textfile
location '/data/myschool/score';createtableifnotexists course(
cid int,
cname string,
tid int)row format delimited fieldsterminatedby'\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=1leftjoin score sco2 
on stu.id=sco2.sid and sco2.cid=2where 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=1leftjoin score sco2 
on stu.id=sco2.sid and sco2.cid=2where 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
groupby stu.id,stu.name
havingavg(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
groupby stu.id,stu.name
havingavg(sco.scores)<60unionallselect stu1.id,stu1.name,0as avg_scores
from student stu1
where stu1.id notin(selectdistinct sid from score);

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

select stu.id,stu.name,count(cid),sum(scores)from student stu leftjoin score sco
on stu.id=sco.sid
groupby stu.id,stu.name;

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

selectcount(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 notin(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 tmpid from score
where cid=1unionallselect sid as tmpid from score
where cid=2) ss on stu.id=ss.tmpid
groupby stu.id,stu.name,stu.birthday,stu.sex,ss.tmpid
havingcount(ss.tmpid)=2;

10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select stu.*from student stu 
join(select sid from score where cid=1) sco1
on stu.id=sco1.sid
leftjoin(select sid from score where cid=2) sco2
on stu.id=sco2.sid
where sco2.sid isnull;

11、查询没有学全所有课程的同学的信息

select student.*from student
leftjoin(select sid
from score
groupby sid
havingcount(cid)=3)tmp
on student.id=tmp.sid
where tmp.sid isnull;

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select stu.*from student stu
join(select cid from score where sid=1) tmp1
join(select sid,cid from score) tmp2
on tmp1.cid=tmp2.cid and stu.id=tmp2.sid
where stu.id notin(1)groupby stu.id,name,birthday,sex;

13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select stu.*,count(tmp2.cid)from student stu
join(select cid from score where sid=1) tmp1
join(select sid,cid from score) tmp2
on tmp1.cid=tmp2.cid and stu.id=tmp2.sid
where stu.id notin(1)groupby stu.id,name,birthday,sex
havingcount(tmp2.cid)in(selectcount(cid)from score where sid=1);

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

select stu.id,stu.name from student stu
leftjoin(select sid,cid from score) sco
leftjoin(select cid,tid from course) cor
leftjoin(select tid from teacher where tname='张三') tea
on stu.id=sco.sid and sco.cid=cor.cid and tea.tid=cor.tid 
groupby stu.id,name
havingcount(tea.tid)=0;

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select stu.*,tmp.savg from student stu 
join(select sid,count(cid) noc,round(avg(scores),1) savg 
from score where scores<60groupby sid having noc>=2) tmp
on tmp.sid=stu.id;

16、检索"01"课程分数小于60，按分数降序排列的学生信息

select stu.*,tmp.scores from student stu join(select sid,scores from score where cid=1and scores<60) tmp
on stu.id=tmp.sid
orderby tmp.scores desc;

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select a.sid,tmp1.scores as c1,tmp2.scores as c2,tmp3.scores as c3,round(avg(a.scores),2)as avgScore
from score a
leftjoin(select sid,scores  from score s1 where  cid='01')tmp1 on  tmp1.sid=a.sid
leftjoin(select sid,scores  from score s2 where  cid='02')tmp2 on  tmp2.sid=a.sid
leftjoin(select sid,scores  from score s3 where  cid='03')tmp3 on  tmp3.sid=a.sid
groupby a.sid,tmp1.scores,tmp2.scores,tmp3.scores orderby avgScore desc;

18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

select course.cid,course.cname,tmp.maxScore,tmp.minScore,tmp.avgScore,tmp.passRate,tmp.moderate,tmp.goodRate,tmp.excellentRates from course
join(select 
    cid,max(scores)as maxScore,min(scores)as minScore,round(avg(scores),2) avgScore,round(sum(casewhen scores>=60then1else0end)/count(cid),2)passRate,round(sum(casewhen scores>=60and scores<70then1else0end)/count(cid),2) moderate,round(sum(casewhen scores>=70and scores<80then1else0end)/count(cid),2) goodRate,round(sum(casewhen scores>=80and scores<90then1else0end)/count(cid),2) excellentRates
from score groupby cid) tmp on tmp.cid=course.cid;

19、按各科成绩进行排序，并显示排名:– row_number() over()分组排序功能(mysql没有该方法)

select cid,sid,scores,row_number()over(partitionby cid orderby scores desc)from score;

20、查询学生的总成绩并进行排名

select score.sid,student.name,sum(scores) sum_sco,row_number()over(orderbysum(scores)desc)nofrom score join student on score.sid=student.id
groupby score.sid,student.name;

21、查询不同老师所教不同课程平均分从高到低显示

select score.cid,round(avg(scores),2) avg_scores,course.tid 
from score join
course on score.cid=course.cid
groupby score.cid,course.tid
orderby avg_scores desc;

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

select tmp.cid,stu.*,tmp.scores,tmp.cno from
student stu join(select cid,sid,scores,row_number()over(partitionby cid orderby scores desc) cno
from score) tmp
on stu.id=tmp.sid
where tmp.cno between2and3;

23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

select 
score.cid,
course.cname,round(sum(casewhen score.scores>=85and score.scores<=100then1else0end)/count(score.scores),2)as100and85,round(sum(casewhen score.scores>=70and score.scores<85then1else0end)/count(score.scores),2)as85and70,round(sum(casewhen score.scores>=60and score.scores<70then1else0end)/count(score.scores),2)as70and60,round(sum(casewhen score.scores>=0and score.scores<60then1else0end)/count(score.scores),2)as60and0
from score leftjoin course
on score.cid = course.cid
groupby score.cid,course.cname;

24、查询学生平均成绩及其名次

select sid,round(avg(scores),2)as avgs,row_number()over(orderbyavg(scores)desc)from score
groupby sid;

25、查询各科成绩前三名的记录

select tmp.cid,stu.*,tmp.scores,tmp.cno from
student stu join(select cid,sid,scores,row_number()over(partitionby cid orderby scores desc) cno
from score) tmp
on stu.id=tmp.sid
where tmp.cno<=3;

26、查询每门课程被选修的学生数

select cid,count(scores)as cnum
from score
groupby cid;

27、查询出只有两门课程的全部学生的学号和姓名

select sid,count(cid)as cnum
from score
groupby sid
havingcount(cid)=2;

28、查询男生、女生人数

select sex,count(1)as pnum
from student
groupby sex;

29、查询名字中含有"风"字的学生信息

select*from student 
where name like'%风%';

30、查询同名同性学生名单，并统计同名人数

select name,sex,count(id)from student
groupby name,sex;

31、查询1990年出生的学生名单

select*from student
whereyear(birthday)=1990;

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select cid,round(avg(scores),2)as avgs,row_number()over(orderbyround(avg(scores),2)desc,cid asc)from score
groupby cid;

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select stu.id,stu.name,avg(scores)as avgs
from student stu join
score sco on stu.id=sco.sid
groupby stu.id,stu.name
havingavg(scores)>85;

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

select stu.name,sco.scores
from student stu
join score sco 
join course cor
on stu.id=sco.sid and sco.cid=cor.cid
where cor.cname='数学'and sco.scores<60;

35、查询所有学生的课程及分数情况

select stu.id,tmp.chinese,tmp.math,tmp.english
from student stu
leftjoin(select sco.sid id,sum(case cor.cname when'语文'then sco.scores else0end)as chinese,sum(case cor.cname when'数学'then sco.scores else0end)as math,sum(case cor.cname when'英语'then sco.scores else0end)as english
 from score sco
 join course cor on sco.cid=cor.cid
 groupby sco.sid
) tmp on stu.id=tmp.id;

36、查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数

select stu.name,cor.cname,sco.scores
from score sco
leftjoin student stu on sco.sid=stu.id
join course cor on sco.cid=cor.cid
where sco.scores>70;

37、查询课程不及格的学生

select sid
from score
where scores<60groupby sid;

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

select sco.sid,stu.name
from score sco
join student stu 
on sco.sid=stu.id
where cid=1and scores>=80;

39、求每门课程的学生人数

select cid,count(sid)from score
groupby cid;

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

select stu.*,sco.cid,max(sco.scores) max_score
from score sco
leftjoin student stu
on stu.id=sco.sid
join course cor
on sco.cid=cor.cid
join teacher tea
on tea.tid=cor.tid
where tea.tname='张三'groupby sco.cid,stu.id,stu.name,stu.birthday,stu.sex
limit1;

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select s1.sid,s1.cid,s1.scores
from score s1,score s2
where s1.cid<>s2.cid and s1.scores=s2.scores;

42、查询每门课程成绩最好的前三名

select tmp.cid,stu.*,tmp.scores,tmp.cno from
student stu join(select cid,sid,scores,row_number()over(partitionby cid orderby scores desc) cno
from score) tmp
on stu.id=tmp.sid
where tmp.cno<=3;

43、统计每门课程的学生选修人数（超过5人的课程才统计）:
–要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select cid,count(sid)as num
from score
groupby cid
having num>=5orderby num desc,cid asc;

44、检索至少选修两门课程的学生学号

select sid
from score
groupby sid
havingcount(cid)>=2;

45、查询选修了全部课程的学生信息

select stu.*from student stu
join(select sid,count(cid) cnum from score groupby sid) tmp
on stu.id=tmp.sid
where tmp.cnum=3;

46、查询各学生的年龄(周岁):
–按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

with tmp as(select id,year(current_date())-year(birthday)as tage
from student)select stu.id,sum(casemonth(current_date())>month(stu.birthday)whentruethen tmp.tage-1else tmp.tage end) s_age 
from student stu
join tmp
on stu.id=tmp.id
groupby stu.id;

47、查询本周过生日的学生:

select*from student 
where weekofyear(concat(year(current_date()),'-',date_format(birthday,'MM-dd')))=
      weekofyear(current_date())

48、查询下周过生日的学生:

select*from student 
where weekofyear(concat(year(current_date()),'-',date_format(birthday,'MM-dd')))=
      weekofyear(current_date())+1;

49、查询本月过生日的学生:

select*from student 
wheremonth(birthday)=month(current_date())；

50、查询12月份过生日的学生:

select*from student wheremonth(birthday)=12