《SQL 必知必会》全解析

不要哀求，学会争取。若是如此，终有所获。

原文：https://mp.weixin.qq.com/s/zbOqyAtsWsocarsFIGdGgw

前言

你是否还在烦恼 SQL 该从何学起，或者学了 SQL 想找个地方练练手？好巧不巧，最近在工作之余登上牛客，发现了牛客不知道啥时候上线了SQL 必知必会的练习题。

《SQL 必知必会》作为麻省理工学院、伊利诺伊大学等众多大学的参考教材，由浅入深地讲解了SQL的基本概念和语法。涉及数据的排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容。实例丰富，方便查阅，可以说作为一个 CRUD BOY/GIRL 必读书目。

想着正好给它刷一遍，然后将自己刷题的一些想法总结下，于是有了今天这篇文章，希望能给需要的小伙伴一点点帮助。

SQL1 从 Customers 表中检索所有的 ID

描述

现有表Customers如下：

cust_id
A
B
C

问题

编写 SQL 语句，从 Customers 表中检索所有的 cust_id。

示例答案

返回 cust_id 列的内容

cust_id
A
B
C

示例

DROP TABLE IF EXISTS `Customers`;

CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) DEFAULT NULL
);

INSERT `Customers` VALUES ('A'),('B'),('C');

解答

考察最简单的查询语句，因为 Customers 表中仅有 cust_id 一列，所以我们可以使用以下两种解答方式。

第一种方式，选择特定列进行输出，这也是我们在工作中更加推荐使用的一种方式，将需要输出的列名全部描述出来。

SELECT cust_id FROM Customers;

第二种方式，使用 * 对表中所有列进行输出，因为 Customers 表中仅有一列，所以可以使用该方式。但在日常工作中，就算查询结果列中包含了数据库表的所有字段，也不要直接使用 *.

SELECT * FROM Customers;

SQL2 检索并列出已订购产品的清单

描述

表OrderItems含有非空的列prod_id代表商品id，包含了所有已订购的商品（有些已被订购多次）。

prod_id
a1
a2
a3
a4
a5
a6
a7

问题

编写SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '商品id'
);
INSERT `OrderItems` VALUES ('a1'),('a2'),('a3'),('a4'),('a5'),('a6'),('a6')

解答

要对结果去重，可以使用 DISTINCT 关键字。使用时，在后边跟上需要去重的字段即可保证这些去重字段的查询结果不重复。

SELECT DISTINCT prod_id FROM OrderItems;

此外还可以使用 GROUP BY 关键字，改关键字支持在去重的同时，同步返回其他字段的信息。

SELECT prod_id FROM OrderItems GROUP BY prod_id;

SQL3 检索所有列

描述

现在有 Customers 表（表中含有列 cust_id 代表客户 id，cust_name 代表客户姓名）。

cust_id	cust_name
a1	andy
a2	ben
a3	tony
a4	tom
a5	an
a6	lee
a7	hex

问题

需要编写 SQL语句，检索所有列。

示例结果

返回所有列 cust_id 和 cust_name。

cust_id	cust_name
a1	andy
a2	ben
a3	tony
a4	tom
a5	an
a6	lee
a7	hex

示例

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 ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

解答

类似于第一题，最简单的查询语句，只不过相比第一题多了一列，所以同样可以使用两种方式。

使用 * 表示所有列。

SELECT * FROM Customers;

将需要打印出的列详细列出。

SELECT cust_id, cust_name FROM Customers;

SQL4 检索顾客名称并且排序

描述

有表 Customers，cust_id 代表客户 id，cust_name 代表客户姓名。

cust_id	cust_name
a1	andy
a2	ben
a3	tony
a4	tom
a5	an
a6	lee
a7	hex

问题

从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。

示例结果

返回客户姓名 cust_name

cust_name
tony
tom
lee
hex
ben
andy
an

示例

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 ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

解答

要对数据库中的数据进行排序，需要使用关键字 ORDER BY。此外，默认情况下，SQL 中列的结果默认是正序排列的，即实际情况下以下语句执行结果是一样的。

SELECT cust_name FROM Customers;

SELECT cust_name FROM Customers ORDER BY cust_name ASC;

而要对查询结果逆序输出，则需要用到 DESC 关键字，表示逆序输出。

SELECT cust_name FROM Customers ORDER BY cust_name DESC;

SQL5 对顾客ID和日期排序

描述

有 Orders 表

cust_id	order_num	order_date
andy	aaaa	2021-01-01 00:00:00
andy	bbbb	2021-01-01 12:00:00
bob	cccc	2021-01-10 12:00:00
dick	dddd	2021-01-11 00:00:00

问题

编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。

示例答案

返回 2 列，cust_id 和 order_num

cust_id	order_num
andy	bbbb
andy	aaaa
bob	cccc
dick	dddd

示例解析

首先根据 cust_id 进行排列，andy 在 bob 和 dick 前，再根据 order_date 进行排列，订单号 bbbb 的订单时间是 "2021-01-01 12:00:00" 大于订单号 aaaa 的时间 "2021-01-01 00:00:00"

示例

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders` (
  `cust_id` varchar(255) NOT NULL COMMENT '顾客 ID',
  `order_num` varchar(255) NOT NULL COMMENT '订单号',
  `order_date` timestamp NOT NULL COMMENT '订单时间'
);
INSERT INTO `Orders` VALUES ('andy','aaaa','2021-01-01 00:00:00'),
('andy','bbbb','2021-01-01 12:00:00'),
('bob','cccc','2021-01-10 12:00:00'),
('dick','dddd','2021-01-11 00:00:00');

解答

要对列进行排序，则需要使用 ORDER BY 关键字，此外就是正序和倒序输出。

ASC：正序输出，也是默认输出的情况。
DESC：倒序输出。

SELECT cust_id, order_num FROM Orders ORDER BY cust_id, order_date DESC;

SQL6 按照数量和价格排序

描述

假设有一个 OrderItems 表

quantity	item_price
1	100
10	1003
2	500

问题

编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。

示例答案

返回 quantity 和 item_price

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems` (
  `quantity` INT(64) NOT NULL COMMENT '数量',
  `item_price` INT(64) NOT NULL COMMENT '订单价格'
);
INSERT INTO `OrderItems` VALUES (1,100),
(10,1003),
(2,500);

解答

同样是查询语句，要对查询结果进行排序，则需要使用 ORDER BY 关键字，最后则是需要注意是正序还是倒序，题目中由多到少和由高到低都是属于倒序，所以需要使用关键字 DESC。

SELECT quantity, item_price FROM OrderItems ORDER BY quantity DESC, item_price DESC;

SQL7 检查SQL语句

