Hive SQL题库(初级)

这篇具有很好参考价值的文章主要介绍了Hive SQL题库(初级)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

第一章 环境准备

1.1 建表语句

hive>
-- 创建学生表
DROP TABLE IF EXISTS student;
create table if not exists student_info(
    stu_id string COMMENT '学生id',
    stu_name string COMMENT '学生姓名',
    birthday string COMMENT '出生日期',
    sex string COMMENT '性别'
) 
row format delimited fields terminated by ',' 
stored as textfile;

-- 创建课程表
DROP TABLE IF EXISTS course;
create table if not exists course_info(
    course_id string COMMENT '课程id',
    course_name string COMMENT '课程名',
    tea_id string COMMENT '任课老师id'
) 
row format delimited fields terminated by ',' 
stored as textfile;

-- 创建老师表
DROP TABLE IF EXISTS teacher;
create table if not exists teacher_info(
    tea_id string COMMENT '老师id',
    tea_name string COMMENT '学生姓名'
) 
row format delimited fields terminated by ',' 
stored as textfile;

-- 创建分数表
DROP TABLE IF EXISTS score;
create table if not exists score_info(
    stu_id string COMMENT '学生id',
    course_id string COMMENT '课程id',
    score int COMMENT '成绩'
) 
row format delimited fields terminated by ',' 
stored as textfile;

1.2 数据准备

(1)创建/opt/module/data目录

[atguigu@hadoop102 module]$ mkdir data

(2)将如下4个文件放到/opt/module/data目录下

[atguigu@hadoop102 data]$ vim student_info.txt

001,彭于晏,1995-05-16,男
002,胡歌,1994-03-20,男
003,周杰伦,1995-04-30,男
004,刘德华,1998-08-28,男
005,唐国强,1993-09-10,男
006,陈道明,1992-11-12,男
007,陈坤,1999-04-09,男
008,吴京,1994-02-06,男
009,郭德纲,1992-12-05,男
010,于谦,1998-08-23,男
011,潘长江,1995-05-27,男
012,杨紫,1996-12-21,女
013,蒋欣,1997-11-08,女
014,赵丽颖,1990-01-09,女
015,刘亦菲,1993-01-14,女
016,周冬雨,1990-06-18,女
017,范冰冰,1992-07-04,女
018,李冰冰,1993-09-24,女
019,邓紫棋,1994-08-31,女
020,宋丹丹,1991-03-01,女


[atguigu@hadoop102 data]$ vim course_info.txt

01,语文,1003
02,数学,1001
03,英语,1004
04,体育,1002
05,音乐,1002



[atguigu@hadoop102 data]$ vim teacher_info.txt

1001,张高数
1002,李体音
1003,王子文
1004,刘丽英

[atguigu@hadoop102 data]$ vim score_info.txt

001,01,94
002,01,74
004,01,85
005,01,64
006,01,71
007,01,48
008,01,56
009,01,75
010,01,84
011,01,61
012,01,44
013,01,47
014,01,81
015,01,90
016,01,71
017,01,58
018,01,38
019,01,46
020,01,89
001,02,63
002,02,84
004,02,93
005,02,44
006,02,90
007,02,55
008,02,34
009,02,78
010,02,68
011,02,49
012,02,74
013,02,35
014,02,39
015,02,48
016,02,89
017,02,34
018,02,58
019,02,39
020,02,59
001,03,79
002,03,87
004,03,89
005,03,99
006,03,59
007,03,70
008,03,39
009,03,60
010,03,47
011,03,70
012,03,62
013,03,93
014,03,32
015,03,84
016,03,71
017,03,55
018,03,49
019,03,93
020,03,81
001,04,54
002,04,100
004,04,59
005,04,85
007,04,63
009,04,79
010,04,34
013,04,69
014,04,40
016,04,94
017,04,34
020,04,50
005,05,85
007,05,63
009,05,79
015,05,59
018,05,87

1.3 插入数据

(1)插入数据

hive>
load data local inpath '/opt/module/data/student_info.txt' into table student_info;

