数据库系统原理与应用教程（066）—— MySQL 练习题：操作题 71-81(1)

最新推荐文章于 2024-05-16 05:34:32 发布

2401_84975342

最新推荐文章于 2024-05-16 05:34:32 发布

阅读量527

点赞数 13

文章标签： c语言 c++ 学习

本文链接：https://blog.csdn.net/2401_84975342/article/details/138937652

版权

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上C C++开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以戳这里获取

/*
DROP TABLE IF EXISTS OrderItems;
CREATE TABLE IF NOT EXISTS OrderItems(
prod_id VARCHAR(255) NOT NULL COMMENT ‘产品id’,
order_num VARCHAR(255) NOT NULL COMMENT ‘商品订单号’
);
INSERT OrderItems VALUES (‘BR01’,‘a0001’),(‘BR01’,‘a0002’),(‘BR02’,‘a0003’),(‘BR02’,‘a0013’);

DROP TABLE IF EXISTS Orders;
CREATE TABLE IF NOT EXISTS Orders(
order_num VARCHAR(255) NOT NULL COMMENT ‘商品订单号’,
cust_id VARCHAR(255) NOT NULL COMMENT ‘顾客id’,
order_date TIMESTAMP NOT NULL COMMENT ‘下单时间’
);
INSERT Orders VALUES (‘a0001’,‘cust10’,‘2022-01-01 00:00:00’),(‘a0002’,‘cust1’,‘2022-01-01 00:01:00’),(‘a0003’,‘cust1’,‘2022-01-02 00:00:00’),(‘a0013’,‘cust2’,‘2022-01-01 00:20:00’);

DROP TABLE IF EXISTS Customers;
CREATE TABLE IF NOT EXISTS Customers(
cust_id VARCHAR(255) NOT NULL COMMENT ‘顾客id’,
cust_email VARCHAR(255) NOT NULL COMMENT ‘顾客email’
);
INSERT Customers VALUES (‘cust10’,‘cust10@cust.com’),(‘cust1’,‘cust1@cust.com’),(‘cust2’,‘cust2@cust.com’);
*/


解答：

/*
– 使用子查询
select cust_email from Customers where cust_id in
(select cust_id from Orders where order_num in
(select order_num from OrderItems where prod_id = ‘BR01’)
);
– 使用连接查询
select c.cust_email
from Customers c join Orders o on c.cust_id = o.cust_id
join OrderItems oi on o.order_num = oi.order_num
where prod_id = ‘BR01’;
*/
– 使用子查询
mysql> select cust_email from Customers where cust_id in
-> (select cust_id from Orders where order_num in
-> (select order_num from OrderItems where prod_id = ‘BR01’)
-> );
±----------------+
| cust_email |
±----------------+
| cust10@cust.com |
| cust1@cust.com |
±----------------+
2 rows in set (0.00 sec)

– 使用连接查询
mysql> select c.cust_email
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> join OrderItems oi on o.order_num = oi.order_num
-> where prod_id = ‘BR01’;
±----------------+
| cust_email |
±----------------+
| cust10@cust.com |
| cust1@cust.com |
±----------------+
2 rows in set (0.00 sec)


75、连接查询（5）


数据表：OrderItems，表中数据如下：




| order\_num | item\_price | quantity |
| --- | --- | --- |
| a1 | 10 | 105 |
| a2 | 1 | 1100 |
| a2 | 1 | 200 |
| a4 | 2 | 1121 |
| a5 | 5 | 10 |
| a2 | 1 | 19 |
| a7 | 7 | 5 |


数据表：Orders，表中数据如下：




| order\_num | cust\_id |
| --- | --- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |


数据表：Customers，表中数据如下：




| cust\_id | cust\_name |
| --- | --- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |


【问题】编写 SQL 语句，查询订单总价不小于 1000 的客户名称和总额（item\_price \* quantity），按总额对结果进行排序。查询结果如下：




| cust\_name | total\_price |
| --- | --- |
| andy | 1050 |
| ben | 1319 |
| tom | 2242 |


表结构及数据如下：