描述

有 Vendors 表

vend_name
海底捞
小龙坎
大龙燚

问题

下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据 vend_name 逆序排列

SELECT vend_name, 
FROM Vendors 
ORDER vend_name DESC;

示例展示

返回 vend_name

vend_name
海底捞
小龙坎
大龙燚

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称'
);
INSERT INTO `Vendors` VALUES ('海底捞'),
('小龙坎'),
('大龙燚');

解答

SELECT vend_name, FROM Vendors ORDER vend_name DESC;

主要存在两处错误，首先是选取需要展示的列 vend_name 后，后面并没有跟上需要展示的其他列，所以此处多了一个 ,。另外，对选取列进行排序使用的关键字是 ORDER BY，而题目中只是给出了 ORDER，忘记了 BY，所以会导致查询结果错误。针对这两处错误改正后，得到的正确的语句如下所示。

SELECT vend_name FROM Vendors ORDER BY vend_name DESC;

SQL8 返回固定价格的产品

描述

有表 Products

prod_id	prod_name	prod_price
a0018	sockets	9.49
a0019	iphone13	600
b0018	gucci t-shirts	1000

问题

从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品。

示例结果

返回 prod_id 和 prod_name

prod_id	prod_name
a0018	sockets

示例

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 '产品名称',
  `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0018','sockets',9.49),
('a0019','iphone13',600),
('b0019','gucci t-shirts',1000);

解答

查询符合指定条件的数据，可以使用关键字 WHERE 对条件进行限制，然后就是对条件的实现，这里可以使用两种方式。

第一种，直接利用 =，既然条件是需要价格为 9.49 美元的产品，则我们将价格列设置为 prod_price = 9.49 即可。

SELECT prod_id, prod_name FROM Products WHERE prod_price = 9.49;

第二种，除开 = 之外，我们也可以使用 in，表示价格只要在指定的数据之中，我们将价格列设置为 in (9.49) 即可，但是此时要注意不要忽略 ()，否则语句会报错。

SELECT prod_id, prod_name FROM Products WHERE prod_price in (9.49);

SQL9 返回更高价格的产品

描述

Products 表

prod_id	prod_name	prod_price
a0018	sockets	9.49
a0019	iphone13	600
b0019	gucci t-shirts	1000

问题

编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品。

示例答案

返回 prod_id 商品 id 和 prod_name 商品名称

prod_id	prod_name
a0018	sockets
a0019	iphone13
b0019	gucci t-shirts

示例

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 '产品名称',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011','usb',9.49),
('a0019','iphone13',600),
('b0019','gucci t-shirts',1000);

解答

条件查询语句，查询格式：

SELECT [列名] FROM [表名] WHERE 判断条件 …

既然判断条件是要产品价格不低于 9 美元，则产品需要大于等于 9 美元，可以将产品价格设置为 prod_price >= 9 即可。

SELECT prod_id, prod_name FROM Products WHERE prod_price >= 9;

此外，我们还可以将 >= 拆分为两个条件，一个是 prod_price > 9，而另一个则是 prod_price = 9，然后满足任一条件即可，则利用关键字 OR 将两个条件进行连接。

SELECT prod_id, prod_name FROM Products WHERE prod_price > 9 OR prod_price = 9;

SQL10 返回产品并且按照价格排序

描述

有 Products 表

prod_id	prod_name	prod_price
a0011	egg	3
a0019	sockets	4
b0019	coffee	15

问题

编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序

示例结果

返回商品名称 prod_name 和商品价格 prod_price

prod_name	prod_price
egg	3
sockets	4

注：不需要考虑商品价格相同的情况

示例

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 '产品名称',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011','egg',3),
('a0019','sockets',4),
('b0019','coffee',15);

解答

条件查询，通过将条件拆分为 prod_price >= 3 和 prod_price <= 6 两个条件，然后利用关键字 AND 将两个条件串联起来，同时满足两个条件的数据则进行输出。然后要对价格进行排序，则可以使用关键字 ORDER BY。

SELECT prod_name, prod_price FROM Products WHERE prod_price >= 3 AND prod_price <=6 ORDER BY prod_price;

除开上述将两个条件拆分开然后利用关键字 AND 串联起来之外，其实 SQL 还提供了关键字 BETWEEN … AND …，可以将满足介于两者之间的数进行筛选输出。

SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 3 AND 6 ORDER BY prod_price;

SQL11 返回更多的产品

描述

OrderItems 表含有：订单号 order_num，quantity 产品数量

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

问题

从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），其中每个订单都要包含 100 个或更多的产品。

示例结果

返回订单号列 order_num

order_num
a1
a2
a4

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a1','105'),('a2','1100'),('a2','200'),('a4','1121'),('a5','10'),('a2','19'),('a7','5')

解答

要筛选指定列中不重复的数据，则需要使用关键字 DISTINCT。此外，要筛选出满足指定条件的数据，则需要关键字 WHERE，然后根据题意，可以将条件写到一起，也可以将条件进行拆分后用关键字 OR 并联。

SELECT DISTINCT order_num FROM OrderItems WHERE quantity >= 100;

SELECT DISTINCT order_num FROM OrderItems WHERE quantity > 100 OR quantity = 100;

SQL12 检索供应商名称

描述

Vendors 表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）

vend_name	vend_country	vend_state
apple	USA	CA
vivo	CNA	shenzhen
huawei	CNA	xian

问题

编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个 CA）

示例答案

返回供应商名称 vend_name

vend_name
apple

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);
INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

解答

既然要筛选出加利福利亚州的提供商，则首要条件是满足 vend_state = 'CA'。另外，除开美国有加利福利亚州之外，有可能其他国家也有加利福利亚州，所以需要对国家也进行限定，设置条件为 vend_country = 'USA'。然后用关键字 AND 将条件串联，满足这两个条件的数据则进行输出。

SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA';

SQL13 检索并列出已订购产品的清单

描述

OrderItems 表包含了所有已订购的产品（有些已被订购多次）。

prod_id	order_num	quantity
BR01	a1	105
BR02	a2	1100
BR02	a2	200
BR03	a4	1121
BR017	a5	10
BR02	a2	19
BR017	a7	5

问题

编写 SQL 语句，查找所有订购了数量至少 100 个的 BR01、BR02 或 BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤。

示例答案

返回商品 id prod_id、订单 order_num、数量 quantity。

order_num	prod_id	quantity
a1	BR01	105
a2	BR02	1100
a2	BR02	200
a4	BR03	1121

示例解析

返回的结果中，数量满足大于等于 100，且满足 prod_id 是 "BR01"，“BR02”，“BR03" 中的任意一个。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(

	prod_id VARCHAR(255) NOT NULL COMMENT '商品号',
	order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
	quantity INT(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('BR01','a1','105'),('BR02','a2','1100'),('BR02','a2','200'),('BR03','a4','1121'),('BR017','a5','10'),('BR02','a2','19'),('BR017','a7','5')

解答

多条件查询，将两个条件进行拆分，然后将两个查询条件进行串联即可。

数量满足大于等于 100，则设置为 quantyty >= 100。

产品 id 满足 "BR01"，“BR02”，“BR03” 中的任意一个，则使用关键字 in。

SELECT order_num, prod_id, quantity FROM OrderItems WHERE quantity >= 100 AND prod_id IN ('BR01', 'BR02', 'BR03');

SQL14 返回所有价格在 3 美元到 6 美元之间的产品的名称和价格

描述

有表 Products

prod_id	prod_name	prod_price
a0011	egg	3
a0019	sockets	4
b0019	coffee	15

问题

编写 SQL 语句，返回所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），使用 AND 操作符，然后按价格对结果进行升序排序

示例结果

返回商品名称 prod_name 和商品价格 prod_price

prod_name	prod_price
egg	3
sockets	4

注：不需要考虑价格相同时的排序问题

示例

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 '产品名称',
`prod_price` INT(255) NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011','egg',3),
('a0019','sockets',4),
('b0019','coffee',15);

解答

两个条件，首先是价格介于 3 到 6 美元，则可以使用关键字 BETWEEN … AND …。另外，按价格升序排序，则使用关键字 ORDER BY。

SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 3 AND 6 ORDER BY prod_price;

题目中指定需要使用关键字 AND，则需要将价格条件替换成 prod_price >= 3 和 prod_price <= 6 两个条件，然后将其串联。

SELECT prod_name, prod_price FROM Products WHERE prod_price >= 3 AND prod_price <= 6 ORDER BY prod_price;

SQL15 纠错2

描述

供应商表 Vendors 有字段供应商名称 vend_name、供应商国家 vend_country、供应商省份 vend_state

vend_name	vend_country	vend_state
apple	USA	CA
vivo	CNA	shenzhen
huawei	CNA	xian

问题

修改正确下面 sql，使之正确返回

SELECT vend_name 
FROM Vendors 
ORDER BY vend_name 
WHERE vend_country = 'USA' AND vend_state = 'CA';

示例结果

结果返回 vend_name

vend_name
apple

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);
INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('beef noodle king','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

解答

主要是一处错误，搞错了关键字 WHERE 和 ORDER BY 的先后顺序，正确的 SQL 语句格式为：

SELECT [列名] FOMR [表名] WHERE [条件] ORDER BY [列名];

SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA' ORDER BY vend_name;

SQL16 检索产品名称和描述（一）

描述

Products表

prod_name	prod_desc
a0011	usb
a0019	iphone13
b0019	gucci t-shirts
c0019	gucci toy
d0019	lego toy

问题

编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称

示例结果

返回产品名称和产品描述

prod_name	prod_desc
c0019	gucci toy
d0019	lego toy

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

解答

模糊查询，主要产品描述中含有 toy 一词，就需要返回这时候需要用到模糊查询关键字 LIKE 然后是通配符，题目中并没有明确说明 toy 前后包含多少个字符，所以用 %。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%'

SQL17 检索产品名称和描述（二）

描述

Products 表

prod_name	prod_desc
a0011	usb
a0019	iphone13
b0019	gucci t-shirts
c0019	gucci toy
d0019	lego toy

问题

编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。

示例结果

返回产品名称和产品描述

prod_name	prod_desc
a0011	usb
a0019	iphone13
b0019	gucci t-shirts

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

解答

这题就是 16 题的反面，那我们只要对 16 的查询语句进行取反即可，此时添加一个关键字 NOT 即可。此外，还需要按“产品名称”对结果排序，使用关键字 ORDER BY 即可。但是需要注意 WHERE 和 ORDER BY 关键字的先后顺序。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc NOT LIKE '%toy%' ORDER BY prod_name;

SQL18 检索产品名称和描述（三）

描述

Products 表

问题

编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。

prod_name	prod_desc
a0011	usb
a0019	iphone13
b0019	gucci t-shirts
c0019	gucci toy
d0019	lego carrots toy

示例结果

返回产品名称和产品描述

prod_name	prod_desc
d0019	lego carrots toy

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego carrots toy');

解答

题目中明确要求使用 LIKE 和 AND 关键字，那么可以将条件拆分为两个模糊查询，一个是描述中含有 toy 的产品，一个是描述中含有 carrots 的产品，然后利用关键字 AND 将两个条件串联就可以了。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';

SQL19 检索产品名称和描述（四）

描述

Products 表

prod_name	prod_desc
a0011	usb
a0019	iphone13
b0019	gucci t-shirts
c0019	gucci toy
d0019	lego toy carrots

问题

编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

示例结果

返回产品名称和产品描述

prod_name	prod_desc
d0019	lego toy carrots

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy carrots ');

解答

题目中已经进行了提示，要查询产品描述中以先后顺序同时出现 toy 和 carrots 的产品，但没有说他们两者之间存在的字符以及 toy 前面和 carrots 后边所包含的字符，那么需要使用通配符 % ，得到最终的结果 %toy%carrots%。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%carrots%';

SQL20 别名

描述

别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。有表 Vendors 代表供应商信息，vend_id 供应商 id、vend_name 供应商名称、vend_address 供应商地址、vend_city 供应商城市。

vend_id	vend_name	vend_address	vend_city
a001	tencent cloud	address1	shenzhen
a002	huawei cloud	address2	dongguan
a003	aliyun cloud	address3	hangzhou
a003	netease cloud	address4	guangzhou

问题

编写 SQL 语句，从 Vendors 表中检索 vend_id、vend_name、vend_address 和 vend_city，将 vend_name 重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address 重命名为 vaddress，按供应商名称对结果进行升序排序。

示例结果

返回 vend_id 供应商 id、vname 供应商名称、vaddress 供应商地址、vcity 供应商城市。

vend_id	vname	vaddress	vcity
a003	aliyun cloud	address3	hangzhou
a002	huawei cloud	address2	dongguan
a003	netease cloud	address4	guangzhou
a001	tencent cloud	address1	shenzhen

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT '供应商id',
  `vend_name` VARCHAR(255) NOT NULL COMMENT '供应商名称',
  `vend_address` VARCHAR(255) NOT NULL COMMENT '供应商地址',
  `vend_city` VARCHAR(255) NOT NULL COMMENT '供应商城市'
);
INSERT INTO `Vendors` VALUES ('a001','tencent cloud','address1','shenzhen'),
('a002','huawei cloud','address2','dongguan'),
('a003','aliyun cloud','address3','alibaba');

解答

SQL 中，要对列取别名，需要用到关键字 AS，使用格式如下：

列名 AS 别名

通过对题目中的三个字段取别名，然后使用 ORDER BY 关键字，按照供应商名称列对结果进行升序排序。

SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity FROM Vendors ORDER BY vend_name;

此外，AS 其实可要可不要，你也可以写成以下的方式，在列名之后跟上别名即可，实现的效果和上面语句一致。

SELECT vend_id, vend_name vname, vend_address vaddress, vend_city vcity FROM Vendors ORDER BY vend_name;

SQL21 打折

描述

我们的示例商店正在进行打折促销，所有产品均降价 10%。Products 表包含 prod_id产品 id、prod_price 产品价格

问题

编写 SQL语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）

示例结果

返回产品 id prod_id、产品价格 prod_price、销售价格 sale_price

prod_id	prod_price	sale_price
a0011	9.49	8.541
a0019	600	540
b0019	1000	900

示例解析

sale_price 的价格是 prod_price 的 90%

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES ('a0011',9.49),
('a0019',600),
('b0019',1000);

解答

此题考察了取别名以及如何在 SQL 中直接使用算数运算，sale_price 不是表中的数据，而是通过 prod_price 而来，所以需要通过关键字 AS 来进行取别名，最终的实现语句如下。

SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price FROM Products;

SQL22 顾客登录名

描述

我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。

给出 Customers 表如下：

cust_id	cust_name	cust_contact	cust_city
a1	Andy Li	Andy Li	Oak Park
a2	Ben Liu	Ben Liu	Oak Park
a3	Tony Dai	Tony Dai	Oak Park
a4	Tom Chen	Tom Chen	Oak Park
a5	An Li	An Li	Oak Park
a6	Lee Chen	Lee Chen	Oak Park
a7	Hex Liu	Hex Liu	Oak Park

问题

编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

示例结果

返回顾客 id cust_id，顾客名称 cust_name，顾客登录名 user_login

cust_id	cust_name	user_login
a1	Andy Li	ANOAK
a2	Ben Liu	BEOAK
a3	Tony Dai	TOOAK
a4	Tom Chen	TOOAK
a5	An Li	ANOAK
a6	Lee Chen	LEOAK
a7	Hex Liu	HEOAK

示例解析

例如，登录名是 ANOAK（Andy Li，居住在 Oak Park）

示例

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 '客户姓名',
	cust_contact VARCHAR(255) NOT NULL COMMENT '客户联系人',
	cust_city VARCHAR(255) NOT NULL COMMENT '客户城市'
);
INSERT `Customers` VALUES ('a1','Andy Li','Andy Li','Oak Park'),('a2','Ben Liu','Ben Liu','Oak Park'),('a3','Tony Dai','Tony Dai','Oak Park'),('a4','Tom Chen','Tom Chen','Oak Park'),('a5','An Li','An Li','Oak Park'),('a6','Lee Chen','Lee Chen','Oak Park'),('a7','Hex Liu','Hex Liu','Oak Park');

解答

根据题意以及示例结果，可以看到登录名全是大写，所以需要用到函数 upper()，然后需要将顾客联系人前两个字符和所在城市的前三个字符相拼接，则需要用到函数 concat()，再接着就是需要从 cust_name 和 cust_city 中截取字符串，则需要用到 substring()，最后则是通过将字符串拼接转换之后取别名为 user_login，需要用到关键字 AS。以上提到的三个函数用法如下：

upper(字符串)：将字符串中所有字符转换为大写。
substring(字符串, 起始位置, 截取的字符数)，需要注意的是起始位置是从 1 开始的。
concat(字符串 1, 字符串 2, 字符串 3, …)

SELECT cust_id, cust_name, upper(concat(substring(cust_name, 1, 2), substring(cust_city, 1, 3))) AS user_login FROM Customers;

SQL23 返回 2020 年 1 月的所有订单的订单号和订单日期

描述

Orders 订单表

order_num	order_date
a0001	2020-01-01 00:00:00
a0002	2020-01-02 00:00:00
a0003	2020-01-01 12:00:00
a0004	2020-02-01 00:00:00
a0005	2020-03-01 00:00:00

问题

编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序

【示例结果】

返回订单号 order_num，和 order_date 订单时间

order_num	order_date
a0001	2020-01-01 00:00:00
a0003	2020-01-01 12:00:00
a0002	2020-01-02 00:00:00

示例解析

a0001、a0002、a0003 时间属于 2020 年 1 月

示例

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
	order_num VARCHAR(255) NOT NULL COMMENT '订单号',
	order_date TIMESTAMP NOT NULL COMMENT '订单日期'
);
INSERT `Orders` VALUES ('a0001','2020-01-01 00:00:00'),
('a0002','2020-01-02 00:00:00'),
('a0003','2020-01-01 12:00:00'),
('a0004','2020-02-01 00:00:00'),
('a0005','2020-03-01 00:00:00');

解答

其实我们依然可以将本题看做模糊查询，只要订单日期满足对应条件即可，可以使用关键字 WHERE … LIKE … 来实现。

SELECT order_num, order_date FROM Orders WHERE order_date LIKE '2020-01%' ORDER BY order_date;

此外，SQL 中也提供了对于日期的操作函数 date_format()，用于返回日期的一部分。

SELECT order_num, order_date FROM Orders WHERE date_format(order_date, '%Y-%m') = '2020-01' ORDER BY order_date;

另外，我们还可以单独提取出日期中的年份和月份，然后将两个条件串联，筛选出同时满足两个条件的数据。

SELECT order_num, order_date FROM Orders WHERE year(order_date) = 2020 AND month(order_date) = 1 ORDER BY order_date;

SQL24 确定已售出产品的总数

描述

OrderItems 表代表售出的产品，quantity 代表售出商品数量。

quantity
10
100
1000
10001
2
15

问题

编写 SQL 语句，确定已售出产品的总数。

示例结果

返回 items_ordered 列名，表示已售出商品的总数。

items_ordered
11128

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES (10),(100),(1000),(10001),(2),(15);

解答

考察求和函数以及取别名两个知识点，取别名需要关键字 AS，而且可要可不要。

而求和函数则是 SUM(列名) ，它会统计列中所有记录的综合。

SELECT SUM(quantity) items_ordered FROM OrderItems;

SQL25 确定已售出产品项 BR01 的总数

描述

OrderItems 表代表售出的产品，quantity 代表售出商品数量，产品项为 prod_id。

quantity	prod_id
10	AR01
100	AR10
1000	BR01
10001	BR010

问题

修改创建的语句，确定已售出产品项（prod_id）为 "BR01" 的总数。

示例结果

返回商品项已订购订单数

items_ordered
1000

示例解析

已订购商品 BR01 的数量 quantity 为 1000。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	quantity INT(16) NOT NULL COMMENT '商品数量',
	prod_id VARCHAR(255) NOT NULL COMMENT '商品项'
);
INSERT `OrderItems` VALUES (10,'AR01'),(100,'AR10'),(1000,'BR01'),(10001,'BR010');

解答

主要考察的知识点：

求和函数：SUM()
取别名：AS
条件查询：WHERE

需要注意的是 SQL 语句中关键字的先后顺序，否则可能会导致语句出错。

SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE prod_id = 'BR01';

SQL26 确定 Products 表中价格不超过 10 美元的最贵产品的价格

描述

Products 表

prod_price
9.49
600
1000

问题

编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。

示例结果

返回 max_price

max_price
9.49

示例解析

返回十元以下最高价格 max_price。

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_price` DOUBLE NOT NULL COMMENT '产品价格'
);
INSERT INTO `Products` VALUES (9.49),
(600),
(1000);

