发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

《SQL 必知必会》全解析

自作多情是种病

12

主题

12

帖子

36

积分

新手上路

Rank: 1

积分
36
显示全部楼层
不要哀求，学会争取。若是如此，终有所获。
原文：https://mp.weixin.qq.com/s/zbOqyAtsWsocarsFIGdGgw
前言

你是否还在烦恼 SQL 该从何学起，或者学了 SQL 想找个地方练练手？好巧不巧，最近在工作之余登上牛客，发现了牛客不知道啥时候上线了SQL 必知必会的练习题。
《SQL 必知必会》作为麻省理工学院、伊利诺伊大学等众多大学的参考教材，由浅入深地讲解了SQL的基本概念和语法。涉及数据的排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容。实例丰富，方便查阅，可以说作为一个 CRUD BOY/GIRL 必读书目。
想着正好给它刷一遍，然后将自己刷题的一些想法总结下，于是有了今天这篇文章，希望能给需要的小伙伴一点点帮助。
SQL1 从 Customers 表中检索所有的 ID

描述

现有表Customers如下：
cust_idABC问题

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

返回 cust_id 列的内容
cust_idABC示例
  1. DROP TABLE IF EXISTS `Customers`;
  3. CREATE TABLE IF NOT EXISTS `Customers`(
  4.     cust_id VARCHAR(255) DEFAULT NULL
  5. );
  7. INSERT `Customers` VALUES ('A'),('B'),('C');
复制代码
解答

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

  • 第一种方式，选择特定列进行输出，这也是我们在工作中更加推荐使用的一种方式，将需要输出的列名全部描述出来。
  1. SELECT cust_id FROM Customers;
复制代码

  • 第二种方式，使用 * 对表中所有列进行输出，因为 Customers 表中仅有一列，所以可以使用该方式。但在日常工作中，就算查询结果列中包含了数据库表的所有字段，也不要直接使用 *.
  1. SELECT * FROM Customers;
复制代码
SQL2 检索并列出已订购产品的清单

描述

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

编写SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单。
示例
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         prod_id VARCHAR(255) NOT NULL COMMENT '商品id'
  4. );
  5. INSERT `OrderItems` VALUES ('a1'),('a2'),('a3'),('a4'),('a5'),('a6'),('a6')
复制代码
解答

要对结果去重，可以使用 DISTINCT 关键字。使用时，在后边跟上需要去重的字段即可保证这些去重字段的查询结果不重复。
  1. SELECT DISTINCT prod_id FROM OrderItems;
复制代码
此外还可以使用 GROUP BY 关键字，改关键字支持在去重的同时，同步返回其他字段的信息。
  1. SELECT prod_id FROM OrderItems GROUP BY prod_id;
复制代码
SQL3 检索所有列

描述

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

需要编写 SQL语句，检索所有列。
示例结果

返回所有列 cust_id 和 cust_name。
cust_idcust_namea1andya2bena3tonya4toma5ana6leea7hex示例
  1. DROP TABLE IF EXISTS `Customers`;
  2. CREATE TABLE IF NOT EXISTS `Customers`(
  3.         cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
  4.         cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
  5. );
  6. INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');
复制代码
解答

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

  • 使用 * 表示所有列。
  1. SELECT * FROM Customers;
复制代码

  • 将需要打印出的列详细列出。
  1. SELECT cust_id, cust_name FROM Customers;
复制代码
SQL4 检索顾客名称并且排序

描述

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

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

返回客户姓名 cust_name
cust_nametonytomleehexbenandyan示例
  1. DROP TABLE IF EXISTS `Customers`;
  2. CREATE TABLE IF NOT EXISTS `Customers`(
  3.         cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
  4.         cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
  5. );
  6. INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');
复制代码
解答

要对数据库中的数据进行排序，需要使用关键字 ORDER BY。此外，默认情况下，SQL 中列的结果默认是正序排列的，即实际情况下以下语句执行结果是一样的。
  1. SELECT cust_name FROM Customers;
复制代码
  1. SELECT cust_name FROM Customers ORDER BY cust_name ASC;
复制代码
而要对查询结果逆序输出，则需要用到 DESC 关键字，表示逆序输出。
  1. SELECT cust_name FROM Customers ORDER BY cust_name DESC;
复制代码
SQL5 对顾客ID和日期排序

描述

有 Orders 表
cust_idorder_numorder_dateandyaaaa2021-01-01 00:00:00andybbbb2021-01-01 12:00:00bobcccc2021-01-10 12:00:00dickdddd2021-01-11 00:00:00问题

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

返回 2 列，cust_id 和 order_num
cust_idorder_numandybbbbandyaaaabobccccdickdddd示例解析

