hive初级练习50题(详细过程)

Hive 专栏收录该内容
11 篇文章 0 订阅

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

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

--找到学过01课程的学生id
select s_id from score where c_id=1
--找到学过02课程的学生id
select s_id from score where c_id=2
--找到没有学过02课程的学生id
select t2.s_id from
(select st.s_id,t1.scores from student st
left join (select s_id,scores from score where c_id=2) t1
on st.s_id=t1.s_id)t2
where t2.scores is null

:如何找到没有学过某课程的学生
(1)先找到学过此课程的学生id
(2)左联学生表,这样没学过的学生成绩则显示为null
(3)再在表中找出课程分数信息为null的学生id

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

--找出所有课程数
select count(*) from course

--找出每个同学的课程数
select s_id ,count(c_id) cc from score group by s_id

--找出课程数没达到总课程数的学生id
select st.s_id,s_name
from 
student st
join
(select s_id,t1.cc
from
(select s_id ,count(c_id) cc from score group by s_id) t1
where t1.cc not in(select count(*) from course))t2
on st.s_id=t2.s_id

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

--找出学号为"01"的同学的所有课程
select c_id from score where s_id="01"
--找到其他同学学的课程01学过课程的学生id
select distinct s_id from score sc
where sc.c_id in
(select c_id from score where s_id="01");

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

--找出学号为"01"的同学的所有课程
select c_id from score where s_id="01"
--找出学号为"01"的同学的所有课程的数量
select count(1) from score where s_id="01"

--找出学习过01号学生学习的课程的学生id
select distinct s_id from score sc
where sc.c_id in
(select c_id from score where s_id="01");
--找出学习过01号学生学习的课程的学生id和学习过课程的数量
(select  sc.s_id,count(c_id) cc from score sc
where sc.c_id in
(select c_id from score where s_id="01") and sc.s_id <>"01"
group by sc.s_id) t1;

--找出和"01"号的同学学习过一样的课程且数量相等的其他同学的id
select
t1.s_id,t1.cc
from
(select  sc.s_id,count(c_id) cc from score sc
where sc.c_id in
(select c_id from score where s_id="01") and sc.s_id <>"01"
group by sc.s_id) t1 where t1.cc  in
(select count(1) from score where s_id="01")

--join学生表,获取学生信息
select
st.s_id,st.s_name
from student st
join
(select
t1.s_id,t1.cc
from
(select  sc.s_id,count(c_id) cc from score sc
where sc.c_id in
(select c_id from score where s_id="01") and sc.s_id <>"01"
group by sc.s_id) t1 where t1.cc  in
(select count(1) from score where s_id="01")
)t2
where st.s_id=t2.s_id

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

--找出"张三"老师教授的课程
select c_id
from course c
join
teacher t
on c.t_id=t.t_id
where t_name="张三"
--找出学过"张三"老师讲授的任一门课程的学生id
select  sc.s_id from score sc
where sc.c_id  in(select c_id
from course c
join
teacher t
on c.t_id=t.t_id
where t_name="张三")
--没学过"张三"老师讲授的任一门课程的学生姓名
select st.s_id,st.s_name
from student st
where st.s_id not in
(select t1.s_id from (select sc.s_id from score sc
where sc.c_id  in (select c_id
from course c
join
teacher t
on c.t_id=t.t_id
where t_name="张三"))t1)

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

--找出两门以上不及格的同学学号
select s_id ,count(scores) cs
from score
where scores <60
group by s_id
having cs>1
--查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩:
select sc.s_id,st.s_name,avg(scores) avgs from score sc
join student st
on st.s_id=sc.s_id
join
(select s_id ,count(scores) cs
from score
where scores <60
group by s_id
having cs>1) t1
on  sc.s_id=t1.s_id
group by sc.s_id ,st.s_name

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

--找出"01"课程分数小于60学生的id
select s_id ,scores from score
where scores<60 and  c_id="01"
order by scores desc

--关联上学生信息
select st.s_id,st.s_name,scores
from student st
join score sc
on st.s_id=sc.s_id
where scores<60 and  c_id="01"
order by scores desc

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

select s_id,scores,avg(scores)  over(partition by s_id ) avgs from score order by avgs desc

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

–及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90

