Relational algebra and SQL

어휘

English	의미
selection/sɪˈlekʃn/	선택
projection/prəˈdʒekʃn/	A representation of an object or surface on another surface according to a specified rule.
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코인 + 오늘 첫 완료 시 작은 보너스 추가