HiveSQL基础练习题

1.环境准备

1.1建表语句

create database if not exists db_hive;
use db_hive;

--创建学生表
drop table if exists student;
create table if not exists student(
	stu_id string COMMENT '学生id',
	stu_name string COMMENT '学生姓名',
	birthday date COMMENT '出生日期',
	sex string COMMENT '性别'
)
row format delimited fields terminated by ','
stored as textfile;

--创建课程表
drop table if exists course;
create table if not exists course(
	course_id string COMMENT '课程id',
	course_name string COMMENT '课程名',
	tea_id string COMMENT '任课老师id'
)
row format delimited fields terminated by ','
stored as textfile;

--创建老师表
drop table if exists teacher;
create table if not exists teacher(
	tea_id string COMMENT '老师id',
	tea_name string COMMENT '老师姓名'
)
row format delimited fields terminated by ','
stored as textfile;

--创建分数表
drop table if exists score;
create table if not exists score(
	stu_id string COMMENT '学生id',
	course_id string COMMENT '课程id',
	grade int COMMENT '成绩'
)
row format delimited fields terminated by ','
stored as textfile;

1.2数据准备

• 创建/opt/module/data目录,将如下4个文件放到/opt/module/data目录下
  以下是部分数据(自己准备数据)

   cat student.txt 
  001,彭于晏,1995-05-16,男
  002,胡歌,1994-03-20,男
  003,周杰伦,1995-04-30,男
  004,刘德华,1998-08-28,男
  005,唐国强,1993-09-10,男
   cat course.txt 
  01,语文,1003
  02,数学,1001
  03,英语,1004
  04,体育,1002
  05,音乐,1002
  [hyj@hadoop102 data]$ cat teacher.txt 
  1001,张高数
  1002,李体音
  1003,王子文
  1004,刘丽英
  cat score.txt 
  001,01,94
  002,01,74
  004,01,85
  005,01,64
  006,01,71
  007,01,48
  008,01,56
  009,01,75
  

1.3数据准备

• 创建/opt/module/data目录,将如下4个文件放到/opt/module/data目录下
  以下是部分数据(自己准备数据)

load data local inpath '/opt/module/data/student.txt' into table student;

load data local inpath '/opt/module/data/course.txt' into table course;

load data local inpath '/opt/module/data/teacher.txt' into table teacher;

load data local inpath '/opt/module/data/score.txt' into table score;

2.查询

2.1 查询姓名中带“华”的学生名单

%代表任意个字符(0个或多个)
-代表一个字符

select * from student where stu_name like ‘%华%’;

2.2 查询姓“王”老师的个数

select count(*) from teacher where tea_name like ‘王%’;

2.3 检索课程编号为“04”且分数小于60的学生学号，结果按分数降序排列

select stu_id from score where course_id=‘04’ and grade < 60 order by grade desc;

2.4 查询语文成绩 < 90分的学生和其对应的成绩，按照学号升序排序

select s#### 2.stu_id,s#### 2.stu_name,s1.grade from course c,score s1,student s2 where c.course_id=s1.course_id
and c.course_name=“语文” and s1.stu_id=s#### 2.stu_id and s1.grade < 90 order by s#### 2.stu_id;

2.5 查询各学生的年龄（精确到月份）

获取年份
select year(‘2023-05-21’);
获取当前日期
select current_date();

获取月份

select month(‘2023-05-21’);
if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select
stu_name,
concat(
if(bir_year>=0,bir_year,bir_year-1),‘岁’,
if(bir_month>=0,bir_month,bir_month+12),‘个月’
) as age
from (
select
stu_name,
year(current_date())-year(birthday) bir_year,
month(current_date())-month(birthday) bir_month
from student
) t;

2.6 查询本月过生日的学生

select * from student where month(birthday)=month(current_date());

2.7 查询课程编号为“04”的总成绩

方法一:
select ‘04’ as course_id,sum(grade) as grade_sum from score where course_id=‘04’;

方法二:
select course_id,sum(grade) as grade_sum from score where course_id=‘04’ group by course_id;

2.8 查询参加考试的学生个数

思路：对成绩表中的学号做去重并count

select count(distinct(stu_id)) from score;
–或
select count(*) from (select distinct(stu_id) from score) t;

2.9 查询各科成绩最高和最低的分，以如下的形式显示：课程号，最高分，最低分

思路：按照学科分组并使用max和min。

select course_id,max(grade),min(grade) from score g