SQL必知必会-牛客刷题50道(适合MySQL初学者)

gk 文档中心 2022-05-29 08:43:59 49

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录

前言
- 🐴 1.检索数据
- - 🚀 1.1 从 Customers 表中检索所有的 ID
  - 🚀 1.2 检索并列出已订购产品的清单
  - 🚀 1.3 检索所有列
- 🐴 2.排序检索数据
- - 🚀 2.1 检索顾客名称并且排序
  - 🚀 2.2 对顾客ID和日期排序
  - 🚀 2.3 按照数量和价格排序
- 🐴 3.过滤数据
- - 🚀 3.1 返回固定价格的产品
  - 🚀 3.2 返回更高价格的产品
  - 🚀 3.3 返回产品并且按照价格排序
  - 🚀 3.4 返回更多的产品
- 🐴 4.高级数据过滤
- - 🚀 4.1 检索供应商名称
  - 🚀 4.2 检索并列出已订购产品的清单
  - 🚀 4.3 返回所有价格在 3美元到 6美元之间的产品的名称和价格
  - 🚀 4.4 纠错2
- 🐴 5.高级数据过滤
- - 🚀 5.1 检索产品名称和描述（一）
  - 🚀 5.2 检索产品名称和描述（二）
  - 🚀 5.3 检索产品名称和描述（三）
  - 🚀 5.4 检索产品名称和描述（四）
- 🐴 6.创建计算字段
- - 🚀 6.1 别名
  - 🚀 6.2 打折
- 🐴 7.使用函数处理数据
- - 🚀 7.1 顾客登录名
  - 🚀 7.2 返回 2020 年 1 月的所有订单的订单号和订单日期
- 🐴 8.汇总数据
- - 🚀 8.1 确定已售出产品的总数
  - 🚀 8.2 确定已售出产品项 BR01 的总数
  - 🚀 8.3 确定 Products 表中价格不超过 10 美元的最贵产品的价格
- 🐴 9.分组数据
- - 🚀 9.1 返回每个订单号各有多少行数
  - 🚀 9.2 每个供应商成本最低的产品
  - 🚀 9.3 返回订单数量总和不小于100的所有订单的订单号
  - 🚀 9.4 计算总和
  - 🚀 9.5 纠错3
- 🐴 10.使用子查询
- - 🚀 10.1 返回购买价格为 10 美元或以上产品的顾客列表
  - 🚀 10.2 确定哪些订单购买了 prod_id 为 BR01 的产品（一）
  - 🚀 10.3 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）
  - 🚀 10.4 返回每个顾客不同订单的总金额
  - 🚀 10.5 从 Products 表中检索所有的产品名称以及对应的销售总数
- 🐴 11.联结表
- - 🚀 11.1 返回顾客名称和相关订单号
  - 🚀 11.2 返回顾客名称和相关订单号以及每个订单的总价
  - 🚀 11.3 确定哪些订单购买了 prod_id 为 BR01 的产品（二）
  - 🚀 11.4 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）
  - 🚀 11.5 确定最佳顾客的另一种方式（二）
- 🐴 12.创建高级联结
- - 🚀 12.1 检索每个顾客的名称和所有的订单号（一）
  - 🚀 12.2 检索每个顾客的名称和所有的订单号（二）
  - 🚀 12.3 返回产品名称和与之相关的订单号
  - 🚀 12.4 返回产品名称和每一项产品的总订单数
  - 🚀 12.5 列出供应商及其可供产品的数量
- 🐴 13.组合查询
- - 🚀 13.1 将两个 SELECT 语句结合起来（一）
  - 🚀 13.2 将两个 SELECT 语句结合起来（二）
  - 🚀 13.3 组合 Products 表中的产品名称和 Customers 表中的顾客名称
  - 🚀 13.4 纠错4

前言

SQL每个人都要用，但是用来衡量产出的并不是SQL本身，你需要用这个工具，去创造其它的价值。

🐴 1.检索数据

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 1.1 从 Customers 表中检索所有的 ID