解答

考察知识点：

条件查询：使用关键字 WHERE，将产品价格不超过 10 美元的产品筛选出来。
MAX(列名)：找出列中的最大值。
取别名：通过关键字 AS 将不超过 10 美元的产品中价格最高的记录筛选出来后重命名。

SELECT MAX(prod_price) AS max_price FROM Products WHERE prod_price <= 10;

SQL27 返回每个订单号各有多少行数

描述

OrderItems 表包含每个订单的每个产品

order_num
a002
a002
a002
a004
a007

问题

编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines 对结果进行升序排序。

示例结果

返回订单号 order_num 和对应订单号的行数 order_lines

order_num	order_lines
a004	1
a007	1
a002	3

示例解析

订单号 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');

解答

考察知识点：

COUNT(列名)：返回指定列的值的数目。
AS：取别名。
GROUP BY：根据指定列或者表达式的值将行进行分组。
ORDER BY：根据尾随的列名进行排序，ASC 表示正序，也是默认排序，DESC 表示倒序。

SELECT order_num, COUNT(order_num) AS order_lines FROM OrderItems GROUP BY order_num ORDER BY order_lines;

SQL28 每个供应商成本最低的产品

描述

有Products表，含有字段prod_price代表产品价格，vend_id代表供应商id

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