首先根据 cust_id 进行排列，andy 在 bob 和 dick 前，再根据 order_date 进行排列，订单号 bbbb 的订单时间是 "2021-01-01 12:00:00" 大于订单号 aaaa 的时间 "2021-01-01 00:00:00"
示例
  1. DROP TABLE IF EXISTS `Orders`;
  2. CREATE TABLE IF NOT EXISTS `Orders` (
  3.   `cust_id` varchar(255) NOT NULL COMMENT '顾客 ID',
  4.   `order_num` varchar(255) NOT NULL COMMENT '订单号',
  5.   `order_date` timestamp NOT NULL COMMENT '订单时间'
  6. );
  7. INSERT INTO `Orders` VALUES ('andy','aaaa','2021-01-01 00:00:00'),
  8. ('andy','bbbb','2021-01-01 12:00:00'),
  9. ('bob','cccc','2021-01-10 12:00:00'),
  10. ('dick','dddd','2021-01-11 00:00:00');
复制代码
解答

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

  • ASC：正序输出，也是默认输出的情况。
  • DESC：倒序输出。
  1. SELECT cust_id, order_num FROM Orders ORDER BY cust_id, order_date DESC;
复制代码
SQL6 按照数量和价格排序

描述

假设有一个 OrderItems 表
quantityitem_price11001010032500问题

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

返回 quantity 和 item_price
示例
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems` (
  3.   `quantity` INT(64) NOT NULL COMMENT '数量',
  4.   `item_price` INT(64) NOT NULL COMMENT '订单价格'
  5. );
  6. INSERT INTO `OrderItems` VALUES (1,100),
  7. (10,1003),
  8. (2,500);
复制代码
解答

同样是查询语句，要对查询结果进行排序，则需要使用  ORDER BY 关键字，最后则是需要注意是正序还是倒序，题目中由多到少和由高到低都是属于倒序，所以需要使用关键字 DESC。
  1. SELECT quantity, item_price FROM OrderItems ORDER BY quantity DESC, item_price DESC;
复制代码
SQL7 检查SQL语句

描述

有 Vendors 表
vend_name海底捞小龙坎大龙燚问题

下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据 vend_name 逆序排列
  1. SELECT vend_name,
  2. FROM Vendors
  3. ORDER vend_name DESC;
复制代码
示例展示

返回 vend_name
vend_name海底捞小龙坎大龙燚示例
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称'
  4. );
  5. INSERT INTO `Vendors` VALUES ('海底捞'),
  6. ('小龙坎'),
  7. ('大龙燚');
复制代码
解答
  1. SELECT vend_name,
  2. FROM Vendors
  3. ORDER vend_name DESC;
复制代码
主要存在两处错误，首先是选取需要展示的列 vend_name 后，后面并没有跟上需要展示的其他列，所以此处多了一个 ,。另外，对选取列进行排序使用的关键字是 ORDER BY，而题目中只是给出了 ORDER，忘记了 BY，所以会导致查询结果错误。针对这两处错误改正后，得到的正确的语句如下所示。
  1. SELECT vend_name FROM Vendors ORDER BY vend_name DESC;
复制代码
SQL8 返回固定价格的产品

描述

有表 Products
prod_idprod_nameprod_pricea0018sockets9.49a0019iphone13600b0018gucci t-shirts1000问题

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

返回 prod_id 和 prod_name
prod_idprod_namea0018sockets示例
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3.   `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4.   `prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称',
  5.   `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
  6. );
  7. INSERT INTO `Products` VALUES ('a0018','sockets',9.49),
  8. ('a0019','iphone13',600),
  9. ('b0019','gucci t-shirts',1000);
复制代码
解答

查询符合指定条件的数据，可以使用关键字 WHERE 对条件进行限制，然后就是对条件的实现，这里可以使用两种方式。
第一种，直接利用 =，既然条件是需要价格为 9.49 美元的产品，则我们将价格列设置为 prod_price = 9.49 即可。
  1. SELECT prod_id, prod_name FROM Products WHERE prod_price = 9.49;
复制代码
第二种，除开 = 之外，我们也可以使用 in，表示价格只要在指定的数据之中，我们将价格列设置为 in (9.49) 即可，但是此时要注意不要忽略 ()，否则语句会报错。
  1. SELECT prod_id, prod_name FROM Products WHERE prod_price in (9.49);
复制代码
SQL9 返回更高价格的产品

描述

Products 表
prod_idprod_nameprod_pricea0018sockets9.49a0019iphone13600b0019gucci t-shirts1000问题

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