/*
DROP TABLE IF EXISTS OrderItems;
CREATE TABLE IF NOT EXISTS OrderItems(
order_num VARCHAR(255) NOT NULL COMMENT ‘商品订单号’,
item_price INT(16) NOT NULL COMMENT ‘售出价格’,
quantity INT(16) NOT NULL COMMENT ‘商品数量’
);
INSERT OrderItems VALUES (‘a1’,10,105),(‘a2’,1,1100),(‘a2’,1,200),(‘a4’,2,1121),(‘a5’,5,10),(‘a2’,1,19),(‘a7’,7,5);

DROP TABLE IF EXISTS Customers;
CREATE TABLE IF NOT EXISTS Customers(
cust_id VARCHAR(255) NOT NULL COMMENT ‘客户id’,
cust_name VARCHAR(255) NOT NULL COMMENT ‘客户姓名’
);
INSERT Customers VALUES (‘cust10’,‘andy’),(‘cust1’,‘ben’),(‘cust2’,‘tony’),(‘cust22’,‘tom’),(‘cust221’,‘an’),(‘cust2217’,‘hex’);

DROP TABLE IF EXISTS Orders;
CREATE TABLE IF NOT EXISTS Orders(
order_num VARCHAR(255) NOT NULL COMMENT ‘商品订单号’,
cust_id VARCHAR(255) NOT NULL COMMENT ‘顾客id’
);
INSERT Orders VALUES (‘a1’,‘cust10’),(‘a2’,‘cust1’),(‘a3’,‘cust2’),(‘a4’,‘cust22’),(‘a5’,‘cust221’),(‘a7’,‘cust2217’);
*/


解答：

/*
select c.cust_name,
sum(oi.item_price * oi.quantity) total_price
from OrderItems oi join Orders o on oi.order_num = o.order_num
join Customers c on o.cust_id = c.cust_id
group by c.cust_name
having total_price >= 1000
order by total_price;
*/
mysql> select c.cust_name,
-> sum(oi.item_price * oi.quantity) total_price
-> from OrderItems oi join Orders o on oi.order_num = o.order_num
-> join Customers c on o.cust_id = c.cust_id
-> group by c.cust_name
-> having total_price >= 1000
-> order by total_price;
±----------±------------+
| cust_name | total_price |
±----------±------------+
| andy | 1050 |
| ben | 1319 |
| tom | 2242 |
±----------±------------+
3 rows in set (0.00 sec)


76、连接查询（6）


数据表：Customers，表中数据如下：




| cust\_id | cust\_name |
| --- | --- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |


数据表：Orders，表中数据如下：




| order\_num | cust\_id |
| --- | --- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |


【问题】编写 SQL语句，查询每个顾客的名称和所有的订单号，根据顾客姓名 cust\_name 升序排序。查询结果如下：




| cust\_name | order\_num |
| --- | --- |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |


表结构及数据如下：

/*
DROP TABLE IF EXISTS Customers;
CREATE TABLE IF NOT EXISTS Customers(
cust_id VARCHAR(255) NOT NULL COMMENT ‘客户id’,
cust_name VARCHAR(255) NOT NULL COMMENT ‘客户姓名’
);
INSERT Customers VALUES (‘cust10’,‘andy’),(‘cust1’,‘ben’),(‘cust2’,‘tony’),(‘cust22’,‘tom’),(‘cust221’,‘an’),(‘cust2217’,‘hex’);


解答：

/*
select c.cust_name, o.order_num
from Customers c join Orders o on c.cust_id = o.cust_id
order by c.cust_name;
*/
mysql> select c.cust_name, o.order_num
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> order by c.cust_name;
±----------±----------+
| cust_name | order_num |
±----------±----------+
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
±----------±----------+
6 rows in set (0.00 sec)


77、连接查询（7）


数据表：Orders，表中数据如下：




| order\_num | cust\_id |
| --- | --- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |


数据表：Customers，表中数据如下：




| cust\_id | cust\_name |
| --- | --- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
| cust40 | ace |


【问题】检索每个顾客的名称和所有的订单号，列出所有的顾客，即使他们没有下过订单。查询结果如下：




| cust\_name | order\_num |
| --- | --- |
| ace | NULL |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |


表结构及数据如下：


解答：

/*
select c.cust_name, o.order_num
from Customers c left join Orders o on c.cust_id = o.cust_id
order by c.cust_name;
*/
mysql> select c.cust_name, o.order_num
-> from Customers c left join Orders o on c.cust_id = o.cust_id
-> order by c.cust_name;
±----------±----------+
| cust_name | order_num |
±----------±----------+
| ace | NULL |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
±----------±----------+
7 rows in set (0.00 sec)