问题

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

示例结果

返回供应商 id vend_id 和对应供应商成本最低的产品 cheapest_item。

vend_id	cheapest_item
a0019	0.1
b0019	20
a0011	100

示例解析

例如 b0019 成本最低的价格是 20，且最后根据成本价格排序返回依次是 a0019、b0019、a0011。

示例

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);

解答

要找出各个供应商中成本最低的产品，则需要通过关键字 GROUP BY 来进行分组，然后借助函数 MIN() 找出 prod_price 中最小的值，接着取别名为 cheapest_item，最后则是按照找出的各供应商中的成本最低产品 cheapest_item 利用关键字 ORDER BY 进行升序排序。

SELECT vend_id, MIN(prod_price) AS cheapest_item FROM Products GROUP BY vend_id ORDER BY cheapest_item;

SQL29 返回订单数量总和不小于100的所有订单的订单号

描述

OrderItems 代表订单商品表，包括：订单号order_num 和订单数量 quantity。

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

问题

请编写 SQL 语句，返回订单数量总和不小于 100 的所有订单号，最后结果按照订单号升序排序。

示例结果

返回 order_num 订单号。

order_num
a1
a2
a4

示例解析

订单号 a1、a2、a4 的 quantity 总和都大于等于 100，按顺序为 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);