返回 prod_id 商品 id 和 prod_name 商品名称
prod_idprod_namea0018socketsa0019iphone13b0019gucci t-shirts示例
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称',
  5. `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
  6. );
  7. INSERT INTO `Products` VALUES ('a0011','usb',9.49),
  8. ('a0019','iphone13',600),
  9. ('b0019','gucci t-shirts',1000);
复制代码
解答

条件查询语句，查询格式：
SELECT [列名] FROM [表名] WHERE 判断条件 …
既然判断条件是要产品价格不低于 9 美元，则产品需要大于等于 9 美元，可以将产品价格设置为 prod_price >= 9 即可。
  1. SELECT prod_id, prod_name FROM Products WHERE prod_price >= 9;
复制代码
此外，我们还可以将 >= 拆分为两个条件，一个是 prod_price > 9，而另一个则是 prod_price = 9，然后满足任一条件即可，则利用关键字 OR 将两个条件进行连接。
  1. SELECT prod_id, prod_name FROM Products WHERE prod_price > 9 OR prod_price = 9;
复制代码
SQL10 返回产品并且按照价格排序

描述

有 Products 表
prod_idprod_nameprod_pricea0011egg3a0019sockets4b0019coffee15问题

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

返回商品名称 prod_name 和商品价格 prod_price
prod_nameprod_priceegg3sockets4注：不需要考虑商品价格相同的情况
示例
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称',
  5. `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
  6. );
  7. INSERT INTO `Products` VALUES ('a0011','egg',3),
  8. ('a0019','sockets',4),
  9. ('b0019','coffee',15);
复制代码
解答


条件查询，通过将条件拆分为 prod_price >= 3 和 prod_price = 3 AND prod_price = 100;[/code]
  1. SELECT prod_name, prod_price FROM Products WHERE prod_price >= 3 AND prod_price <=6 ORDER BY prod_price;
复制代码
SQL12 检索供应商名称

描述

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

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

返回供应商名称 vend_name
vend_nameapple示例
  1. SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA';
复制代码
解答

既然要筛选出加利福利亚州的提供商，则首要条件是满足 vend_state = 'CA'。另外，除开美国有加利福利亚州之外，有可能其他国家也有加利福利亚州，所以需要对国家也进行限定，设置条件为 vend_country = 'USA'。然后用关键字  AND 将条件串联，满足这两个条件的数据则进行输出。
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  4.         quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
  5. );
  6. INSERT `OrderItems` VALUES ('a1','105'),('a2','1100'),('a2','200'),('a4','1121'),('a5','10'),('a2','19'),('a7','5')
复制代码
SQL13 检索并列出已订购产品的清单

描述

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

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

返回商品 id prod_id、订单 order_num、数量 quantity。
order_numprod_idquantitya1BR01105a2BR021100a2BR02200a4BR031121示例解析

返回的结果中，数量满足大于等于 100，且满足 prod_id 是 "BR01"，“BR02”，“BR03" 中的任意一个。
示例
  1. SELECT DISTINCT order_num FROM OrderItems WHERE quantity >= 100;
复制代码
解答

多条件查询，将两个条件进行拆分，然后将两个查询条件进行串联即可。
数量满足大于等于 100，则设置为 quantyty >= 100。
产品 id 满足 "BR01"，“BR02”，“BR03” 中的任意一个，则使用关键字 in。
  1. SELECT DISTINCT order_num FROM OrderItems WHERE quantity > 100 OR quantity = 100;
复制代码
SQL14 返回所有价格在 3 美元到 6 美元之间的产品的名称和价格

描述

有表 Products
prod_idprod_nameprod_pricea0011egg3a0019sockets4b0019coffee15问题

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

返回商品名称 prod_name 和商品价格 prod_price
prod_nameprod_priceegg3sockets4注：不需要考虑价格相同时的排序问题
示例
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  4.   `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  5.   `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
  6. );
  7. INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
  8. ('vivo','CNA','shenzhen'),
  9. ('huawei','CNA','xian');
复制代码
解答

两个条件，首先是价格介于 3 到 6 美元，则可以使用关键字 BETWEEN … AND …。另外，按价格升序排序，则使用关键字 ORDER BY。
  1. SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA';
复制代码
题目中指定需要使用关键字 AND，则需要将价格条件替换成 prod_price >= 3 和 prod_price = 3 AND prod_price = 1000 ORDER BY order_num;[/code]SQL31 纠错3

描述

OrderItems 表含有 order_num 订单号
order_numa002a002a002a004a007问题

将下面代码修改正确后执行
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  4.         prod_id VARCHAR(255) NOT NULL COMMENT '商品号',
  5.         order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  6.         quantity INT(255) NOT NULL COMMENT '商品数量'
  7. );
  8. INSERT `OrderItems` VALUES ('BR01','a1','105'),('BR02','a2','1100'),('BR02','a2','200'),('BR03','a4','1121'),('BR017','a5','10'),('BR02','a2','19'),('BR017','a7','5')
复制代码
示例结果

返回订单号 order_num 和出现的次数 items
order_numitemsa0023示例解析

由于订单号 a002 出现了三次，所以返回3
示例
  1. SELECT order_num, prod_id, quantity FROM OrderItems WHERE quantity >= 100 AND prod_id IN ('BR01', 'BR02', 'BR03');
复制代码
解答

最主要的错误在于 GROUP BY 后边跟着的是统计结果，其次在于 HAVING 后边的 COUNT() 其实是可以不用再次计算的，可以直接利用已经统计出的结果。第二处不算错误，但是改了之后能提高 SQL 语句所执行的效率。
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称',
  5. `prod_price` INT(255) NOT NULL COMMENT '产品价格'
  6. );
  7. INSERT INTO `Products` VALUES ('a0011','egg',3),
  8. ('a0019','sockets',4),
  9. ('b0019','coffee',15);
