本文通过简单易懂的业务场景,旨在提高大家的 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),
(