[大数据]hive

步骤2： 项目指标分析
2.1查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩（答案1）

【代码】

select student.s_id,student.s_name,tmp.avgscore from student
join (
select score.s_id,round(avg(score.s_score),1)as avgscore
from score group by s_id)as tmp
on tmp.avgscore>=60
where student.s_id = tmp.s_id;

【运行结果截屏】

2.2查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩（答案2）

【代码】

select student.s_id,student.s_name,round(avg (score.s_score),1) as avgscore from student
join score on student.s_id = score.s_id
group by student.s_id,student.s_name
having avg (score.s_score) >= 60;

【运行结果截屏】

2.3查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（答案1）

【代码】

select student.s_id,student.s_name,tmp.avgScore from student
join (
select score.s_id,round(avg(score.s_score),1)as avgScore from score group by s_id)as tmp
on tmp.avgScore < 60
where student.s_id=tmp.s_id
union all
select s2.s_id,s2.s_name,0 as avgScore from student s2
where s2.s_id not in
(select distinct sc2.s_id from score sc2);

【运行结果截屏】

2.4查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（答案2）

【代码】

select score.s_id,student.s_name,round(avg (score.s_score),1) as avgScore from student
inner join score on student.s_id=score.s_id
group by score.s_id,student.s_name
having avg (score.s_score) < 60
union all
select s2.s_id,s2.s_name,0 as avgScore from student s2
where s2.s_id not in
(select distinct sc2.s_id from score sc2);

【运行结果截屏】

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

【代码】

select student.s_id,student.s_name,(count(score.c_id) )as total_count,sum(score.s_score)as total_score
from student
left join score on student.s_id=score.s_id
group by student.s_id,student.s_name ;

【运行结果截屏】

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

【代码】

select t_name,count(1) from teacher where t_name like ‘李%’ group by t_name;

【运行结果截屏】

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

【代码】

select student.* from student
join score on student.s_id =score.s_id
join course on course.c_id=score.c_id
join teacher on course.t_id=teacher.t_id and t_name=‘张三’;

【运行结果截屏】

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

【代码】

select student.* from student
left join (select s_id from score
join course on course.c_id=score.c_id
join teacher on course.t_id=teacher.t_id and t_name=‘张三’)tmp
on student.s_id =tmp.s_id
where tmp.s_id is null;

【运行结果截屏】

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

【代码】

select * from student
join (select s_id from score where c_id =1 )tmp1
on student.s_id=tmp1.s_id
join (select s_id from score where c_id =2 )tmp2
on student.s_id=tmp2.s_id;

【运行结果截屏】

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

【代码】

select student.* from student
join (select s_id from score where c_id =1 )tmp1
on student.s_id=tmp1.s_id
left join (select s_id from score where c_id =2 )tmp2
on student.s_id =tmp2.s_id
where tmp2.s_id is null;

【运行结果截屏】