HQL练习题及答案-有了它妈妈再也不用担心我的SQL面试了

本文通过简单易懂的业务场景，旨在提高大家的 SQL 水平。把文中所有的 SQL 全理解了，工作中遇到的各种 SQL 场景和面试中遇到的各种 SQL 问题都能灵活应对。文中所有的 SQL 都支持 Hive 语法，学会了 Hive 的 SQL，那么 MySQL 的 SQL 题也都可以 cover 住，下面的题目难度从简单到困难的都有。笔者在之前练习 SQL 过程中，发现了网上一些比较好的 SQL 题目，但是苦于好多博客提供的 SQL 可读性、规范性、执行效率并不高，因此才有了今天这篇博文，后续如果发现有可优化的的 SQL，笔者会持续更新本博文。

数据表介绍

学生表

create table study.student (
     student_id   string  -- 学生编号
    ,name         string  -- 学生姓名
    ,birthday     string  -- 学生生日
    ,sex          string  -- 学生性别
)
stored as parquet
tblproperties("orc.compress"="snappy");

教师表

create table study.teacher (
     teacher_id   string  -- 教师编号
    ,name         string  -- 教师姓名
)
stored as parquet
tblproperties("orc.compress"="snappy");

课程表

create table study.course (
     course_id    string  -- 课程编号
    ,name         string  -- 课程名
    ,teacher_id   string  -- 课程对应的教师编号
)
stored as parquet
tblproperties("orc.compress"="snappy");

成绩表

create table study.score (
     student_id   string  -- 学生编号
    ,course_id    string  -- 课程编号
    ,score        int     -- 对应的成绩
)
stored as parquet
tblproperties("orc.compress"="snappy");

答题时请注意：

• 这里认为课程可能是选修课，学生不一定学了所有课程
• 成绩表里的课程都应该在课程表里存在，而且每门课程都应该有对应的教师，且在教师表里可以找到
• 成绩表里的学生也应该在学生表里存在
• 下面的导入数据也是随机写的，读者也可以自己随机制造生成数据导入

导入数据

学生表

insert overwrite table study.student VALUES
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-12-20' , '男'),
('04' , '李云' , '1990-12-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-01-01' , '女'),
('07' , '郑竹' , '1989-01-01' , '女'),
('09' , '张三' , '2017-12-20' , '女'),
('10' , '李四' , '2017-12-25' , '女'),
('11' , '李四' , '2012-06-06' , '女'),
('12' , '赵六' , '2013-06-13' , '女'),
('13' , '孙七' , '2014-06-01' , '女');

教师表

insert overwrite table study.teacher VALUES
('01' , '张三'),
('02' , '李四'),
('03' , '王五');

课程表

insert overwrite table study.course VALUES
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');

成绩表

insert overwrite table study.score VALUES
('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. 查询所有教师的全部信息（教师编号和姓名）

2. 输出所有学生中男生的全部信息

3. 查询所有学生中男生的全部信息，按照生日排降序

4. 查询所有学生的全部信息，先按照性别排序，再按照生日排降序

5. 求出学生总数

6. 查询学生中男生、女生人数