Hive练习

Hive50道练习题

一、建库建表，插入数据

– 建库
create database if not exists test;
use test;
– 建表
– 课程表
create table if not exists course(course_id int,course_name string,teacher_id int);
– 分数表
create table if not exists score(student_id int,course_id int,score int);
– 学生表
create table if not exists student(student_id int,student_name string,student_birth string,student_sex string);
– 教师表
create table if not exists teacher(teacher_id int,
teacher_name string
);
– 插入数据
– 课程表
insert into table course values
(01,‘语文’,02),
(02,‘数学’,01),
(03,‘英语’,03);
– 分数表
insert into table 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);
– 学生表
insert into table student values
(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’,‘女’);
– 教师表
insert into table teacher values (01,‘张三’),(02,‘李四’),(03,‘王五’);

二、练习

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
%hive
with
s1 as (select stu.,sc.score score from student stu join
score sc on stu.student_id=sc.student_id and course_id=1 ),
s2 as (select stu.,sc.score score from student stu join
score sc on stu.student_id=sc.student_id and course_id=2)
select a.*,b.score from s1 a left join s2 b on a.student_id=b.student_id where a.score>b.score or b.score is null

2.查询"01"课程比"02"课程成绩低的学生的信息及课程分数:
%hive
with
s1 as (select stu.,sc.score score from student stu join
score sc on stu.student_id=sc.student_id and course_id=1 ),
s2 as (select stu.,sc.score score from student stu join
score sc on stu.student_id=sc.student_id and course_id=2)
select b.*,a.score from s1 a right join s2 b on a.student_id=b.student_id where a.score<b.score or a.score is null

3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:
%hive
SELECT stu.name ,s1.bb FROM(
SELECT stu_id,AVG(score) bb FROM score GROUP BY stu_id )s1
JOIN student stu
ON stu.id=s1.stu_id
WHERE s1.bb>60

4。查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:
– (包括有