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 的員工報告的員工。
查詢計算訂單表中的訂單總數，依狀態分組。
列出員工及其經理的姓名。