Unit 4 · Grocery Store Practice

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

Data file / 資料檔：sales table (grocery store) — download and load into sqliteviz.com.

• ClassWork 4 / 課後練習：grocery store sales file This unit combines everything from Units 1–3: SELECT, WHERE, ORDER BY, LIMIT, ROUND, CONCAT, UPPER, and GROUP BY. 本次練習綜合前三個單元的所有語法。

Table Reference / 資料表參考

sales table columns (assumed — check your actual file): Customer, Product, Category, Price, Quantity, Total, Payment. sales 資料表欄位（僅供參考，請以實際檔案為準）：顧客、商品、分類、單價、數量、總價、付款方式。

Start Here / 開始做題

1. Identify which columns the question needs in the output. 先確認題目要求輸出哪些欄位。
2. Identify filter conditions (WHERE) before deciding on sorting or grouping. 先確認篩選條件，再決定排序或分組。
3. For "each ___" questions, that is a GROUP BY question. 題目出現「每個／各」，代表要用 GROUP BY。

ClassWork 4 Tasks / 練習題

ClassWork 4-1 · Show All Columns

Show all columns from the grocery store table. 顯示所有欄位。

Reference Answer / 參考答案

ClassWork 4-2 · First 10 Sales

Show only the first 10 sales. 顯示前 10 筆交易。

Reference Answer / 參考答案

ClassWork 4-3 · Filter by Payment Method

Show all sales paid by card. 查詢付款方式為 card 的交易。

Reference Answer / 參考答案

ClassWork 4-4 · Filter by Product Name

Show all customers who bought "apple". 查詢買了 apple 的顧客。

Reference Answer / 參考答案

ClassWork 4-5 · Top 5 Highest Prices

Show the top 5 most expensive single prices (Price). 顯示單價最高的前 5 筆商品。

Reference Answer / 參考答案

ClassWork 4-6 · Filter by Quantity

Show all sales where quantity is more than 3. 查詢購買數量大於 3 的交易。

Reference Answer / 參考答案

ClassWork 4-7 · Filter and Sort by Category

Show all sales for fruits (Category = 'fruit'), ordered by Total from high to low. 查詢水果類商品，並按總價由高到低排列。

Reference Answer / 參考答案

ClassWork 4-8 · Round the Total

Show each sale's total rounded to 1 decimal place. 將總價小數點取 1 位。

Reference Answer / 參考答案

ClassWork 4-9 · Combine Product and Customer

Combine the product name and customer name as one column, e.g. "apple - Tom". 合併商品與顧客名稱成一欄。

Reference Answer / 參考答案

ClassWork 4-10 · Uppercase Customer Names

Show all customer names in uppercase. 將顧客名稱轉為大寫。

Reference Answer / 參考答案

ClassWork 4-11 · Total Quantity per Product

Find the total quantity sold of each product. 計算每種商品的總銷售數量。

Reference Answer / 參考答案

ClassWork 4-12 · Average Spending per Customer (Filtered)

Find the average spending (Total) of each customer, only showing customers whose average is more than 15. 計算每位顧客的平均消費金額，只顯示平均大於 15 的。

Reference Answer / 參考答案

Active Recall / 主動回想

• For “total quantity of each product”, which column is grouped and which function is used?
• Which task requires a text function?
• Which task needs sorting before limiting?

Spaced Review / 間隔複習

• Next lesson: choose the first SQL clause you would write for three unseen grocery questions.
• 1 week later: complete two similar questions using a new sales table.

Key Takeaways / 重點帶走

• Read the required output before writing SQL.
• “Each” usually signals GROUP BY; a top-N question needs ORDER BY then LIMIT.
• Run and inspect every result rather than relying on syntax alone.

Common Mistakes / 常見錯誤

• Grouping by the wrong output column.
• Returning extra columns that the question did not ask for.

30-Second Review / 30 秒複習

Choose one task and state: output columns, filter, grouping, sorting, limit.