难度系数：🚩 入门🚀 建表语句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 |+---------+| A|| B|| C|+---------+🚀 问题编写 SQL 语句，从 Customers 表中检索所有的cust_id🚀 示例答案返回cust_id列的内容+---------+| cust_id |+---------+| A|| B|| C|+---------+🐴🐴 答案mysql> select cust_id from Customers;

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述表OrderItems含有非空的列prod_id代表商品id，包含了所有已订购的商品（有些已被订购多次）+---------+| prod_id |+---------+| a1      || a2      || a3      || a4      || a5      || a6      || a6      |+---------+🚀 问题编写SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单🚀 示例答案返回cust_id列的内容+---------+| prod_id |+---------+| a1      || a2      || a3      || a4      || a5      || a6      |+---------+6 rows in set (0.04 sec)🐴🐴 答案mysql> select distinct prod_id from OrderItems;

🚀 1.3 检索所有列

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述现在有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|+---------+-----------+7 rows in set (0.00 sec)🐴🐴 答案mysql> select * from Customers;

🐴 2.排序检索数据

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 2.1 检索顾客名称并且排序

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述有表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 |+-----------+7 rows in set (0.01 sec)🐴🐴 答案mysql> select cust_name from Customersorder by cust_name desc;

🚀 2.2 对顾客ID和日期排序

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述有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      |+---------+-----------+4 rows in set (0.00 sec)示例解析首先根据cust_id进行排列，andy在bob和dick前，再根据order_date进行排列，订单号bbbb的订单时间是 "2021-01-01 12:00:00"大于订单号aaaa的时间"2021-01-01 00:00:00"🐴🐴 答案mysql> select cust_id,order_numfrom Ordersorder by cust_id,order_date desc;

🚀 2.3 按照数量和价格排序

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述假设有一个OrderItems表+----------+------------+| quantity | item_price |+----------+------------+| 1 | 100 ||10 |1003 || 2 | 500 |+----------+------------+🚀 问题编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。🚀 示例答案返回quantity和item_price+----------+------------+| quantity | item_price |+----------+------------+|10 |1003 || 2 | 500 || 1 | 100 |+----------+------------+🐴🐴 答案mysql> select quantity,item_pricefrom OrderItemsorder by quantity desc,item_price desc;

🐴 3.过滤数据

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 3.1 返回固定价格的产品

难度系数：🚩 入门🚀 建表语句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);🚀 描述有表Products+---------+----------------+------------+| prod_id | prod_name      | prod_price |+---------+----------------+------------+| a0018   | sockets |9.49 || a0019   | iphone13| 600 || b0019   | gucci t-shirts |1000 |+---------+----------------+------------+🚀 问题从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品。🚀 示例答案返回prod_id和prod_name+---------+-----------+| prod_id | prod_name |+---------+-----------+| a0018   | sockets   |+---------+-----------+1 row in set (0.01 sec)🐴🐴 答案mysql> select prod_id,prod_name from Productswhere prod_price=9.49;

🚀 3.2 返回更高价格的产品

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述有表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      |+---------+----------------+| a0011   | usb     || a0019   | iphone13|| b0019   | gucci t-shirts |+---------+----------------+3 rows in set (0.01 sec)🐴🐴 答案mysql> select prod_id,prod_name from Productswhere prod_price >=9;

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

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述有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 |+-----------+------------+2 rows in set (0.01 sec)🐴🐴 答案mysql> select prod_name,prod_price from Products where prod_price between 3 and 6order by prod_price;

🚀 3.4 返回更多的产品

难度系数：🚩 入门🚀 建表语句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');🚀 描述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 |+-----------+3 rows in set (0.00 sec)🐴🐴 答案mysql> select distinct order_num from OrderItemswhere quantity >=100;

🐴 4.高级数据过滤

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 4.1 检索供应商名称

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述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     |+-----------+1 row in set (0.00 sec)🐴🐴 答案mysql> select vend_name from Vendorswhere vend_country='USA'and vend_state='CA';

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述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 |+-----------+---------+----------+4 rows in set (0.00 sec)示例解析返回的结果中，数量满足大于等于100，且满足prod_id 是"BR01"，“BR02”，“BR03"中的任意一个。🐴🐴 答案mysql> select order_num,prod_id,quantity from OrderItems where quantity >=100and prod_id in ('BR01','BR02','BR03');

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

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述有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 |+-----------+------------+2 rows in set (0.01 sec)🐴🐴 答案mysql> select prod_name,prod_price from Products where prod_price between 3 and 6order by prod_price;

