Querying with SQL

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.

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 than COUNT(*), which also counts the retained unmatched row. The empty course should report zero.

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;

Filter rows and groups at different stages

• WHERE filters input rows before grouping. HAVING filters groups using a condition such as COUNT(e.student_id) >= 2.
• In this example, adding that HAVING condition before ORDER BY returns only course 8. Filtering e.student_id in WHERE can remove the unmatched rows you meant to retain.

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.