Relational algebra and SQL

Từ vựng

English	Ý nghĩa
selection/sɪˈlekʃn/	Lựa chọ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}

Luyện tập

The operator that keeps columns is —

Độ khó 1/5 · 100 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay

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}

Luyện tập

Filtering on COUNT(*) happens in —

Độ khó 1/5 · 100 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay

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}

Luyện tập

The course query on the sample schema returns —

Độ khó 1/5 · 100 xu + phần thưởng nhỏ khi hoàn thành lần đầu tiên hôm nay