Unit 2 · ROUND, CONCAT, UPPER / LOWER
Unit 2 · ROUND, CONCAT, UPPER / LOWER
Data file / 資料檔：student table (sql_class) — download link shared in class.
Start Here / 開始做題
- Decide whether the task needs a number function (
ROUND) or a text function (CONCAT,UPPER,LOWER). 中文：先判斷是數字函式還是文字函式。 - Wrap only the column that needs changing — leave the others as normal columns. 中文：只對需要處理的欄位套用函式。
- Always name the new column with
ASso the output is easy to read. 中文：使用AS為新欄位命名。
Unit Dashboard / 單元地圖
| Learn | Meaning | Use |
|---|---|---|
ROUND(x, n) | round a number to n decimal places | tidy scores and prices |
CONCAT(a, b, ...) | join text values together | build a combined label |
UPPER(text) | convert text to uppercase | standardise display |
LOWER(text) | convert text to lowercase | standardise display |
AS | rename an output column | readable results |
Quick Concepts / 重點
| Syntax | Student note |
|---|---|
ROUND(87.654, 2) | rounds to 2 decimal places → 87.65. 中文：四捨五入到小數第 2 位。 |
ROUND(Grade, 0) | rounds to the nearest whole number. 中文：四捨五入到整數。 |
CONCAT('Hello ', 'World') | joins two text values → Hello World. 中文：文字合併。 |
CONCAT(Name, ' (', Class, ')') | builds a label like Alice (F6A). 中文：組合出自訂格式的欄位。 |
UPPER('hello') | → HELLO. |
LOWER('SQL') | → sql. |
col AS new_name | renames the output column. 中文：替輸出欄位取新名稱。 |
SQL Syntax / SQL 語法
SELECT Name,
CONCAT(Name, '-', Class) AS name_class,
ROUND(Grade, 0) AS rounded_grade
FROM student
LIMIT 5;
Yellow Syntax Guide / 黃色語法重點
| Syntax | Meaning |
|---|---|
CONCAT(Name, '-', Class) | joins Name and Class with a - in between |
ROUND(Grade, 0) | rounds Grade to the nearest whole number |
AS name_class | names the combined-text column |
AS rounded_grade | names the rounded-number column |
Dry Run / 手算追蹤
For Name = 'Alice', Class = 'F6A', Grade = 87.6:
| Expression | Result |
|---|---|
CONCAT(Name, '-', Class) | Alice-F6A |
ROUND(Grade, 0) | 88 |
UPPER(Name) | ALICE |
LOWER(Class) | f6a |
Examples / 例題
Example 2-1 · Round a Grade
題目說明 / Problem Statement
Show Name and Grade rounded to 1 decimal place, for the first 5 students.
中文：顯示前 5 位學生的姓名，及四捨五入到小數 1 位的成績。
Reference Answer / 參考答案
SELECT Name, ROUND(Grade, 1) AS rounded_grade
FROM student
LIMIT 5;
Example 2-2 · Combine Name and Class
題目說明 / Problem Statement
Show a single column student_info in the format Alice (F6A), for the first 5 students.
中文：合併姓名與班級成一欄，例如 Alice (F6A)。
Reference Answer / 參考答案
SELECT CONCAT(Name, ' (', Class, ')') AS student_info
FROM student
LIMIT 5;
Example 2-3 · Change Text Case
題目說明 / Problem Statement
Show Name in uppercase and Class in lowercase, for the first 5 students.
中文：把姓名轉換為大寫，班級轉換為小寫。
Reference Answer / 參考答案
SELECT UPPER(Name) AS upper_name, LOWER(Class) AS lower_class
FROM student
LIMIT 5;
ClassWork 2 / 課後練習
Name every calculated column with
AS. 中文：每個計算後的欄位都要用AS命名。
ClassWork 2-1 · Top 10 by Grade
Show the first 10 students ordered by grade (highest first). 中文：查詢成績最高的前 10 名學生。
SELECT * FROM student ORDER BY Grade DESC LIMIT 10;
ClassWork 2-2 · Bottom 10 by Grade
Show the last 10 students ordered by grade (lowest first). 中文：查詢成績最低的 10 名學生。
SELECT * FROM student ORDER BY Grade ASC LIMIT 10;
ClassWork 2-3 · Round All Grades
Round all students' grades to 0 decimal places. Name the column. 中文：把所有學生的成績四捨五入到整數（命名欄位）。
SELECT Name, ROUND(Grade, 0) AS rounded_grade
FROM student;
ClassWork 2-4 · Combine Name and Class
Show student name and class combined as one column in the format: Alice-F6A. Name the column.
中文：把姓名與班級合併成一欄（命名欄位）。
SELECT CONCAT(Name, '-', Class) AS name_class
FROM student;
ClassWork 2-5 · Change Case of Two Columns
Show all student names in uppercase and class in lowercase. Name each column. 中文：將姓名轉大寫、班級轉小寫（命名欄位）。
SELECT UPPER(Name) AS upper_name, LOWER(Class) AS lower_class
FROM student;
