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中文網其他相關文章！