🚀 4.4 纠错2

难度系数：🚩 入门🚀 建表语句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');🚀 描述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     |+-----------+1 row in set (0.00 sec)🐴🐴 答案mysql> SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA'ORDER BY vend_name;注解：order by 必须位于WHERE之后

🐴 5.高级数据过滤

SQL必知必会-牛客刷题50道(适合MySQL初学者)

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述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  |+-----------+-----------+2 rows in set (0.00 sec)🐴🐴 答案mysql> select prod_name,prod_desc from Productswhere prod_desc like '%toy%';

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述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 |+-----------+----------------+3 rows in set (0.01 sec)🐴🐴 答案mysql> select prod_name,prod_desc from Productswhere prod_desc not like '%toy%'order by prod_name;

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述Products表+-----------+------------------+| prod_name | prod_desc |+-----------+------------------+| a0011     | usb|| a0019     | iphone13  || b0019     | gucci t-shirts   || c0019     | gucci toy || d0019     | lego carrots toy |+-----------+------------------+🚀 问题编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。🚀 示例答案返回产品名称和产品描述+-----------+------------------+| prod_name | prod_desc |+-----------+------------------+| d0019     | lego carrots toy |+-----------+------------------+1 row in set (0.00 sec)🐴🐴 答案mysql> select prod_name,prod_descfrom Productswhere prod_desc like '%toy%' and prod_desc like '%carrots%';

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

难度系数：🚩 入门🚀 建表语句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 ');🚀 描述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 carrots toy |+-----------+------------------+1 row in set (0.00 sec)🐴🐴 答案mysql> select prod_name,prod_descfrom Productswhere prod_desc like '%toy%carrots%'；

🐴 6.创建计算字段

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 6.1 别名

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。有表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     | alibaba   |+---------+---------------+--------------+-----------+🚀 问题编写 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 | alibaba  || a002    | huawei cloud  | address2 | dongguan || a001    | tencent cloud | address1 | shenzhen |+---------+---------------+----------+----------+3 rows in set (0.00 sec)🐴🐴 答案mysql> select vend_id,vend_name as vname,vend_address as vaddress,vend_city as vcityfrom Vendors order by vend_name;

🚀 6.2 打折

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述我们的示例商店正在进行打折促销，所有产品均降价 10%。Products表包含prod_id产品id、prod_price产品价格+---------+------------+| prod_id | prod_price |+---------+------------+| a0011   |9.49 || a0019   | 600 || b0019   |1000 |+---------+------------+🚀 问题编写 SQL语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。🚀 示例答案返回产品id prod_id、产品价格prod_price、销售价格 sale_price+---------+------------+------------+| prod_id | prod_price | sale_price |+---------+------------+------------+| a0011   |9.49 |      8.541 || a0019   | 600 | 540 || b0019   |1000 | 900 |+---------+------------+------------+3 rows in set (0.00 sec)提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）🐴🐴 答案mysql> select prod_id,prod_price,prod_price*0.9 sale_pricefrom Products;

🐴 7.使用函数处理数据

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 7.1 顾客登录名

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。给出 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      |+---------+-----------+------------+7 rows in set (0.01 sec)示例解析例如，登录名是 ANOAK（Andy Li，居住在 Oak Park）🐴🐴 答案mysql> select cust_id,cust_name,upper(concat(left(cust_name,2),left(cust_city,3))) as user_loginfrom Customers;

🚀 7.2 返回 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');🚀 描述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 |+-----------+---------------------+3 rows in set (0.00 sec)示例解析a0001、a0002、a0003 时间属于2020年1月🐴🐴 答案mysql> select order_num, order_date from Orderswhere date_format(order_date, '%Y-%m')='2020-01'order by order_date;select order_num, order_datefrom Orderswhere year(order_date) = 2020 and month(order_date) = 1order by order_date;

🐴 8.汇总数据

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 8.1 确定已售出产品的总数