解答

条件查询，只是此时不再是过滤指定的行，而是需要过滤分组，所以这个时候不能再使用关键字 WHERE，而是需要使用到关键字 HAVING，它通常是和关键字 GROUP BY 连用。另外需要注意的是各个关键字之间的先后顺序，先是 GROUP BY，紧接着是 HAVING，最后才是 ORDER BY。

SELECT order_num FROM OrderItems GROUP BY order_num HAVING SUM(quantity) >= 100 ORDER BY order_num;

SQL30 计算总和

描述

OrderItems表代表订单信息，包括字段：订单号 order_num 和 item_price 商品售出价格、quantity 商品数量。

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);

解答

以上几题都已经将知识点讲过了，这题只是综合运用，主要涉及如下：

SUM()：对同一产品的总价求和。
AS：取别名。
GROUP BY：按照列进行分组。
HAVING：与 GROUP BY 联合使用从而实现条件过滤。
ORDER BY：按列进行排序。

SELECT order_num, SUM(item_price * quantity) AS total_price FROM OrderItems GROUP BY order_num HAVING total_price >= 1000 ORDER BY order_num;

SQL31 纠错3

描述

OrderItems 表含有 order_num 订单号

order_num
a002
a002
a002
a004
a007

问题

将下面代码修改正确后执行

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

示例结果

返回订单号 order_num 和出现的次数 items

order_num	items
a002	3

示例解析

由于订单号 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');

解答

最主要的错误在于 GROUP BY 后边跟着的是统计结果，其次在于 HAVING 后边的 COUNT() 其实是可以不用再次计算的，可以直接利用已经统计出的结果。第二处不算错误，但是改了之后能提高 SQL 语句所执行的效率。

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING items >= 3 ORDER BY items, order_num;

SQL32 返回购买价格为 10 美元或以上产品的顾客列表

描述

OrderItems 表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders 表代表订单信息表，含有顾客 id：cust_id 和订单号：order_num

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 美元或以上产品的顾客列表，结果无需排序。
注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。

示例结果

返回顾客 id cust_id

cust_id
cust10

示例解析

cust10 顾客下单的订单为 a1，a1 的售出价格大于等于 10

示例

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 '售出价格'
  );
  INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);

  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'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');

解答

题目已经提示使用子查询，则先用最简单的条件查询从 OrderItems 表中找出订单价格不低于 10 美元的订单，接着从筛选出的结果中再次筛选出对应订单的顾客 id，需要注意的一点是对顾客 id 去重，需要使用到关键字 DISTINCT。

SELECT DISTINCT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE item_price >= 10)

SQL33 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

描述

表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date

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" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

示例结果

返回顾客 id cust_id 和定单日期 order_date。

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

示例解析

产品 id 为 "BR01" 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的下单时间分别为 2022-01-01 00:00:00 和 2022-01-01 00:01:00

示例

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

解答

