Hive sql练手

#在hive中创建表
create table student(sno int,sname string,ssex string,sage int,sdept string) row format delimited fields terminated by "," stored as textfile;
create table course(cno int,cname string) row format delimited fields terminated by "," stored as textfile;

create table sc(sno int,sco int ,grade int) row format delimited fields terminated by "," stored as textfile;

#数据准备

https://pan.baidu.com/s/1ek6UcQ

#将数据从hdfs中导入到hive表中
load data inpath "/data/student.txt" overwrite into table student
load data inpath "/data/course.txt" overwrite into table course;
load data inpath "/data/sc.txt" overwrite into table sc;
##########hql查询#############
(1)查询全体学生的学号与姓名
select sno ,sname from student;
(2)查询选修了课程的学生姓名(+课程数量)
select st.sname,count(*) from student st,sc where sc.sno=st.sno group by st.sname;
select st.sname from student st,sc where sc.sno=st.sno group by st.sname;
select distinct st.sname from student inner join sc on(sc.sno=st.sno);
(2)查询学生的总人数
select count(1) from student;
(4)计算1号课程的学生平均成绩
select avg(grade) from sc where sco = 1;
(5)查询各科成绩平均分(保留2位小数)
select co.cname,round(avg(sc.grade),2) from course co left join sc on (co.cno = sc.sco) group by co.cname;
(6)查询选修1号课程的学生最高分数
select max(sc.grade) from sc where sc.sco = 1;
(7)求各个课程号(或课程名称)及相应的选课人数
select sc.sco,count(sc.sno) from sc  group by sc.sco;
select sc.sco,course.cname,count(sc.sno) from sc left join course on(sc.sco = course.cno)  group by sc.sco , course.cname;
(8)***查询选修了3门以上的课程的学生学号(或者姓名)
select sc.sno,count(sc.sco) from sc group by sc.sno having count(sc.sco)>3;#分组后聚合
select sc.sno,student.sname,count(sc.sco) from sc left join student on (sc.sno=student.sno) group by sc.sno,student.sname having count(sc.sco)>3;
(9)查询学生信息，结果按学号全局有序
select*from student order by sno asc;
(10)***查询学生信息，结果区分性别按年龄有序
select*from student order by sage desc,ssex asc;
(11)查询每个学生及其选修课程的情况
select*from student,sc where student.sno = sc.sno; 
(12)查询学生的得分情况
select st.sname,st.sno,co.cname ,sc.grade from student st left join sc on(sc.sno = st.sno) left join course co on(co.cno = sc.sco);
(13)查询选修2号课程且成绩在90分以上的所有学生
select st.sname,st.sno,co.cname,sc.grade from student st left join sc on(sc.sno = st.sno) left join course co on(co.cno = sc.sco) where co.cno=2 and sc.grade>=90; 
(14)查询所有学生的信息，如果在成绩表中有成绩，则输出成绩表中的课程号
select st.*,sc.sco,sc.grade from student st left join sc on(sc.sno=st.sno) left join course co on(co.cno = sc.sco);
select st.*,sc.sco,sc.grade from student st left join sc on(sc.sno=st.sno) where sc.grade is not null;
(15)查询与“刘晨”在同一个系学习的学生
select st.* from student st where st.sdept in (select sdept from student where sname ="刘晨" );
select st.* from student st where st.sdept in (select sdept from student where sname ="刘晨" ) and st.sname != "刘晨";