load data local inpath '/opt/module/data/course_info.txt' into table course_info;

load data local inpath '/opt/module/data/teacher_info.txt' into table teacher_info;

load data local inpath '/opt/module/data/score_info.txt' into table score_info;

(2)验证插入数据情况

hive>
select * from student_info limit 5;
select * from course_info limit 5;
select * from teacher_info limit 5;
select * from score_info limit 5;

第二章 简单查询

2.1 查找特定条件

2.1.1 查询姓名中带“冰”的学生名单

select 
       *
from
     student_info
where stu_name like '%冰%';

2.1.3 检索课程编号为“04”且分数小于60的学生的课程信息,结果按分数降序排列

select
    stu_id,
    course_id,
    score
from score_info
where course_id ='04' and score<60
order by score desc;

2.1.4 查询数学成绩不及格的学生和其对应的成绩,按照学号升序排序

答案一:


SELECT
    student.stu_id,
    student.stu_name,
    score.score 
FROM
    student_info student
    INNER JOIN score_info score ON student.stu_id = score.stu_id
    INNER JOIN course_info course ON score.course_id = course.course_id 
WHERE
    course.course_name = '数学' 
    AND score.score < 60 
ORDER BY
    student.stu_id ASC;

答案二:

select
    s.stu_id,
    s.stu_name,
    t1.score
from student_info s
join (
    select
        *
    from score_info
    where course_id=(select course_id from course_info where course_name='数学') and score < 60
    ) t1 on s.stu_id = t1.stu_id
order by s.stu_id;

第三章 汇总分析

3.1 汇总分析

3.1.1 查询编号为“02”的课程的总成绩

select sum(score) from score_info where  course_id='02';

3.2 分组

3.2.1 查询各科成绩最高和最低的分,以如下的形式显示:课程号,最高分,最低分

select course_id ,max(score) ,min(score)  from score_info group by course_id;

3.2.2 查询每门课程有多少学生参加了考试(有考试成绩)

select course_id,count(stu_id) from score_info group by course_id;

3.3 分组结果的条件

3.3.3 查询同姓(假设每个学生姓名的第一个字为姓)的学生名单并统计同姓人数大于2的姓

答案一:

select substring(stu_name,0,1)   ,count(*) as cnt from student_info group by substring(stu_name,0,1) having cnt>=2;

答案二:

select
    t1.first_name,
    count(*) count_first_name
from (
         select
             stu_id,
             stu_name,
             substr(stu_name,0,1) first_name
         from student_info
     ) t1
group by t1.first_name
having count_first_name >= 2;

3.4 查询结果排序&分组指定条件

3.4.2 按照如下格式显示学生的语文、数学、英语三科成绩,没有成绩的输出为0,按照学生的有效平均成绩降序显示

学生id 语文 数学 英语 有效课程数 有效平均成绩

答案一:

select
    si.stu_id,
    sum(if(ci.course_name='语文',score,0))  `语文`,
    sum(if(ci.course_name='数学',score,0))  `数学`,
    sum(if(ci.course_name='英语',score,0))  `英语`,
    count(*)  `有效课程数`,
    avg(si.score)  `平均成绩`
from
    score_info si
        join
    course_info ci
    on
            si.course_id=ci.course_id
group by
    si.stu_id
order by
    `平均成绩` desc;

答案二:

SELECT
    tab1.stu_id,
    tab1.score,
    tab2.score,
    tab3.score,
    tab4.cnt,
    tab5.avg_score 