使用子查询，先从 OrderItems 表中查询出 prod_id 为 BR01 的记录，然后再从 Orders 表中筛选出 order_num 为子查询结果集中的记录，最后按照 order_date 进行排序即可。主要是通过对条件查询语句的嵌套使用，从而实现多重筛选。

SELECT cust_id, order_date FROM Orders WHERE order_num in (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01') ORDER BY order_date;

SQL34 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

描述

你想知道订购 BR01 产品的日期，有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期order_date；Customers表含有 cust_email 顾客邮件和 cust_id 顾客 id

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 为顾客 id，cust_email 为顾客 email

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

问题

返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

示例结果

返回顾客 email cust_email

cust_email
cust10@cust.com
cust1@cust.com

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的顾客email 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');

解答

多重条件查询的过滤，只要细心一点，就能做出来。拆分为 3 个条件查询后，从内向外依次查询，然后基于上一层查询结果再做条件过滤。

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

SQL35 返回每个顾客不同订单的总金额

描述

我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

OrderItems 表代表订单信息，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、顾客 id：cust_id

order_num	cust_id
a0001	cust10
a0002	cust1
a0003	cust1
a0013	cust2

问题

编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

提示：你之前已经使用 SUM() 计算订单总数。

示例结果

返回顾客 id cust_id 和 total_order 下单总额

cust_id	total_ordered
cust2	2242
cust1	1300
cust10	1050
cust2	104

示例解析

cust2 在 Orders 里面的订单 a0013，a0013 的售出价格是 2 售出数量是 1121，总额是 2242，最后返回 cust2 的支付总额是 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 ('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'),('a0003','cust1'),('a0013','cust2');

解答

题目要求利用子查询，则实现如下。

SELECT cust_id, (SELECT SUM(item_price * quantity) FROM OrderItems WHERE OrderItems.order_num = Orders.order_num) AS total_ordered FROM Orders ORDER BY total_ordered DESC;

此外我们可以使用内连接方式：利用 SUM() 函数进行求和，求出每个顾客的订单总数，然后通过条件查询找出两个表中 order_num 相同记录的并通过 cust_id 分组，最后则是将求和的订单总数倒序排列。

SELECT cust_id, SUM(item_price * quantity) AS total_ordered FROM OrderItems, Orders WHERE OrderItems.order_num = Orders.order_num GROUP BY cust_id ORDER BY total_ordered DESC;

SQL36 从 Products 表中检索所有的产品名称以及对应的销售总数

描述

Products 表中检索所有的产品名称：prod_name、产品 id：prod_id

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola

OrderItems 代表订单商品表，订单产品：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 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity) 检索）。

示例结果

返回产品名称 prod_name 和产品售出数量总和

prod_name	quant_sold
egg	105
sockets	1300
coffee	1121
cola	34

示例解析

prod_name 是 cola 的 prod_id 为 a0003，quantity 总量为 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);

解答

解法类似于 35 题，同样主要有两种方式，第一种是通过内连接的方式。

SELECT prod_name, SUM(quantity) AS quant_sold FROM OrderItems, Products WHERE Products.prod_id = OrderItems.prod_id GROUP BY prod_name;

第二种则是通过子查询的方式。

SELECT prod_name, (SELECT SUM(quantity) FROM OrderItems WHERE OrderItems.prod_id = Products.prod_id) FROM Products;

SQL37 返回顾客名称和相关订单号

描述

Customers 表有字段顾客名称 cust_name、顾客 id cust_id

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

Orders订单信息表，含有字段order_num订单号、cust_id顾客id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

问题

编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法，一个使用简单的等联结语法，另外一个使用 INNER JOIN。

示例结果

cust_name 代表用户名称 cust_name 和订单号 order_num。

cust_name	order_num
an	a5
andy	a1
ben	a2
hex	a7
tom	a4
tony	a3

示例解析

顾客名称为 an 的 cust_id 为 cust221，他的订单号为 a5。

示例

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

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

解答

考察 SQL 中的 INNER JOIN，其实也就是 JOIN。主要用于筛选出两个表中的交集部分。

使用语法如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

因此，此题使用 INNER JOIN 实现的方法如下。

SELECT cust_name, Orders.order_num FROM Customers INNER JOIN Orders ON Orders.cust_id = Customers.cust_id ORDER BY cust_name;

此外，也可以使用最常用的 WHERE 来进行联接。

SELECT cust_name, order_num FROM Customers, Orders WHERE Customers.cust_id = Orders.cust_id ORDER BY cust_name;

SQL38 返回顾客名称和相关订单号以及每个订单的总价

描述

Customers 表有字段，顾客名称：cust_name、顾客 id：cust_id

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

Orders 订单信息表，含有字段，订单号：order_num、顾客 id：cust_id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

OrderItems 表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

order_num	quantity	item_price
a1	1000	10
a2	200	10
a3	10	15
a4	25	50
a5	15	25
a7	7	7

问题

除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

示例结果

返回顾客名称 cust_name、订单号 order_num、订单总额 OrderTotal

cust_name	order_num	OrderTotal
an	a5	375
andy	a1	10000
ben	a2	2000
hex	a7	49
tom	a4	1250
tony	a3	150

示例解析

例如顾客名称 cust_name 为 an 的顾客的订单 a5 的订单总额为 quantity*item_price = 15 * 25 = 375，最后以 cust_name 和 order_num 来进行升序排序。

