【牛客网】SQL必知必会题解

SQL 必知必会题解

地址：牛客网在线编程_SQL篇_SQL必知必会 (nowcoder.com)

检索数据

从 Customers 表中检索所有的 ID

现有表 Customers 如下：

【问题】编写 SQL 语句，从 Customers 表中检索所有的 cust_id

答案：

select cust_id from Customers;

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

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

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

答案：

select distinct prod_id from OrderItems;

检索所有列

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

【问题】需要编写 SQL 语句，检索所有列。

答案：

select cust_id, cust_name from Customers;

排序检索数据

检索顾客名称并且排序

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

【问题】从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。(其实就是降序)

答案：

select cust_name from Customers order by cust_name desc;

对顾客 ID 和日期排序

有 Orders 表

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

答案：

select cust_id, order_num from Orders order by cust_id, order_date desc;

按照数量和价格排序⭐️

假设有一个 OrderItems 表

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

select quantity, item_price from OrderItems order by quantity desc,item_price desc;

检查 SQL 语句

有 Vendors 表

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

SELECT vend_name, 
FROM Vendors 
ORDER vend_name DESC;

答案：少了个 by

select vend_name from Vendors order by vend_name desc;

过滤数据

返回固定价格的产品

有表 Products

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

答案：

select prod_id, prod_name from Products where prod_price = 9.49; 

返回更高价格的产品

Products 表

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

答案：

select prod_id, prod_name from Products where prod_price >= 9; 

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

有 Products 表

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

答案：

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

# 或者
SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price

返回更多的产品⭐️

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

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

答案：

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

知识点：

• group by: 字段进行分组可以实现不同且不重复的结果。

• HAVING 子句：用于过滤分组后的结果，只返回满足条件的分组，这里是筛选满足 SUM(quantity) >= 100 条件的分组。

• SUM() 函数：用于计算指定字段的总和，这里是计算 quantity 字段的总和。

高级数据过滤

检索供应商名称

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

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

答案：

select vend_name from Vendors where vend_country = 'USA' AND vend_state = 'CA'

注意：

• 字符串需要使用 单引号 或 双引号 包裹

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

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

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

答案：

select order_num, prod_id, quantity 
from OrderItems 
where prod_id in ('BR01', 'BR02','BR03') and quantity >= 100;

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

有表 Products

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

答案：

select prod_name, prod_price 
from Products 
where prod_price between 3 and 6 
order by prod_price asc

纠错 2

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

【问题】修改正确下面 sql，使之正确返回

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

答案：

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

知识点：考察 sql 关键词的顺序。

order by 的位置位于所有语句的倒数第二，倒数第一是 limit

用通配符进行过滤

SQL 通配符必须与 LIKE 运算符一起使用

• [^abc]：表示匹配除了 a、b、c 以外的任意一个字符。
• [^a-z]：表示匹配除了小写字母a到z以外的任意一个字符。
• [^0-9]：表示匹配除了数字0到9以外的任意一个字符。
• [^aeiou]：表示匹配除了元音字母 a、e、i、o、u 以外的任意一个字母。

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

Products 表

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

select prod_name, prod_desc 
from Products 
where prod_desc like '%toy%'

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

Products 表

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

答案：

select prod_name, prod_desc 
from Products 
where prod_desc not like '%toy%'
order by prod_name

知识点：

• not like 不包含

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

Products 表

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

答案：

select prod_name, prod_desc 
from Products 
# where prod_desc like '%carrots%toy%'
where prod_desc like '%toy%' and prod_desc like '%carrots%'

注意：and 的两边都要写上字段名

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

Products 表

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

答案：

select prod_name, prod_desc 
from Products 
where prod_desc like '%toy%carrots%'

创建计算字段

别名

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

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

答案：

select vend_id, vend_name as vname, vend_address as vaddress, vend_city as vcity
from Vendors
order by vname

# as 可以省略
SELECT vend_id, vend_name vname, vend_address vaddress, vend_city vcity
FROM Vendors
ORDER BY vname

打折⭐️

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

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

答案：

select prod_id, prod_price, prod_price * 0.9 as sale_price
from Products

使用函数处理数据

顾客登录名⭐️

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

给出 Customers 表如下：

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

答案：

select cust_id, cust_name, upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
from Customers

知识点：

关键词：substing,concat,upper

用法：

