hive sql实战50题

最新推荐文章于 2024-08-14 06:53:59 发布

MasterBoy428

最新推荐文章于 2024-08-14 06:53:59 发布

阅读量3.5k

点赞数 5

文章标签： sql hive p2p

本文链接：https://blog.csdn.net/weixin_44200704/article/details/122902552

版权

本文列举了50道关于Hive SQL的实战题目，涵盖各种查询场景，如平均成绩筛选、课程比较、学生选课统计、教师授课情况等。通过这些题目，读者可以深入理解Hive SQL在大数据分析中的应用。

摘要由CSDN通过智能技术生成

查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

思路：将score做自连接

select *

from student

where s_id in

(select distinct  s1.s_id

from score s1

join score s2

on s1.s_id = s2.s_id

where s1.c_id = "01"

and s2.c_id = "02" and

s1.s_score>s2.s_score)

结果：

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

思路：连接三张表

Select student.* ,a.s_score,b.s_score from student

 join score a on a.c_id = "01"

join score b on b.c_id="02"

Where a.s_id = student.s_id and b.s_id = student.s_id and a.s_score < b.s_score

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

Select student.s_id,student.s_name,round(avg(score.s_score),1) 
from score join student on score.s_id = student.s_id
Group by student.s_id,student.s_name
Having avg(score.s_score)>60

4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:

(包括有成绩的和无成绩的)

Select student.s_id,student.s_name,round(avg(if(score.s_score==NULL,0,score.s_score)),1) from student left join score on student.s_id=score.s_id
Group by student.s_id,student.s_name
Having avg(score.s_score)<60 or avg(score.s_score) is null

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

思路：将student表和score表左连接后查出存在有成绩在score表中的置为1，无成绩在score表中的置为0，方便统计选课数目，再以此表为基础分组聚合

Select t1.sid,t1.name,sum(choice),sum(sumscore)
from
(Select st.s_id as sid,st.s_name as name, if(sc.s_id is null,0,1) as choice,sc.s_score sumscore
from student st left join score sc on st.s_id = sc.s_id) t1
Group by t1.sid,t1.name

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

Select count(1) from teacher where t_name like '李%';

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

Select st.* from teacher t
join course c on t.t_id = c.c_id
Join score sc on sc.c_id = c.c_id
Join student st on st.s_id = sc.s_id
Where t.t_name = '张三'