Unit 5 · JOIN … ON

Data files / 資料檔：students, products, sales — three separate tables, download links shared in class.

Table Reference / 資料表參考

• students: student_id, name
• products: product_id, product_name, price
• sales: sale_id, student_id, product_id, quantity

中文：三張表以 student_id 及 product_id 互相關聯。

Start Here / 開始做題

1. Identify which tables hold the columns you need. 中文：先確認需要的欄位分別在哪張表。
2. Find the shared key column that links each pair of tables (e.g. student_id). 中文：找出兩表共有的關聯欄位。
3. Give each table a short alias (s, p, sa) so column names stay readable. 中文：用簡短別名區分欄位來源。
4. Decide JOIN (only matching rows) vs LEFT JOIN (keep all rows from the left table, even without a match). 中文：決定要用 JOIN（只留有對應的資料）還是 LEFT JOIN（保留左表全部資料）。

Unit Dashboard / 單元地圖

Quick Concepts / 重點

SQL Syntax / SQL 語法

This is a complete three-table join. Copy it and change the columns. 中文：以下是完整的三表連接查詢，可修改欄位再執行。

SELECT s.name, p.product_name, sa.quantity
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id;

Yellow Syntax Guide / 黃色語法重點

Dry Run / 手算追蹤

Sample rows (illustrative only / 僅供示範):

students: (1, Alice), (2, Ben) products: (1, Pen), (2, Book) sales: (student_id=1, product_id=2, quantity=3)

Joining on the matching ids gives: Alice, Book, 3. 中文：以 student_id 及 product_id 分別對應到姓名與商品名稱，組出一列完整資料。

Examples / 例題

Example 5-1 · Students Who Made a Purchase

題目說明 / Problem Statement

Show students who made a purchase, with the product name and quantity. 中文：只顯示有購買紀錄的學生。

Reference Answer / 參考答案

SELECT s.name, p.product_name, sa.quantity
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id;

Example 5-2 · All Students, Including Non-buyers

題目說明 / Problem Statement

Show all students, including those with no purchases. 中文：即使學生沒有購物紀錄，也會顯示。

Reference Answer / 參考答案

SELECT s.name, p.product_name, sa.quantity
FROM students s
LEFT JOIN sales sa ON s.student_id = sa.student_id
LEFT JOIN products p ON sa.product_id = p.product_id;

Example 5-3 · Who Bought What, and Total Cost

題目說明 / Problem Statement

Show who bought what, how many, and the total cost (price × quantity). 中文：顯示學生、商品、購買數量與總金額。

Reference Answer / 參考答案

SELECT s.name, p.product_name, p.price, sa.quantity,
       (p.price * sa.quantity) AS total
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id;

ClassWork 5 / 課後練習

ClassWork 5-1 · Sales with Names

List all sales with student name and product name. 中文：顯示所有購買資料，學生與商品名稱。

SELECT s.name, p.product_name, sa.quantity
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id;

ClassWork 5-2 · Total Items per Student

Show each student's total items bought. 中文：每位學生買了多少件商品。

SELECT s.name, SUM(sa.quantity) AS total_items
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
GROUP BY s.name;

ClassWork 5-3 · Total Quantity per Product

Show each product's total quantity sold. 中文：各商品的銷售數量。

SELECT p.product_name, SUM(sa.quantity) AS total_quantity
FROM sales sa
JOIN products p ON sa.product_id = p.product_id
GROUP BY p.product_name;

ClassWork 5-4 · Total Spent per Student (Buyers Only)

Show each student's name and the total money they spent (price × quantity) — only students who made a purchase. 中文：顯示每位有購物紀錄的學生姓名與總花費金額（單價 × 數量）。

SELECT s.name, SUM(p.price * sa.quantity) AS total_spent
FROM sales sa
JOIN students s ON sa.student_id = s.student_id
JOIN products p ON sa.product_id = p.product_id
GROUP BY s.name;

ClassWork 5-5 · Total Spent per Student (All Students)

Show every student's name and total money spent, including students who bought nothing (show 0 instead of blank). 中文：顯示所有學生姓名與總花費金額，即使沒有購物紀錄也顯示為 0。

SELECT s.name, COALESCE(SUM(p.price * sa.quantity), 0) AS total_spent
FROM students s
LEFT JOIN sales sa ON s.student_id = sa.student_id
LEFT JOIN products p ON sa.product_id = p.product_id
GROUP BY s.name;

ClassWork 5-6 · Products Never Sold

Show all products even if not sold. 中文：顯示所有商品（包含沒被賣出）。

SELECT p.product_name
FROM products p
LEFT JOIN sales sa ON p.product_id = sa.product_id
WHERE sa.product_id IS NULL;