复制代码
  1. SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 3 AND 6 ORDER BY prod_price;
复制代码
SQL32 返回购买价格为 10 美元或以上产品的顾客列表

描述

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

使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。
注意：你需要使用 OrderItems 表查找匹配的订单号（order_num），然后使用Order 表检索这些匹配订单的顾客 ID（cust_id）。
示例结果

返回顾客 id cust_id
cust_idcust10示例解析

cust10 顾客下单的订单为 a1，a1 的售出价格大于等于 10
示例
  1. SELECT prod_name, prod_price FROM Products WHERE prod_price >= 3 AND prod_price <= 6 ORDER BY prod_price;
复制代码
解答

题目已经提示使用子查询，则先用最简单的条件查询从 OrderItems 表中找出订单价格不低于 10 美元的订单，接着从筛选出的结果中再次筛选出对应订单的顾客 id，需要注意的一点是对顾客 id 去重，需要使用到关键字 DISTINCT。
  1. SELECT vend_name
  2. FROM Vendors
  3. ORDER BY vend_name
  4. WHERE vend_country = 'USA' AND vend_state = 'CA';
复制代码
SQL33 确定哪些订单购买了 prod_id 为 BR01 的产品（一）

描述

表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date
OrderItems 表
prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013Orders表
order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00问题

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。
示例结果

返回顾客 id cust_id 和定单日期 order_date。
cust_idorder_datecust102022-01-01 00:00:00cust12022-01-01 00:01:00示例解析

产品 id 为 "BR01" 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的下单时间分别为 2022-01-01 00:00:00 和 2022-01-01 00:01:00
示例
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  4.   `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  5.   `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
  6. );
  7. INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
  8. ('beef noodle king','USA','CA'),
  9. ('vivo','CNA','shenzhen'),
  10. ('huawei','CNA','xian');
复制代码
解答

使用子查询，先从 OrderItems 表中查询出 prod_id 为 BR01 的记录 ，然后再从 Orders 表中筛选出 order_num 为子查询结果集中的记录，最后按照 order_date 进行排序即可。主要是通过对条件查询语句的嵌套使用，从而实现多重筛选。
  1. SELECT [列名] FOMR [表名] WHERE [条件] ORDER BY [列名];
复制代码
SQL34 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

描述

你想知道订购 BR01 产品的日期，有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期order_date；Customers表含有 cust_email 顾客邮件和 cust_id 顾客 id
OrderItems 表
prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013Orders 表
order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email
cust_idcust_emailcust10cust10@cust.comcust1cust1@cust.comcust2cust2@cust.com问题

返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。
提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。
示例结果

返回顾客 email cust_email
cust_emailcust10@cust.comcust1@cust.com示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的顾客email cust_email 分别是：cust10@cust.com 、cust1@cust.com
示例
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  4.   `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  5.   `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
  6. );
  7. INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
  8. ('beef noodle king','USA','CA'),
  9. ('vivo','CNA','shenzhen'),
  10. ('huawei','CNA','xian');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 个条件查询后，从内向外依次查询，然后基于上一层查询结果再做条件过滤。
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
  5. );
  6. INSERT INTO `Products` VALUES ('a0011','usb'),
  7. ('a0019','iphone13'),
  8. ('b0019','gucci t-shirts'),
  9. ('c0019','gucci toy'),
  10. ('d0019','lego toy');
复制代码
SQL35 返回每个顾客不同订单的总金额

描述

我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。
OrderItems 表代表订单信息，OrderItems 表有订单号：order_num 和商品售出价格：item_price、商品数量：quantity。
order_numitem_pricequantitya000110105a000211100a00021200a001321121a0003510a0003119a000375Orders 表订单号：order_num、顾客 id：cust_id
order_numcust_ida0001cust10a0002cust1a0003cust1a0013cust2问题

编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。
提示：你之前已经使用 SUM() 计算订单总数。
示例结果

返回顾客 id cust_id 和 total_order 下单总额
cust_idtotal_orderedcust22242cust11300cust101050cust2104示例解析

cust2 在 Orders 里面的订单 a0013，a0013 的售出价格是 2 售出数量是 1121，总额是 2242，最后返回 cust2 的支付总额是 2242。
示例
  1. SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%'
复制代码
解答

题目要求利用子查询，则实现如下。
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
  5. );
  6. INSERT INTO `Products` VALUES ('a0011','usb'),
  7. ('a0019','iphone13'),
  8. ('b0019','gucci t-shirts'),
  9. ('c0019','gucci toy'),
  10. ('d0019','lego toy');
复制代码
此外我们可以使用内连接方式：利用 SUM() 函数进行求和，求出每个顾客的订单总数，然后通过条件查询找出两个表中 order_num 相同记录的并通过 cust_id 分组，最后则是将求和的订单总数倒序排列。
  1. SELECT prod_name, prod_desc FROM Products WHERE prod_desc NOT LIKE '%toy%' ORDER BY prod_name;
