SQL面试基础和中级问题-mysql教程-PHP中文网

咱们进入正题吧。

我创建了一个 Awesome SQL Interview GitHub 存储库来准备面试问题和练习 SQL 查询。我将 SQL 查询分为三个部分：基础 (L0)、中级 (L1) 和高级 (L2)。这是基本部分的解决方案。

这是 L1（中级）SQL 查询练习，请先参考 L0 以获得更好的练习。

注意：这些示例是在 MySQL 中测试的。对于 MS-SQL 或 Oracle 等其他数据库，语法可能会有所不同。

L1：中级 SQL

涉及使用多个表、使用 JOIN、GROUP BY、HAVING 和复杂 WHERE 条件的查询。
子查询、聚合函数和 case 语句简介。

问题：

编写一个查询来检索“美国”和“法国”客户的客户名称和城市。
如何获取在“旧金山”办公室工作的所有员工的员工编号、姓氏和办公室代码？
编写一个查询，使用订单和客户表查找每个客户的订单总数。
如何检索已订购超过 10 次的产品的 ProductName、QuantityInStock 和 buyPrice？
编写一个查询来获取 customerNumber 为 103 的客户所下订单的 orderNumber、状态和 customerName。
编写一个查询来查找 orderdetails 表中每个订单的总销售额 (quantityOrdered * PriceEach)。
如何找到 orderdetails 表中每个 orderNumber 的平均订购数量？
编写一个查询，列出 orderdetails 表中总收入最高的产品线 (quantityOrdered * PriceEach)。
编写一个查询，通过连接员工表和办公室表来显示员工编号、名字、姓氏以及员工工作的办公室名称。
如何找到从未下过订单的顾客？
编写一个查询来检索 customerName 和每个客户下的订单总数（包括未下订单的客户）。
编写一个查询来查找订购产品数量大于 50 的所有订单的产品名称和订购数量。
检索已下订单的客户被分配为销售代表的员工的员工编号、名字和订单编号。
编写一个查询，根据 buyPrice 计算 products 表中产品的平均价格。
如何获取产品表中前 3 个最昂贵的产品？
编写一个查询来检索状态为“已发货”的所有订单的 customerName、orderNumber 和 orderDate。
如何显示每个产品线销售的产品总数？
编写一个查询来查找直接向员工编号 = 1143 的员工汇报的员工。
编写一个查询来计算订单表中的订单总数（按状态分组）。
列出员工及其经理的姓名。

我也会提到错误的事情，知道什么该做很重要，但也很重要什么不该做，以及我们在哪里犯了错误。让我们再次进入正题吧...

需要说明的解决方案

检索“美国”和“法国”客户的客户名称和城市的查询。
- 或->如果条件很多，速度会稍微慢一些，因为查询会逐一检查每个条件。
- 在->数据库引擎在内部进行了稍微优化，特别是对于长列表。
- 两者都适合 2-3 种情况。对于可读性和可扩展性而言，IN 更好，尤其是在处理较大的值列表时。
- IS 用于检查 IS NULL 或 IS NOT NULL 等条件，而不用于字符串比较。
获取在“旧金山”办公室工作的所有员工的员工编号、姓氏和办公室代码。
使用订单和客户表查询查找每个客户的订单总数。
- 在查询中使用聚合函数时，始终在 GROUP BY 子句中包含非聚合列。
- 这确保 SQL 知道如何对行进行分组并避免在选择其他列时出现歧义。
- 在我们的示例中：customerNumber 和 customerName 必须都在 GROUP BY 子句中，因为我们将它们与 COUNT(*) 一起选择。
？黄金法则：
SELECT 列表中的每一列都必须：
位于 GROUP BY 子句中，或者
使用 COUNT()、SUM() 等聚合函数
检索已订购超过10次的产品的产品名称、库存数量和购买价格？
- 这个查询对于中小型数据库来说是高效的，对于大型数据库我们可以使用索引，并使用WHERE子句减少扫描数据，而不是仅仅依赖HAVING子句
获取 customerNumber 为 103 的客户所下订单的 orderNumber、状态和 customerName。

