Unit 1 · SELECT, LIMIT, OFFSET, WHERE, ORDER BY
Unit 1 · SELECT, LIMIT, OFFSET, WHERE, ORDER BY
Data file / 資料檔：student table — download from the link shared in class and load into sqliteviz.com.
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 / 課後練習
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 筆學生資料。
SELECT * FROM student LIMIT 5;
ClassWork 1-2 · Skip 10, Show Next 5
Show the next 5 rows after skipping the first 10 rows. 中文：跳過前 10 筆後，顯示 11–15 筆學生資料。
SELECT * FROM student LIMIT 5 OFFSET 10;
ClassWork 1-3 · Filter by Class
Show all students in class F6A. 中文：查詢 F6A 班級的所有學生。
SELECT * FROM student WHERE Class = 'F6A';
ClassWork 1-4 · Exclude a Class
Show all students not in class F6B. 中文：查詢所有班級不是 F6B 的學生。
SELECT * FROM student WHERE Class <> 'F6B';
ClassWork 1-5 · Filter by Grade
Show students whose grade is greater than 90. 中文：查詢成績大於 90 的學生。
SELECT * FROM student WHERE Grade > 90;
ClassWork 1-6 · Combine Class and Grade Filters
Show students in class F6C whose grade is less than 60. 中文：查詢 F6C 班級中成績低於 60 的學生。
SELECT * FROM student WHERE Class = 'F6C' AND Grade < 60;
ClassWork 1-7 · Top 10 by Grade
Show the top 10 students ordered by grade (highest first). 中文：查詢成績最高的前 10 名學生。
SELECT * FROM student ORDER BY Grade DESC LIMIT 10;
ClassWork 1-8 · Bottom 5 by Grade
Show the bottom 5 students ordered by grade (lowest first). 中文：查詢成績最低的 5 名學生。
SELECT * FROM student ORDER BY Grade ASC LIMIT 5;
ClassWork 1-9 · Sort by Class, then Grade
Show all students ordered by class, then by grade descending. 中文：查詢所有學生，先依班級排序，再依成績由高到低排序。
SELECT Name, Class, Grade
FROM student
ORDER BY Class ASC, Grade DESC;
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 的學生姓名與成績。
SELECT Name, Grade
FROM student
WHERE Class = 'F6D' AND Grade BETWEEN 70 AND 85;
