AI & Data Dreamers練功房
寶箱片場規則
QUERY GATES・資料庫關卡・中階・4 題

SQL 中階・訂單三表的連接與彙總

三表連接、LEFT JOIN 條件放在 ON、先彙總再連接、類別營收占比。

這一頁是題幹試閱;登入/註冊後每題可以在瀏覽器裡執行、對答案,第一次交對 +10 XP。

  1. 01

    每張訂單金額

    中等三表連接

    對帳要列出每張有效訂單的金額與客戶姓名。有效訂單=status 不是 'cancelled';沒有任何明細的訂單不列。

    訂單金額=該訂單所有明細的 quantity * unit_price 加總。

    輸出欄位依序為:order_id、客戶姓名、order_date、訂單金額。依訂單金額由高到低排序;金額相同時依訂單編號由小到大。

    資料表 customers(客戶):customer_id 客戶編號(文字,主鍵)、name 姓名(文字)、city 城市(文字,沒填為 NULL)、signup_date 註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。

    資料表 orders(訂單):order_id 訂單編號(整數,主鍵)、customer_id 客戶編號(文字)、order_date 下單日(文字,格式 'YYYY-MM-DD')、status 狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。

    資料表 order_items(訂單明細,一張訂單可有多列):order_id 訂單編號(整數)、product 商品名稱(文字)、category 類別(文字)、quantity 數量(整數)、unit_price 單價(整數,元)。同一張訂單裡不會出現重複的商品。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答
  2. 02

    每位客戶消費含零

    中等LEFT JOIN

    要列出每一位客戶的有效訂單數與消費總額,沒有有效訂單的客戶也要列出、數字顯示 0。有效訂單=status 不是 'cancelled'(沒有明細的有效訂單也算一張,金額 0)。

    消費總額=有效訂單所有明細的 quantity * unit_price 加總。沒有消費的客戶請用 COALESCE(SUM(...), 0) 顯示整數 0(不要用 TOTAL(),它會回傳 0.0)。

    輸出欄位依序為:customer_id、name、有效訂單數、消費總額。依消費總額由高到低排序;相同時依客戶編號由小到大。

    資料表 customers(客戶):customer_id 客戶編號(文字,主鍵)、name 姓名(文字)、city 城市(文字,沒填為 NULL)、signup_date 註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。

    資料表 orders(訂單):order_id 訂單編號(整數,主鍵)、customer_id 客戶編號(文字)、order_date 下單日(文字,格式 'YYYY-MM-DD')、status 狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。

    資料表 order_items(訂單明細,一張訂單可有多列):order_id 訂單編號(整數)、product 商品名稱(文字)、category 類別(文字)、quantity 數量(整數)、unit_price 單價(整數,元)。同一張訂單裡不會出現重複的商品。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答
  3. 03

    平均每單金額

    中等先彙總再連接

    要算每位客戶「平均一張訂單花多少錢」。只看有效訂單(status 不是 'cancelled');沒有明細的有效訂單金額算 0,也算一張。

    注意:直接把訂單和明細接起來再取 AVG,平均的會是「每一列明細」而不是「每一張訂單」。要先算出每張訂單的金額,再以訂單為單位求平均。

    只列出至少有一張有效訂單的客戶。輸出欄位依序為:customer_id、有效訂單數、平均每單金額(ROUND(..., 1))、最大一張訂單的金額。依平均每單金額由高到低排序;相同時依客戶編號由小到大。

    資料表 customers(客戶):customer_id 客戶編號(文字,主鍵)、name 姓名(文字)、city 城市(文字,沒填為 NULL)、signup_date 註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。

    資料表 orders(訂單):order_id 訂單編號(整數,主鍵)、customer_id 客戶編號(文字)、order_date 下單日(文字,格式 'YYYY-MM-DD')、status 狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。

    資料表 order_items(訂單明細,一張訂單可有多列):order_id 訂單編號(整數)、product 商品名稱(文字)、category 類別(文字)、quantity 數量(整數)、unit_price 單價(整數,元)。同一張訂單裡不會出現重複的商品。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答
  4. 04

    類別營收占比

    中等占比

    要看各商品類別在有效訂單(status 不是 'cancelled')的營收裡各占多少。

    類別營收=該類別明細的 quantity * unit_price 加總;占比(百分比)=類別營收 × 100.0 ÷ 全部類別營收合計,ROUND(..., 1)。另外列出這個類別出現在幾張不同的有效訂單裡。

    輸出欄位依序為:category、出現的訂單數、類別營收、占比。依類別營收由高到低排序;營收相同時依類別由小到大。

    資料表 customers(客戶):customer_id 客戶編號(文字,主鍵)、name 姓名(文字)、city 城市(文字,沒填為 NULL)、signup_date 註冊日(文字,格式 'YYYY-MM-DD',舊系統匯入的客戶可能為 NULL)。有些客戶沒有任何訂單。

    資料表 orders(訂單):order_id 訂單編號(整數,主鍵)、customer_id 客戶編號(文字)、order_date 下單日(文字,格式 'YYYY-MM-DD')、status 狀態(文字:'pending'、'shipped'、'completed'、'cancelled')。同一位客戶同一天可能有兩張訂單;有的訂單還沒有任何明細。

    資料表 order_items(訂單明細,一張訂單可有多列):order_id 訂單編號(整數)、product 商品名稱(文字)、category 類別(文字)、quantity 數量(整數)、unit_price 單價(整數,元)。同一張訂單裡不會出現重複的商品。

    本題在瀏覽器內以 SQLite 執行,只寫一段 SELECT 即可。

    作答登入後可作答