hive SQL基础经典练习题(上）

最新推荐文章于 2024-05-16 10:45:48 发布

一代码动乾坤

最新推荐文章于 2024-05-16 10:45:48 发布

阅读量2.9k

点赞数 3

分类专栏：大数据 hive 文章标签： sql hive

原文链接：https://zhuanlan.zhihu.com/p/59066049

版权

大数据同时被 2 个专栏收录

100 篇文章 3 订阅

订阅专栏

hive

23 篇文章 0 订阅

订阅专栏

hive SQL基础经典练习题(上）

hive SQL 入门容易，精通很难。基础的巩固相当重要，看起来简单易入门的SQL其实是千变万化的，能一次写正确SQL的工程师，亦然遵守二八法则。

编程环境：hive

创建练习题数据（一）

表1 COURSE (课程表)

create table  COURSE
(`CNO`      string  comment '课程编号',
`CNAME`     string   comment '课程名称,',
`TNO`       string     comment '教师编号'
)
row format delimited fields terminated by '\t' lines terminated by '\n'
--课程表数据插入
insert into  COURSE  values   ('3-105' ,'计算机导论',825)
                                        ,('3-245' ,'操作系统' ,804)
                                        ,('6-166' ,'数据电路' ,856)
                                        ,('9-888' ,'高等数学' ,100);

select * from  COURSE

表1 COURSE (课程表)

表2 SCORE （成绩表）

create table  SCORE
(`SNO`      string  comment '学生编号',
`CNO`       string  comment '课程编号',
`DEGREE`    int  comment '分数'
)
--成绩表数据插入
insert into  SCORE  values   (103,'3-245',86)
                                        ,(105,'3-245',75)
                                        ,(109,'3-245',68)
                                        ,(103,'3-105',92)
                                        ,(105,'3-105',88)
                                        ,(109,'3-105',76)
                                        ,(101,'3-105',64)
                                        ,(107,'3-105',91)
                                        ,(108,'3-105',78)
                                        ,(101,'6-166',85)
                                        ,(107,'6-106',79)
                                        ,(108,'6-166',81);

select * from  SCORE

表2 SCORE （成绩表）

表3 学生表（Student）

create table  Student
(`SNO`      string  comment '学生编号',
`SNAME`     string  comment '学生姓名',
`SSEX`      string     comment '学生性别',
`SBIRTHDAY` string     comment '出生年月',
`CLASS` string     comment '班级'
)
row format delimited fields terminated by '\t' lines terminated by '\n'
--学生表数据插入
insert into  Student  values  (108 ,'曾华' ,'男' ,1977-09-01,95033)
                                        ,(105 ,'匡明' ,'男' ,1975-10-02,95031)
                                        ,(107 ,'王丽' ,'女' ,1976-01-23,95033)
                                        ,(101 ,'李军' ,'男' ,1976-02-20,95033)
                                        ,(109 ,'王芳' ,'女' ,1975-02-10,95031)
                                        ,(103 ,'陆君' ,'男' ,1974-06-03,95031);

select * from  Student

表3 学生表（Student）

表4 教师表（TEACHER）

create table  TEACHER
(`TNO`      string  comment '教师编号',
`TNAME`     string  comment '教师姓名',
`TSEX`      string     comment '性别',
`TBIRTHDAY` string     comment '出生年月',
`PROF` string     comment '职称',
`DEPART` string     comment '课程'
)
--教师表数据插入
insert into  TEACHER  values  (804,'李诚','男','1958-12-02','副教授','计算机系')
                                        ,(856,'张旭','男','1969-03-12','讲师','电子工程系')
                                        ,(825,'王萍','女','1972-05-05','助教','计算机系')
                                        , (831,'刘冰','女','1977-08-14','助教','电子工程系');

select * from  TEACHER

表4 教师表（TEACHER）

题目（二）：

1、查询Score表中成绩在60到80之间的所有记录

SELECT * FROM SCORE
WHERE degree BETWEEN 60 and 80

1、查询Score表中成绩在60到80之间的所有记录

2、查询Score表中成绩为85，86或88的记录。

SELECT * FROM SCORE
WHERE degree in(85,86,88)

2、查询Score表中成绩为85，86或88的记录。

3、以Cno升序、Degree降序查询Score表的所有记录。

select * from Score
order by Cno asc, Degree desc

3、以Cno升序、Degree降序查询Score表的所有记录。

4、查询Score表中的最高分的学生学号和课程号。

select a.SNO,a.CNO
from 
(select * from SCORE sort BY DEGREE desc limit 1) a

4、查询Score表中的最高分的学生学号和课程号。

5、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select avg(degree),cno
 from SCORE
where cno like '3%'
group by cno
having count(sno)>= 5

5、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

6、查询最低分大于70，最高分小于90的Sno列。

SELECT SNO FROM SCORE
GROUP BY SNO 
HAVING MIN(DEGREE)>70 AND MAX(DEGREE)<90

6、查询最低分大于70，最高分小于90的Sno列。

7、查询所有学生的Sname、Cno和Degree列

select a.SNAME,b.Cno,b.Degree
 from Student a 
left join SCORE b 
on a.SNO = b.sno

7、查询所有学生的Sname、Cno和Degree列

8、查询“95033”班所选课程的平均分。

select avg(b.Degree)
 from (select * from Student  where class = '95033')   a 
left join SCORE b 
on a.SNO = b.sno

8、查询“95033”班所选课程的平均分。

9、查询 score 中选学一门以上课程的同学中分数为非最高分成绩的记录。

 SELECT a.*
FROM SCORE a
 left join  (SELECT *
FROM SCORE
SORT BY degree desc limit 1 ) b 
on a.sno = b.sno and a.degree= b.degree
 join (
SELECT sno
FROM SCORE
GROUP BY sno
HAVING COUNT(*) > 1) c 
on a.sno = c.sno 
WHERE b.sno is NULL

9、查询 score 中选学一门以上课程的同学中分数为非最高分成绩的记录。

Hive对子查询的支持很有限。它只允许子查询出现在SELECT语句的FROM子句中。Hive SQL 不同于MYSQL当中可在where语句后写子查询。在此笔者用了left join 与join 连接操作。

把题目拆分分两步可一探究竟

第一步为目的是查询出 score 的同学中分数为非最高分成绩的记录。

 SELECT *
FROM SCORE a
 left join  (SELECT *
FROM SCORE
SORT BY degree desc limit 1 ) b 
on a.sno = b.sno and a.degree= b.degree
WHERE b.sno is NULL

第一步为目的是查询出 score 的同学中分数为非最高分成绩的记录。

第二步为目的是查询出 score 中选学一门以上课程的同学的记录。

SELECT sno
FROM SCORE
GROUP BY sno
HAVING COUNT(*) > 1

第二步为目的是查询出 score 中选学一门以上课程的同学的记录。

以上两步拆分的方法是由于hive SQL的语法所限制，需要进行连接操作方可得到查询结果。

如果是MYSQL的环境的话，那么会更简单一些。

# MYSQL方法
SELECT *
FROM score
WHERE degree NOT IN (
SELECT MAX(degree)
FROM score)
AND sno in (
SELECT sno
FROM score
GROUP BY sno
HAVING COUNT(*) > 1)

10、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

笔者这里分两步展开

第一步

select 
 a.*,b.*
from
(
    select 
        SNO
        ,CNO 
        ,DEGREE
        ,1 mark
    from SCORE 
)a
left join 
(
    select 
        DEGREE DEGREE2
        ,1 mark
    from SCORE 
    where SNO = '109'
    and cno = '3-105'
)b on a.mark = b.mark

第一步只是演示

第二步查询所需结果

select 
    a.SNO
    ,CNO 
    ,DEGREE
from
(
    select 
        SNO
        ,CNO 
        ,DEGREE
        ,1 mark
    from SCORE 
)a
left join 
(
    select 
        DEGREE DEGREE2
        ,1 mark
    from SCORE 
    where SNO = '109'
    and cno = '3-105'
)b on a.mark = b.mark
where a.DEGREE > b.DEGREE2

10、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

11、查询“张旭“教师任课的学生成绩。

    select a.* from SCORE a 
 join 
	 (SELECT  CNO  --'课程编号'
	         ,CNAME
			 ,TNO FROM COURSE )b
  on a.CNO = b.CNO
inner join  
   (SELECT TNO FROM TEACHER  
	 where  TNAME  = '张旭'
	   ) c 
on b.TNO = c.TNO

11、查询“张旭“教师任课的学生成绩。

12、查询选修某课程的同学人数多于5人的教师姓名。

SELECT    a.TNO       --'教师编号'
         ,a.TNAME     --'教师姓名'
         ,a.PROF      --'职称'
         ,a.DEPART    --'课程'
FROM TEACHER  a	 
join 
(  SELECT  CNO  --'课程编号'
	      ,CNAME
		  ,TNO  --'教师编号'
  FROM COURSE )b
on a.TNO = b.TNO  
inner join
 (select  CNO  from SCORE 
	 group by cno --课程编号
	 having count(sno)>5 )c 
on b.CNO = c.CNO

12、查询选修某课程的同学人数多于5人的教师姓名。

为了让大家可以更方便的回顾数据，笔者把教师编号、职称、课程数据也查询出来。

13、查询存在有85分以上成绩的课程 Cno

第一种方法

SELECT CNO from SCORE 
 GROUP BY CNO HAVING MAX(DEGREE)>85

第一种方法查询存在有85分以上成绩的课程 Cno

第二种方法

select  CNO   --课程编号
from SCORE 
 where degree > 85 --有85分以上成绩的课程
 group by CNO

第二种方法查询存在有85分以上成绩的课程 Cno

一代码动乾坤

关注

3
点赞
踩
28

收藏

觉得还不错? 一键收藏
0
评论
hive SQL基础经典练习题(上）

hive SQL基础经典练习题(上）hive SQL 入门容易，精通很难。基础的巩固相当重要，看起来简单易入门的SQL其实是千变万化的，能一次写正确SQL的工程师，亦然遵守二八法则。编程环境：hive 创建练习题数据（一）表1 COURSE (课程表)create table COURSE(`CNO` string comment '课程编号',`CN...
复制链接

扫一扫