难度系数：🚩 入门🚀 建表语句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);🚀 描述OrderItems表代表售出的产品，quantity代表售出商品数量。+----------+| quantity |+----------+|10 ||      100 ||     1000 ||    10001 || 2 ||15 |+----------+🚀 问题编写 SQL 语句，确定已售出产品的总数🚀 示例答案返回items_ordered列名，表示已售出商品的总数。+---------------+| items_ordered |+---------------+|  11128 |+---------------+1 row in set (0.00 sec)🐴🐴 答案mysql> select sum(quantity) items_ordered from OrderItems;

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

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述OrderItems表代表售出的产品，quantity代表售出商品数量，产品项为prod_id。+----------+---------+| quantity | prod_id |+----------+---------+|10 | AR01    ||      100 | AR10    ||     1000 | BR01    ||    10001 | BR010   |+----------+---------+🚀 问题确定已售出产品项（prod_id）为"BR01"的总数。🚀 示例答案返回商品项已订购订单数+---------------+| items_ordered |+---------------+|   1000 |+---------------+1 row in set (0.00 sec)🐴🐴 答案mysql> select sum(quantity) items_ordered from OrderItemswhere prod_id='BR01';

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

难度系数：🚩🚩🚩 中等🚀 建表语句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);🚀 描述Products 表+------------+| prod_price |+------------+|9.49 || 600 ||1000 |+------------+🚀 问题编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。🚀 示例答案返回max_price+-----------+| max_price |+-----------+|      9.49 |+-----------+1 row in set (0.00 sec)示例解析：返回十元以下最高价格max_price。🐴🐴 答案mysql> select max(prod_price) as max_pricefrom Productswhere prod_price<=10;

🐴 9.分组数据

SQL必知必会-牛客刷题50道(适合MySQL初学者)

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

难度系数：🚩 入门🚀 建表语句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');🚀 描述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 |+-----------+-------------+3 rows in set (0.00 sec)示例解析订单号a002有3行订单记录也是最多的订单号故排在最后一位返回，相同订单行数的订单无需过多处理。🐴🐴 答案mysql> select order_num,count(*) order_linesfrom OrderItems group by order_numorder by order_lines;

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

难度系数：🚩🚩🚩 中等🚀 建表语句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);🚀 描述有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 |+---------+---------------+3 rows in set (0.00 sec)示例解析例如b0019成本最低的价格是20，且最后根据成本价格排序返回依次是a0019、b0019、a0011🐴🐴 答案mysql> select vend_id,min(prod_price) cheapest_itemfrom Productsgroup by vend_idorder by cheapest_item;

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

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述OrderItems代表订单商品表，包括：订单号order_num和订单数量quantity。+-----------+----------+| order_num | quantity |+-----------+----------+| a1 |      105 || a2 |      200 || a4 |     1121 || a5 |10 || a7 | 5 |+-----------+----------+🚀 问题请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。🚀 示例答案返回order_num订单号。+-----------+| order_num |+-----------+| a1 || a2 || a4 |+-----------+3 rows in set (0.00 sec)示例解析订单号a1、a2、a4的quantity总和都大于等于100，按顺序为a1、a2、a4。🐴🐴 答案mysql> select order_numfrom OrderItemsgroup by order_numhaving sum(quantity)>=100order by order_num;

🚀 9.4 计算总和

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述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+-----------+-------------+| order_num | total_price |+-----------+-------------+| a1 | 1050 || a2 | 1319 || a4 | 2242 |+-----------+-------------+3 rows in set (0.00 sec)🐴🐴 答案mysql> select order_num,sum(item_price*quantity) total_pricefrom OrderItemsgroup by order_numhaving sum(item_price*quantity)>=1000order by order_num;

🚀 9.5 纠错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');🚀 描述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 |+-----------+-------+1 row in set (0.00 sec)🐴🐴 答案mysql> SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_numHAVING COUNT(*) >= 3 ORDER BY items, order_num;

🐴 10.使用子查询

SQL必知必会-牛客刷题50道(适合MySQL初学者)

🚀 10.1 返回购买价格为 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表示订单商品表，含有字段订单号：order_num、订单价格：item_price；+-----------+------------+| order_num | item_price |+-----------+------------+| a1 |  10 || a2 |   1 || a2 |   1 || a4 |   2 || a5 |   5 || a2 |   1 || a7 |   7 |+-----------+------------+Orders表代表订单信息表，含有顾客id：cust_id和订单号：order_num+-----------+---------+| 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+-----------+-------+| order_num | items |+-----------+-------+| a002      |     3 |+-----------+-------+1 row in set (0.00 sec)示例解析:cust10顾客下单的订单为a1，a1的售出价格大于等于10🐴🐴 答案mysql> select cust_id from Orderswhere order_num in (select order_num from OrderItemswhere item_price >=10);

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

