🌿挑战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的规范问题
- 尽量把逗号放在前面,为什么?
- 方便排查,不会遗漏逗号
- 方便注释,可以单行直接注释,不用再改逗号
- 排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
- 特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号,写起来很快
- 使用关键字大写,养成好习惯