78、连接查询（8）


数据表：Products，表中数据为：




| prod\_id | prod\_name |
| --- | --- |
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
| a0023 | soda |


数据表：OrderItems，表中数据为：




| prod\_id | order\_num |
| --- | --- |
| a0001 | a105 |
| a0002 | a1100 |
| a0002 | a200 |
| a0013 | a1121 |
| a0003 | a10 |
| a0003 | a19 |
| a0003 | a5 |


【问题】编写 SQL 语句，查询产品名称（prod\_name）和与之相关的订单号（order\_num）的列表，并按照产品名称升序排序。查询结果如下：




| prod\_name | order\_num |
| --- | --- |
| coffee | a1121 |
| cola | a5 |
| cola | a19 |
| cola | a10 |
| egg | a105 |
| sockets | a200 |
| sockets | a1100 |
| soda | NULL |


表结构及数据如下：

/*
DROP TABLE IF EXISTS Products;
CREATE TABLE IF NOT EXISTS Products (
prod\_id VARCHAR(255) NOT NULL COMMENT ‘产品 ID’,
prod\_name VARCHAR(255) NOT NULL COMMENT ‘产品名称’
);
INSERT INTO Products VALUES (‘a0001’,‘egg’),
(‘a0002’,‘sockets’),
(‘a0013’,‘coffee’),
(‘a0003’,‘cola’),
(‘a0023’,‘soda’);

DROP TABLE IF EXISTS OrderItems;
CREATE TABLE IF NOT EXISTS OrderItems(
prod_id VARCHAR(255) NOT NULL COMMENT ‘产品id’,
order_num VARCHAR(255) NOT NULL COMMENT ‘商品数量’
);
INSERT OrderItems VALUES (‘a0001’,‘a105’),(‘a0002’,‘a1100’),(‘a0002’,‘a200’),(‘a0013’,‘a1121’),(‘a0003’,‘a10’),(‘a0003’,‘a19’),(‘a0003’,‘a5’);
*/


解答：

/*
select p.prod_name, oi.order_num
from Products p left join OrderItems oi on p.prod_id = oi.prod_id
order by p.prod_name;
*/
mysql> select p.prod_name, oi.order_num
-> from Products p left join OrderItems oi on p.prod_id = oi.prod_id
-> order by p.prod_name;
±----------±----------+
| prod_name | order_num |
±----------±----------+
| coffee | a1121 |
| cola | a10 |
| cola | a19 |
| cola | a5 |
| egg | a105 |
| sockets | a1100 |
| sockets | a200 |
| soda | NULL |
±----------±----------+
8 rows in set (0.00 sec)


79、连接查询（9）


数据表：Products，表中数据如下：




| prod\_id | prod\_name |
| --- | --- |
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
| a0023 | soda |


数据表：OrderItems，表中数据如下：




| prod\_id | order\_num |
| --- | --- |
| a0001 | a105 |
| a0002 | a1100 |
| a0002 | a200 |
| a0013 | a1121 |
| a0003 | a10 |
| a0003 | a19 |
| a0003 | a5 |


【问题】查询产品名称（prod\_name）和每一项产品的总订单数，并按产品名称升序排序。查询结果如下：




| prod\_name | orders |
| --- | --- |
| coffee | 1 |
| cola | 3 |
| egg | 1 |
| sockets | 2 |
| soda | 0 |


解答：

/*
select p.prod_name, count(order_num) orders
from Products p left join OrderItems oi on p.prod_id = oi.prod_id
group by p.prod_name
order by p.prod_name;
*/
mysql> select p.prod_name, count(order_num) orders
-> from Products p left join OrderItems oi on p.prod_id = oi.prod_id
-> group by p.prod_name
-> order by p.prod_name;
±----------±-------+
| prod_name | orders |
±----------±-------+
| coffee | 1 |
| cola | 3 |
| egg | 1 |
| sockets | 2 |
| soda | 0 |
±----------±-------+
5 rows in set (0.00 sec)


80、连接查询（10）


数据表：Vendors，表中数据如下：




| vend\_id |
| --- |
| a0002 |
| a0013 |
| a0003 |
| a0010 |


数据表：Products，表中数据如下：




