ClassWork 6 · Multi-table Join Practice

Data files / 資料檔：customers, products, sales — download links shared in class. 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 / 開始做題

Decide whether you need every row from a table (LEFT JOIN) or only matching rows (JOIN). 中文：先決定要不要保留沒有對應資料的紀錄。
Decide whether the question needs GROUP BY (look for "each", "per", "total"). 中文：題目出現「每個／各／總」代表要分組。
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. 中文：顯示所有銷售資料，包含顧客姓名與商品名稱。

SELECT c.name, p.product_name, sa.quantity
FROM sales sa
JOIN customers c ON sa.customer_id = c.customer_id
JOIN products p ON sa.product_id = p.product_id;

ClassWork 6-2 · Total Items per Customer

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

SELECT c.name, SUM(sa.quantity) AS total_items
FROM sales sa
JOIN customers c ON sa.customer_id = c.customer_id
GROUP BY c.name;

ClassWork 6-3 · Total Quantity per Product

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

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 6-4 · Customer, Product, and Total Price

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

SELECT c.name, p.product_name, (p.price * sa.quantity) AS total_price
FROM sales sa
JOIN customers c ON sa.customer_id = c.customer_id
JOIN products p ON sa.product_id = p.product_id;

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

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

SELECT c.name, sa.sale_id
FROM customers c
LEFT JOIN sales sa ON c.customer_id = sa.customer_id;

ClassWork 6-6 · All Products, Including Never Sold

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

SELECT p.product_name, sa.sale_id
FROM products p
LEFT JOIN sales sa ON p.product_id = sa.product_id;

ClassWork 6-7 · High-spending Customers

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

SELECT c.name, SUM(p.price * sa.quantity) AS total_spent
FROM sales sa
JOIN customers c ON sa.customer_id = c.customer_id
JOIN products p ON sa.product_id = p.product_id
GROUP BY c.name
HAVING SUM(p.price * sa.quantity) > 40;

ClassWork 6-8 · Average Spending per City

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

SELECT c.city, AVG(p.price * sa.quantity) AS avg_spending
FROM sales sa
JOIN customers c ON sa.customer_id = c.customer_id
JOIN products p ON sa.product_id = p.product_id
GROUP BY c.city;

ClassWork 6-9 · Most Expensive Product Purchased

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

SELECT DISTINCT p.product_name, p.price
FROM sales sa
JOIN products p ON sa.product_id = p.product_id
ORDER BY p.price DESC
LIMIT 1;

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

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

SELECT p.category, SUM(p.price * sa.quantity) AS total_sales
FROM sales sa
JOIN products p ON sa.product_id = p.product_id
GROUP BY p.category
HAVING SUM(p.price * sa.quantity) >= 50;