复制代码


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

描述

Products 表中检索所有的产品名称：prod_name、产品 id：prod_id
prod_idprod_namea0001egga0002socketsa0013coffeea0003colaOrderItems 代表订单商品表，订单产品：prod_id、售出数量：quantity
prod_idquantitya0001105a00021100a0002200a00131121a000310a000319a00035问题

编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity) 检索）。
示例结果

返回产品名称 prod_name 和产品售出数量总和
prod_namequant_soldegg105sockets1300coffee1121cola34示例解析

prod_name 是 cola 的 prod_id 为 a0003，quantity 总量为 34，返回结果无需排序。
示例
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
  5. );
  6. INSERT INTO `Products` VALUES ('a0011','usb'),
  7. ('a0019','iphone13'),
  8. ('b0019','gucci t-shirts'),
  9. ('c0019','gucci toy'),
  10. ('d0019','lego carrots toy');
复制代码
解答

解法类似于 35 题，同样主要有两种方式，第一种是通过内连接的方式。
  1. SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';
复制代码
第二种则是通过子查询的方式。
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_name` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_desc` VARCHAR(255) NOT NULL COMMENT '产品名称'
  5. );
  6. INSERT INTO `Products` VALUES ('a0011','usb'),
  7. ('a0019','iphone13'),
  8. ('b0019','gucci t-shirts'),
  9. ('c0019','gucci toy'),
  10. ('d0019','lego toy carrots ');
复制代码
SQL37 返回顾客名称和相关订单号

描述

Customers 表有字段顾客名称 cust_name、顾客 id cust_id
cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hexOrders订单信息表，含有字段order_num订单号、cust_id顾客id
order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217问题

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

cust_name 代表用户名称 cust_name 和订单号 order_num。
cust_nameorder_numana5andya1bena2hexa7toma4tonya3示例解析

顾客名称为 an 的 cust_id 为 cust221，他的订单号为 a5。
示例
  1. SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%carrots%';
复制代码
解答

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

使用语法如下：
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_id` VARCHAR(255) NOT NULL COMMENT '供应商id',
  4.   `vend_name` VARCHAR(255) NOT NULL COMMENT '供应商名称',
  5.   `vend_address` VARCHAR(255) NOT NULL COMMENT '供应商地址',
  6.   `vend_city` VARCHAR(255) NOT NULL COMMENT '供应商城市'
  7. );
  8. INSERT INTO `Vendors` VALUES ('a001','tencent cloud','address1','shenzhen'),
  9. ('a002','huawei cloud','address2','dongguan'),
  10. ('a003','aliyun cloud','address3','alibaba');
复制代码
因此，此题使用 INNER JOIN 实现的方法如下。
  1. 列名 AS 别名
复制代码
此外，也可以使用最常用的 WHERE 来进行联接。
  1. SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity FROM Vendors ORDER BY vend_name;
复制代码
SQL38 返回顾客名称和相关订单号以及每个订单的总价

描述

Customers 表有字段，顾客名称：cust_name、顾客 id：cust_id
cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hexOrders 订单信息表，含有字段，订单号：order_num、顾客 id：cust_id
order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217OrderItems 表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price
order_numquantityitem_pricea1100010a220010a31015a42550a51525a777问题

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

返回顾客名称 cust_name、订单号 order_num、订单总额 OrderTotal
cust_nameorder_numOrderTotalana5375andya110000bena22000hexa749toma41250tonya3150示例解析

例如顾客名称 cust_name 为 an 的顾客的订单 a5 的订单总额为 quantity*item_price = 15 * 25 = 375，最后以 cust_name 和 order_num 来进行升序排序。
示例
  1. SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%carrots%';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 语句先后顺序：
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
  4. `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
  5. );
  6. INSERT INTO `Products` VALUES ('a0011',9.49),
  7. ('a0019',600),
  8. ('b0019',1000);
复制代码
书写 SQL 语句时，一定要遵守以上关键字的先后顺序。然后根据题意将各个条件组合即可。
  1. SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price FROM Products;
复制代码
SQL39 确定哪些订单购买了 prod_id 为 BR01 的产品（二）

描述

表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客id和订单日期 order_date
OrderItems 表
prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013Orders 表
order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00问题

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。
提示：这一次使用联结和简单的等联结语法。
示例结果

返回顾客 id cust_id 和定单日期 order_date
cust_idorder_datecust102022-01-01 00:00:00cust12022-01-01 00:01:00示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的下单时间分别为 2022-01-01 00:00:00 和 2022-01-01 00:01:00
示例
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  4.   `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  5.   `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
  6. );
  7. INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
  8. ('beef noodle king','USA','CA'),
  9. ('vivo','CNA','shenzhen'),
  10. ('huawei','CNA','xian');
复制代码
解答

