Relational Algebra in SQL using variables?

Question

How would I write this in SQL. Let's say I have a relation which is

college(name, age, address)

I want to find the names of the students that are 20

Is there a way I can write this in SQL

R1 = σ(age = 20) from college

R2 = π(name) from R1

I guess I can write this as

select name from college where age = 20;

Just wondering how I could use variables to make it cleaner?

I actually find the ordinary SQL you've written both clearer and terser. The relational algebra formalism bears no relation (ha!) to how people think about data processing in the real world. — Steve, Feb 04 '18 at 09:01
@steve Relational algebra reflects exactly "how people think". Operators are 1:1 with AND, OR, AND NOT, EXISTS, etc. Unfortunately this is not taught. Whatever sense one makes in SQL is a contortion of simpler thinking in relational algebra. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Oct 11 '18 at 23:45
This post asks four questions, what is your question? Are your R1/R2 assignments supposed to be in RA (relational algebra)? Please give a reference to its definition, there are many versions of RA. What does "cleaner" mean? What did you learn from googling the MySQL manual for varialbles & assignment? — philipxy, Oct 13 '18 at 02:06

score 0 · Accepted Answer · answered Feb 04 '18 at 09:09

Notwithstanding my comment on the question, if you really wanted to split a query into intermediate steps (which is relevant to anything but the most straightforward queries), you'd write it something like this:

WITH age_20_colleges AS
(
    SELECT * 
    FROM college 
    WHERE age = 20
)
SELECT name 
FROM age_20_colleges

This basically assigns a filtered set to a named table "variable" age_20_colleges (although in SQL this would not be called a "variable" in this context, it would be called a "derived table"), and then the final query selects names from this set.

Relational Algebra in SQL using variables?

1 Answers1