1、前提准备，在自己的hive中创建这些表：
课程表名： kc
表字段：kcid,kc,tcid
成绩表名： sc
表字段：sid,kcid,score
学生信息表名： student
表字段：sid,sname,sage,ssex
教师表名： teacher
表字段：tcid,tcname
2、相关信息
2.1、 sc
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
2.2、 student
01,zhaolei,nan
02,qiandian,nan
03,sunfeng,nan
04,liyun, nan
05,zhoumei,nv
06,wulan,nv
07,zhengzhu,nv
08,wangju,nv
2.3、teacher
01,zhanglaoshi
02,lilaoshi
03,wanglaoshi
2.4、 kc
01,语文,02
02,数学,01
03,英语,03
3、习题讲解
3.1、习题1.查询每门课授课教师姓名
select kc.kc,teacher.tcname from teacher join kc on teacher.tcid=kc.tcid;
3.2、习题2查询"01"课程比"02“课程成绩高的学生的信息及课程分数(注意起别名，才能够区别表并进行三表联立)
答案1：select student.* ,sc1.score from sc sc1 join student on sc1.sid=student.sid and sc1.kcid='01'
join sc on sc.sid=student.sid and sc.kcid='02' where sc1.score>sc.score;
答案2：select s.*,s1.score 1score ,s2.score 2score from student s, sc s1, sc s2 where s.sid = s1.sid and s.sid= s2.sid and s1.kcid ='01' and s2.kcid = '02' and s1.score > s2.score;
3.3、习题3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩(注意group by 后面的字段和前面查询的字段相同、group by 和having知识点)
select sc.sid,student.sname,avg(sc.score) from sc join student on sc.sid=student.sid group by sc.sid,student.sname having avg(sc.score)>=60;
3.4、习题4.查询平均成绩小于60分同学的学生编号姓名和平均成绩（注意group by 后面的字段和前面查询的字段相同、group by 和having知识点）
select sc.sid,student.sname,avg(sc.score) from sc join student on sc.sid=student.sid group by sc.sid,student.sname having avg(sc.score)<60;
3.5、习题5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(group by 和order by 相关知识点以及left左连接，如果有null值可以使用coalesce函数)
答案1：select student.sid,student.sname,count(sc.kcid),sum(sc.score) from student left join sc on student.sid=sc.sid group by student.sid, student.sname order by student.sid,student.sname;
答案2：SELECT student.sid, student.sname, COUNT(sc.kcid), COALESCE(SUM(sc.score), 0)
FROM student
LEFT JOIN sc ON student.sid = sc.sid
GROUP BY student.sid, student.sname
ORDER BY student.sid, student.sname;
3.6、习题6.查询“李"姓老师的数量（注意匹配老师姓名是‘李%’）
select count(teacher.tcname) from teacher where tcname like '李%';
3.7、习题7.查询学过“zhang"老师授课的同学的信息(四张表连立，现有student表和sc表连立,然后再和kc表连立起来,最后再和teacher表联立得出最终结果)
select student.* ,sc.kcid, sc.score from sc join student on sc.sid=student.sid join kc on kc.kcid=sc.kcid
join teacher on kc.tcid=teacher.tcid where teacher.tcname like '张%';
3.8、习题8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息习题(注意在查询的时候给表起别名，方便多表联立查询)
select student.*
from student
join sc sc1 on student.sid=sc1.sid and sc1.kcid='01'
join sc on sc1.sid=sc.sid and sc.kcid='02';
3.9、习题9.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息习题(和前面那道题不同的是，最后加一个where筛选语句)
select student.* ,sc.kcid
from student
join sc sc1 on student.sid=sc1.sid and sc1.kcid='01'
left join sc on sc1.sid=sc.sid and sc.kcid = '02' where sc1.kcid = '01' and sc.kcid is null;
3.10、习题10.检索"01"课程分数小于60，按分数降序排列的学生信息
select student.* ,sc1.score from student join sc sc1 on student.sid=sc1.sid and sc1.kcid='01'
and sc1.score<60 order by sc1.score desc;
3.11、习题11.查询没有学全所有课程的同学的信息
答案1：
select student.* from student
left join sc on student.sid=sc.sid
group by student.sid ,student.sname ,student.sage,student.ssex
having count(sc.kcid)<3;
答案2：
SELECT st.* FROM student st LEFT JOIN sc sc1 ON sc1.sid=st.sid AND sc1.kcid='01' LEFT JOIN sc sc2 ON sc2.sid=st.sid AND sc2.kcid='02' LEFT JOIN sc sc3 ON sc3.sid=st.sid AND sc3.kcid='03' WHERE sc1.score IS NULL OR sc2.score IS NULL OR sc3.score IS NULL;
3.12、习题12.查询至少有一门课与学号为‘01’ 的同学所学相同的同学的信息
答案1：
select * from student
where student.sid in
(select sc1.sid from sc sc1 where kcid in
(select sc.kcid from sc where sc.sid="01") and student.sid!='01') ;
distinct：去重作用
答案2
select distinct student.* from student
stu1 where student.sid on stu1.sid=sc.sid in
(select sc1.sid from sc sc1 where kcid in
(select sc.kcid from sc where sc.sid="01") and student.sid!='01') ;
3.13、习题13.查询和‘01’号的同学学习的 课程完全相同的其他同学的信息(执行顺序)
select student.* from （select sc2.sid from sc sc1
join sc sc2 on sc1.kcid=sc2.kcid
join student on student.sid=sc1.sid
where sc1.sid=1 and sc2.sid!=1
group by
sc2.sid
having count(sc2.kcid)=3)a join student on student.sid=a.sid ;
3.14、习题14.查询没学过‘张三’老师讲授的任一门课程的学生姓名(作业，周一晚上发！！！！)
答案2：
select * from student where student.sid not in
(select distinct sc.sid from sc inner join
(select kc.kcid from kc inner join teacher t on kc.tcid = t.tcid where t.tcname = '张老师') t1
on sc.kcid = t1.kcid );文章来源：https://www.toymoban.com/news/detail-480300.html
3.15、习题15.查询两门及其以上不合格课程的同学的学号，姓名及其平均成绩
研究后再发...文章来源地址https://www.toymoban.com/news/detail-480300.html
附录：有什么不对的地方请指正，或者有更好的解法。不懂的也可以在评论区交流哦。
到了这里，关于Hive，相关任务查询作业的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