多个条件的组合查询，先查询出产品 id 为 BR01 的 order_num，然后从查询出的结果列中再去筛选出 Orders 表中 order_num，最后则是正序排序即可。
  1. SELECT cust_id, cust_name, upper(concat(substring(cust_name, 1, 2), substring(cust_city, 1, 3))) AS user_login FROM Customers;
复制代码
SQL40 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）

描述

有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date；Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id
OrderItems 表
prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013Orders 表
order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email
cust_idcust_emailcust10cust10@cust.comcust1cust1@cust.comcust2cust2@cust.com问题

返回购买 prod_id 为BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。
提示：涉及到 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id，但是必须使用 INNER JOIN 语法。
示例结果

返回顾客 email cust_email
cust_emailcust10@cust.comcust1@cust.com示例解析

产品 id 为 BR01 的订单 a0001 和 a002 的下单顾客 cust10 和 cust1 的顾客 email cust_email 分别是：cust10@cust.com 、cust1@cust.com
示例
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名称',
  4.   `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend国家',
  5.   `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
  6. );
  7. INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
  8. ('beef noodle king','USA','CA'),
  9. ('vivo','CNA','shenzhen'),
  10. ('huawei','CNA','xian');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 表中找对应的顾客信息即可。
  1. SELECT order_num, order_date FROM Orders WHERE order_date LIKE '2020-01%' ORDER BY order_date;
复制代码
SQL41 确定最佳顾客的另一种方式（二）

描述

OrderItems 表代表订单信息，确定最佳顾客的另一种方式是看他们花了多少钱，OrderItems 表有订单号 order_num 和 item_price 商品售出价格、quantity 商品数量
order_numitem_pricequantitya110105a211100a21200a421121a5510a2119a775Orders 表含有字段 order_num 订单号、cust_id 顾客 id
order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217顾客表 Customers 有字段 cust_id 客户 id、cust_name 客户姓名
cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hex问题

编写 SQL 语句，返回订单总价不小于 1000 的客户名称和总额（OrderItems 表中的 order_num）。
提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。
示例结果

cust_nametotal_priceandy1050ben1319tom2242示例解析

总额（item_price 乘以 quantity）大于等于 1000 的订单号，例如 a2 对应的顾客id 为 cust1，cust1 的顾客名称 cust_name 是 ben，最后返回 ben 作为 order_num a2 的quantity * item_price 总和的结果 1319。
示例
  1. SELECT order_num, order_date FROM Orders WHERE date_format(order_date, '%Y-%m') = '2020-01' ORDER BY order_date;
复制代码
解答

主要考察的知识点：

  • 取别名：AS 关键字
  • 求和：SUM()
  • GROUP BY 和 HAVING 同时使用用于过滤结果
  • 排序：ORDER BY 关键字
主要考察对多个 SQL 关键字的运用，同时还需要注意 SQL 中关键字的前后顺序。
  1. SELECT order_num, order_date FROM Orders WHERE year(order_date) = 2020 AND month(order_date) = 1 ORDER BY order_date;
复制代码
SQL42 检索每个顾客的名称和所有的订单号（一）

描述

Customers 表代表顾客信息含有顾客 id cust_id 和顾客名称 cust_name
cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hexOrders 表代表订单信息含有订单号 order_num 和顾客 id cust_id
order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217问题

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

返回顾客名称 cust_name 和订单号 order_num
cust_nameorder_numana5andya1bena2hexa7toma4tonya3示例
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         quantity INT(16) NOT NULL COMMENT '商品数量'
  4. );
  5. INSERT `OrderItems` VALUES (10),(100),(1000),(10001),(2),(15);
复制代码
解答

主要考察 INNER JOIN 的用法，其使用语法如下：
  1. DROP TABLE IF EXISTS `Vendors`;
  2. CREATE TABLE IF NOT EXISTS `Vendors` (
  3.   `vend_id` VARCHAR(255) NOT NULL COMMENT '供应商id',
  4.   `vend_name` VARCHAR(255) NOT NULL COMMENT '供应商名称',
  5.   `vend_address` VARCHAR(255) NOT NULL COMMENT '供应商地址',
  6.   `vend_city` VARCHAR(255) NOT NULL COMMENT '供应商城市'
  7. );
  8. INSERT INTO `Vendors` VALUES ('a001','tencent cloud','address1','shenzhen'),
  9. ('a002','huawei cloud','address2','dongguan'),
  10. ('a003','aliyun cloud','address3','alibaba');
复制代码
要实现本题，利用上述语法填入对应字段名和表名即可，最后则是关键字 ORDER BY 的使用。
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         quantity INT(16) NOT NULL COMMENT '商品数量',
  4.         prod_id VARCHAR(255) NOT NULL COMMENT '商品项'
  5. );
  6. INSERT `OrderItems` VALUES (10,'AR01'),(100,'AR10'),(1000,'BR01'),(10001,'BR010');
复制代码
SQL43 检索每个顾客的名称和所有的订单号（二）

描述

Orders 表代表订单信息含有订单号 order_num和顾客 id cust_id
order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217Customers 表代表顾客信息含有顾客 id cust_id 和 顾客名称 cust_name
cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hexcust40ace问题

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

