Querying with SQL
| English | Chinese | Pinyin |
|---|---|---|
| SQL | 结构化查询语言 | jié gòu huà chá xún yǔ yán |
| JOIN | 连接 | lián jiē |
| GROUP BY | 分组 | fēn zǔ |
Asking a database a question
- SQL 结构化查询语言 is how you ask.
SELECT columns FROM table WHERE conditionis the core, and most queries are a variation on it. - It is declarative: you say what you want, not how to find it.
- Every example here runs in the site's own playground, and the SQL reference goes further than this lesson does.
JOIN
- A JOIN 连接 combines rows from two tables where a key matches.
FROM enrolments e JOIN courses c ON c.id = e.course_idfollows the foreign key you designed in the last lesson.- Without the
ONcondition you get every row paired with every other row, which is why a forgotten join condition returns thousands of nonsense rows rather than an error.
What happens if you join two tables and forget the ON condition?
It returns thousands of nonsense rows rather than failing, which is why it is easy to miss.
GROUP BY
- GROUP BY 分组 collapses rows into groups, and
COUNT,SUMorAVGthen summarise each group. - It answers every "how many per…" question: per course, per month, per student.
- The columns you select must either be grouped by or aggregated. Anything else has no single value per group.
Which clause collapses rows so COUNT can summarise each set?
It answers every "how many per…" question: per course, per month, per student.
Which courses have more than 20 students?
SELECT c.title, COUNT(*) AS students
FROM enrolments e
JOIN courses c ON c.id = e.course_id
GROUP BY c.title
HAVING COUNT(*) > 20;
The count is a property of the group, so the filter is HAVING. Written as WHERE COUNT(*) > 20 it does not run at all — and that failure is the lucky case.
You want only groups whose COUNT(*) exceeds 20. Which keyword filters them?
WHERE filters rows before grouping; the count only exists after the grouping has happened.
In one sentence, say what makes WHERE and HAVING different.
Example: "WHERE filters rows before they are grouped, while HAVING filters the groups once the aggregate has been calculated."
WHERE filters rows before grouping; HAVING filters groups after. This is the classic SQL error, and the dangerous version is not the query that fails — it is the one that runs and silently answers a different question.
Check a query against a small table where you can count the right answer by hand. A query that returns plausible numbers is not a query you have verified, and "it ran" is not "it is right".
A query that runs and returns plausible numbers has been verified.
Test it against a small table where you can count the answer by hand. "It ran" is not "it is right".