首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >hive sql练习1

hive sql练习1

作者头像
用户1733462
发布2018-10-10 10:51:14
6580
发布2018-10-10 10:51:14
举报
文章被收录于专栏:数据处理数据处理

参考

DROP TABLE student;
create TEMPORARY TABLE student
(
  sid string,
  sname string,
  sage int,
  ssex string
);
INSERT into student values("01","zhaolei",19900101,"M");
INSERT into student values("02","qiandian",19901221,"M");
INSERT into student values("03","sunfeng",19900520,"M");
INSERT into student values("04","liyun",19900806,"M");
INSERT into student values("05","zhoumei",19911201,"F");
INSERT into student values("06","wulan",19920301,"F");
INSERT into student values("07","zhenzhu",19890701,"F");
INSERT into student values("08","wangju",19900120,"F");

DROP TABLE temp_course;
create TEMPORARY table temp_course(
  cid string,
  cname string,
  tid string
);
INSERT into temp_course values("01","china","02");
INSERT into temp_course values("02","math","01");
INSERT into temp_course values("03","english","03");


create TEMPORARY table temp_teacher(
  tid string,
  tname string);

INSERT into temp_teacher values("01","zhangsan");
INSERT into temp_teacher values("02","lisi");
INSERT into temp_teacher values("03","wangwu");

create TEMPORARY table sc(
  sid string,
  cid string,
  score int
);

INSERT into sc values("01","01",80);
INSERT into sc values("01","02",90);
INSERT into sc values("01","03",99);
INSERT into sc values("02","01",70);
INSERT into sc values("02","02",60);
INSERT into sc values("02","03",80);
INSERT into sc values("03","01",80);
INSERT into sc values("03","02",80);
INSERT into sc values("03","03",80);
INSERT into sc values("04","01",50);
INSERT into sc values("04","02",30);
INSERT into sc values("04","03",20);
INSERT into sc values("05","01",76);
INSERT into sc values("05","02",87);
INSERT into sc values("06","01",31);
INSERT into sc values("06","03",34);
INSERT into sc values("07","02",89);
INSERT into sc values("07","03",98);

-- 查询“某1”课程比“某2”课程成绩高的所有学生的学号
SELECT s1.sid FROM sc s1
JOIN sc s2
on s1.sid=s2.sid and s1.cid =1 and s2.cid =2
WHERE  s1.score > s2.score;

-- 查询平均成绩大于60分的同学的学号和平均成绩;
SELECT sc.sid, avg(sc.score) as avg_s FROM sc GROUP BY sc.sid HAVING avg_s > 60;

select sid,round(avg(score),1) from sc group by sid having avg(score)>60;

-- 查询所有同学的学号、姓名、选课数、总成绩
SELECT student.sid,student.sname,count(1), sum(sc.score) FROM student
JOIN sc
on student.sid=sc.sid
GROUP BY student.sid,student.sname;

select student.sid,student.sname,s.num,s.total
from
(
select sid id,count(cid) num,sum(score) total
from sc group by sid
) s
join student
on s.id=student.sid;

-- 查询姓“李”的老师的个数
SELECT count(1) FROM temp_teacher WHERE temp_teacher.tname like "li%";

-- 5.查询没学过“张三”老师课的同学的学号、姓名
SELECT DISTINCT student.sid,student.sname FROM student
JOIN sc
on student.sid=sc.sid
JOIN temp_course
on sc.cid = temp_course.cid
JOIN temp_teacher
on temp_course.tid = temp_teacher.tid and temp_teacher.tname != 'zhangshan';
-- GROUP BY student.sid,student.sname;

-- 6.查询学过数学并且也学过编号语文课程的同学的学号、姓名
-- 两种思路
SELECT student.sid,student.sname FROM student
JOIN sc
on sc.sid=student.sid
JOIN temp_course course1
on course1.cid=sc.cid and (course1.cname in ('math','china'))
GROUP BY student.sid,student.sname HAVING count(1)=2;

select * from (SELECT stu1.sid,stu1.sname FROM student stu1
JOIN sc sc1
on sc1.sid = stu1.sid
JOIN  temp_course course1
on course1.cid = sc1.cid and course1.cname = 'china') s1
join (SELECT stu1.sid,stu1.sname FROM student stu1
JOIN sc sc1
on sc1.sid = stu1.sid
JOIN  temp_course course1
on course1.cid = sc1.cid and course1.cname = 'math') s2
on s1.sid = s2.sid;

-- 7、查询学过“张三”老师所教的所有课的同学的学号、姓名
SELECT student.sid,student.sname FROM student
JOIN sc
on sc.sid = student.sid
JOIN temp_course
on temp_course.cid = sc.cid
JOIN temp_teacher
on temp_course.tid = temp_teacher.tid
WHERE temp_teacher.tname='zhangsan';


-- 8.查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名
SELECT student.sid,student.sname FROM student
JOIN sc sc1
on sc1.sid = student.sid
JOIN sc sc2
on sc2.sid = student.sid
WHERE sc1.cid ='01' and sc2.cid='02' and sc1.score < sc2.score;

-- 9.查询所有课程成绩小于60分的同学的学号、姓名
SELECT student.sid,student.sname FROM student
JOIN sc
on sc.sid=student.sid
where sc.score < 60
GROUP BY student.sid,student.sname;


SELECT student.sid,student.sname FROM student
WHERE  exists(
  -- sc.score 有多个,会分别执行,必须都小于60才返回真
  SELECT * FROM sc WHERE sc.sid = student.sid and sc.score <  60
);

SELECT student.sid, student.Sname FROM sc,student WHERE sc.sid = student.sid and sc.score <  60
GROUP BY student.sid, student.Sname;
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2018.09.23 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档