返回顾客名称 cust_name 和订单号 order_num
cust_nameorder_numaceNULLana5andya1bena2hexa7toma4tonya3示例解析

基于两张表，返回订单号 a1 的顾客名称 andy 等人，没有下单的顾客 ace 也统计了进来。
示例
  1. SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE prod_id = 'BR01';
复制代码
解答

根据题意，主要以 Customers 表中的列为主，然后取 Customers 和 Orders 中的交集。对于 Orders 表中不存在的列则取值 null。所以可以使用外联结中的 LEFT JION，其使用语法如下：
  1. DROP TABLE IF EXISTS `Products`;
  2. CREATE TABLE IF NOT EXISTS `Products` (
  3. `prod_price` DOUBLE NOT NULL COMMENT '产品价格'
  4. );
  5. INSERT INTO `Products` VALUES (9.49),
  6. (600),
  7. (1000);
复制代码
套用上述语法，填入对应表和列名即可实现，最后则是再加入简单的排序即可。
  1. SELECT MAX(prod_price) AS max_price FROM Products WHERE prod_price <= 10;
复制代码
SQL44 返回产品名称和与之相关的订单号

描述

Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称
prod_idprod_namea0001egga0002socketsa0013coffeea0003colaa0023sodaOrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id
prod_idorder_numa0001a105a0002a1100a0002a200a0013a1121a0003a10a0003a19a0003a5问题

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

返回产品名称 prod_name 和订单号 order_num
prod_nameorder_numcoffeea1121colaa5colaa19colaa10egga105socketsa200socketsa1100sodaNULL示例解析

返回产品和对应实际支付订单的订单号，但是无实际订单的产品 soda 也返回，最后根据产品名称升序排序。
示例
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  4. );
  5. INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');
复制代码
解答

此题解法类似于 43 题，主要使用 OUTER JOIN 中的左联结 LEFT JOIN，主要清除相关语法，然后套用填入表名和列名即可。
  1. SELECT order_num, COUNT(order_num) AS order_lines FROM OrderItems GROUP BY order_num ORDER BY order_lines;
复制代码
SQL45 返回产品名称和每一项产品的总订单数

描述

Products 表为产品信息表含有字段 prod_id 产品 id、prod_name 产品名称
prod_idprod_namea0001egga0002socketsa0013coffeea0003colaa0023sodaOrderItems 表为订单信息表含有字段 order_num 订单号和产品 id prod_id
prod_idorder_numa0001a105a0002a1100a0002a200a0013a1121a0003a10a0003a19a0003a5问题

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

返回产品名称 prod_name 和订单号订单数 orders
prod_nameorderscoffee1cola3egg1sockets2soda0示例解析

返回产品和产品对应的实际支付的订单数，但是无实际订单的产品 soda 也返回，最后根据产品名称升序排序。
示例
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  4. );
  5. INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');
复制代码
解答

考察的知识点仍然是外连接，只不过加入了 COUNT() 函数用于分组统计，最后同样是简单的排序。
  1. SELECT vend_id, MIN(prod_price) AS cheapest_item FROM Products GROUP BY vend_id ORDER BY cheapest_item;
复制代码
SQL46 列出供应商及其可供产品的数量

描述

有 Vendors 表含有 vend_id 供应商id.
vend_ida0002a0013a0003a0010有 Products 表含有供应商 id 和供应产品 id
vend_idprod_ida0001egga0002prod_id_iphonea00113prod_id_teaa0003prod_id_vivo phonea0010prod_id_huawei phone问题

列出供应商（Vendors 表中的 vend_id）及其可供产品的数量，包括没有产品的供应商。你需要使用 OUTER JOIN 和 COUNT() 聚合函数来计算 Products 表中每种产品的数量，最后根据 vend_id 升序排序。
注意：vend_id 列会显示在多个表中，因此在每次引用它时都需要完全限定它。
示例结果

返回供应商 id 和对应供应商供应的产品的个数
vend_idprod_ida00021a00130a00031a00101示例解析

供应商 a00013 供应的商品不在 Products 表中所以为 0，其他供应商供应的产品为 1 个。
示例
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  4.         quantity INT(255) NOT NULL COMMENT '商品数量'
  5. );
  6. INSERT `OrderItems` VALUES ('a1',105),('a2',200),('a4',1121),('a5',10),('a7',5);
复制代码
解答

利用 COUNT() 函数对 Vendors 中的 vend_id 分组并统计。
另外则是考察 LEFT JOIN，即题目中所说的 OUTER JOIN，其具体使用语法如下。
  1. SELECT order_num FROM OrderItems GROUP BY order_num HAVING SUM(quantity) >= 100 ORDER BY order_num;
复制代码
  1. DROP TABLE IF EXISTS `OrderItems`;
  2. CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.         order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  4.         item_price INT(16) NOT NULL COMMENT '售出价格',
  5.         quantity INT(16) NOT NULL COMMENT '商品数量'
  6. );
  7. INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);