• 字符串的截取：substring(字符串，起始位置，截取字符数）
• 字符串的拼接：concat(字符串1，字符串2，字符串3,…)
• 字母大写：upper(字符串）

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

Orders 订单表

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

答案：

select order_num, order_date
from Orders
where month(order_date) = '01' and year(order_date) = '2020'
order by order_date

知识点：

SQL语法基础小结 | 日期和时间处理

其他解法

字符串匹配（近似查找法）

用 like 来查找

select order_num, order_date 
from Orders
where order_date like '2020-01%'
order by order_date

切割字符串

select order_num, order_date 
from Orders
where left(order_date, 7) = '2020-01'
order by order_date

字符串比较

select *
from Orders
where order_date >= '2020-01-01 00:00:00' and order_date <= '2020-01-31 23:59:59'
order by order_date;

用正则来查找（效率不如 like，能用 like 就用 like）

select order_num, order_date 
from Orders
where order_date regexp '2020-01'
order by order_date

时间函数匹配

利用date_format函数 （参考其中的匹配规则进行匹配）

select order_num, order_date 
from Orders
where date_format(order_date, '%Y-%m')='2020-01'
order by order_date

汇总数据

确定已售出产品的总数

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

【问题】编写 SQL 语句，确定已售出产品的总数。返回 items_ordered 列名，表示已售出商品的总数。

答案：

select sum(quantity) as items_ordered
from OrderItems

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

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

【问题】修改创建的语句，确定已售出产品项（prod_item）为 “BR01” 的总数。

select sum(quantity) as items_ordered
from OrderItems
where prod_id = 'BR01'

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

Products 表

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

答案：

select max(prod_price) as max_price
from Products
where prod_price <= 10

分组数据

GROUP BY：

• GROUP BY 子句将记录分组到汇总行中。
• GROUP BY 为每个组返回一个记录。
• GROUP BY 通常还涉及聚合COUNT，MAX，SUM，AVG 等。
• GROUP BY 可以按一列或多列进行分组。
• GROUP BY 按分组字段进行排序后，ORDER BY 可以以汇总字段来进行排序。

HAVING：

• HAVING 用于对汇总的 GROUP BY 结果进行过滤。
• HAVING 必须要与 GROUP BY 连用。
• WHERE 和 HAVING 可以在相同的查询中。

HAVING vs WHERE：

• WHERE：过滤指定的行，后面不能加聚合函数（分组函数）。
• HAVING：过滤分组，必须要与 GROUP BY 连用，不能单独使用。

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

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

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

select order_num, count(order_num) as order_lines
from OrderItems
group by order_num
order by order_lines

count(*),count(列名) 都可以，区别在于：count(列名) 是统计非 NULL 的行数；

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

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

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

答案：

select vend_id, min(prod_price) as cheapest_item 
from Products
group by vend_id
order by cheapest_item

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

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

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

答案：

select order_num
from OrderItems
group by order_num
having sum(quantity) >= 100
order by order_num

计算总和

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

【问题】编写 SQL 语句，根据订单号聚合，返回订单总价不小于 1000 的所有订单号，最后的结果按订单号进行升序排序。

select order_num, sum(item_price * quantity) as total_priceice
from OrderItems
group by order_num
having sum(item_price * quantity) >= 1000
order by order_num

纠错 3⭐️

OrderItems 表含有 order_num 订单号

【问题】将下面代码修改正确后执行

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

答案：

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
# HAVING COUNT(*) >= 3 这个也可
HAVING items >= 3
ORDER BY items, order_num;

知识点：执行顺序问题，别名的使用规则

1. SQL 查询的执行顺序是先执行 FROM、WHERE、GROUP BY、HAVING 和最后是 SELECT。

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING items >= 3 ORDER BY items, order_num; 这个使用别名不报错；

select order_num, sum(item_price * quantity) as total_price from OrderItems group by order_num having total_price >= 1000 order by order_num 但为什么这个使用别名就会报错

2. 在 SQL 查询中，使用别名通常分为两种情况：

• 使用别名在 SELECT 子句中展示计算结果：这种情况下，别名可以直接在其他子句中使用，如在 GROUP BY、HAVING、ORDER BY 等子句中使用别名进行条件过滤或排序。这就是为什么你的第一个查询中使用别名 items 在 HAVING 子句中不会报错的原因。
• 使用别名在 HAVING 子句中进行条件过滤：这种情况下，数据库管理系统不会识别别名，因为 HAVING 子句是在 GROUP BY 子句之后执行的，此时数据库还没有计算别名对应的结果。所以在 HAVING 子句中使用别名进行条件过滤是会报错的。

使用子查询

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

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

OrderItems 表

Orders 表

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

答案：

select cust_id
from Orders
where order_num in (select order_num
                   from OrderItems
                   group by order_num
                   having sum(item_price) >= 10)

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

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

OrderItems 表

Orders 表

【问题】

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

答案：

select cust_id, order_date
from Orders
where order_num in (select order_num
                   from OrderItems
                   where prod_id = 'BR01')

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

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

OrderItems 表

Orders 表

Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email

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

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

答案：

# 写法 1：子查询
select cust_email
from Customers 
where cust_id in (select cust_id
                   from Orders
                   where order_num in (select order_num
                                       from OrderItems
                                       where prod_id = 'BR01'))                                                                          
# 写法 2: 连接表（inner join）
SELECT c.cust_email
FROM OrderItems a,Orders b,Customers c
WHERE a.order_num = b.order_num AND b.cust_id = c.cust_id AND a.prod_id = 'BR01'

# 写法 3：连接表（left join）
SELECT c.cust_email
FROM Orders a LEFT JOIN
  OrderItems b ON a.order_num = b.order_num LEFT JOIN
  Customers c ON a.cust_id = c.cust_id
WHERE b.prod_id = 'BR01'                             

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

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

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

Orders 表订单号：order_num、顾客 id：cust_id

【问题】

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

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

答案：

# 写法 1：子查询
select o.cust_id as cust_id, tb.total_ordered as total_ordered
from (select order_num, sum(item_price * quantity) as total_ordered
    from OrderItems 
    group by order_num) as tb,
  Orders  o
where tb.order_num = o.order_num
order by total_ordered desc

# 写法 2：连接表
select b.cust_id, sum(a.quantity * a.item_price) as total_ordered
from OrderItems a, Orders b
where a.order_num = b.order_num
group by cust_id
order by total_ordered desc

知识点：

• 隐式内连接用 where
• 显示内连接用 on

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

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

OrderItems 代表订单商品表，订单产品：prod_id、售出数量：quantity

【问题】

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

答案：

# 写法 1：子查询
SELECT p.prod_name, tb.quant_sold
FROM (SELECT prod_id, Sum(quantity) AS quant_sold
    FROM OrderItems
    GROUP BY prod_id) AS tb,
  Products p
WHERE tb.prod_id = p.prod_id

# 写法 2：连接表
SELECT p.prod_name, Sum(o.quantity) AS quant_sold
FROM Products p,
  OrderItems o
WHERE p.prod_id = o.prod_id
GROUP BY p.prod_name（这里不能用 p.prod_id，会报错）

联结表

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

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

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

【问题】

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

# 方法一
select c.cust_name, o.order_num
from Customers c, Orders o
where c.cust_id = o.cust_id
order by c.cust_name

# 方法二：inner join
select c.cust_name, o.order_num
from Customers c join Orders o
on c.cust_id = o.cust_id
order by c.cust_name

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

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

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

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

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

答案：

# 等连接语法
select c.cust_name, o.order_num, sum(o2.quantity * o2.item_price) as OrderTotal
from Customers c, Orders o, OrderItems o2
where c.cust_id = o.cust_id and o.order_num = o2.order_num
group by c.cust_name, o.order_num
order by c.cust_name, o.order_num

知识点：

• 分组：通过在 GROUP BY 子句中使用 c.cust_name, o.order_num 来指定按 Customers 表中的 cust_name 和 Orders 表中的 order_num 对查询结果进行分组。这样，查询结果将按照顾客姓名和订单号进行分组，每个分组对应一个顾客的一个订单。（唯一的）

分组的目的是将具有相同 cust_name 和 order_num 字段值的数据行归为一组，并对每个组内的数据进行聚合操作。

一句话，select 中的字段要么都聚类，要么都不聚类

是否要分组取决于查询的需求。

如果不需要对数据进行聚合操作或按照某些字段进行统计计算，那么可以不使用分组。但在这个查询中，由于需要计算每个订单的总金额，所以需要按照 cust_name 和 order_num 字段进行分组。如果不使用 GROUP BY，查询结果将会是没有经过聚合计算的所有数据行，而不是按订单进行汇总后的结果。

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

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

OrderItems 表

Orders 表

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

答案：

select cust_id, order_date
from Orders
where order_num in (select order_num
                    from OrderItems
                    where prod_id = 'BR01')
order by order_date

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

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

OrderItems 表

Orders表

Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email

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

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

select cust_email
from Customers 
where cust_id in (select cust_id
                    from Orders
                    where order_num in (select order_num
                                        from OrderItems
                                        where prod_id = 'BR01'))

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

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

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

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

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

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

答案：

select cust_name, sum(item_price * quantity) as total_price
from Customers
inner join Orders using(cust_id)
inner join OrderItems using(order_num)
group by cust_name
having total_price >= 1000
order by total_price

知识点：

通过 using 关键字，根据 cust_id 将 Customers 表和 Orders 表连接，根据 order_num 将 Orders 表和 OrderItems 表连接。

using 与 on 区别

using 和 on 是在 SQL 中用于连接表的两种不同方式。

1. using：

• using 是一种简化的连接语法，用于连接两个表，并指定连接条件的字段。它适用于连接那些具有相同名称的字段的表。
• 在使用 using 进行连接时，只需在连接的表名之间使用 using 关键字，并在后面紧跟需要进行连接的字段名，这些字段必须在两个表中都存在且名称相同。
• 例如：INNER JOIN table2 using(column_name)。

2. on：

• on 是一种更灵活的连接语法，用于连接两个表，并指定连接条件的具体条件。它适用于连接那些具有不同名称或者复杂条件的字段的表。
• 在使用 on 进行连接时，需要在 on 关键字后面写入连接条件，连接条件可以是简单的等值条件，也可以是更复杂的逻辑条件。
• 例如：INNER JOIN table2 on table1.column_name = table2.column_name。

总结：

• using 适用于连接具有相同名称的字段的表，它简洁且适用于某些特定场景。
• on 更灵活，适用于连接具有不同名称或复杂条件的字段的表，它提供了更多的连接方式和条件控制。

创建高级联结

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

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

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

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

答案：

select cust_name, order_num
from Customers
inner join Orders using(cust_id)
order by cust_name

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

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

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

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

select cust_name,order_num
from Customers
left join Orders using(cust_id)
order by cust_name;

知识点：

1. 内联结：inner join。取两列的交集。

2. 外联结：

   • left join。左连接，以左边表的列为主，取两列的交集，对于不在右边列存在的名称取 null。

   • right join。右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取 null。

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

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

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

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

答案：

select prod_name,order_num
from Products
left join OrderItems using(prod_id)
order by prod_name;

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

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

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

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

答案：

# select prod_name, count(order_num) as orders
select prod_name, count(o.prod_id) as orders
from Products
left join OrderItems o using(prod_id)
group by prod_name
order by prod_name;

知识点：

• 使用 COUNT() 聚合函数计算每个产品的总订单数，并将结果作为 total_orders 列的值返回。

• 有聚合函数就要分组。

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

有 Vendors 表含有 vend_id 供应商 id.

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

【问题】

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

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

答案：

select v.vend_id, count(p.prod_id) as prod_id
from Vendors v
left join Products p using(vend_id)
group by v.vend_id
order by v.vend_id;

组合查询

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

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

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

答案：

select prod_id, quantity
from OrderItems
where quantity = 100
union
select prod_id, quantity
from OrderItems
where prod_id like 'BNBG%'

知识点：

UNION 组合 | DreamRain (cmty256.github.io)

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

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

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

【示例结果】

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

答案：

select prod_id, quantity
from OrderItems
where quantity = 100 or prod_id like 'BNBG%'

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

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

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

【问题】

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

答案：

# UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名.
select prod_name
from Products
union
select cust_name
from Customers
order by prod_name

纠错 4

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

【问题】修正下面错误的 SQL

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

答案：

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

错误点：写了两个 order by

知识点：

使用 union 组合查询时，只能使用一条 order by 子句，他必须位于最后一条 select 语句之后，因为对于【结果集】不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。
品 id 对结果进行升序排序。
注意：这次仅使用单个 SELECT 语句。

【示例结果】

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

答案：

select prod_id, quantity
from OrderItems
where quantity = 100 or prod_id like 'BNBG%'

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

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

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

【问题】

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

答案：

# UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名.
select prod_name
from Products
union
select cust_name
from Customers
order by prod_name

纠错 4

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

[外链图片转存中…(img-JowxTx4V-1701079893572)]

【问题】修正下面错误的 SQL

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

答案：

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

错误点：写了两个 order by

知识点：

使用 union 组合查询时，只能使用一条 order by 子句，他必须位于最后一条 select 语句之后，因为对于【结果集】不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况。