示例

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

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 `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  quantity INT(16) NOT NULL COMMENT '商品数量',
  item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);

解答

SQL 语句先后顺序：

SELECT  ……
FROM ……
WHERE ……
GROUP BY ……
ORDER BY ……

书写 SQL 语句时，一定要遵守以上关键字的先后顺序。然后根据题意将各个条件组合即可。

SELECT cust_name, Orders.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id AND Orders.order_num = OrderItems.order_num
GROUP BY Customers.cust_name, Orders.order_num
ORDER BY cust_name, Orders.order_num;

SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

描述

表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客id和订单日期 order_date

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

问题

提示：这一次使用联结和简单的等联结语法。

示例结果

返回顾客 id cust_id 和定单日期 order_date

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

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的下单时间分别为 2022-01-01 00:00:00 和 2022-01-01 00:01:00

示例

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

解答

多个条件的组合查询，先查询出产品 id 为 BR01 的 order_num，然后从查询出的结果列中再去筛选出 Orders 表中 order_num，最后则是正序排序即可。

SELECT cust_id, order_date 
FROM Orders, (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01') AS result
WHERE result.order_num = Orders.order_num
ORDER BY order_date;

SQL40 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

描述

有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date；Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id

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 为顾客 id，cust_email 为顾客 email

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

问题

返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法。

示例结果

返回顾客 email cust_email

cust_email
cust10@cust.com
cust1@cust.com

示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的顾客 email 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');

解答

多个内联接的组合，先是筛选出 OrderItems 和 Orders 表中 order_num 相同的列，然后根据筛选出的结果列中的 cust_id 从 Customers 表中找对应的顾客信息即可。

SELECT  
    cust_email
FROM
    Customers JOIN Orders ON
    Orders.cust_id = Customers.cust_id   
    JOIN OrderItems  ON OrderItems.prod_id = 'BR01' AND OrderItems.order_num = Orders.order_num;

SQL41 确定最佳顾客的另一种方式（二）

描述

OrderItems 表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems 表有订单号 order_num 和 item_price 商品售出价格、quantity 商品数量

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 顾客 id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

顾客表 Customers 有字段 cust_id 客户 id、cust_name 客户姓名

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

问题

编写 SQL 语句，返回订单总价不小于 1000 的客户名称和总额（OrderItems 表中的 order_num）。

提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

示例结果

cust_name	total_price
andy	1050
ben	1319
tom	2242

示例解析

总额（item_price 乘以 quantity）大于等于 1000 的订单号，例如 a2 对应的顾客id 为 cust1，cust1 的顾客名称 cust_name 是 ben，最后返回 ben 作为 order_num a2 的quantity * item_price 总和的结果 1319。

示例

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

解答

主要考察的知识点：

取别名：AS 关键字
求和：SUM()
GROUP BY 和 HAVING 同时使用用于过滤结果
排序：ORDER BY 关键字

主要考察对多个 SQL 关键字的运用，同时还需要注意 SQL 中关键字的前后顺序。

SELECT cust_name, SUM(item_price * quantity) AS total_price
FROM OrderItems, Orders, Customers
WHERE OrderItems.order_num = Orders.order_num AND Orders.cust_id = Customers.cust_id
GROUP BY cust_name
HAVING total_price >= 1000
ORDER BY total_price;

SQL42 检索每个顾客的名称和所有的订单号（一）

描述

Customers 表代表顾客信息含有顾客 id cust_id 和顾客名称 cust_name

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex

Orders 表代表订单信息含有订单号 order_num 和顾客 id cust_id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

问题

使用 INNER JOIN 编写 SQL 语句，检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），最后根据顾客姓名 cust_name 升序返回。

示例结果

返回顾客名称 cust_name 和订单号 order_num

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

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

解答

主要考察 INNER JOIN 的用法，其使用语法如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

要实现本题，利用上述语法填入对应字段名和表名即可，最后则是关键字 ORDER BY 的使用。

SELECT cust_name, Orders.order_num FROM Customers JOIN Orders ON Orders.cust_id = Customers.cust_id ORDER BY cust_name;

SQL43 检索每个顾客的名称和所有的订单号（二）

描述

Orders 表代表订单信息含有订单号 order_num和顾客 id cust_id

order_num	cust_id
a1	cust10
a2	cust1
a3	cust2
a4	cust22
a5	cust221
a7	cust2217

Customers 表代表顾客信息含有顾客 id cust_id 和顾客名称 cust_name

cust_id	cust_name
cust10	andy
cust1	ben
cust2	tony
cust22	tom
cust221	an
cust2217	hex
cust40	ace

问题

检索每个顾客的名称（Customers 表中的 cust_name）和所有的订单号（Orders 表中的 order_num），列出所有的顾客，即使他们没有下过订单。最后根据顾客姓名 cust_name 升序返回。

示例结果

返回顾客名称 cust_name 和订单号 order_num

cust_name	order_num
ace	NULL
an	a5
andy	a1
ben	a2
hex	a7
tom	a4
tony	a3

示例解析

基于两张表，返回订单号 a1 的顾客名称 andy 等人，没有下单的顾客 ace 也统计了进来。

示例

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'),('cust40','ace');

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

解答

根据题意，主要以 Customers 表中的列为主，然后取 Customers 和 Orders 中的交集。对于 Orders 表中不存在的列则取值 null。所以可以使用外联结中的 LEFT JION，其使用语法如下：

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

套用上述语法，填入对应表和列名即可实现，最后则是再加入简单的排序即可。

SELECT cust_name, Orders.order_num FROM Customers LEFT JOIN Orders On Orders.cust_id = Customers.cust_id ORDER BY cust_name;

SQL44 返回产品名称和与之相关的订单号

描述

Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id

prod_id	order_num
a0001	a105
a0002	a1100
a0002	a200
a0013	a1121
a0003	a10
a0003	a19
a0003	a5

问题

使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。

示例结果

返回产品名称 prod_name 和订单号 order_num

prod_name	order_num
coffee	a1121
cola	a5
cola	a19
cola	a10
egg	a105
sockets	a200
sockets	a1100
soda	NULL

示例解析

返回产品和对应实际支付订单的订单号，但是无实际订单的产品 soda 也返回，最后根据产品名称升序排序。

示例

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

解答

此题解法类似于 43 题，主要使用 OUTER JOIN 中的左联结 LEFT JOIN，主要清除相关语法，然后套用填入表名和列名即可。

SELECT prod_name, OrderItems.order_num FROM Products 
LEFT JOIN OrderItems ON OrderItems.prod_id = Products.prod_id
ORDER BY prod_name;

SQL45 返回产品名称和每一项产品的总订单数

描述

Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称

prod_id	prod_name
a0001	egg
a0002	sockets
a0013	coffee
a0003	cola
a0023	soda

OrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id

prod_id	order_num
a0001	a105
a0002	a1100
a0002	a200
a0013	a1121
a0003	a10
a0003	a19
a0003	a5

问题

使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。

示例结果

返回产品名称 prod_name 和订单号订单数 orders

prod_name	orders
coffee	1
cola	3
egg	1
sockets	2
soda	0

示例解析

返回产品和产品对应的实际支付的订单数，但是无实际订单的产品 soda 也返回，最后根据产品名称升序排序。

示例

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

解答

考察的知识点仍然是外连接，只不过加入了 COUNT() 函数用于分组统计，最后同样是简单的排序。

SELECT prod_name, COUNT(order_num) AS orders
FROM Products LEFT JOIN OrderItems ON OrderItems.prod_id = Products.prod_id
GROUP BY prod_name ORDER BY prod_name;

SQL46 列出供应商及其可供产品的数量

描述

有 Vendors 表含有 vend_id 供应商id.

vend_id
a0002
a0013
a0003
a0010

有 Products 表含有供应商 id 和供应产品 id

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）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT() 聚合函数来计算 Products 表中每种产品的数量，最后根据 vend_id 升序排序。

注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。

示例结果

返回供应商 id 和对应供应商供应的产品的个数

vend_id	prod_id
a0002	1
a0013	0
a0003	1
a0010	1

示例解析

供应商 a00013 供应的商品不在 Products 表中所以为 0，其他供应商供应的产品为 1 个。

示例

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

解答

利用 COUNT() 函数对 Vendors 中的 vend_id 分组并统计。

另外则是考察 LEFT JOIN，即题目中所说的 OUTER JOIN，其具体使用语法如下。

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SELECT Vendors.vend_id, COUNT(Products.prod_id) FROM Vendors LEFT JOIN Products ON Vendors.vend_id = Products.vend_id GROUP BY Vendors.vend_id ORDER BY Vendors.vend_id;

SQL47 将两个 SELECT 语句结合起来（一）

描述

表 OrderItems 包含订单产品信息，字段 prod_id 代表产品 id、quantity 代表产品数量

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

问题

将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。

示例结果

返回产品 id prod_id和产品数量 quantity

prod_id	quantity
a0002	100
BNBG	10002

示例解析

产品 id a0002 因为数量等于 100 被选取返回；BNBG 因为是以 BNBG 开头的产品所以返回；最后以产品 id 进行排序返回。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

解答

依照题意，其实我们利用一个 SELECT 然后搭配 WHERE 条件查询就能将满足题意的纪律筛选出来。但是为了练习关键字 UNION 的使用，可以将两个条件查询语句分开，然后用 UNION 联接起来，最后则是按照 prod_id 正序排列。

SELECT prod_id, quantity FROM OrderItems WHERE quantity = '100' UNION SELECT prod_id, quantity FROM OrderItems WHERE prod_id LIKE 'BNBG%' ORDER BY prod_id;

SQL48 将两个 SELECT 语句结合起来（二）

描述

表 OrderItems 包含订单产品信息，字段 prod_id 代表产品 id、quantity 代表产品数量。

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

问题

将两个 SELECT 语句结合起来，以便从 OrderItems 表中检索产品 id（prod_id）和 quantity。其中，一个 SELECT 语句过滤数量为 100 的行，另一个 SELECT 语句过滤 id 以 BNBG 开头的产品，最后按产品 id 对结果进行升序排序。
注意：这次仅使用单个 SELECT 语句。

示例结果

返回产品 id prod_id 和产品数量 quantity

prod_id	quantity
a0002	100
BNBG	10002

示例解析

产品 id a0002 因为数量等于 100 被选取返回；BNBG 因为是以 BNBG 开头的产品所以返回；最后以产品 id 进行排序返回。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

解答

根据题意仅使用单个 SELECT 语句，那就直接利用 WHERE 条件查询，然后将两个条件利用连接符 OR 并联即可。

SELECT prod_id, quantity FROM OrderItems WHERE prod_id LIKE 'BNBG%' OR quantity = '100' ORDER BY prod_id;

SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名称

描述

Products 表含有字段 prod_name 代表产品名称

prod_name
flower
rice
ring
umbrella

Customers 表代表顾客信息，cust_name 代表顾客名称

cust_name
andy
ben
tony
tom
an
lee
hex

问题

编写 SQL 语句，组合 Products 表中的产品名称（prod_name）和 Customers 表中的顾客名称（cust_name）并返回，然后按产品名称对结果进行升序排序。

示例结果

prod_name
an
andy
ben
flower
hex
lee
rice
ring
tom
tony
umbrella

示例解析

拼接 cust_name 和 prod_name 并根据结果升序排序

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('flower'),
('rice'),
('ring'),
('umbrella');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('andy'),('ben'),('tony'),('tom'),('an'),('lee'),('hex');

解答

同样考察 UNION 操作符，通过将两个 SELECT 语句的结果合并起来即可。

这里需要注意的是得出的结果是 prod_name，所以需要注意两个 SELECT 语句的先后顺序，再然后就是需要对查询出的记录按照 prod_name 正序排列。

SELECT prod_name FROM Products UNION SELECT cust_name FROM Customers ORDER BY prod_name;

SQL50 纠错4

描述

表 Customers 含有字段 cust_name 顾客名、cust_contact 顾客联系方式、cust_state 顾客州、cust_email 顾客 email

cust_name	cust_contact	cust_state	cust_email
cust10	8695192	MI	cust10@cust.com
cust1	8695193	MI	cust1@cust.com
cust2	8695194	IL	cust2@cust.com

问题

修正下面错误的 SQL

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL' ORDER BY cust_name;

示例结果

返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客 email：cust_email

cust_name	cust_contact	cust_email
cust1	8695193	cust1@cust.com
cust10	8695192	cust10@cust.com
cust2	8695194	cust2@cust.com

示例解析

返回住在 "IL" 和 "MI" 的顾客信息，最后根据顾客名称升序排序。

示例

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_name VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_contact VARCHAR(255) NOT NULL COMMENT '顾客联系方式',
    cust_state VARCHAR(255) NOT NULL COMMENT '顾客州',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','8695192','MI','cust10@cust.com'),('cust1','8695193','MI','cust1@cust.com'),('cust2','8695194','IL','cust2@cust.com');

解答

主要有两处错误，第一是 UNION 前多加了 ;，; 表示一条 SQL 的结束，加在此处不适合。

第二则是只主要对 cust_name 排序一次即可，放在 UNION 前的 ORDER BY 关键字显然位置不对。

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI'  
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL' ORDER BY cust_name;

致谢

感谢牛客网提供的题目列表。

posted @ 2023-06-07 17:16 村雨遥阅读(205) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

村雨遥

公众号：村雨遥； https://cunyu1943.site

《SQL 必知必会》全解析

前言

SQL1 从 Customers 表中检索所有的 ID

描述

问题

示例答案

示例

解答

SQL2 检索并列出已订购产品的清单

描述

问题

示例

解答

SQL3 检索所有列

描述

问题

示例结果

示例

解答

SQL4 检索顾客名称并且排序

描述

问题

示例结果

示例

解答

SQL5 对顾客ID和日期排序

描述

问题

示例答案

示例解析

示例

解答

SQL6 按照数量和价格排序

描述

问题

示例答案

示例

解答

SQL7 检查SQL语句

描述

问题

示例展示

示例

解答

SQL8 返回固定价格的产品

描述

问题

示例结果

示例

解答

SQL9 返回更高价格的产品

描述

问题

示例答案

示例

解答

SQL10 返回产品并且按照价格排序

描述

问题

示例结果

示例

解答

SQL11 返回更多的产品

描述

问题

示例结果

示例

解答

SQL12 检索供应商名称

描述

问题

示例答案

示例

解答

SQL13 检索并列出已订购产品的清单

描述

问题

示例答案