大数据第52天—Mysql练习题12道之七-图书管理数据库-杨大伟

现有图书管理数据库的三个数据模型如下：

图书（数据表名：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 '	';
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 '	';
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 '	';
 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;
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/shui68home/p/13604398.html