Querying with SQL · ⁨Consultando com 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 · ⁨ e ⁩(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.