Unit 6 · Multi-table Join Practice

Student task / 學生任務：先自行規劃與執行 SQL；每題下方附有可對照的答案截圖。

Data files / 資料檔：customers, products, sales — three files below (原始 notebook 未逐一標註檔名，下載後請對照欄位確認對應的資料表)。

• File 1
• File 2
• File 3 This unit combines JOIN, LEFT JOIN, GROUP BY, HAVING, and ORDER BY from Units 1–5. 本次練習綜合前面所有單元的語法。

Table Reference / 資料表參考

• customers: customer_id, name, city
• products: product_id, product_name, category, price
• sales: sale_id, customer_id, product_id, quantity

欄位僅供參考，請以實際檔案為準。

Start Here / 開始做題

1. Decide whether you need every row from a table (LEFT JOIN) or only matching rows (JOIN). 先決定要不要保留沒有對應資料的紀錄。
2. Decide whether the question needs GROUP BY (look for "each", "per", "total"). 題目出現「每個／各／總」代表要分組。
3. Add HAVING only when filtering happens after aggregation (e.g. "total ≥ 50"). 篩選聚合後的結果才用 HAVING。

ClassWork 6 Tasks / 練習題

ClassWork 6-1 · Sales with Customer and Product Names

Show all sales with customer name and product name. 顯示所有銷售資料，包含顧客姓名與商品名稱。

Reference Answer / 參考答案

ClassWork 6-2 · Total Items per Customer

Show the total number of items each customer bought. 顯示每位顧客購買的總件數。

Reference Answer / 參考答案

ClassWork 6-3 · Total Quantity per Product

Show each product's total sales quantity. 顯示每種商品的總銷售數量。

Reference Answer / 參考答案

ClassWork 6-4 · Customer, Product, and Total Price

Show customer name, product name, and total price (price × quantity). 顯示顧客姓名、商品名稱與總價。

Reference Answer / 參考答案

ClassWork 6-5 · All Customers, Including Non-buyers

Show all customers even if they didn't buy anything. 顯示所有顧客，即使沒有購物紀錄。

Reference Answer / 參考答案

ClassWork 6-6 · All Products, Including Never Sold

Show all products even if they were never sold. 顯示所有商品，即使沒有被賣出。

Reference Answer / 參考答案

ClassWork 6-7 · High-spending Customers

Find customers who spent more than 40 in total. 查出總花費金額超過 40 的顧客。

Reference Answer / 參考答案

ClassWork 6-8 · Average Spending per City

Show the average spending (price × quantity) per city. 顯示每個城市的平均消費金額。

Reference Answer / 參考答案

ClassWork 6-9 · Most Expensive Product Purchased

Show the most expensive product purchased (by price). 顯示被購買過的最高單價商品。

Reference Answer / 參考答案

ClassWork 6-10 · Total Sales per Category (Filtered)

Find the total sales per category, showing only categories with total ≥ 50. 顯示總銷售額 ≥ 50 的商品分類。

Reference Answer / 參考答案

Active Recall / 主動回想

• Which table should be placed on the left when all customers must appear?
• When does a condition belong in HAVING instead of WHERE?
• Trace one task: tables → join keys → output → grouping → filter.

Spaced Review / 間隔複習

• Next lesson: rebuild one query from its result description only.
• 1 week later: solve two unseen multi-table questions and justify the join type.

Key Takeaways / 重點帶走

• Multi-table questions start by locating required columns and shared keys.
• Choose LEFT JOIN only when unmatched left-table rows must remain.
• Aggregate first, then use HAVING to filter aggregate results.

Common Mistakes / 常見錯誤

• Treating WHERE and HAVING as interchangeable.
• Missing a join condition and accidentally creating too many result rows.

30-Second Review / 30 秒複習

For one question, say: tables, keys, join type, grouping, and final filter.