难度系数：🚩 入门🚀 建表语句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为产品id；+---------+-----------+| prod_id | order_num |+---------+-----------+| BR01    | a0001     || BR01    | a0002     || BR02    | a0003     || BR02    | a0013     |+---------+-----------+Orders表代表订单表有cust_id代表顾客id和订单日期order_date+-----------+---------+---------------------+| 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🐴🐴 答案mysql> select cust_id,order_date from  Orderswhere order_num in (select order_num from OrderItemswhere prod_id='BR01') order by order_date;

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述你想知道订购 BR01 产品的日期，有表OrderItems代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；Customers表含有cust_email 顾客邮件和cust_id顾客idOrderItems表+---------+-----------+| 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  |+-----------------+2 rows in set (0.00 sec)示例解析:产品id为BR01的订单a0001和a002的下单顾客cust10和cust1的顾客email cust_email分别是：cust10@cust.com 、cust1@cust.com🐴🐴 答案mysql> select cust_email from Customerswhere cust_id in (select cust_id from  Orderswhere order_num in  (select order_num from OrderItemswhere prod_id='BR01' ) );

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

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述我们需要一个顾客 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  || a0003     | cust1   || a0013     | cust2   |+-----------+---------+🚀 问题编写 SQL语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。提示：你之前已经使用 SUM()计算订单总数。🚀 示例答案返回顾客id cust_id和total_order下单总额+---------+---------------+| cust_id | total_ordered |+---------+---------------+| cust2   |   2242 || cust10  |   1050 || cust1   |    104 |+---------+---------------+3 rows in set (0.03 sec)示例解析:cust2在Orders里面的订单a0013，a0013的售出价格是2售出数量是1121，总额是2242，最后返回cust2的支付总额是2242。🐴🐴 答案mysql> select cust_id,(select SUM(item_price*quantity)FROM OrderItems a WHERE a.order_num=b.order_num) total_orderedfrom Orders bORDER BY total_ordered DESC;

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

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述 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 | quantity |+-----------+----------+| egg|      105 || sockets   |     1300 || coffee    |     1121 || cola      |34 |+-----------+----------+4 rows in set (0.01 sec)示例解析:prod_name是cola的prod_id为a0003，quantity总量为34，返回结果无需排序。🐴🐴 答案mysql> SELECT     p.prod_name,    tb.quantityFROM (    SELECT prod_id, SUM(quantity) quantity    FROM OrderItems    GROUP BY prod_id    ) tb,    Products pWHERE    tb.prod_id = p.prod_id;

🐴 11.联结表

SQL必知必会-牛客刷题50道(适合MySQL初学者)

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述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 |+-----------+-----------+6 rows in set (0.00 sec)示例解析:顾客名称为an的cust_id为cust221，他的订单号为a5。🐴🐴 答案等联结语法:mysql> select cust_name,order_numfrom Customers,Orderswhere Customers.cust_id=Orders.cust_idorder by cust_name,order_num;使用内联结mysql> select cust_name,order_numfrom CustomersINNER JOIN Orders ON Orders.cust_id=Customers.cust_idorder by cust_name,order_num;

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

难度系数：🚩🚩🚩 中等🚀 建表语句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);🚀 描述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 |+-----------+-----------+------------+6 rows in set (0.00 sec)示例解析:例如顾客名称cust_name为an的顾客的订单a5的订单总额为quantity*item_price = 15 * 25 = 375，最后以cust_name和order_num来进行升序排序。🐴🐴 答案mysql> SELECT     c.cust_name cust_name,    o.order_num order_num,    SUM(oi.quantity * oi.item_price) OrderTotalFROM Customers c INNER JOIN Orders o ON    c.cust_id = o.cust_id INNER JOIN OrderItems oi ON    o.order_num = oi.order_numGROUP BY    cust_name,    order_numORDER BY    cust_name,    order_num;

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

