hivesq面试50题

使用hive语句完成sq

 数据

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 女

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


01	张三
02	李四
03	王五



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

题目

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

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

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

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

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

6、查询"李"姓老师的数量:

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

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

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

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

11、查询没有学全所有课程的同学的信息:
–先查询出课程的总数量–再查询所需结果

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

28、查询男生、女生人数:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 1
select distinct st.s_id,st.s_name,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
where (select s.s_score
from score s
where s.s_id=st.s_id and s.c_id='01')>
(select s1.s_score
from score s1
where s1.s_id=st.s_id and s1.c_id='02')
and sc.c_id='01';
-- 2
select distinct st.s_id,st.s_name,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
where (select s.s_score
from score s
where s.s_id=st.s_id and s.c_id='01')<
(select s1.s_score
from score s1
where s1.s_id=st.s_id and s1.c_id='02')
and sc.c_id='01';
-- 3
select student.s_id,student.s_name,round(avg(s.s_score),2) as avg_score
from student
join score s on student.s_id=s.s_id 
group by student.s_id,student.s_name
having avg(s.s_score) >=60;
-- 4
select student.s_id,student.s_name,round(avg(s.s_score),2) as avg_score
from student
left join score s on student.s_id=s.s_id 
group by student.s_id,student.s_name
having avg(s.s_score) <60 or avg(s.s_score) is null;
-- 5
select student.s_id,student.s_name,(count(score.c_id)) as total_count,sum(score.s_score) as total_score
from student
left join score on student.s_id=score.s_id
group by student.s_id,student.s_name;
-- 6
select count(t_name) count
from teacher
where t_name like '李%'

-- 7
select student.*
from student 
join score on student.s_id=score.s_id
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id and teacher.t_name='张三';
-- 8
select student.*
from student
left join
(select student.s_id
from student 
join score on student.s_id=score.s_id
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id where teacher.t_name='张三') temp
on student.s_id=temp.s_id
where temp.s_id is null;
-- 9
select st.*,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
left join course co
on co.c_id=sc.c_id
left join teacher te
on te.t_id=co.t_id
where sc.c_id='01' and sc.c_id in (select  ss.c_id from score ss where ss.s_id =st.s_id)
-- 10
select st.*,sc.c_id
from student st
left join
score sc
on st.s_id=sc.s_id
left join
(select score.c_id,score.s_id
from student 
join score 
on student.s_id=score.s_id
join course 
on score.c_id=course.c_id
join teacher 
on course.t_id=teacher.t_id 
where score.c_id='02') temp
on st.s_id=temp.s_id
where temp.c_id is null 
and sc.c_id='01';
-- 11
select st.s_id,st.s_name
from student st
left join score sc
on st.s_id=sc.s_id
group by st.s_id,st.s_name
having count(*)<3
-- 12
select distinct st.s_id,st.s_name
from student st
left join score s1
on s1.s_id=st.s_id
where s1.c_id in
(select sc.c_id
from student ss 
left join score sc 
on ss.s_id=sc.s_id
where ss.s_id='01') 
-- 13

-- 14
select student.*
from student
left join
(select student.s_id
from student 
join score on student.s_id=score.s_id
join course on score.c_id=course.c_id
join teacher on course.t_id=teacher.t_id where teacher.t_name='张三') temp
on student.s_id=temp.s_id
where temp.s_id is null;
-- 15
select st.s_id,st.s_name,round(avg(sc.s_score),2)
from student st
left join score sc
on st.s_id=sc.s_id
where sc.s_score<60
group by st.s_id,st.s_name
having count(sc.s_score)>=2
-- 16
select st.s_id,st.s_name,sc.s_score
from student st
left join score sc
on st.s_id = sc.s_id
where sc.c_id='01' and sc.s_score<60 
order by sc.s_score DESC
-- 17
select
s_id,
round(avg(s_score),2) avg_score,
sum(case when c_id='01' then s_score else 0 end) `语文`,
sum(case when c_id='02' then s_score else 0 end) `数学`,
sum(case when c_id='03' then s_score else 0 end) `英语`
from score
group by s_id
order by avg_score DESC

