| English | 释义 | 拼音 |
|---|---|---|
| SQL/ˌes kjuː ˈel/ | 结构化查询语言 | jié gòu huà chá xún yǔ yán |
| JOIN/dʒɔɪn/ | 连接 | lián jiē |
| GROUP BY/ɡruːp baɪ/ | 分组 | fēn zǔ |
Two different courses share a title
- Two courses are both called Robotics. Grouping only by title combines their enrolments and hides that they are different courses.
- SQL 结构化查询语言 describes the data result you want. Check row identity and expected output before trusting a plausible count.
Follow the relationship in the join
- A JOIN 连接 combines rows using a relationship such as
e.course_id = c.id. Write the intended relationship explicitly. - A cross join pairs every row on one side with every row on the other. In SQLite, an unconstrained join can produce that product; other SQL systems can reject missing join conditions.
在 SQLite 中,无约束连接将三门课程与三个注册记录配对。它能产生多少对?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
在应用任何后续过滤之前,交叉积包含 3 × 3 行。
Keep zero-enrolment courses
- A left join keeps every course, including one without a matching enrolment. Its unmatched enrolment fields are null.
- Count
e.student_id, which excludes null, rather thanCOUNT(*), which also counts the retained unmatched row. The empty course should report zero.

哪个表达式能正确统计左连接后未匹配的课程的零注册数?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
COUNT 作用于未匹配的可为空的字段时会排除空值;COUNT(*) 则统计保留的行。
Run a query with known answers
- Run this complete script in a local SQLite database. Its two Robotics courses have different IDs, and Art has no enrolments.
- GROUP BY 分组 groups by both course ID and title. Expected rows are
(7, Robotics, 1),(8, Robotics, 2)and(9, Art, 0).
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (student_id INTEGER, course_id INTEGER);
INSERT INTO courses VALUES (7, 'Robotics'), (8, 'Robotics'), (9, 'Art');
INSERT INTO enrolments VALUES (1, 7), (1, 8), (2, 8);
SELECT c.id, c.title, COUNT(e.student_id) AS enrolled
FROM courses AS c
LEFT JOIN enrolments AS e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY c.id;
保留空课程并统计匹配记录
用本课的三门课程区分LEFT JOIN、COUNT和GROUP BY。
补全按课程标识和标题分组的子句:____ c.id, c.title
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
GROUP BY 定义分组。包含 漏极电流 可使不同但标题相同的课程保持分离。
该完整查询针对课程 ID 7、8 和 9 返回哪些注册计数?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
样本中,7 有一项匹配,8 有两项匹配,而 9 没有匹配。
Filter rows and groups at different stages
WHEREfilters input rows before grouping.HAVINGfilters groups using a condition such asCOUNT(e.student_id) >= 2.- In this example, adding that HAVING condition before ORDER BY returns only course 8. Filtering
e.student_idin WHERE can remove the unmatched rows you meant to retain.
哪个子句筛选出至少有两名已注册学生的分组?
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
HAVING对分组应用条件;WHERE过滤输入行。
结合课程计数示例解释 WHERE 和 HAVING 的用法。
难度 3/5 · 今日首次完成可获 200 金币及少量额外奖励
WHERE 用于过滤行;HAVING 用于测试每组的计数。
Use edge cases to verify the result
- Check repeated titles, zero matches and multiple matches against known counts. A result that looks reasonable is not enough.
- Explain the selected fields, join condition, grouping and count. Decide whether duplicate relationship rows are valid or should be prevented by the schema.
Join on the relationship, group by the identity, and count the intended thing. A tiny known dataset makes incorrect queries easier to see.
一个合理的计数足以证明 SQL 查询是正确的。
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
对比已知的预期行数,包括零匹配和重复标题的情况。
仅按标题分组会导致两个同名课程被合并。
难度 1/5 · 今日首次完成可获 100 金币及少量额外奖励
正确。相同标题会归为一组,因此两门机器人学课程会被一起计数;还需按课程唯一标识进行分组。