| vend\_id | prod\_id |
| --- | --- |
| a0001 | egg |
| a0002 | prod\_id\_iphone |
| a00113 | prod\_id\_tea |
| a0003 | prod\_id\_vivo phone |
| a0010 | prod\_id\_huawei phone |


【问题】列出供应商（Vendors 表中的 vend\_id）及可供产品的数量（包括没有产品的供应商），根据 vend\_id 升序排序。查询结果如下：




| vend\_id | prod\_id |
| --- | --- |
| a0002 | 1 |
| a0003 | 1 |
| a0010 | 1 |
| a0013 | 0 |


表结构及数据如下：

/*
DROP TABLE IF EXISTS Vendors;
CREATE TABLE IF NOT EXISTS Vendors (
vend\_id VARCHAR(255) NOT NULL COMMENT ‘vend名称’
);
INSERT INTO Vendors VALUES (‘a0002’),
(‘a0013’),
(‘a0003’),
(‘a0010’);

DROP TABLE IF EXISTS Products;
CREATE TABLE IF NOT EXISTS Products (
vend\_id VARCHAR(255) NOT NULL COMMENT ‘产品 ID’,
prod\_id VARCHAR(255) NOT NULL COMMENT ‘产品名称’
);
INSERT INTO Products VALUES (‘a0001’,‘egg’),
(‘a0002’,‘prod_id_iphone’),
(‘a00113’,‘prod_id_tea’),
(‘a0003’,‘prod_id_vivo phone’),
(‘a0010’,‘prod_id_huawei phone’);
*/


解答：

/*
select v.vend_id, count(prod_id) prod_id
from Vendors v left join Products p on v.vend_id = p.vend_id
group by v.vend_id
order by v.vend_id;
*/
mysql> select v.vend_id, count(prod_id) prod_id
-> from Vendors v left join Products p on v.vend_id = p.vend_id
-> group by v.vend_id
-> order by v.vend_id;
±--------±--------+
| vend_id | prod_id |
±--------±--------+
| a0002 | 1 |
| a0003 | 1 |
| a0010 | 1 |
| a0013 | 0 |
±--------±--------+
4 rows in set (0.00 sec)


81、union 联合查询


数据表：OrderItems，表中数据如下：




| prod\_id | quantity |
| --- | --- |
| a0001 | 105 |
| a0002 | 100 |
| a0002 | 200 |
| a0013 | 1121 |
| a0003 | 10 |
| a0003 | 19 |
| a0003 | 5 |
| BNBG | 10002 |


【问题】查询数量（quantity）为 100 的产品信息和产品 id（prod\_id）以【BNBG】开头的产品信息，按产品 id 对结果进行升序排序。查询结果如下：




| prod\_id | quantity |
| --- | --- |
| a0002 | 100 |


![img](https://img-blog.csdnimg.cn/img_convert/434942dee50777333b987793b9a8e21c.png)
![img](https://img-blog.csdnimg.cn/img_convert/2b9c1d3f823f750a9dfe4e9b0e7dcf1f.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上C C++开发知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[如果你需要这些资料，可以戳这里获取](https://bbs.csdn.net/topics/618668825)**

-----+
4 rows in set (0.00 sec)

81、union 联合查询

数据表：OrderItems，表中数据如下：

prod_id	quantity
a0001	105
a0002	100
a0002	200
a0013	1121
a0003	10
a0003	19
a0003	5
BNBG	10002

【问题】查询数量（quantity）为 100 的产品信息和产品 id（prod_id）以【BNBG】开头的产品信息，按产品 id 对结果进行升序排序。查询结果如下：

prod_id	quantity
a0002	100

[外链图片转存中…(img-SfiUNXHe-1715808818003)]
[外链图片转存中…(img-ZcO3gNDz-1715808818003)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上C C++开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以戳这里获取

2401_84975342

关注

13
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
数据库系统原理与应用教程（066）—— MySQL 练习题：操作题 71-81(1)

cust_id VARCHAR(255) NOT NULL COMMENT ‘顾客id’,cust_id VARCHAR(255) NOT NULL COMMENT ‘顾客id’,cust_id VARCHAR(255) NOT NULL COMMENT ‘客户id’,cust_id VARCHAR(255) NOT NULL COMMENT ‘客户id’,cust_id VARCHAR(255) NOT NULL COMMENT ‘客户id’,
复制链接

扫一扫