FROM
    (
    SELECT
        stu.stu_id,
    IF
        ( si.score IS NULL, 0, si.score ) AS score 
    FROM
        student_info stu
        INNER JOIN score_info si ON stu.stu_id = si.stu_id 
    WHERE
        si.course_id = '01' 
    ) tab1
    FULL JOIN (
    SELECT
        stu.stu_id,
    IF
        ( si.score IS NULL, 0, si.score ) AS score 
    FROM
        student_info stu
        INNER JOIN score_info si ON stu.stu_id = si.stu_id 
    WHERE
        si.course_id = '02' 
    ) tab2 ON tab1.stu_id = tab2.stu_id
    FULL JOIN (
    SELECT
        stu.stu_id,
    IF
        ( si.score IS NULL, 0, si.score ) AS score 
    FROM
        student_info stu
        INNER JOIN score_info si ON stu.stu_id = si.stu_id 
    WHERE
        si.course_id = '03' 
    ) tab3 ON tab3.stu_id = tab2.stu_id
    FULL JOIN (
    SELECT
        stu.stu_id,
        count(*) AS cnt 
    FROM
        student_info stu
        INNER JOIN score_info si ON stu.stu_id = si.stu_id 
    WHERE
        si.score IS NOT NULL 
    GROUP BY
        stu.stu_id 
    ) tab4 ON tab4.stu_id = tab3.stu_id
    FULL JOIN (
    SELECT
        stu.stu_id,
        avg( si.score ) AS avg_score 
    FROM
        student_info stu
        INNER JOIN score_info si ON stu.stu_id = si.stu_id 
    WHERE
        si.score IS NOT NULL 
    GROUP BY
        stu.stu_id 
    ) tab5 ON tab5.stu_id = tab4.stu_id 
ORDER BY
    avg_score DESC;

3.4.3 查询一共参加三门课程且其中一门为语文课程的学生的id和姓名

答案一:

select stu_id,stu_name
from student_info
where stu_id in (select stu_id
                 from (
                          select stu_id, count(*) course_num, collect_set(course_id) course_list
                          from score_info
                          group by stu_id
                          having course_num = 3
                             and array_contains(course_list, '01')) t1);

答案二:

select
    t2.stu_id,
    s.stu_name
from (
         select t1.stu_id
         from (
                  select stu_id,
                         course_id
                  from score_info
                  where stu_id in (
                      select stu_id
                      from score_info
                      where course_id = "01"
                  )
              ) t1
         group by t1.stu_id
         having count(t1.course_id) = 3
     ) t2
join student_info s on t2.stu_id = s.stu_id;

输出:

stu_id       stu_name
006          陈道明
008          吴京
011          潘长江
012          杨紫
019          邓紫棋

第四章 复杂查询

4.1 子查询

4.1.1 查询所有课程成绩均小于60分的学生的学号、姓名

答案一:

select stu_id
from score_info
where score < 60
  and stu_id not in (select stu_id from score_info where score >= 60)

答案二:

select s.stu_id,s.stu_name from (
                  select stu_id, sum(if(score >= 60, 1, 0)) flag
                  from score_info
                  group by stu_id
                  having flag = 0
                  )t1 join student_info s on s.stu_id=t1.stu_id;

结果:

s.stu_id  s.stu_name
008          吴京
017          范冰冰

第五章 多表查询

5.1 表联结

5.1.1 查询有两门以上的课程不及格的同学的学号及其平均成绩

① 先找出有两门以上不及格的学生名单,按照学生分组,过滤组内成绩低于60的并进行count,count>=2。

② 接着做出一张表查询学生的平均成绩并和上一个子查询中的学生学号进行连接

select t1.stu_id, t1.avg_score
from (
         select stu_id, sum(if(score < 60, 1, 0)) cn, avg(score) avg_score
         from score_info
         group by stu_id
         having cn >= 2) t1
         join student_info s on t1.stu_id = s.stu_id;

5.2 多表连接

5.2.6 查询学过“李体音”老师所教的所有课的同学的学号、姓名

答案一:

select stu_id, stu_name
from student_info
where stu_id in (select stu_id
                 from score_info
                 where course_id in (select course_id
                                     from course_info
                                     where tea_id in (select tea_id from teacher_info where tea_name = '李体音'))
                 group by stu_id
                 having count(*) = 2);

答案二:

select
    t1.stu_id,
    si.stu_name
from
(
    select
        stu_id
    from score_info si
    where course_id in
    (
        select
           course_id
        from course_info c
        join teacher_info t
        on c.tea_id = t.tea_id
        where tea_name='李体音'      --李体音教的所有课程
    )
    group by stu_id
    having count(*)=2       --学习所有课程的学生
)t1
left join student_info si
on t1.stu_id=si.stu_id;

