Here is one question, written two ways:
SQL: SELECT DISTINCT name FROM instructor WHERE salary > 80000;
Relational algebra: π name (σ salary > 80000 (instructor))The second line is : the mathematics under SQL. Database textbooks and exams use it to define what a query means.
Operators take relations and return a relation
Each operator gets one or two relations and returns a new relation. The result of one operator can be the input of the next one:
The name of a relation alone is also an expression: instructor returns every tuple of instructor.
Relational algebra is procedural: the expression gives the steps and their order. SQL is declarative (Chapter 1): it describes the result only.
The operators
| Symbol | Name | Takes | This chapter's SQL |
|---|---|---|---|
| σ | selection | 1 relation | WHERE |
| π | projection | 1 relation | SELECT DISTINCT columns |
| ρ | rename | 1 relation | AS |
| × | Cartesian product | 2 relations | CROSS JOIN |
| ∪ | union | 2 relations | UNION |
| − | set difference | 2 relations | EXCEPT |
| ⋈ | join | 2 relations | JOIN |
| ∩ | intersection | 2 relations | INTERSECT |
| ÷ | division | 2 relations | no keyword |
| γ | grouping | 1 relation | GROUP BY |
The first six are the fundamental operators. You can write ⋈, ∩ and ÷ with them; they are short forms. γ is an extended operator: it adds aggregates.
Notation in this course
Textbooks write the condition of an operator as a subscript: σsalary > 80000(instructor). In plain text, this course writes the subscript on the line, after the symbol:
σ salary > 80000 (instructor)A relation is a set of tuples: it never holds the same tuple twice. Keep this in mind. The next lessons show where it changes the SQL.