复制代码
SQL47 将两个 SELECT 语句结合起来（一）

描述

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

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

返回产品 id prod_id和产品数量 quantity
prod_idquantitya0002100BNBG10002示例解析

产品 id a0002 因为数量等于 100 被选取返回；BNBG 因为是以 BNBG 开头的产品所以返回；最后以产品 id 进行排序返回。
示例
  1. SELECT order_num, SUM(item_price * quantity) AS total_price FROM OrderItems GROUP BY order_num HAVING total_price >= 1000 ORDER BY order_num;
复制代码
解答

依照题意，其实我们利用一个 SELECT 然后搭配 WHERE 条件查询就能将满足题意的纪律筛选出来。但是为了练习关键字 UNION 的使用，可以将两个条件查询语句分开，然后用 UNION 联接起来，最后则是按照 prod_id 正序排列。
  1. SELECT order_num, COUNT(*) AS items
  2. FROM OrderItems
  3. GROUP BY items
  4. HAVING COUNT(*) >= 3
  5. ORDER BY items, order_num;
复制代码
SQL48 将两个 SELECT 语句结合起来（二）

描述

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

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

返回产品 id prod_id 和产品数量 quantity
prod_idquantitya0002100BNBG10002示例解析

产品 id a0002 因为数量等于 100 被选取返回；BNBG 因为是以 BNBG 开头的产品所以返回；最后以产品 id 进行排序返回。
示例
  1. SELECT order_num, SUM(item_price * quantity) AS total_price FROM OrderItems GROUP BY order_num HAVING total_price >= 1000 ORDER BY order_num;
复制代码
解答

根据题意仅使用单个 SELECT 语句，那就直接利用 WHERE 条件查询，然后将两个条件利用连接符 OR 并联即可。
  1. SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;
复制代码
SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名称

描述

Products 表含有字段 prod_name 代表产品名称
prod_nameflowerriceringumbrellaCustomers 表代表顾客信息，cust_name 代表顾客名称
cust_nameandybentonytomanleehex问题

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

prod_nameanandybenflowerhexleericeringtomtonyumbrella示例解析

拼接 cust_name 和 prod_name 并根据结果升序排序
示例
  1. SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING items >= 3 ORDER BY items, order_num;
复制代码
解答

同样考察 UNION 操作符，通过将两个 SELECT 语句的结果合并起来即可。
这里需要注意的是得出的结果是 prod_name，所以需要注意两个 SELECT 语句的先后顺序，再然后就是需要对查询出的记录按照 prod_name 正序排列。
  1. DROP TABLE IF EXISTS `OrderItems`;
  2.   CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.     order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  4.     item_price INT(16) NOT NULL COMMENT '售出价格'
  5.   );
  6.   INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);
  8.   DROP TABLE IF EXISTS `Orders`;
  9.   CREATE TABLE IF NOT EXISTS `Orders`(
  10.     order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  11.     cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
  12.   );
  13.   INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');
复制代码
SQL50 纠错4

描述

表 Customers 含有字段 cust_name 顾客名、cust_contact 顾客联系方式、cust_state 顾客州、cust_email 顾客 email
cust_namecust_contactcust_statecust_emailcust108695192MIcust10@cust.comcust18695193MIcust1@cust.comcust28695194ILcust2@cust.com问题

修正下面错误的 SQL
  1. SELECT DISTINCT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE item_price >= 10)
复制代码
示例结果

返回顾客名称：cust_name、顾客联系方式：cust_contact、顾客 email：cust_email
cust_namecust_contactcust_emailcust18695193cust1@cust.comcust108695192cust10@cust.comcust28695194cust2@cust.com示例解析

返回住在 "IL" 和 "MI" 的顾客信息，最后根据顾客名称升序排序。
示例
  1. DROP TABLE IF EXISTS `OrderItems`;
  2.   CREATE TABLE IF NOT EXISTS `OrderItems`(
  3.     prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
  4.     order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  5.   );
  6.   INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');
  8.   DROP TABLE IF EXISTS `Orders`;
  9.   CREATE TABLE IF NOT EXISTS `Orders`(
  10.     order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  11.     cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
  12.     order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  13.   );
  14.   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');
复制代码
解答

主要有两处错误，第一是 UNION 前多加了 ;，; 表示一条 SQL 的结束，加在此处不适合。
第二则是只主要对 cust_name 排序一次即可，放在 UNION 前的 ORDER BY 关键字显然位置不对。
  1. SELECT cust_id, order_date FROM Orders WHERE order_num in (SELECT order_num FROM OrderItems WHERE prod_id = 'BR01') ORDER BY order_date;
复制代码
致谢

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

来源:https://www.cnblogs.com/cunyu1943/p/17462724.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

本帖子中包含更多资源

您需要 登录 才可以下载或查看，没有账号？立即注册

x

上一篇: 《SQL 必知必会》全解析

下一篇: 《SQL 必知必会》全解析

发表于 2023-6-7 22:13:12

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4