数据库系统原理与应用教程（065）—— MySQL 练习题：操作题 62-70（九）

数据库系统原理与应用教程（065）—— MySQL 练习题：操作题 62-70（九）：分组查询与子查询

62、分组查询（1）

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

order_num
a002
a002
a002
a004
a007

【问题】编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines 对结果进行升序排序。查询结果如下：

order_num	order_lines
a004	1
a007	1
a002	3

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');
*/
1
2
3
4
5
6
7

解答：

/*
select order_num, count(*) order_lines
from OrderItems
group by order_num order by order_lines;
*/
mysql> select order_num, count(*) order_lines
    -> from OrderItems
    -> group by order_num order by order_lines;
+-----------+-------------+
| order_num | order_lines |
+-----------+-------------+
| a004      |           1 |
| a007      |           1 |
| a002      |           3 |
+-----------+-------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

63、分组查询（2）

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

vend_id	prod_price
a0011	100
a0019	0.1
b0019	1000
b0019	6980
b0019	20

【问题】编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。查询结果如下：

vend_id	cheapest_item
a0019	0.1
b0019	20
a0011	100

表结构及数据如下：

/*
DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '供应商ID',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011',100),
('a0019',0.1),
('b0019',1000),
('b0019',6980),
('b0019',20);
*/
1
2
3
4
5
6
7
8
9
10
11
12

解答：

/*
select vend_id, min(prod_price) cheapest_item
from Products
group by vend_id
order by cheapest_item;
*/
mysql> select vend_id, min(prod_price) cheapest_item
    -> from Products
    -> group by vend_id
    -> order by cheapest_item;
+---------+---------------+
| vend_id | cheapest_item |
+---------+---------------+
| a0019   |           0.1 |
| b0019   |            20 |
| a0011   |           100 |
+---------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

64、分组查询（3）

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

order_num	quantity
a1	105
a2	1100
a2	200
a4	1121
a5	10
a2	19
a7	5

【问题】编写 SQL 语句，返回订单数量总和不小于 100 的所有订单号，最后结果按照订单号升序排序。查询结果如下：

order_num
a1
a2
a4

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	quantity INT(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1',105),('a2',200),('a4',1121),('a5',10),('a7',5);
*/
1
2
3
4
5
6
7
8

解答：

/*
select order_num from OrderItems
group by order_num having sum(quantity) >= 100
order by order_num;
*/
mysql> select order_num from OrderItems
    -> group by order_num having sum(quantity) >= 100
    -> order by order_num;
+-----------+
| order_num |
+-----------+
| a1        |
| a2        |
| a4        |
+-----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

65、分组查询（4）

数据表：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

【问题】编写 SQL 语句，返回订单总价不小于 1000 的所有订单号，最后的结果按订单号进行升序排序（订单总价 = item_price * quantity）。查询结果如下：

order_num	total_price
a1	1050
a2	1319
a4	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);
*/
1
2
3
4
5
6
7
8
9

解答：

/*
select order_num, sum(item_price * quantity) total_price 
from OrderItems
group by order_num having total_price >= 1000
order by order_num;
*/
mysql> select order_num, sum(item_price * quantity) total_price 
    -> from OrderItems
    -> group by order_num having total_price >= 1000
    -> order by order_num;
+-----------+-------------+
| order_num | total_price |
+-----------+-------------+
| a1        |        1050 |
| a2        |        1319 |
| a4        |        2242 |
+-----------+-------------+
3 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

66、子查询（1）

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

order_num	item_price
a1	10
a2	1
a2	1
a4	2
a5	5
a2	1
a7	7

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

order_num	cust_id
a1	cust10
a2	cust1
a2	cust1
a4	cust2
a5	cust5
a2	cust1
a7	cust7

【问题】使用子查询，查询购买价格为 10 元或以上产品的顾客列表。查询结果如下：

cust_id
cust10

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
DROP TABLE IF EXISTS `Orders`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  item_price INT(16) NOT NULL COMMENT '售出价格'
);

CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);

INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

解答：

/*
select cust_id from Orders 
where order_num in 
(select order_num from OrderItems where item_price >= 10);
*/
mysql> select cust_id from Orders 
    -> where order_num in 
    -> (select order_num from OrderItems where item_price >= 10);
+---------+
| cust_id |
+---------+
| cust10  |
+---------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

67、子查询（2）

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

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

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

order_num	cust_id	order_date
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

【问题】编写 SQL 语句，查询哪些订单（在 OrderItems 中）购买了 prod_id 为 “BR01” 的产品，返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。查询结果如下：

cust_id	order_date
cust10	2022-01-01 00:00:00
cust1	2022-01-01 00:01:00