难度系数：🚩🚩🚩 中等🚀 建表语句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为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_dateOrderItems表+---------+-----------+| 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 |+---------+---------------------+2 rows in set (0.00 sec)示例解析:产品id为BR01的订单a0001和a002的下单顾客cust10和cust1的下单时间分别为2022-01-01 00:00:00和2022-01-01 00:01:00🐴🐴 答案mysql> select o.cust_id as cust_id, o.order_date as order_datefrom OrderItems oi join Orders o on oi.order_num=o.order_numwhere oi.prod_id='BR01'order by order_date;

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

难度系数：🚩🚩 简单🚀 建表语句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代表订单商品信息表，prod_id为产品id；Orders表代表订单表有cust_id代表顾客id和订单日期order_date；Customers表含有cust_email 顾客邮件和cust_id顾客idOrderItems表+---------+-----------+| 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  |+-----------------+2 rows in set (0.00 sec)示例解析:产品id为BR01的订单a0001和a002的下单顾客cust10和cust1的顾客email cust_email分别是：cust10@cust.com 、cust1@cust.com🐴🐴 答案mysql> SELECT     c.cust_email cust_emailFROM    OrderItems oi INNER JOIN Orders o ON    (oi.prod_id = 'BR01') AND    (oi.order_num = o.order_num) INNER JOIN Customers c ON    o.cust_id = c.cust_id;

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

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述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 |+-----------+-------------+3 rows in set (0.00 sec)示例解析:总额（item_price 乘以 quantity）大于等于1000的订单号，例如a2对应的顾客id为cust1，cust1的顾客名称cust_name是ben，最后返回ben作为order_num a2的quantity * item_price总和的结果1319。🐴🐴 答案mysql> select c.cust_name, sum(oi.item_price * oi.quantity) as total_pricefrom Orders o join Customers c on o.cust_id=c.cust_idjoin OrderItems oi on o.order_num=oi.order_numgroup by c.cust_namehaving total_price >= 1000order by total_price;

🐴 12.创建高级联结

SQL必知必会-牛客刷题50道(适合MySQL初学者)

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

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述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 |+-----------+-----------+6 rows in set (0.00 sec)🐴🐴 答案mysql> SELECT  c.cust_name cust_name,  o.order_num order_numFROM  Customers c  INNER JOIN Orders o ON c.cust_id = o.cust_idORDER BY  cust_name;

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

难度系数：🚩🚩🚩 中等🚀 建表语句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');🚀 描述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 |+-----------+-----------+7 rows in set (0.00 sec)示例解析:基于两张表，返回订单号a1的顾客名称andy等人，没有下单的顾客ace也统计了进来🐴🐴 答案mysql> SELECT  c.cust_name cust_name,  o.order_num order_numFROM  Customers c  LEFT JOIN Orders o ON c.cust_id = o.cust_idORDER BY  c.cust_name;

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述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      |+-----------+-----------+8 rows in set (0.00 sec)示例解析:返回产品和对应实际支付订单的订单号，但是无实际订单的产品soda也返回，最后根据产品名称升序排序。🐴🐴 答案mysql> SELECT p.prod_name, i.order_numFROM Products pLEFT OUTER JOIN OrderItems i USING(prod_id)ORDER BY p.prod_name

🚀 12.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 '产品名称');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');🚀 描述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 |+-----------+--------+5 rows in set (0.00 sec)示例解析:返回产品和产品对应的实际支付的订单数，但是无实际订单的产品soda也返回，最后根据产品名称升序排序。🐴🐴 答案mysql> select a1.prod_name,count(a2.order_num) prod_idfrom Products as a1 left join OrderItems as a2 on a1.prod_id = a2.prod_idgroup by a1.prod_nameorder by a1.prod_name;

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述有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 || a0003   |1 || a0010   |1 || a0013   |0 |+---------+---------+4 rows in set (0.00 sec)示例解析:供应商a00013供应的商品不在Products表中所以为0，其他供应商供应的产品为1个。🐴🐴 答案mysql> select vend_id,count(prod_id) prod_idfrom Vendorsleft join Products using(vend_id)group by Vendors.vend_idorder by vend_id;

🐴 13.组合查询

