1.正确建表,导入数据(三张表,三份数据),并验证是否正确
create table course(cid int ,cname string) row format delimited fields terminated by ‘,’;
load data local inpath ‘/home/hadoop/file/hive/course.csv’ into table course;
select * from course;
create table student(sid int ,sname string,grade int ,class int) row format delimited fields terminated by ‘,’;
load data local inpath ‘/home/hadoop/file/hive/student.csv’ into table student;
select * from student;
create table score(sid int ,cid int ,score int) row format delimited fields terminated by ‘,’;
load data local inpath ‘/home/hadoop/file/hive/score.csv’ into table score;
select * from score;
2.查询所有学生的成绩信息:学生姓名、课程名、课程成绩。
select sname,cname,score from student,course,score where student.sid=score.sid and score.cid=course.cid;
3.查询编号为10的课程比编号为20的课程成绩高的学生的编号及课程分数
select distinct x.sid,x.score,y.score from score x ,score y where (x.cid=10 and y.cid=20) and (x.score>y.score) and x.sid=y.sid;
4.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select x.sid,y.sname,avg(x.score) from score x,student y where x.sid=y.sid
group by x.sid,y.sname having avg (x.score)>=60 ;
5.查询并创建表格:从数据中获取学生的姓名和各科成绩,并将学生姓名,各科成绩的数组 形式数据保存在temp表格中。
结果形如:Eric [53,29,33,27,22,43,55]
insert overwrite local directory ‘/home/hadoop/temp’
select sname,collect_list(score) from student,score where student.sid=score.sid group by sname;
6.查询编号为10的课程的平均分
select avg(score) from score where cid=10 group by cid;
7.查询每门课程的平均分(课程编号,课程名,平均分)
select score.cid,course.cname,avg(score.score) from score ,course where course.cid=score.cid group by score.cid,course.cname;
8.按照课程对学生的成绩进行顺序排序(课程编号,学生编号,成绩,排名)
select cid,sid,score,rank() over (partition by cid order by score desc ) rank from score ;
9.查询每门课程第一名(所有第一名)(课程名称,学生姓名,成绩)
Select * from (select course.cname,student.sname,score.score,rank() over(partition by score.cid order by score.score desc) seq from student,score,course where student.sid=score.sid and course.cid=score.cid) tab where tab.seq=1 ;
10.统计每门课程不及格的学生人数(课程编号,不及格人数)
select cid,count(sid) from score where score.score<60 group by cid ;
11.统计每门课程不及格的学生的姓名(课程编号,不及格学生姓名集合)形如: 20 [“Eric”,“Joy”]
select cid,collect_list(sname) from score,student where score.sid=student.sid and score<60 group by cid;
12.查询两门及其以上不及格课程的学生的学号,姓名
Select student.sid,student.sname from student , score where student.sid=score.sid and score.score<60 group by student.sid,student.sname having count(score.score)>=2;
13.查询学生的总成绩并进行排名(姓名 总分 名次)
Select sname,sum(score),rank() over(order by sum(score) desc) from student,score where student.sid=score.sid group by student.sid,student.sname;
14.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 (sid,cid,score,average)
Select sid,cid,score,avg(score) over (partition by sid ) average from score order by average desc;
15.查询各科成绩最高分、最低分和平均分(课程编号,最高分,最低分,平均分)
Select cid,max(score),min(score),avg(score) from score group by cid;