挑战100天不停更hive sql第28天-场景题-图书馆

🌿挑战100天不停更，刷爆 hive sql🧲

详情请点击🔗我的专栏🖲,共同学习,一起进步~

NUM: 第28天 -图书馆

需求
现有图书管理数据库的三个数据模型如下：
图书（数据表名：BOOK）
读者（数据表名：READER)
借阅记录（数据表名：BORROW LOG）
需求:
（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_L0G_ BAK中。
需求都不难,那我们直接刷题吧~~ 千万不要眼高手低 , sql就是得多敲!!🎈

🧨那就不废话了, 刷题~~🧨

🎈表结构

读者（数据表名：READER)

图书（数据表名：BOOK）

借阅记录（数据表名：BORROW LOG）

🎉建表并插入数据

-- 创建图书表book
CREATE TABLE daily_sql.book
(
    book_id   string,
    `SORT`    string,
    book_name string,
    writer    string,
    OUTPUT    string,
    price     decimal(10, 2)
);
INSERT INTO TABLE daily_sql.book
VALUES ('001', 'TP391', '信息处理', 'author1', '机械工业出版社', '20');
INSERT INTO TABLE daily_sql.book
VALUES ('002', 'TP392', '数据库', 'author12', '科学出版社', '15');
INSERT INTO TABLE daily_sql.book
VALUES ('003', 'TP393', '计算机网络', 'author3', '机械工业出版社', '29');
INSERT INTO TABLE daily_sql.book
VALUES ('004', 'TP399', '微机原理', 'author4', '科学出版社', '39');
INSERT INTO TABLE daily_sql.book
VALUES ('005', 'C931', '管理信息系统', 'author5', '机械工业出版社', '40');
INSERT INTO TABLE daily_sql.book
VALUES ('006', 'C932', '运筹学', 'author6', '科学出版社', '55');
-- 创建读者表reader
CREATE TABLE daily_sql.reader
(
    reader_id string,
    company   string,
    name      string,
    sex       string,
    grade     string,
    addr      string
);
INSERT INTO TABLE daily_sql.reader
VALUES ('0001', '阿里巴巴', 'jack', '男', 'vp', 'addr1');
INSERT INTO TABLE daily_sql.reader
VALUES ('0002', '百度', 'robin', '男', 'vp', 'addr2');
INSERT INTO TABLE daily_sql.reader
VALUES ('0003', '腾讯', 'tony', '男', 'vp', 'addr3');
INSERT INTO TABLE daily_sql.reader
VALUES ('0004', '京东', 'jasper', '男', 'cfo', 'addr4');
INSERT INTO TABLE daily_sql.reader
VALUES ('0005', '网易', 'zhangsan', '女', 'ceo', 'addr5');
INSERT INTO TABLE daily_sql.reader
VALUES ('0006', '搜狐', 'lisi', '女', 'ceo', 'addr6');

-- 创建借阅记录表borrow_log
CREATE TABLE daily_sql.borrow_log
(
    reader_id   string,
    book_id     string,
    borrow_date string
);
INSERT INTO TABLE daily_sql.borrow_log
VALUES ('0001', '002', '2019-10-14');
INSERT INTO TABLE daily_sql.borrow_log
VALUES ('0002', '001', '2019-10-13');
INSERT INTO TABLE daily_sql.borrow_log
VALUES ('0003', '005', '2019-09-14');
INSERT INTO TABLE daily_sql.borrow_log
VALUES ('0004', '006', '2019-08-15');
INSERT INTO TABLE daily_sql.borrow_log
VALUES ('0005', '003', '2019-10-10');
INSERT INTO TABLE daily_sql.borrow_log
VALUES ('0006', '004', '2019-17-13');

👓sql

直接看sql吧

🧨SQL

-- 1,找出姓李的读者姓名（NAME）和所在单位（COMPANY）。
SELECT name
     , company
FROM reader
WHERE name LIKE 'li%';
-- 2,查找“科学出版社”的所有图书名称（BOOK_NAME）及单价（PRICE），结果按单价降序排序。
SELECT book_name
     , OUTPUT
     , price
FROM book
WHERE OUTPUT = '科学出版社'
ORDER BY price DESC;
-- 3,查找价格介于10元和20元之间的图书种类(SORT）出版单位（OUTPUT）和单价（PRICE），结果按出版单位（OUTPUT）和单价（PRICE）升序排序。
SELECT book_id
     , SORT
     , book_name
     , writer
     , OUTPUT
     , price
FROM book
WHERE price BETWEEN 10 AND 20
ORDER BY OUTPUT, price;
-- 4,查找所有借了书的读者的姓名（NAME）及所在单位（COMPANY）。
SELECT name
     , company
FROM reader
         JOIN borrow_log bl ON reader.reader_id = bl.reader_id;
-- 5,求”科学出版社”图书的最高单价、最低单价、平均单价。
SELECT OUTPUT
     , max(price)
     , min(price)
     , round(avg(price), 2)
FROM book
GROUP BY OUTPUT
HAVING OUTPUT = '科学出版社';
-- 6,找出当前至少借阅了2本图书（大于等于2本）的读者姓名及其所在单位。

SELECT b.name,
       b.company
FROM (SELECT reader_id
      FROM daily_sql.borrow_log
      GROUP BY reader_id
      HAVING count(*) >= 2) a
         JOIN daily_sql.reader b ON a.reader_id = b.reader_id;
-- 7,考虑到数据安全的需要，需定时将“借阅记录”中数据进行备份，请使用一条SQL语句，在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_L0G_ BAK中。
DROP TABLE BORROW_LOG_BAK;
CREATE TABLE
    BORROW_LOG_BAK
AS
SELECT *
FROM borrow_log;

关于sql的规范问题

1. 尽量把逗号放在前面,为什么?
   1. 方便排查,不会遗漏逗号
   2. 方便注释,可以单行直接注释,不用再改逗号
   3. 排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
   4. 特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快
2. 使用关键字大写,养成好习惯