说明：
- 使用的表：
  - 订单：包含订单号和状态。
  - customers：包含 customerName。
- 内连接：
  - 使用 customerNumber 列（公共键）组合订单和客户表。
- WHERE 子句：
  - 过滤数据以仅包含 customerNumber = 103 的记录。
- 选择的列：
  - o.orderNumber：订单号。
  - o.status：订单状态。
  - c.customerName：下订单的客户姓名。
在 orderdetails 表中查找每个订单的总销售额（订购数量 * 每个价格）。
在 orderdetails 表中查找每个 orderNumber 的平均订购数量。
- 说明：
- 订单号：
  - 按 orderNumber 对行进行分组。
- AVG（订购数量）：
  - 计算属于同一 orderNumber 的所有行的平均订购数量。
- 分组依据：
  - 确保分别计算每个 orderNumber 的平均值。
查询列出 orderdetails 表中总收入最高的产品线 (quantityOrdered * PriceEach)。
- 说明：
- 产品线：
  - 将产品分类为不同的系列，例如“摩托车”或“飞机”。
- SUM(od.quantityOrdered * od.priceEach):
  - 计算每个产品线的总收入。
- 内连接：
  - 加入productCode 上的产品和订单详细信息表，以将产品线与其订单详细信息相关联。
- 按 p.productLine 分组：
  - 按每个产品线对结果进行分组。
- 按总收入排序：
  - 按收入降序对分组结果进行排序，因此收入最高的首先出现。
- 限制 1：
  - 将结果限制为仅收入最高的产品线。
通过连接employees表和offices表来查询显示employeeNumber、firstName、lastName以及员工工作的办公室名称。
- CONCAT（列，'分隔符'，列，'分隔符'，列）
- CONCAT_WS('分隔符', 列)
查找从未下过订单的顾客

说明：
- LEFT JOIN: 从客户表中检索所有客户，无论他们在订单表中是否有匹配的行。
- o.orderNumber IS NULL: 标识没有任何对应订单的客户（即 orderNumber 为 NULL，因为订单表中没有匹配项）。
- 专栏：
  - 客户编号： 客户的唯一标识符。
  - customerName: 客户姓名。
查询获取每个客户的客户名称和下单总数（包括未下单的客户）。
查找订购产品数量大于 50 的所有订单的产品名称和订购数量。
检索已下订单的客户被分配为销售代表的员工的员工编号、名字和订单编号。

说明：
- 来自员工 e：
  - 我们从员工表（别名为 e）开始，因为我们需要员工详细信息，特别是员工编号和名字。
- 加入客户 c ON e.employeeNumber = c.salesRepEmployeeNumber：
  - 我们将来自员工的员工编号和来自客户的 salesRepEmployeeNumber 加入客户表（别名为 c）。这在员工（销售代表）和客户之间建立了关系。现在，我们可以确定为每个客户分配了哪些员工。
- JOIN 订单 o ON c.customerNumber = o.customerNumber：
  - 我们进一步使用 customerNumber 将订单表（别名为 o）与客户表连接起来。这为我们提供了每个客户所下的订单。
- 选择 e.employeeNumber、e.firstName、o.orderNumber：
  - 最后，我们从员工表（销售代表）中选择员工编号和名字，并从订单表中为每个下订单的客户选择订单编号。
查询根据buyPrice计算products表中产品的平均价格。
获取产品表中前 3 个最昂贵的产品？
重新检索状态为“已发货”的所有订单的 customerName、orderNumber 和 orderDate。
显示每个产品系列销售的产品总数
查找直接向员工编号 = 1143 的员工汇报的员工。
查询计算订单表中的订单总数，按状态分组。
列出员工及其经理的姓名。