SQL面试基础和中级问题

咱们进入正题吧。

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

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

---

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

---

L1：中级 SQL

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

问题：

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

---

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

---

需要说明的解决方案

1. 检索“美国”和“法国”客户的客户名称和城市的查询。
   

   • 或->如果条件很多，速度会稍微慢一些，因为查询会逐一检查每个条件。
   • 在->数据库引擎在内部进行了稍微优化，特别是对于长列表。
   • 两者都适合 2-3 种情况。对于可读性和可扩展性而言，IN 更好，尤其是在处理较大的值列表时。
   • IS 用于检查 IS NULL 或 IS NOT NULL 等条件，而不用于字符串比较。

2. 获取在“旧金山”办公室工作的所有员工的员工编号、姓氏和办公室代码。
   

3. 使用订单和客户表查询查找每个客户的订单总数。
   

   • 在查询中使用聚合函数时，始终在 GROUP BY 子句中包含非聚合列。
   • 这确保 SQL 知道如何对行进行分组并避免在选择其他列时出现歧义。
   • 在我们的示例中：customerNumber 和 customerName 必须都在 GROUP BY 子句中，因为我们将它们与 COUNT(*) 一起选择。

   ？黄金法则：
   SELECT 列表中的每一列都必须：
   位于 GROUP BY 子句中，或者
   使用 COUNT()、SUM() 等聚合函数

4. 检索已订购超过10次的产品的产品名称、库存数量和购买价格？
   

   • 这个查询对于中小型数据库来说是高效的，对于大型数据库我们可以使用索引，并使用WHERE子句减少扫描数据，而不是仅仅依赖HAVING子句

5. 获取 customerNumber 为 103 的客户所下订单的 orderNumber、状态和 customerName。
   

   说明：

   • 使用的表：
     • 订单：包含订单号和状态。
     • customers：包含 customerName。
   • 内连接：
     • 使用 customerNumber 列（公共键）组合订单和客户表。
   • WHERE 子句：
     • 过滤数据以仅包含 customerNumber = 103 的记录。
   • 选择的列：
     • o.orderNumber：订单号。
     • o.status：订单状态。
     • c.customerName：下订单的客户姓名。

6. 在 orderdetails 表中查找每个订单的总销售额（订购数量 * 每个价格）。
   

7. 在 orderdetails 表中查找每个 orderNumber 的平均订购数量。
   

   • 说明：
   • 订单号：
     • 按 orderNumber 对行进行分组。
   • AVG（订购数量）：
     • 计算属于同一 orderNumber 的所有行的平均订购数量。
   • 分组依据：
     • 确保分别计算每个 orderNumber 的平均值。

8. 查询列出 orderdetails 表中总收入最高的产品线 (quantityOrdered * PriceEach)。
   

   • 说明：
   • 产品线：
     • 将产品分类为不同的系列，例如“摩托车”或“飞机”。
   • SUM(od.quantityOrdered * od.priceEach):
     • 计算每个产品线的总收入。
   • 内连接：
     • 加入productCode 上的产品和订单详细信息表，以将产品线与其订单详细信息相关联。
   • 按 p.productLine 分组：
     • 按每个产品线对结果进行分组。
   • 按总收入排序：
     • 按收入降序对分组结果进行排序，因此收入最高的首先出现。
   • 限制 1：
     • 将结果限制为仅收入最高的产品线。

9. 通过连接employees表和offices表来查询显示employeeNumber、firstName、lastName以及员工工作的办公室名称。
   

   • CONCAT（列，'分隔符'，列，'分隔符'，列）
   • CONCAT_WS('分隔符', 列)

10. 查找从未下过订单的顾客
    

    说明：

    • LEFT JOIN: 从客户表中检索所有客户，无论他们在订单表中是否有匹配的行。
    • o.orderNumber IS NULL: 标识没有任何对应订单的客户（即 orderNumber 为 NULL，因为订单表中没有匹配项）。
    • 专栏：
      • 客户编号： 客户的唯一标识符。
      • customerName: 客户姓名。

11. 查询获取每个客户的客户名称和下单总数（包括未下单的客户）。

12. 查找订购产品数量大于 50 的所有订单的产品名称和订购数量。
    

13. 检索已下订单的客户被分配为销售代表的员工的员工编号、名字和订单编号。
    

    说明：

    • 来自员工 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：
      • 最后，我们从员工表（销售代表）中选择员工编号和名字，并从订单表中为每个下订单的客户选择订单编号。

14. 查询根据buyPrice计算products表中产品的平均价格。
    

15. 获取产品表中前 3 个最昂贵的产品？
    
    

16. 重新检索状态为“已发货”的所有订单的 customerName、orderNumber 和 orderDate。
    

17. 显示每个产品系列销售的产品总数
    

18. 查找直接向员工编号 = 1143 的员工汇报的员工。
    

19. 查询计算订单表中的订单总数，按状态分组。
    

20. 列出员工及其经理的姓名。
    

---

嘿，我的名字是 Jaimin Baria，又名 Cloud Boy...，如果您喜欢并学到了一些有用的东西，例如这篇文章，请添加评论，并访问我的 Awesome SQL Interview GitHub 存储库。

别忘了开始吧？

快乐编码?‍?

---

其他帖子

• SQL 实践：
  • 第 1 部分
    • L0：基本 SQL
    • L1：中级 SQL
    • L2：高级 SQL - 即将推出
• 系统设计
  • 数据库中ACID事务的实现
  • 系统设计中的 ACID 事务

---

?️ 读者建议的修复

以上是SQL面试基础和中级问题的详细内容。更多信息请关注PHP中文网其他相关文章！