--查询各科成绩最高分、最低分和平均分
select c_id,max(scores),min(scores),avg(scores) from score group by c_id;
-- 及格率,中等率,优良率,优秀率:
round(count(if(scores>=60,scores,null))/count(scores)*100,2) as jige
round(count(if(scores>=70 and scores<80,scores,null))/count(scores)*100,2) as zhongdeng
round(count(if(scores>=80 and scores<90,scores,null))/count(scores)*100,2) as youliang
round(count(if(scores>=90,scores,null))/count(scores)*100,2) as youxiu
--找出课程id,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
select c_id,
max(scores) max_score,
min(scores) min_score,
avg(scores) avg_score,
round(count(if(scores>=60,scores,null))/count(scores)*100,2) as jige,
round(count(if(scores>=70 and scores<80,scores,null))/count(scores)*100,2) as zhongdeng,
round(count(if(scores>=80 and scores<90,scores,null))/count(scores)*100,2) as youliang,
round(count(if(scores>=90,scores,null))/count(scores)*100,2) as youxiu
from score group by c_id;
--和course进行join,然后得出课程name
select
c.c_id,c.c_name,max_score,min_score, avg_score,jige,zhongdeng,youliang,youxiu
from
course c
join(select c_id,
max(scores) max_score,
min(scores) min_score,
avg(scores) avg_score,
round(count(if(scores>=60,scores,null))/count(scores)*100,2) as jige,
round(count(if(scores>=70 and scores<80,scores,null))/count(scores)*100,2) as zhongdeng,
round(count(if(scores>=80 and scores<90,scores,null))/count(scores)*100,2) as youliang,
round(count(if(scores>=90,scores,null))/count(scores)*100,2) as youxiu
from score group by c_id)t1 on t1.c_id=c.c_id

19.按各科成绩进行排序,并显示排名

select c_id,s_id,scores,row_number()over(partition by c_id order by scores) from score

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

select s_id,t1.s,row_number()over(order by t1.s desc)
from
(select s_id,sum(scores) s from score group by s_id)t1

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

--不同课程平均分排序
select c_id, avg(scores) avgs
from score
group by c_id
order by avgs
--通过课程id找到老师名字
select t.t_id,t.t_name,c.c_id,avgs
from
teacher t
join
course c
on t.t_id=c.t_id
join
(select c_id, avg(scores) avgs
from score
group by c_id) t1
on t1.c_id=c.c_id
order by avgs desc

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

--查每种课程的第2名到第3名的学生id和成绩
select s_id,c_id, scores,rn
from
(select s_id,c_id,scores,row_number()over(partition by c_id order by scores desc) rn
from score)t1
where t1.rn in (2,3)

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

select c.c_id,c.c_name,0to60,60to70,70to85,85to100 from
course c
join
(select c_id,
round(count(if(scores>0 and scores<60,scores,null))/count(scores)*100,2) as 0to60,
round(count(if(scores>=60 and scores<70,scores,null))/count(scores)*100,2) as 60to70,
round(count(if(scores>=70 and scores<85,scores,null))/count(scores)*100,2) as 70to85,
round(count(if(scores>=85 and scores<100,scores,null))/count(scores)*100,2) as 85to100
from score
group by c_id)t1
on t1.c_id=c.c_id

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

select s_id,avgs,
row_number()over(order by avgs desc)
from 
(select s_id,avg(scores) avgs
from score group by s_id)t1

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

select c.c_id,c.c_name,st.s_id,st.s_name,scores,rn
from course c
join
(select s_id,c_id,scores,row_number()over(partition by c_id order by scores) rn from score)t1
on c.c_id=t1.c_id
join student st
on st.s_id=t1.s_id
where rn in (1,2,3)

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

select c.c_id,c.c_name,t1.c
from course c
join
(select c_id,count(s_id)c
from score
group by c_id)t1
on c.c_id=t1.c_id; 

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

select st.s_id,st.s_name
from student st
join
(select s_id,count(c_id)
from score
group by s_id)t1
on st.s_id=t1.s_id

28.查询男生、女生人数

select 
count(if(gender="男",1,null)) as boyN,
count(if(gender="女",1,null)) as girlN
from student 

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

select * from student
where s_name like "%风%"

30.查询同名同姓学生名单,并统计同名人数

select s_name,count(*) c
from  student group by s_name
having c>1

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

select s_name
from student
where year(birth)=1990

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

select c_id,avg(scores) avgs
from score group by c_id
order by avgs desc ,c_id

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

select st.s_id,st.s_name,avgs
from student st
join
(select s_id,avg(scores) avgs
from score 
group by s_id
having avgs >85)t1
on st.s_id=t1.s_id;

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

