Relational algebra and SQL

語彙

English	意味
selection/sɪˈlekʃn/	選択
projection/prəˈdʒekʃn/	A relational-algebra operation choosing specified columns from a relation.
join/dʒɔɪn/	An SQL operation that combines rows from tables according to a specified relationship or condition.

English

Five operators, one pipeline

The algebra: selection σ keeps rows, projection π keeps columns, join ⋈ combines on a condition, union and difference combine tables. Every SELECT is these five in a pipeline.

{exercise:uni-cs0802-algebra}

実践

The operator that keeps columns is —

難易度 1/5 · 100 コイン + 今日初回達成時の小ボーナス

English

FROM builds the row set, WHERE filters rows, GROUP BY partitions, HAVING filters groups, SELECT projects, ORDER BY sorts. Filtering an aggregate in WHERE is a category error the engine rejects — use HAVING.
The course query: join Course and Enrollment, select credits = 15, group by title, keep groups of size $\ge 2$. Executed on the sample schema the result is Databases | 3.

{exercise:uni-cs0802-order}

実践

Filtering on COUNT(*) happens in —

難易度 1/5 · 100 コイン + 今日初回達成時の小ボーナス

English

A plain join drops the 2 courses with no enrollment; LEFT JOIN keeps them with n = 0 — 6 rows. COUNT(*) counts rows; COUNT(col) skips NULLs.
Projecting a many-side column repeats values: 14 enrollment rows project 14 student ids — add DISTINCT when you mean the set.

Example

Read every query as a pipeline and the output stops being surprising.

{exercise:uni-cs0802-join}

実践

The course query on the sample schema returns —

難易度 1/5 · 100 コイン + 今日初回達成時の小ボーナス