结果:

s.stu_id    s.stu_name
005       唐国强
007       陈坤
009       郭德纲
Time taken: 27.16 seconds, Fetched: 3 row(s)

5.2.7 查询学过“李体音”老师所讲授的任意一门课程的学生的学号、姓名

答案一:

select stu_id,stu_name
from student_info
where stu_id in (
    select stu_id
    from score_info
    where course_id in (
        select course_id
        from course_info
        where tea_id in (select tea_id from teacher_info where tea_name = '李体音')
    ));

答案二:

select t1.stu_id,
       si.stu_name
from (
         select stu_id
         from score_info si
         where course_id in
               (
                   select course_id
                   from course_info c
                            join teacher_info t
                                 on c.tea_id = t.tea_id
                   where tea_name = '李体音'
               )
         group by stu_id
     ) t1
         left join student_info si
                   on t1.stu_id = si.stu_id;

结果:

s.stu_id    s.stu_name
001       彭于晏
002       胡歌
004       刘德华
005       唐国强
007       陈坤
009       郭德纲
010       于谦
013       蒋欣
014       赵丽颖
015       刘亦菲
016       周冬雨
017       范冰冰
018       李冰冰
020       宋丹丹

5.2.8 查询没学过"李体音"老师讲授的任一门课程的学生姓名

答案一:

select stu_id,stu_name
from student_info
where stu_id not in (
    select stu_id
    from score_info
    where course_id in (
        select course_id
        from course_info
        where tea_id in (select tea_id from teacher_info where tea_name = '李体音')
    ));

答案二:

select
    stu_id,
    stu_name
from student_info
where stu_id not in
(
    select
        stu_id
    from score_info si
    where course_id in
    (
        select
           course_id
        from course_info c
        join teacher_info t
        on c.tea_id = t.tea_id
        where tea_name='李体音'
    )
    group by stu_id
);

结果:

stu_id  stu_name
003     周杰伦
006     陈道明
008     吴京
011     潘长江
012     杨紫
019     邓紫棋

5.2.9 查询至少有一门课与学号为“001”的学生所学课程相同的学生的学号和姓名

答案一:

select stu_id, stu_name
from student_info
where stu_id in (select stu_id
                 from score_info
                 where course_id in (select course_id from score_info where stu_id = '001'))
  and stu_id != '001';

答案二:

select si.stu_id,
       si.stu_name
from score_info sc
         join student_info si
              on sc.stu_id = si.stu_id
where sc.course_id in
      (
          select course_id
          from score_info
          where stu_id = '001' --001的课程
      )
  and sc.stu_id <> '001' --排除001学生
group by si.stu_id, si.stu_name;

结果:文章来源地址https://www.toymoban.com/news/detail-495152.html

s1.stu_id     s2.stu_name
002          胡歌
004          刘德华
005          唐国强
006          陈道明
007          陈坤
008          吴京
009          郭德纲
010          于谦
011          潘长江
012          杨紫
013          蒋欣
014          赵丽颖
015          刘亦菲
016          周冬雨
017          范冰冰
018          李冰冰
019          邓紫棋
020          宋丹丹

