How to subtract values form two columns in RelaX (online relational algebra calculator)

Question

Is there any way to subtract values from two different columns using RelaX (an relational algebra online calculator)? I have tried using projection, group by, as well as a few examples I saw here on SO. I am trying to subtract the average wage from value the of the wage of employees.

Answer 1

The RelaX projection operator takes a list of expressions giving the column values of each row returned. Those expressions can be just column names but they don't have to be. (As with an SQL select clause.)

From the help link:

projection

Expressions can be used to create more complex statements using one or more columns of a single row.

pi c.id, lower(username)->user, concat(firstname, concat(' ', lastname))->fullname (
        ρ c ( Customer )
    )

Value expressions
With most operators you can use a value-expression which connects one or more columns of a single row to calculate a new value. This is possible for:

• the projection creating a new column (make sure to give the column a name)
• the selection any expression evaluating to boolean can be used
• for the joins any expression evaluating to boolean can be used; note that the rownum() expression always represents the index of the lefthand relation

PS RelaX is properly a query language, not an algebra. Its "value expressions" are not evaluated to a value before the call. That begs the question of how we would implement a language using an algebra.

From Is multiplication allowed in relational algebra?:

Some so-called "algebras" are really languages because the expressions don't only represent the results of operators being called on values. Although it is possible for an algebra to have operand values that represent expressions and/or relation values that contain names for themselves.

The projection that takes attribute expressions begs the question of its implementation given an algebra with projection only on a relation value and attribute names. This is important in an academic setting because a question may be wanting you to actually figure out how to do that, or because the difficulty of a question is dependent on the operators available. So find out what algebra you are supposed to use.

We can introduce an operator on attribute values when we only have basic relation operators taking attribute names and relation values. Each such operator can be associated with a relation value that has an attribute for each operand and an attribute for the result. The relation holds the tuples where the result value is equal to the the result of the operator called on the operand values. (The result is functionally dependent on the operands.)

From Relational Algebra rule for column transformation:

Suppose you supply the division operator on values of a column in the form of a constant base relation called DIVIDE holding tuples where dividend/divisor=quotient. I'll use the simplest algebra, with headings that are sets of attribute names. Assume we have input relation R with column c & average A. We want the relation like R but with each column c value set to its original value divided by A.

/* rows where
EXISTS dividend [R(dividend) & DIVIDE(dividend, A, c)]
*/
PROJECT c (
        RENAME c\dividend (R)
    NATURAL JOIN
        RENAME quotient\c (
            PROJECT dividend, quotient (SELECT divisor=A (DIVIDE))))

From Relational algebra - recode column values:

To introduce specific values into a relational algebra expression you have to have a way to write table literals. Usually the necessary operators are not made explicit, but on the other hand algebra exercises frequently use some kind of notation for example values.