hive sql练习

参考

-- 创建学生表,学号,姓名,性别,年龄,班级
DROP TABLE student;
CREATE TEMPORARY TABLE  student(
  Sno int,
  Sname VARCHAR(20),
  Sex VARCHAR(20),
  Sage int,
  Sdept VARCHAR(20)
);

-- 创建 课程表, 课程编号,课程名称
DROP TABLE course;
create TEMPORARY TABLE course(
  Cno int,
  Cname VARCHAR(20)
);
-- 创建 学生 课程关联表,学号,课号,年级
create table sc(
  Sno int,
  Cno int,
  Grade int
);

-- 插入课程数据
INSERT into course values(1, 'database');
INSERT into course values(2, 'math');
INSERT into course values(3, 'info system');
INSERT into course values(4, 'system');
INSERT into course values(5, 'data structure');
INSERT into course values(6, 'data deal');

-- 插入学生选课数据和成绩
INSERT into sc values(95001,1,81);
INSERT into sc values(95001,2,85);
INSERT into sc values(95001,3,88);
INSERT into sc values(95001,4,70);
INSERT into sc values(95002,2,90);
INSERT into sc values(95002,3,80);
INSERT into sc values(95002,4,71);
INSERT into sc values(95002,5,60);
INSERT into sc values(95003,1,82);
INSERT into sc values(95003,3,90);
INSERT into sc values(95003,5,100);
INSERT into sc values(95004,1,80);
INSERT into sc values(95004,2,92);
INSERT into sc values(95004,4,91);
INSERT into sc values(95004,5,70);
INSERT into sc values(95005,1,70);
INSERT into sc values(95005,2,92);
INSERT into sc values(95005,3,99);
INSERT into sc values(95005,6,87);
INSERT into sc values(95006,1,72);
INSERT into sc values(95006,2,62);
INSERT into sc values(95006,3,100);
INSERT into sc values(95006,4,59);
INSERT into sc values(95006,5,60);
INSERT into sc values(95006,6,98);
INSERT into sc values(95007,3,68);
INSERT into sc values(95007,4,91);
INSERT into sc values(95007,5,94);
INSERT into sc values(95007,6,78);
INSERT into sc values(95008,1,98);
INSERT into sc values(95008,3,89);
INSERT into sc values(95008,6,91);
INSERT into sc values(95009,2,81);
INSERT into sc values(95009,4,89);
INSERT into sc values(95009,6,100);
INSERT into sc values(95010,2,98);
INSERT into sc values(95010,5,90);
INSERT into sc values(95010,6,80);
INSERT into sc values(95011,1,81);
INSERT into sc values(95011,2,91);
INSERT into sc values(95011,3,81);
INSERT into sc values(95011,4,86);
INSERT into sc values(95012,1,81);
INSERT into sc values(95012,3,78);
INSERT into sc values(95012,4,85);
INSERT into sc values(95012,6,98);
INSERT into sc values(95013,1,98);
INSERT into sc values(95013,2,58);
INSERT into sc values(95013,4,88);
INSERT into sc values(95013,5,93);
INSERT into sc values(95014,1,91);
INSERT into sc values(95014,2,100);
INSERT into sc values(95014,4,98);
INSERT into sc values(95015,1,91);
INSERT into sc values(95015,3,59);
INSERT into sc values(95015,4,100);
INSERT into sc values(95015,6,95);
INSERT into sc values(95016,1,92);
INSERT into sc values(95016,2,99);
INSERT into sc values(95016,4,82);
INSERT into sc values(95017,4,82);
INSERT into sc values(95017,5,100);
INSERT into sc values(95017,6,58);
INSERT into sc values(95018,1,95);
INSERT into sc values(95018,2,100);
INSERT into sc values(95018,3,67);
INSERT into sc values(95018,4,78);
INSERT into sc values(95019,1,77);
INSERT into sc values(95019,2,90);
INSERT into sc values(95019,3,91);
INSERT into sc values(95019,4,67);
INSERT into sc values(95019,5,87);
INSERT into sc values(95020,1,66);
INSERT into sc values(95020,2,99);
INSERT into sc values(95020,5,93);
INSERT into sc values(95021,2,93);
INSERT into sc values(95021,5,91);
INSERT into sc values(95021,6,99);
INSERT into sc values(95022,3,69);
INSERT into sc values(95022,4,93);
INSERT into sc values(95022,5,82);
INSERT into sc values(95022,6,100);

INSERT into student values(95001,"liyong","M",20,"CS");
INSERT into student values(95002,"licheng","F",19,"IS");
INSERT into student values(95003,"wangming","F",22,"MA");
INSERT into student values(95004,"zhangli","M",19,"IS");
INSERT into student values(95005,"liugang","M",18,"MA");
INSERT into student values(95006,"sunqing","M",23,"CS");
INSERT into student values(95007,"yisiling","F",19,"MA");
INSERT into student values(95008,"lina","F",18,"CS");
INSERT into student values(95009,"mengyuanyuan","F",18,"MA");
INSERT into student values(95010,"kongxiaotao","M",19,"CS");
INSERT into student values(95011,"baoxiaobo","M",18,"MA");
INSERT into student values(95012,"sunhua","F",20,"CS");
INSERT into student values(95013,"fengwei","M",21,"CS");
INSERT into student values(95014,"wangxiaoli","F",19,"CS");
INSERT into student values(95015,"wangjun","M",18,"MA");
INSERT into student values(95016,"qianguo","M",21,"MA");
INSERT into student values(95017,"wangfengjuan","F",18,"IS");
INSERT into student values(95018,"wangyi","F",19,"IS");
INSERT into student values(95019,"xingxiaoli","F",19,"IS");
INSERT into student values(95020,"zhaoqian","M",21,"IS");
INSERT into student values(95021,"zhouer","M",17,"MA");
INSERT into student values(95022,"zhangming","M",20,"MA");