SQL必知必会-牛客刷题50道(适合MySQL初学者)

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

难度系数：🚩🚩🚩 中等🚀 建表语句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);🚀 描述表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    |+---------+----------+2 rows in set (0.01 sec)示例解析:产品id a0002因为数量等于100被选取返回；BNBG因为是以 BNBG 开头的产品所以返回；最后以产品id进行排序返回。🐴🐴 答案mysql> select prod_id,quantityfrom OrderItemswhere quantity=100union select prod_id,quantityfrom OrderItemswhere prod_id like 'BNBG%'order by prod_id;关键词：union用法：join---连接表，对列操作union--连接表，对行操作。union--将两个表做行拼接，同时自动删除重复的行。union all---将两个表做行拼接，保留重复的行。思路：筛选条件：like用法。where quantity=100，where prod_id like 'BNBG%'排序：放在最后进行排序，不能先排序在拼接。order by prod_id

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

难度系数：🚩🚩 简单🚀 建表语句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);🚀 描述表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    |+---------+----------+2 rows in set (0.00 sec)示例解析:产品id a0002因为数量等于100被选取返回；BNBG因为是以 BNBG 开头的产品所以返回；最后以产品id进行排序返回。🐴🐴 答案mysql> select    prod_id,    quantityfrom    OrderItemswhere    quantity = 100 or prod_id like "BNBG%"order by    prod_id;

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

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述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  |+-----------+11 rows in set (0.00 sec)示例解析:拼接cust_name和prod_name并根据结果升序排序🐴🐴 答案mysql> SELECT  prod_nameFROM  ProductsUNION ALLSELECT  cust_name prod_nameFROM  CustomersORDER BY  prod_name;

🚀 13.4 纠错4

难度系数：🚩🚩 简单🚀 建表语句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');🚀 描述表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  |+-----------+--------------+------------+-----------------+🚀 问题修正下面错误的SQLSELECT 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  |+-----------+--------------+-----------------+3 rows in set (0.00 sec)示例解析:返回住在"IL"和"MI"的顾客信息，最后根据顾客名称升序排序。🐴🐴 答案方法一：使用union子句进行双重查询，再将两次查询的结果结合在一起，但是由于使用了union子句，所以order by 子句只能使用一次且只能在最后一个 select 中使用；具体如下：select cust_name,cust_contact,cust_emailfrom Customerswhere cust_state = 'MI'unionselect cust_name,cust_contact,cust_emailfrom Customerswhere cust_state = 'IL'order by cust_name;方法二： 使用 or 进行多条件查询，具体如下：select cust_name,cust_contact,cust_emailfrom Customerswhere cust_state = 'MI'or cust_state = 'IL'order by cust_name;

SQL必知必会-牛客刷题50道(适合MySQL初学者)

体系化学习SQL，请关注CSDN博客
https://blog.csdn.net/weixin_41645135/category_11653817.html

在这里插入图片描述

SQL必知必会-牛客刷题50道(适合MySQL初学者)

文章目录

前言

🐴 1.检索数据

🚀 1.1 从 Customers 表中检索所有的 ID

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

🚀 1.3 检索所有列

🐴 2.排序检索数据

🚀 2.1 检索顾客名称并且排序

🚀 2.2 对顾客ID和日期排序

🚀 2.3 按照数量和价格排序

🐴 3.过滤数据

🚀 3.1 返回固定价格的产品

🚀 3.2 返回更高价格的产品

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

🚀 3.4 返回更多的产品

🐴 4.高级数据过滤

🚀 4.1 检索供应商名称

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

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

🚀 4.4 纠错2

🐴 5.高级数据过滤

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

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

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

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

🐴 6.创建计算字段

🚀 6.1 别名

🚀 6.2 打折

🐴 7.使用函数处理数据

🚀 7.1 顾客登录名

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

🐴 8.汇总数据

🚀 8.1 确定已售出产品的总数

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

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

🐴 9.分组数据

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

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

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

🚀 9.4 计算总和

🚀 9.5 纠错3

🐴 10.使用子查询

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

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

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

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

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

🐴 11.联结表

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

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

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

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

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

🐴 12.创建高级联结

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

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

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

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

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

🐴 13.组合查询

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

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

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

🚀 13.4 纠错4

标签