到了这里,关于Hive SQL题库(初级)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 老杜Java零基础视频笔记-第一章 学前准备(动力节点)

    为什么使用截图工具 在听课的过程中,有的时候老师操作的比较快,通过截图的方式将老师的操作保存下来,以便后期的操作。另外截图之后的图片也可以用于笔记的记录,在笔记当中最好采用图文并茂的方式,这样更加利于知识的回顾。 使用哪个截图工具 snipaste 免费 免安

    2024年02月07日
    浏览(59)
  • 基本环境准备(第一节)

    基本环境准备(第一节) 2023年8月9日 16:37   1.安装Node.js; Windows 上安装 Node.js 你可以采用以下两种方式来安装。 1、Windows 安装包(.msi) 本文实例以 v0.10.26 版本为例,其他版本类似, 安装步骤:   步骤 1 : 双击下载后的安装包 v0.10.26,如下所示: 步骤 2 : 点击以上的Run(运行),将

    2024年02月13日
    浏览(41)
  • 大数据技术之Hive SQL题库-中级

    1)表结构 user_id(用户id) gender(性别) birthday(生日) 101 男 1990-01-01 102 女 1991-02-01 103 女 1992-03-01 104 男 1993-04-01 2)建表语句 hive 3)数据装载 hive 1)表结构 sku_id (商品id) name (商品名称) category_id (分类id) from_date (上架日期) price (商品价格) 1 xiaomi 10 1 2020-01-01 2000 6 洗碗机 2 2020-02-01

    2024年02月11日
    浏览(48)
  • 第一章 SQL Server 数据库部署

     个人简介:云计算网络运维专业人员,了解运维知识,掌握TCP/IP协议,每天分享网络运维知识与技能。 座右铭:海不辞水,故能成其大;山不辞石,故能成其高。 个人主页: 小李会科技的主页   目录 一 数据库介绍 (1)使用数据库的必要性 (2)数据库的基本概念  1.数

    2024年02月07日
    浏览(45)
  • Flink第一章:环境搭建

    Flink第一章:环境搭建 Flink也是现在现在大数据技术中火爆的一门,反正大数据的热门技术学的也差不多了,啃完Flink基本的大数据技术就差不多哦学完了. 略 这里说明一下我选择的环境. java8 scala2.12 flink采用最新的1.17 请大家根据自己的环境更换版本 注 :这里使用DataSet对数据进行

    2024年02月04日
    浏览(46)
  • GoNote第一章 环境搭建

    Go 是一个开源的编程语言,它能让构造简单、可靠且高效的软件变得容易。 Go是从2007年末由Robert Griesemer, Rob Pike, Ken Thompson主持开发,后来还加入了Ian Lance Taylor, Russ Cox等人, 并最终于2009年11月开源,在2012年早些时候发布了Go 1稳定版本 。现在Go的开发已经是完全开放的,并且

    2023年04月22日
    浏览(45)
  • RCP系列-第一章 环境安装

    第一章 Matlab安装 提示:这里可以添加本文要记录的大概内容: 例如:随着人工智能的不断发展,机器学习这门技术也越来越重要,很多人都开启了学习机器学习,本文就介绍了机器学习的基础内容。 提示:以下是本篇文章正文内容,下面案例可供参考 名称 Value Matlab云盘链

    2024年02月09日
    浏览(39)
  • 《操作系统真象还原》第一章 部署工作环境

    配合视频阅读体验更佳!https://www.bilibili.com/video/BV1kg4y1V7TV/?pop_share=1vd_source=701807c4f8684b13e922d0a8b116af31 环境vmware + deepin-desktop-community-20.8-amd64 (已在纯净ubuntu 22.04上验证过此教程,完全可行!只是需要安装vim 命令: sudo apt install vim ) 先安装其他需要的东西: sudo apt install bui

    2024年02月08日
    浏览(60)
  • 第一章 Linux基础及Linux环境搭建(保姆级别)

    第一章 Linux基础及Linux环境的搭建(保姆级别) 一、Linux简介 1、什么是Linux? ​ 一款 免费开源流行 的操作系统。 2、Linux为什么流行? 1)Windows以用户的体验很好而流行 2)Linux流行主要是因为稳定而流行 Linux一般用于企业中中的服务器 Linux用来做服务器操作系统使用 3、L

    2023年04月16日
    浏览(36)
  • 第一篇 香橙派刷机和开发环境准备(ubuntu20.04版)

    目录 一、香橙派刷ubuntu系统和SSH登录 1.Ubuntu有趣的Codename 2.刷机步骤(ubuntu20.04) 🔖格式化TF卡 🔖烧写系统到TF卡 🔖调试串口登陆系统 🔖SSH登陆系统  二、开发环境准备 1.香橙派ubuntu20.04换源 2.VNC远程连接香橙派 (1)安装VNC服务 (2)VNC连接香橙派 3.安装wiringPi (1)安装

    2023年04月21日
    浏览(46)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包