SELECT * FROM course;

-- 查询学生总人数
SELECT count(*) FROM student;

-- 查询全体学生的学号与姓名
SELECT student.Sno, student.Sname FROM student;

-- 查询选修了课程的学生姓名,
SELECT student.Sname FROM student
JOIN sc
on student.Sno = sc.Sno
GROUP BY student.Sname;

-- 计算1号课程的学生平均成绩,sc表
SELECT avg(sc.Grade) FROM sc WHERE Cno = 1 GROUP BY Cno;

-- 这一个写法要快一点
select avg(Grade) from sc group by Cno having Cno=1;

-- 查询各科成绩平均分
-- 这个所有学系的各科平均分
SELECT course.Cname, avg(sc.Grade) FROM course
JOIN sc
on course.Cno=sc.Cno
GROUP BY course.Cname;

-- 这样写要快一点
SELECT course.Cname, avg(sc.Grade) FROM course
JOIN sc
on course.Cno=sc.Cno
GROUP BY course.Cname,sc.Cno;

-- 查询选修1号课程的学生最高分数
SELECT max(sc.Grade) FROM sc WHERE sc.Cno = 1;


-- 这个写法要快一点
select Grade from sc where Cno=1 order by Grade desc limit 1;

select Grade from sc where Cno=1 distribute by Grade sort by Grade desc limit 1;

-- 求各个课程号及相应的选课人数
SELECT sc.Cno, count(1) as num from sc GROUP BY Cno;

-- 查询选修了3门以上的课程的学生学号
SELECT sc.Sno FROM sc GROUP BY Sno HAVING count(1) > 3;

-- 查询学生信息,结果按学号全局有序
SELECT student.* FROM student ORDER BY student.Sno;


-- 查询学生信息,结果区分性别按年龄有序
SELECT * FROM student ORDER BY sex,Sage asc;

-- 查询每个学生及其选修课程的成绩情况
SELECT student.*,course.Cname,sc.Grade FROM student
JOIN sc
on sc.Sno = student.Sno
JOIN course
on course.Cno=sc.Cno
ORDER BY student.Sno;

-- 查询学生的得分情况
SELECT student.Sname,course.Cname,sc.Grade FROM student
JOIN sc
on sc.Sno = student.Sno
JOIN course
on course.Cno=sc.Cno
ORDER BY student.Sname;

-- 查询选修2号课程且成绩在90分以上的所有学生。
SELECT student.* FROM student
JOIN sc
on student.Sno=sc.Sno
WHERE sc.Cno=2 and sc.Grade > 90;

-- 查询所有学生的信息,如果在成绩表中有成绩,则输出成绩表中的课程号
SELECT student.*,sc.Cno FROM student
left JOIN sc
on student.Sno=sc.Sno;

-- 查询与“licheng”在同一个系学习的学生
-- exists写法于join差不多快
SELECT stu1.Sname FROM student stu1 WHERE exists
(
  SELECT * FROM student stu2 WHERE stu2.Sname = 'licheng' and stu1.Sdept = stu2.Sdept
);

-- in 写法
SELECT stu1.Sname FROM student stu1 WHERE stu1.Sdept in
(
  SELECT stu2.Sdept FROM student stu2 WHERE stu2.Sname = 'licheng'
);

-- join写法最快
SELECT stu.Sname FROM student stu
JOIN student st
on st.Sname = 'licheng' and st.Sdept = stu.Sdept and stu.Sname != 'licheng';

select s2.* from student as s1 inner join student as s2 on(s2.Sdept=s1.Sdept) where s1.Sname='licheng';

原先数据格式是这样,写了个脚本转成insert 形式

ss = '''95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA
'''
rr = "INSERT into student values(%s);";
for el in ss.split('\n'):
    temp = ""
    i = 0
    for ell in el.split(','):
        if i == 0 or i==3:
            temp +=ell+','
        elif i == 1 or i == 2:
            temp +="\"%s\""%ell+','
        else:
            temp += "\"%s\""%ell
        i += 1
        
    print (rr%temp)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • hive sql练习1

    用户1733462
  • 笔记之一

    用户1733462
  • tensorflow dropout用法

    用户1733462
  • 《快学BigData》--Hive总结(C)(43)

    Hive 总结 - - - - - - - - - - - - - - - - - - - - - - - - - - - - 262

    小徐
  • SpringBoot2版本Caused by: java.sql.SQLSyntaxErrorException: Table 'dinner.hibernate_sequenc

    1、SpringBoot2版本Caused by: java.sql.SQLSyntaxErrorException: Table 'dinner.hibern...

    别先生
  • React第三方组件6(状态管理之Mobx的使用⑤异步操作)

    本教程总共5篇,每日更新一篇,请关注我们!你可以进入历史消息查看以往文章,也敬请期待我们的新文章! 1、React第三方组件6(状态管理之Mobx的使用①简单...

    前端人人
  • “游戏障碍”被世界卫生组织正式承认

    程序你好
  • 游戏制作与热点

    每次都不知道该更新啥。再用一些无聊的碎碎念填充下。 最近h5游戏的制作好像忽然变火了点,不知道是不是我的错觉,然后有些同学来问『怎样才能做出一款自己的游戏?』 ...

    沙因Sign
  • Centos7创建LVM磁盘管理

    在centos7下需要挂载两个新的磁盘。为了方便后续的扩容方便,决定将这其设置为LVM管理的方式。

    Devops海洋的渔夫
  • 数据分享3

    小卜巅峰的时候达到dau 8k+,眼看着就要越过1w的槛了,然后,被人拉下来,踢出去了。

    沙因Sign

扫码关注云+社区

领取腾讯云代金券