ClassWork 4 · Grocery Store Practice

Data file / 資料檔：sales table (grocery store) — download link shared in class. 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 / 開始做題

Identify which columns the question needs in the output. 中文：先確認題目要求輸出哪些欄位。
Identify filter conditions (WHERE) before deciding on sorting or grouping. 中文：先確認篩選條件，再決定排序或分組。
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. 中文：顯示所有欄位。

SELECT * FROM sales;

ClassWork 4-2 · First 10 Sales

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

SELECT * FROM sales LIMIT 10;

ClassWork 4-3 · Filter by Payment Method

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

SELECT * FROM sales WHERE Payment = 'card';

ClassWork 4-4 · Filter by Product Name

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

SELECT Customer FROM sales WHERE Product = 'apple';

ClassWork 4-5 · Top 5 Highest Prices

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

SELECT * FROM sales ORDER BY Price DESC LIMIT 5;

ClassWork 4-6 · Filter by Quantity

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

SELECT * FROM sales WHERE Quantity > 3;

ClassWork 4-7 · Filter and Sort by Category

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

SELECT * FROM sales
WHERE Category = 'fruit'
ORDER BY Total DESC;

ClassWork 4-8 · Round the Total

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

SELECT *, ROUND(Total, 1) AS rounded_total
FROM sales;

ClassWork 4-9 · Combine Product and Customer

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

SELECT CONCAT(Product, ' - ', Customer) AS product_customer
FROM sales;

ClassWork 4-10 · Uppercase Customer Names

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

SELECT UPPER(Customer) AS customer_upper
FROM sales;

ClassWork 4-11 · Total Quantity per Product

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

SELECT Product, SUM(Quantity) AS total_quantity
FROM sales
GROUP BY Product;

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 的。

SELECT Customer, AVG(Total) AS avg_spending
FROM sales
GROUP BY Customer
HAVING AVG(Total) > 15;