--找出数学的课程id
select c_id from course where c_name="数学"
--找出数学小于60分的学生id
select st.s_id ,s_name,scores from score sc
join course c on sc.c_id=c.c_id
join student st on st.s_id=sc.s_id
where c_name="数学"
and scores <60

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

--所有学生哦,包括没有成绩的学生,需要左连
select st.s_id,st.s_name,c.c_id,c.c_name,scores
from  student st
left join score sc
on sc.s_id=st.s_id
join course c

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

select st.s_name,c.c_name,scores
from score sc
join student st
on sc.s_id=st.s_id
join course c
on c.c_id=sc.c_id
where scores>70

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

select st.s_id,st.s_name,c.c_id,c.c_name,scores
from score sc
join student st
on sc.s_id=st.s_id
join course c
on c.c_id=sc.c_id
where scores<60

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

select st.s_id,st.s_name
from student st
join score sc
on sc.s_id=st.s_id
where c_id="01" and scores>80;

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

select c_id,count(s_id) c
from score
group by c_id;

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

select st.s_id,st.s_name,scores
from score sc
join student st
on st.s_id=sc.s_id
join course c
on c.c_id=sc.c_id
join teacher t
on t.t_id=c.t_id
where t_name="张三"
order by scores desc
limit 1;

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

select s1.s_id,s1.c_id,s1.scores
from score s1, score s2
where s1.scores=s2.scores
and s1.c_id!=s2.c_id

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

select t.* from 
(select c_id, s_id,row_number()over(partition by c_id order by scores) rn from score)t
where rn<=3

43.统计每门课程的学生选修人数(超过5人的课程才统计)

– 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列

select c_id, count(s_id) c
from score
group by c_id
having c>5
order by c desc,c_id

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

select s_id,count(c_id) c
from score
group by s_id
having c>=2

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

select count(c_id) from course
--
select t1.s_id,t1.s_name
from
(select st.s_id,st.s_name,count(c_id) c1
from student st
join score sc on st.s_id=st.s_id group by st.s_id,st.s_name)t1
join (select count(c_id) c2 from course)t2
on t1.c1=t2.c2

46.查询各学生的年龄(周岁)

– 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一

select *, cast(date_format(current_date(),'yyyy') as int)-cast(date_format(birth,'yyyy') as int)
from student;

47.查询本周过生日的学生

--本周的第几天
select DAYOFWEEK(current_date());

--本周的第一天日期
 select date_add(current_date(), -(DAYOFWEEK(current_date())-1));

--本周最后一天的日期
select date_add(current_date(), (7-DAYOFWEEK(current_date())));
--转换成月份和日期的格式
select date_format(birth,'%M-%d') from student;
 
 select date_format((date_add(current_date(), -(DAYOFWEEK(current_date())-1))),'%M-%d') 
 
 select date_format(( date_add(current_date(), (7-DAYOFWEEK(current_date())))),'%M-%d') 
 
 -- 
 select date_format(birth,'%M-%d') between
 ((date_format((date_add(current_date(), -(DAYOFWEEK(current_date())-1))),'%M-%d')),
 (date_format(( date_add(current_date(), (7-DAYOFWEEK(current_date())))),'%M-%d'))) from student
 --结果
select s_id,s_name from student 
where (date_format(birth,'%M-%d'))between 
 (date_format((date_add(current_date(), -(DAYOFWEEK(current_date())-1))),'%M-%d')) and
 (date_format(( date_add(current_date(), (7-DAYOFWEEK(current_date())))),'%M-%d'))

48.查询下周过生日的学生

--下周的第一天
select date_add(current_date(), (8-DAYOFWEEK(current_date())));
--下周的最后一天
select date_add(current_date(), (14-DAYOFWEEK(current_date())));
--查询下周过生日的学生
select s_id,s_name from student 
where (date_format(birth,'%M-%d'))
between 
(date_format( date_add(current_date(), (8-DAYOFWEEK(current_date()))),'%M-%d'))
and
(date_format( date_add(current_date(), (14-DAYOFWEEK(current_date()))),'%M-%d'))

49.查询本月过生日的学生

--找出月份
select month(current_date());
--
select s_id,s_name
from student
where month(birth)=month(current_date())

50.查询12月份过生日的学生

select s_id,s_name
from student
where month(birth)='12'
  • 1
    点赞
  • 3
    评论
  • 10
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 游动-白 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值