mysql hive题目_Hive SQL练习题

hive学习之经典sql 50题 hive版

建表:

create table student(s_id string,s_name string,s_birth string,s_sex string) row format delimited fields terminated by '\t';

create table course(c_id string,c_name string,t_id string) row format delimited fields terminated by '\t';

create table teacher(t_id string,t_name string) row format delimited fields terminated by '\t';

create table score(s_id string,c_id string,s_score int) row format delimited fields terminated by '\t';

生成数据

vi /export/data/hivedatas/student.csv

01 赵雷 1990-01-01 男

02 钱电 1990-12-21 男

03 孙风 1990-05-20 男

04 李云 1990-08-06 男

05 周梅 1991-12-01 女

06 吴兰 1992-03-01 女

07 郑竹 1989-07-01 女

08 王菊 1990-01-20 女

vi /export/data/hivedatas/course.csv

01 语文 02

02 数学 01

03 英语 03

vi /export/data/hivedatas/teacher.csv

01 张三

02 李四

03 王五

vi /export/data/hivedatas/score.csv

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

导数据到hive

load data local inpath '/export/data/hivedatas/student.csv' into table student;

load data local inpath '/export/data/hivedatas/course.csv' into table course;

load data local inpath '/export/data/hivedatas/teacher.csv' into table teacher;

load data local inpath '/export/data/hivedatas/score.csv' into table score;

–注:–hive查询语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...

FROM table_reference

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list

| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]

]

[LIMIT number]

– 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:

select student.*,a.s_score as 01_score,b.s_score as 02_score

from student

join score a on student.s_id=a.s_id and a.c_id='01'

left join score b on student.s_id=b.s_id and b.c_id='02'

where a.s_score>b.s_score;

–答案2

select student.*,a.s_score as 01_score,b.s_score as 02_score

from student

join score a on a.c_id='01'

join score b on b.c_id='02'

where a.s_id=student.s_id and b.s_id=student.s_id and a.s_score>b.s_score;

– 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数:

select student.*,a.s_score as 01_score,b.s_score as 02_score

from student

join score a on student.s_id=a.s_id and a.c_id='01'

left join score b on student.s_id=b.s_id and b.c_id='02'

where a.s_score

–答案2

select student.*,a.s_score as 01_score,b.s_score as 02_score

from student

join score a on a.c_id='01'

join score b on b.c_id='02'

where a.s_id=student.s_id and b.s_id=student.s_id and a.s_score

– 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩:

select student.s_id,student.s_name,tmp.平均成绩 from student

join (

select score.s_id,round(avg(score.s_score),1)as 平均成绩

from score group by s_id)as tmp

on tmp.平均成绩>=60

where student.s_id = tmp.s_id

–答案2

select student.s_id,student.s_name,round(avg (score.s_score),1) as 平均成绩 from student

join score on student.s_id = score.s_id

group by student.s_id,student.s_name

having avg (score.s_score) >= 60;

– 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩:

– (包括有成绩的和无成绩的)

select student.s_id,student.s_name,tmp.avgScore from student

join (

select score.s_id,round(avg(score.s_score),1)as avgScore from score group by s_id)as tmp

on tmp.avgScore < 60

where student.s_id=tmp.s_id

union all

select s2.s_id,s2.s_name,0 as avgScore from student s2

where s2.s_id not in

(select distinct sc2.s_id from score sc2);

–答案2

select score.s_id,student.s_name,round(avg (score.s_score),1) as avgScore from student

inner join score on student.s_id=score.s_id

group by score.s_id,student.s_name

having avg (score.s_score) < 60

union all

select s2.s_id,s2.s_name,0 as avgScore from student s2

where s2.s_id not in

(select distinct sc2.s_id from score sc2);

– 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩:

select student.s_id,student.s_name,(count(score.c_id) )as total_count,sum(score.s_score)as total_score

from student

left join score on student.s_id=score.s_id

group by student.s_id,student.s_name ;

– 6、查询"李"姓老师的数量:

select t_name,count(1) from teacher where t_name like '李%' group by t_name;

– 7、查询学过"张三"老师授课的同学的信息:

select student.* from student

join score on student.s_id =score.s_id

join course on course.c_id=score.c_id

join teacher on course.t_id=teacher.t_id and t_name='张三';

– 8、查询没学过"张三"老师授课的同学的信息:

select student.* from student

left join (select s_id from score

join cour