现有图书管理数据库的三个数据模型如下:
图书(数据表名:BOOK)
序号 | 字段名称 | 字段描述 | 字段类型 |
1 | BOOK_ID | 总编号 | 文本 |
2 | SORT | 分类号 | 文本 |
3 | BOOK_NAME | 书名 | 文本 |
4 | WRITER | 作者 | 文本 |
5 | OUTPUT | 出版单位 | 文本 |
6 | PRICE | 单价 | 数值(保留小数点后2位) |
读者(数据表名:READER)
序号 | 字段名称 | 字段描述 | 字段类型 |
1 | READER_ID | 借书证号 | 文本 |
2 | COMPANY | 单位 | 文本 |
3 | NAME | 姓名 | 文本 |
4 | SEX | 性别 | 文本 |
5 | GRADE | 职称 | 文本 |
6 | ADDR | 地址 | 文本 |
借阅记录(数据表名:BORROW LOG)
序号 | 字段名称 | 字段描述 | 字段类型 |
1 | READER_ID | 借书证号 | 文本 |
2 | BOOK_D | 总编号 | 文本 |
3 | BORROW_ATE | 借书日期 | 日期 |
(1)创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。
(2)找出姓李的读者姓名(NAME)和所在单位(COMPANY)。
(3)查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE),结果按单价降序排序。
(4)查找价格介于10元和20元之间的图书种类(SORT)出版单位(OUTPUT)和单价(PRICE),结果按出版单位(OUTPUT)和单价(PRICE)升序排序。
(5)查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。
(6)求”科学出版社”图书的最高单价、最低单价、平均单价。
(7)找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。
(8)考虑到数据安全的需要,需定时将“借阅记录”中数据进行备份,请使用一条SQL语句,在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_1.0G_ BAK中。
(9)现在需要将原Oracle数据库中数据迁移至Hive仓库,请写出“图书”在Hive中的建表语句(Hive实现,提示:列分隔符|;数据表数据需要外部导入:分区分别以month_part、day_part 命名)
(10)Hive中有表A,现在需要将表A的月分区 201505 中 user_id为20000的user_dinner字段更新为bonc8920,其他用户user_dinner字段数据不变,请列出更新的方法步骤。(Hive实现,提示:Hlive中无update语法,请通过其他办法进行数据更新)
1 --(1)创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。
2 --图书
3 create table test_seven_BOOK
4 (
5 BOOK_ID String COMMENT '总编号',
6 SORT String COMMENT '分类号',
7 BOOK_NAME String COMMENT '书名',
8 WRITER String COMMENT '作者',
9 OUTPUT String COMMENT '出版单位',
10 PRICE decimal(10,2) COMMENT '单价'
11 )
12 row format delimited fields terminated by '\t';
13
14 --读者
15 create table test_seven_READER
16 (
17 READER_ID String COMMENT '借书证号',
18 COMPANY String COMMENT '单位',
19 NAME String COMMENT '姓名',
20 SEX String COMMENT '性别',
21 GRADE String COMMENT '职称',
22 ADDR String COMMENT '地址'
23 )
24 row format delimited fields terminated by '\t';
25
26 --借阅记录
27 create table test_seven_BORROW_LOG
28 (
29 READER_ID String COMMENT '借书证号',
30 BOOK_D String COMMENT '总编号',
31 BORROW_ATE date COMMENT '借书日期'
32 )
33 row format delimited fields terminated by '\t';
1 -- 插入数据
2 insert into table test_seven_book values ('1001','A1','Java','James Gosling','sun','11');
3 insert into table test_seven_book values ('1002','A2','linux','Linus Benedict Torvalds','sun','22');
4 insert into table test_seven_book values ('1003','A3','Java3','James Gosling3','sun3','33');
5 insert into table test_seven_book values ('1004','A4','Java4','James Gosling4','sun4','44');
6 insert into table test_seven_book values ('1005','B1','Java5','James Gosling5','sun','55');
7 insert into table test_seven_book values ('1006','C1','Java6','James Gosling6','sun5','66');
8 insert into table test_seven_book values ('1007','D1','Java7','James Gosling7','sun6','77');
9 insert into table test_seven_book values ('1008','E1','Java8','James Gosling4','sun3','88');
10 insert into table test_seven_reader values ('7','buu',decode(binary('李大帅'),'utf-8'),'man','lay1','beijing4');
11 insert into table test_seven_reader values ('2','buu2','苏大强','man','lay2','beijing2');
12 insert into table test_seven_reader values ('3','buu2','李二胖','woman','lay3','beijing3');
13 insert into table test_seven_reader values ('4','buu3','王三涛','man','lay4','beijing4');
14 insert into table test_seven_reader values ('5','buu4','刘四虎','woman','lay5','beijing1');
15 insert into table test_seven_reader values ('6','buu','宋冬野','woman','lay6','beijing5');
16 insert into table test_seven_borrow_log values ('1','1002','2019-06-01');
17 insert into table test_seven_borrow_log values ('1','1003','2019-06-02');
18 insert into table test_seven_borrow_log values ('1','1006','2019-06-03');
19 insert into table test_seven_borrow_log values ('2','1001','2019-06-04');
20 insert into table test_seven_borrow_log values ('3','1002','2019-06-05');
21 insert into table test_seven_borrow_log values ('4','1005','2019-06-06');
22 insert into table test_seven_borrow_log values ('5','1003','2019-06-06');
23 insert into table test_seven_borrow_log values ('3','1006','2019-06-07');
24 insert into table test_seven_borrow_log values ('2','1003','2019-06-03');
25 insert into table test_seven_borrow_log values ('3','1008','2019-06-03');
26 insert into table test_seven_borrow_log values ('1','1002','2019-06-04');
1 --(2)找出姓李的读者姓名(NAME)和所在单位(COMPANY)。
2 select name,company from test_seven_reader where name like '李%';
3 --(3)查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE),结果按单价降序排序。
4 select BOOK_NAME,PRICE from test_seven_book order by PRICE desc;
5 --(4)查找价格介于10元和20元之间的图书种类(SORT)出版单位(OUTPUT)和单价(PRICE),结果按出版单位(OUTPUT)和单价(PRICE)升序排序。
6 select SORT,OUTPUT,PRICE from test_seven_book where PRICE between 10 and 20 order by OUTPUT,PRICE asc;
7
8 --(5)查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。
9 select
10 rd.name,
11 rd.COMPANY
12 from
13 (
14 select
15 READER_ID
16 from test_seven_borrow_log
17 group by READER_ID
18 ) t1
19 join
20 test_seven_reader rd
21 on t1.READER_ID = rd.READER_ID;
22 --(6)求”科学出版社”图书的最高单价、最低单价、平均单价。
23 select
24 max(PRICE) max,
25 min(PRICE) min,
26 avg(PRICE) avg
27 from
28 test_seven_book;
29 --(7)找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。
30 select
31 rd.READER_ID,
32 rd.name,
33 rd.COMPANY
34 from
35 (
36 select
37 READER_ID,
38 count(*) num
39 from test_seven_BORROW_LOG
40 group by READER_ID
41 having count(*) >= 2
42 ) t1
43 join
44 test_seven_reader rd
45 on t1.READER_ID = rd.READER_ID;
46
47 --(8)考虑到数据安全的需要,需定时将“借阅记录”中数据进行备份,
48 -- 请使用一条SQL语句,在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.
49 --井且将“借阅记录”中现有数据全部复制到BORROW_l0G_BAK中。
50 create table BORROW_LOG_BAK
51 (
52 READER_ID String COMMENT '借书证号',
53 BOOK_D String COMMENT '总编号',
54 BORROW_ATE date COMMENT '借书日期'
55 )
56 as select * from test_seven_BORROW_LOG;
57
58 --(9)现在需要将原Oracle数据库中数据迁移至Hive仓库,
59 --请写出“图书”在Hive中的建表语句(Hive实现,提示:列分隔符|;数据表数据需要外部导入:分区分别以month_part、day_part 命名)
60 create table test_seven_book_oracle (
61 book_id string COMMENT '总编号',
62 sort string COMMENT '分类号',
63 book_name string COMMENT '书名',
64 writer string COMMENT '作者',
65 output string COMMENT '出版单位',
66 price decimal(10,2) COMMENT '单价'
67 )
68 PARTITIONED BY (month string,day string)
69 row format delimited fields terminated by '|';
70
71 --(10)Hive中有表A,现在需要将表A的月分区 201505 中 user_id为20000的user_dinner字段更新为bonc8920,
72 -- 其他用户user_dinner字段数据不变,请列出更新的方法步骤。
73 --(Hive实现,提示:Hlive中无update语法,请通过其他办法进行数据更新)
74 create table tmp_A as select * from A where user_id<>20000 and month_part=201505;
75 insert into table tmp_A partition(month_part=’201505’) values(20000,其他字段,bonc8920);
76 insert overwrite table A partition(month_part=’201505’) select * from tmp_A where month_part=201505;