Hive QL 50道挑战题第四部分详解

一.数据准备

---------------------表名和字段-----------------
--学生表
--Student(s_id,s_name,s_birth,s_sex) 学生编号,学生姓名, 出生年月,学生性别
--课程表
--Course(c_id,c_name,t_id) 课程编号, 课程名称, 教师编号
--教师表
--Teacher(t_id,t_name) 教师编号,教师姓名
--成绩表
--Score(s_id,c_id,s_score) 学生编号,课程编号,分数
​
​
create database if not exists exercise;
use exercise;
​
---------------------建表------------------
--学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20),
`s_name` varchar(20),
`s_birth` varchar(20),
`s_sex` varchar(10)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath './student.csv' into table student;
​
--课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` varchar(20),
  `c_name` varchar(20),
  `t_id` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'course.csv' into table course;
​
--教师表
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
`t_id` varchar(20),
`t_name` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'teacher.csv' into table teacher;
​
--成绩表
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`(
`s_id` varchar(20),
`c_id` varchar(20),
`s_score` int
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'score.csv' into table score;
​
​
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,女
09,张飞,1990-9-25,男
10,刘备,1990-01-25,男
11,关羽,1990-01-25,男
​
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
09,01,85
09,02,80
09,04,99
10,01,80
10,02,56
10,03,30
10,04,90
11,04,90
​
​
01,语文,02
02,数学,01
03,英语,03
04,物理,04
​
​
01,张三
02,李四
03,王五
04,赵六

二.练习题

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

my语句1：
select *
from student 
where s_birth like '1990%';
​
官方语句2：
select * from student where substr(s_birth,1,4) ='1990';
​
result:
01      赵雷    1990-01-01      男
02      钱电    1990-12-21      男
03      孙风    1990-05-20      男
04      李云    1990-08-06      男
08      王菊    1990-01-20      女
09      张飞    1990-9-25       男
10      刘备    1990-01-25      男
11      关羽    1990-01-25      男

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

my语句1：
select c_id,avg(s_score) avgnum
from score
group by c_id 
order by avgnum desc,c_id;
​
result:
04      93.0
02      71.5
01      69.0
03      63.0
​
官方语句2：
select * from(
select 
c_id,
round(avg(s_score),2) as avgnum
from score 
group by c_id
) t1
order by avgnum desc,t1.c_id asc;
result:
04      93.0
02      71.5
01      69.0
03      63.0
​

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

select a.s_id,a.s_name,avg(b.s_score)
from student a join score b on a.s_id = b.s_id
group by a.s_id,a.s_name
having avg(b.s_score)>=85;
​
rusult:
01      赵雷    89.66666666666667
07      郑竹    93.5
09      张飞    88.0
11      关羽    90.0

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

分析：
course:c_name   b
score: s_score c
student:s_name a
​
​
select a.s_name,c_name,s_score
from student a join score b on a.s_id = b.s_id 
join course c on b.c_id = c.c_id 
where c.c_name = '数学' and s_score<60;
​
result:
李云    数学    30
刘备    数学    56

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

分析：
course:c_id c
score:s_core
student: group by s_id,c_id
​
select a.s_id,a.s_name,c.c_id,c.c_name,s_score
from student a join score b on a.s_id = b.s_id 
join course c on b.c_id = c.c_id
group by a.s_id,a.s_name,c.c_id,c.c_name,s_score;
​
result:
01      赵雷    01      语文    80
01      赵雷    02      数学    90
01      赵雷    03      英语    99
02      钱电    02      数学    60
02      钱电    01      语文    70
02      钱电    03      英语    80
03      孙风    01      语文    80
03      孙风    02      数学    80
03      孙风    03      英语    80
04      李云    03      英语    20
04      李云    02      数学    30
04      李云    01      语文    50
05      周梅    01      语文    76
05      周梅    02      数学    87
06      吴兰    01      语文    31
06      吴兰    03      英语    34
07      郑竹    02      数学    89
07      郑竹    03      英语    98
09      张飞    02      数学    80
09      张飞    01      语文    85
09      张飞    04      物理    99
10      刘备    03      英语    30
10      刘备    02      数学    56
10      刘备    01      语文    80
10      刘备    04      物理    90
11      关羽    04      物理    90

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

分析：
score: s_score c_id s_id b
course: c_name c_id c 
student:s_name  a 
​
select a.s_name,c.c_name,b.s_score
from student a join score b on a.s_id = b.s_id
join course c on b.c_id = c.c_id 
where b.s_score >= 70;
​
rusult:
赵雷    语文    80
赵雷    数学    90
赵雷    英语    99
钱电    语文    70
钱电    英语    80
孙风    语文    80
孙风    数学    80
孙风    英语    80
周梅    语文    76
周梅    数学    87
郑竹    数学    89
郑竹    英语    98
张飞    语文    85
张飞    数学    80
张飞    物理    99
刘备    语文    80
刘备    物理    90
关羽    物理    90

37、查询不及格的课程

分析：
course:c_name
score: s_score<60
student: s_id group by s_id,c_id
 
select a.s_id,a.s_name,b.c_id,b.s_score
from student a join score b on a.s_id = b.s_id
where  b.s_score < 60 
group by a.s_id,a.s_name,b.c_id,b.s_score;
​
result:
04      李云    01      50
04      李云    02      30
04      李云    03      20
06      吴兰    01      31
06      吴兰    03      34
10      刘备    02      56
10      刘备    03      30

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

分析：
score: c_id,s_score
student: s_naem,s_id
​
select a.s_id,a.s_name
from student a join score b on a.s_id = b.s_id 
where b.c_id = 01 and b.s_score >= 80;
​
rusult:
01      赵雷
03      孙风
09      张飞
10      刘备

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

分析：
score：c_id 通过课程号进行分组统计学生人数
student: s_id 
​
select b.c_id,count(1)
from student a join  score b on a.s_id = b.s_id
group by b.c_id;
​
result:
01      8
02      8
03      7
04      3
​

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

分析：
teacher: t_name d 
score: max(score) || limit 1 desc b
student: *
​
select a.*,b.s_score
from student a join score b on a.s_id = b.s_id
join course c on c.c_id = b.c_id 
join teacher d on d.t_id = c.c_id
where d.t_name = '张三'
order by b.s_score desc limit 1;
​
result：
09      张飞    1990-9-25       男      85
​