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 เหรียญ + บอนัสน้อยๆ เมื่อทำสำเร็จครั้งแรกในวันนี้