表结构及数据如下：

/*
DROP TABLE IF EXISTS `OrderItems`;
DROP TABLE IF EXISTS `Orders`;

CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
);
 
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 `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');
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');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

解答：

/*
select cust_id, order_date from Orders 
where order_num in (select order_num from OrderItems where prod_id = 'BR01')
order by order_date;
*/
mysql> select cust_id, order_date from Orders 
    -> where order_num in (select order_num from OrderItems where prod_id = 'BR01')
    -> order by order_date;
+---------+---------------------+
| cust_id | order_date          |
+---------+---------------------+
| cust10  | 2022-01-01 00:00:00 |
| cust1   | 2022-01-01 00:01:00 |
+---------+---------------------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

68、子查询（3）

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

prod_id	order_num
BR01	a0001
BR01	a0002
BR02	a0003
BR02	a0013

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

order_num	cust_id	order_date
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

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

cust_id	cust_email
cust10	cust10@cust.com
cust1	cust1@cust.com
cust2	cust2@cust.com

【问题】查询购买 prod_id 为 BR01 的产品的所有顾客的电子邮件。查询结果如下：

cust_email
cust10@cust.com
cust1@cust.com

表结构及数据如下：

/*
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');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

解答：

/*
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')
    );
*/
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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

69、子查询（4）

数据表：OrderItems（订单信息），表中有订单号：order_num，商品售出价格：item_price，商品数量：quantity。表中的数据如下：

order_num	item_price	quantity
a0001	10	105
a0002	1	1100
a0002	1	200
a0013	2	1121
a0003	5	10
a0003	1	19
a0003	7	5

数据表：Orders（订单表），表中有订单号：order_num，客户号：cust_id，表中数据如下：

order_num	cust_id
a0001	cust10
a0002	cust1
a0003	cust1
a0013	cust2

【问题】编写 SQL语句，查询顾客 ID（Orders 表中的 cust_id）以及每个顾客的订单总数（total_ordered），将结果按金额从大到小排序。查询结果如下：

cust_id	total_ordered
cust2	2242
cust1	1404
cust10	1050

表结构及数据如下：

/*
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 ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

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 ('a0001','cust10'),('a0002',
'cust1'),('a0003','cust1'),('a0013','cust2');
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

解答：

/*
select o.cust_id, sum(item_price * quantity) total_ordered
from OrderItems oi join Orders o on oi.order_num = o.order_num
group by o.cust_id
order by total_ordered desc;
*/
mysql> select o.cust_id, sum(item_price * quantity) total_ordered
    -> from OrderItems oi join Orders o on oi.order_num = o.order_num
    -> group by o.cust_id
    -> order by total_ordered desc;
+---------+---------------+
| cust_id | total_ordered |
+---------+---------------+
| cust2   |          2242 |
| cust1   |          1404 |
| cust10  |          1050 |
+---------+---------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

70、子查询（5）

数据表：Products，包含产品名称：prod_name、产品 id：prod_id，表中数据如下：

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola

数据表：OrderItems（订单商品表），包含产品 id：prod_id、售出数量：quantity，表中数据如下：

prod_id	quantity
a0001	105
a0002	1100
a0002	200
a0013	1121
a0003	10
a0003	19
a0003	5

【问题】

【问题】编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold（产品售出数量总和）的计算列。查询结果如下：

prod_name	quant_sold
egg	105
sockets	1300
coffee	1121
cola	34

表结构及数据如下：

/*
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');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',1100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5);
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

解答：

/*
select p.prod_name, sum(quantity) quant_sold
from OrderItems o join Products p on o.prod_id = p.prod_id
group by p.prod_name;
*/

mysql> select p.prod_name, sum(quantity) quant_sold
    -> from OrderItems o join Products p on o.prod_id = p.prod_id
    -> group by p.prod_name;
+-----------+------------+
| prod_name | quant_sold |
+-----------+------------+
| coffee    |       1121 |
| cola      |         34 |
| egg       |        105 |
| sockets   |       1300 |
+-----------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

相关阅读:
FastAdmin后台开发框架 lang 任意文件读取漏洞复现
 KubeSphere 3.3.0 离线安装教程
 华为OD机试 - 代表团坐车 - 动态规划（Java 2023 B卷 200分）
以太坊跌至675美元 - 长期ETH预测大幅下调
 《凤凰架构》-全局事务章节读书笔记
 C++ STL容器初识：迭代器
 sitk::ERROR: No ImageJ/Fiji application found.
RabbitMQ 死信队列详解
 SpringMVC 学习（八）整合SSM
什么是Nginx？
原文地址：https://blog.csdn.net/weixin_44377973/article/details/126041549