Hive 基础50题 【1-10题】

1 创建表

create table rosh_test.student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';
create table rosh_test.course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';
create table rosh_test.teacher(t_id string,t_name string) row format delimited fields terminated by '\t';
create table rosh_test.score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';

2 数据准备

course:

01,语文,02
02,数学,01
03,英语,03

score:

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

student:

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,女

teacher:

01,张三
02,李四
03,王五

3 加载数据

load data local inpath '/usr/local/rosh/data/student.txt' into table rosh_test.student;
load data local inpath '/usr/local/rosh/data/course.txt' into table rosh_test.course;
load data local inpath '/usr/local/rosh/data/teacher.txt' into table rosh_test.teacher;
load data local inpath '/usr/local/rosh/data/score.txt' into table rosh_test.score;

4 1-10题

4.1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数

select
  a.*,
  b.c_id,
  b.s_score,
  c.c_id,
  c.s_score
from rosh_test.student a
left join  rosh_test.score b on b.s_id=a.s_id and b.c_id='01'
left join  rosh_test.score c on c.s_id=a.s_id and c.c_id='02'
where
    b.s_score>c.s_score

4.2 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select a.s_id,
       a.s_name,
       t1.avg_score
from rosh_test.student a
         inner join (select s_id,
                            round(avg(s_score)) avg_score
                     from rosh_test.score
                     group by s_id
                     having round(avg(s_score)) >= 60) t1 on a.s_id = t1.s_id;

4.3 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select a.s_id,
       a.s_name,
       count(b.s_id) count_course,
       case
           when sum(b.s_score) is null then '0'
           else sum(b.s_score)
           end as    sum_score
from rosh_test.student a
         left join rosh_test.score b on a.s_id = b.s_id
group by a.s_id, a.s_name;

4.4 查询"李"姓老师的数量

-- 查询"李"姓老师的数量:
select a.t_name,
       count(1) count
from rosh_test.teacher a where a.t_name like '李%' group by a.t_name

4.5 查询学过"张三"老师授课的同学的信息

---查询学过"张三"老师授课的同学的信息
select
    a.*
from
    rosh_test.student a
inner join rosh_test.score  b on a.s_id=b.s_id
inner join rosh_test.course c on b.c_id=c.c_id
inner join rosh_test.teacher d on d.t_id=c.t_id
where
    d.t_name='张三';

4.6 查询没学过"张三"老师授课的同学的信息

select *
from rosh_test.student a
where a.s_id not in (select distinct (c.s_id)
                     from rosh_test.teacher a
                              inner join rosh_test.course b on a.t_id = b.t_id
                              inner join rosh_test.score c on b.c_id = c.c_id
                     where t_name == '张三');

4.7 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select
    a.*
from rosh_test.student a
         inner join (select * from rosh_test.score where c_id = "01") b on a.s_id = b.s_id
         inner join (select * from rosh_test.score where c_id = "02") c on a.s_id = c.s_id;

4.8 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息:

select
    a.*
from
    rosh_test.student a inner join  (select * from rosh_test.score where c_id="01") b on a.s_id=b.s_id
                        left join (select * from rosh_test.score where c_id="02") c on a.s_id=c.s_id
where
    c.c_id is null

4.9 查询没有学全所有课程的同学的信息

select *
from rosh_test.student
where s_id in (select s_id
               from rosh_test.score
               group by s_id
               having count(s_id) < (select count(1) sum_course
                                     from rosh_test.course));

4.10 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

--查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select *
from rosh_test.student
where s_id in
      (select distinct(s_id)
       from rosh_test.score
       where c_id in (select c_id from rosh_test.score where s_id = "01") and s_id != '01');