七十二、Hive——hive练习50条

对于这类问题，首先是建表、导入数据，然后是写sql语句，难到不难，就是题目量有点大
create database  if not exists  school;
use school;
drop table course;

一、建表并逐个导入数据
1.course
create table  if not exists  course(
    sub_no  string,
    sub_name  string,
    teacher_id  string
)
row format delimited fields terminated by '\t';

load data  local  inpath '/opt/tmp/school/course.txt' into table  course;

2.score
create table if not exists  score(
    stu_id string,
    sub_no  string,
    result  double
)
row format delimited fields terminated by '\t';
load data  local  inpath '/opt/tmp/school/score.txt' into table  score;

3.student
create table if not exists student(
    stu_id string,
    stu_name string,
    birthday date,
    gender string
)
row format delimited fields terminated by '\t';

load data  local  inpath '/opt/tmp/school/student.txt' into table  student;

4.teacher
create  table if not exists teacher(
    teacher_id string,
    teacher_name string
)
row format delimited fields terminated by '\t';
load data  local  inpath '/opt/tmp/school/teacher.txt' into table  teacher;


二、解决综合问题
//31、查询 1990 年出生的学生名单:
create  table  student1 as
    select stu_name, substr(birthday,1,4)years from student;
select  stu_name from student1 where years='1990';


//32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时， 按课程编号升序排列:
select  sub_no ,round(avg(result) ,2)avg_score from score
group by sub_no
order by avg_score desc,sub_no;

select s.sub_no,c.sub_name,round(avg(result),2)avg_score from score as s
left join course c on s.sub_no = c.sub_no
group by s.sub_no,c.sub_name
order by avg_score desc,s.sub_no;


//33、查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩:
select stu.stu_id,stu_name,avg(result) avg_score  from student as stu
left join  score s on s.stu_id=stu.stu_id
group by  stu.stu_id,stu_name
having avg_score>=85;


//34、查询课程名称为"数学"，且分数低于 60 的学生姓名和分数:
select stu.stu_name,s.result from score as s
join student as stu on stu.stu_id=s.stu_id
join course c on s.sub_no = c.sub_no
where c.sub_name="数学"
and s.result<60;

//35、查询所有学生的课程及分数情况:
select s2.stu_name ,c.sub_name,s.result from score as s
left join  student s2 on s.stu_id = s2.stu_id
left join course c on s.sub_no = c.sub_no;


//36、查询任何一门课程成绩在 70 分以上的学生姓名、课程名称和分数
select s2.stu_name,c.sub_name,s.result from score as s
left join course c on s.sub_no = c.sub_no
left join student s2 on s.stu_id = s2.stu_id
where s.result>70;

//37、查询课程不及格的学生:
select s.stu_id,s2.stu_name,c.sub_name,s.result from score as s
left join student s2 on s.stu_id = s2.stu_id
left join course c on s.sub_no = c.sub_no
where s.result<60;

select distinct s.stu_id, s2.stu_name from score as s
left join student s2 on s.stu_id = s2.stu_id
where s.result<60;


//38、查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名:
select s.stu_id,s2.stu_name from score as s
left join student s2 on s.stu_id = s2.stu_id
where s.result>80
and  s.sub_no='01';



//39、求每门课程的学生人数:
select c.sub_name ,count(stu_id) from score as s
left join course c on s.sub_no = c.sub_no
group by c.sub_name;

//40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.stu_id,s2.stu_name, result from score as s
left join student s2 on s.stu_id = s2.stu_id
left join course c on s.sub_no = c.sub_no
left join teacher t on c.teacher_id = t.teacher_id
                where t.teacher_name="张三"
order by result desc
limit 1;

//41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select  s1.stu_id,s1.sub_no,s2.stu_id,s2.sub_no,s1.result,s2.result from score s1,score s2
where s1.sub_no != s2.sub_no  and  s1.result=s2.result;


//42、查询每门课程成绩最好的前三名:
select  sub_no,result from (
select  sub_no,result,row_number() over (partition by sub_no
    order by  result desc) as rn from score) as sc
where sc.rn <=3;

//43、统计每门课程的学生选修人数（超过 5 人的课程才统计）:
-- – 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同， 按课程号升序排列
select sub_no,count(stu_id)  cnt from score
group by  sub_no
having cnt>5
order by cnt desc,sub_no ;

//44、检索至少选修两门课程的学生学号:
select stu_id ,count(sub_no) cou from score
group by stu_id
having cou>=2;

//45、查询选修了全部课程的学生信息:
select  sc.stu_id,s.stu_name,s.birthday ,s.gender,count(sub_no) cou from score as sc
left join student s on sc.stu_id = s.stu_id
group by  sc.stu_id,s.stu_name, sc.stu_id, s.birthday, s.gender
having  cou=3;


//46、查询各学生的年龄(周岁): – 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
with
t1 as (
    select st.stu_name student_name,date_format(st.birthday,'yyyy-MM-dd') birth,
    date_format(current_date(),'yyyy-MM-dd') now,
    date_format(current_date(),'yyyy')-date_format(st.birthday,'yyyy') age from student st
)
select student_name,
       if(month(now)=month(birth),if(day(now)<day(birth),age-1,age),
       if(month(now)<month(birth),age-1,age)) age
from t1;


//47、查询本周过生日的学生:
with
t1 as (
    select st.stu_name student_name,date_format(st.birthday,'yyyy-MM-dd') birth,
date_format(current_date(),'yyyy-MM-dd') now,dayofweek(current_date()) week_day from student st
)
select student_name from t1 where month(birth)=month(now) and (day(birth)
    between day(date_sub(now,week_day-1)) and day(date_sub(now,week_day-7)));


//48、查询下周过生日的学生:
with
t1 as (
    select st.stu_name student_name,date_format(st.birthday,'yyyy-MM-dd') birth,
    date_format(current_date(),'yyyy-MM-dd') now,dayofweek(current_date()) week_day
    from student st
)
select student_name from t1 where month(birth)=month(now) and (day(birth)
    between day(date_sub(now,week_day-8)) and day(date_sub(now,week_day-14)));


//49、查询本月过生日的学生:
select stu_name from student where month(current_date())=month(birthday);

//50、查询 12 月份过生日的学生:
select stu_name from student where month(birthday)="12";

 乐于奉献共享，帮助你我他！