Hive，相关任务查询作业

库库新

已于 2023-05-27 14:36:48 修改

阅读量736

点赞数 6

文章标签：数据库

于 2023-05-17 21:37:21 首次发布

本文链接：https://blog.csdn.net/kkx_lhmd/article/details/130735276

版权

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;