Unit 1 · SELECT, LIMIT, OFFSET, WHERE, ORDER BY
Unit 1 · SELECT, LIMIT, OFFSET, WHERE, ORDER BY
Data file / 資料檔：student table — download and load into sqliteviz.com.
- Unit content / 單元內容：student.csv
- ClassWork 1 / 課後練習：student.csv
-- Note 單行註解
/*
Note 多行註解
*/
Start Here / 開始做題
- Read the question and decide which columns you need. 先確定題目要求的欄位。
- Decide whether you need to filter rows (
WHERE) or sort rows (ORDER BY). 判斷是否需要篩選或排序。 - Write the query, run it, then check the row count and column names. 執行後核對筆數與欄位名稱。
- Screenshot a successful run. 截圖成功結果。
Table Reference / 資料表參考
student table columns: ID, Name, Class, Grade
student 資料表欄位：學號、姓名、班級、成績。
Unit Dashboard / 單元地圖
| Learn | Meaning | Use |
|---|---|---|
SELECT | choose which columns to show | pick specific fields |
LIMIT | restrict number of rows returned | top-N results |
OFFSET | skip a number of rows before returning | pagination |
WHERE | filter rows by a condition | matching / excluding rows |
ORDER BY | sort rows | rank highest / lowest |
Quick Concepts / 重點
| Syntax | Student note |
|---|---|
SELECT * FROM table; | * means all columns. * 代表所有欄位。 |
SELECT colA, colB FROM table; | list only the columns you need. 只選需要的欄位。 |
LIMIT n | keep only the first n rows. 只保留前 n 筆。 |
LIMIT n OFFSET m | skip m rows, then keep the next n. 跳過 m 筆，再取 n 筆。 |
WHERE col = 'value' | keep rows that match exactly. 完全相符才保留。 |
WHERE col <> 'value' | keep rows that do NOT match. <> 表示不等於。 |
WHERE col > x / < x | keep rows above / below a number. 大於／小於篩選。 |
WHERE col BETWEEN a AND b | keep rows inside a range (inclusive). 介於 a 和 b 之間（含頭尾）。 |
ORDER BY col ASC | sort smallest to largest (default). 由小到大（預設）。 |
ORDER BY col DESC | sort largest to smallest. 由大到小。 |
ORDER BY colA ASC, colB DESC | sort by colA first, then break ties with colB. 先依 colA 排序，再依 colB 排序。 |
SQL Syntax / SQL 語法
This is a complete, runnable query. Copy it into sqliteviz.com and change the values. 以下是可直接執行的完整查詢，可修改欄位或數值再執行。
SELECT Name, Class, Grade
FROM student
WHERE Class = 'F6A'
ORDER BY Grade DESC
LIMIT 5;
Yellow Syntax Guide / 黃色語法重點
| Syntax | Meaning |
|---|---|
SELECT Name, Class, Grade | choose which columns to display |
WHERE Class = 'F6A' | keep only rows where Class is F6A |
ORDER BY Grade DESC | sort from the highest grade down |
LIMIT 5 | keep only the top 5 rows |
Dry Run / 手算追蹤
Table student has these sample rows (illustrative only / 僅供示範):
| Name | Class | Grade |
|---|---|---|
| Alice | F6A | 95 |
| Ben | F6A | 88 |
| Cindy | F6B | 91 |
| David | F6A | 70 |
Applying WHERE Class = 'F6A' keeps Alice, Ben, David. Then ORDER BY Grade DESC gives Alice (95), Ben (88), David (70). LIMIT 5 keeps all three since there are fewer than 5 rows.
先篩選 F6A 班，再依成績由高到低排序，最後取前 5 筆（不足 5 筆則全部保留）。
Examples / 例題
Example 1-1 · First N Rows
題目說明 / Problem Statement
Show the first 5 rows of the student table.
顯示 student 資料表的前 5 筆資料。
Reference Answer / 參考答案
SELECT * FROM student LIMIT 5;
Example 1-2 · Skip and Take (Pagination)
題目說明 / Problem Statement
Skip the first 10 rows, then show the next 5 rows (rows 11–15), with columns ID, Name, Class, Grade.
跳過前 10 筆，顯示第 11 至 15 筆資料。
Reference Answer / 參考答案
SELECT ID, Name, Class, Grade
FROM student
LIMIT 5 OFFSET 10;
Example 1-3 · Sorting with a Tie-breaker
題目說明 / Problem Statement
Show Name, Class, Grade for all students, ordered by Class ascending, then by Grade descending within each class.
所有學生依班級由小到大排序，同班內再依成績由高到低排序。
Reference Answer / 參考答案
SELECT Name, Class, Grade
FROM student
ORDER BY Class ASC, Grade DESC;
ClassWork 1 / 課後練習
Student task / 學生任務：先自行完成 SQL，再用每題下方的截圖核對結果。
Run every task in sqliteviz.com and screenshot a successful result. 於 sqliteviz.com 執行每一題，並截圖成功結果。
ClassWork 1-1 · First 5 Rows
Show the first 5 rows of the student table.
顯示前 5 筆學生資料。
Reference Answer / 參考答案
ClassWork 1-2 · Skip 10, Show Next 5
Show the next 5 rows after skipping the first 10 rows. 跳過前 10 筆後，顯示 11–15 筆學生資料。
Reference Answer / 參考答案
ClassWork 1-3 · Filter by Class
Show all students in class F6A. 查詢 F6A 班級的所有學生。
Reference Answer / 參考答案
ClassWork 1-4 · Exclude a Class
Show all students not in class F6B. 查詢所有班級不是 F6B 的學生。
Reference Answer / 參考答案
ClassWork 1-5 · Filter by Grade
Show students whose grade is greater than 90. 查詢成績大於 90 的學生。
Reference Answer / 參考答案
ClassWork 1-6 · Combine Class and Grade Filters
Show students in class F6C whose grade is less than 60. 查詢 F6C 班級中成績低於 60 的學生。
Reference Answer / 參考答案
ClassWork 1-7 · Top 10 by Grade
Show the top 10 students ordered by grade (highest first). 查詢成績最高的前 10 名學生。
Reference Answer / 參考答案
ClassWork 1-8 · Bottom 5 by Grade
Show the bottom 5 students ordered by grade (lowest first). 查詢成績最低的 5 名學生。
Reference Answer / 參考答案
ClassWork 1-9 · Sort by Class, then Grade
Show all students ordered by class, then by grade descending. 查詢所有學生，先依班級排序，再依成績由高到低排序。
Reference Answer / 參考答案
ClassWork 1-10 · Grade Range within a Class
Show Name and Grade of students in class F6D who scored between 70 and 85.
查詢 F6D 班級中分數介於 70 和 85 的學生姓名與成績。
Reference Answer / 參考答案
Active Recall / 主動回想
- Cover the notes: what is the difference between
WHEREandORDER BY? - Which clause gives rows 11–15?
- Write the query-clause order for: filter F6A, sort by grade high to low, keep five rows.
Spaced Review / 間隔複習
- Next lesson: explain why
LIMITalone does not guarantee the highest scores. - 1 week later: write a query for a different class and score range without copying a previous answer.
Key Takeaways / 重點帶走
SELECTchooses columns;WHEREchooses rows.ORDER BYdecides the order beforeLIMITkeeps the first rows.- Check both result columns and row count after every query.
Common Mistakes / 常見錯誤
- Using
LIMITbeforeORDER BYwhen asking for a top-N result. - Forgetting quotation marks around text values such as
F6A.
30-Second Review / 30 秒複習
Without looking, complete: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...;