-- 18
select
t.c_id,
c.c_name,
max(t.s_score) `最高分`,
min(t.s_score) `最低分`,
round(avg(t.s_score),2) `最低分`,
round(sum(case when (t.s_score)>=60 then 1 else 0 end)/count(*),2) `及格率`,
round(sum(case when (t.s_score)>=70 and (t.s_score)<80 then 1 else 0 end)/count(*),2) `中等率`,
round(sum(case when (t.s_score)>=80 and (t.s_score)<90 then 1 else 0 end)/count(*),2) `优良率`,
round(sum(case when (t.s_score)>=90 then 1 else 0 end)/count(*),2) `优秀率`
from score t
join course c
on t.c_id=c.c_id
group by t.c_id,c.c_name
-- 19
select
c_id,
s_score,
row_number() over(partition by c_id order by s_score DESC) rn
from score
-- 20
select
st.s_id,
st.s_name,
sum(sc.s_score) sum_score,
row_number() over(order by sum(sc.s_score) DESC) 
from student st
join score sc
on st.s_id=sc.s_id
group by st.s_id,st.s_name
-- 21
select sc.c_id,avg(sc.s_score) avg
from score sc
join course co
on sc.c_id=co.c_id
join teacher te
on te.t_id=co.t_id
group by sc.c_id
order by avg 
-- 22
select
id,
name,
score
from 
(select
st.s_id id,
st.s_name name,
sc.s_score score,
row_number() over(order by sc.s_score DESC) rn
from  student st
join score sc
on st.s_id=sc.s_id
) t
where rn=2  or rn=3
-- 23
select
t.c_id,
c.c_name,
concat(round(sum(case when (t.s_score)>=0 and (t.s_score)<=60 then 1 else 0 end)/count(*),2)*100,'%')`[0-60]`,
concat(round(sum(case when (t.s_score)>=60 and (t.s_score)<=70 then 1 else 0 end)/count(*),2)*100,'%') `[70-60]`,
concat(round(sum(case when (t.s_score)>=70 and (t.s_score)<=85 then 1 else 0 end)/count(*),2)*100,'%') `[85-70]`,
concat(round(sum(case when (t.s_score)>=85 and (t.s_score)<=100 then 1 else 0 end)/count(*),2)*100,'%') `[100-85]`
from score t
join course c
on t.c_id=c.c_id
group by t.c_id,c.c_name
-- 24
select st.s_id,st.s_name,round(avg(sc.s_score),2) avg
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
join teacher te
on te.t_id=co.t_id
group by st.s_id,st.s_name
order by avg DESC
-- 25
select st.*,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
join teacher te
on te.t_id=co.t_id
where sc.c_id='01'
order by sc.s_score DESC

select st.*,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
join teacher te
on te.t_id=co.t_id
where sc.c_id='02'
order by sc.s_score DESC

select st.*,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
join teacher te
on te.t_id=co.t_id
where sc.c_id='03'
order by sc.s_score DESC
-- 26
select
c_id,
count(*) `学生人数`
from score
group by c_id
-- 27
select st.s_id,st.s_name
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
join teacher te
on te.t_id=co.t_id
group by st.s_id,st.s_name
having count(sc.c_id)=2
-- 28
select s_sex,count(*)
from student
group by s_sex
-- 29
select *
from student 
where s_name like '%风%'
-- 30
select s_id,s_name,count(s_name) count_name
from student 
group by s_id,s_name
having count_name>1
-- 31
select *
from student 
where year(s_date)=1990
-- 32
select c_id,round(avg(s_score),2) avg
from score
group by c_id
order by avg DESC,c_id
-- 33
select st.s_id,st.s_name,round(avg(sc.s_score),2) avg
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
join teacher te
on te.t_id=co.t_id
group by st.s_id,st.s_name
having avg(sc.s_score)>=85
-- 34
select st.s_name,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
where co.c_name='数学' and sc.s_score<60
-- 35
select st.s_name,sc.c_id,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
join course co
on co.c_id=sc.c_id
-- 36
select
name,
`语文`,
`数学`,
`英语`
from
(
select
st.s_name name,
sum(case when sc.c_id='01' then sc.s_score else 0 end) `语文`,
sum(case when sc.c_id='02' then sc.s_score else 0 end) `数学`,
sum(case when sc.c_id='03' then sc.s_score else 0 end) `英语`
from student st
join score sc
on st.s_id=sc.s_id
group by st.s_name) t
where `语文`>=70 and `数学`>=70 and `英语`>=70
-- 37
select st.s_name,sc.c_id,sc.s_score
from student st
left join score sc
on st.s_id=sc.s_id
where sc.s_score<60
-- 38
select st.s_id,st.s_name
from student st
left join score sc
on st.s_id=sc.s_id
where sc.s_score>60 and sc.c_id='01'
-- 39
select	c_id,count(*) 
from score
group by c_id
-- 40
select student.*,score.s_score
from student 
join score 
on student.s_id=score.s_id
join course 
on score.c_id=course.c_id
join teacher 
on course.t_id=teacher.t_id and teacher.t_name='张三'
order by score.s_score DESC
limit 1
-- 41
select
name,
yw,
sx,
yy
from
(
select
st.s_name name,
sum(case when sc.c_id='01' then sc.s_score else 0 end) yw,
sum(case when sc.c_id='02' then sc.s_score else 0 end) sx,
sum(case when sc.c_id='03' then sc.s_score else 0 end) yy
from student st
join score sc
on st.s_id=sc.s_id
group by st.s_name) t
where yw=sx or yw=yy or yy=sx or yy=sx=yw
-- 42
select
c_id,
id,
name,
rn
from
(select
sc.c_id c_id,
st.s_id id,
st.s_name name,
row_number() over(partition by sc.c_id order by sc.s_score DESC) rn
from student st
join score sc
on st.s_id=sc.s_id) t
where t.rn<=3
-- 43
select c_id,count(c_id) sum
from score
group by c_id
having sum>5
order by sum DESC,c_id
-- 44
select st.s_id
from student st
left join score sc
on st.s_id=sc.s_id
where sc.c_id>1
group by st.s_id
-- 45
select st.*
from student st
left join score sc
on st.s_id=sc.s_id
where sc.c_id=3
group by st.s_id
-- 46

-- 47
select *
from student
where weekofyear(s_date)=weekofyear(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'))
-- 48
select *
from student
where weekofyear(s_date)=weekofyear(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'))+1
-- 49
select *
from student
where month(s_date)=month(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'))
-- 50
select *
from student
where month(s_date)=12