Algebra Relational sql GROUP BY SORT BY ORDER BY

Question

I wanted to know what is the equivalent in GROUP BY, SORT BY and ORDER BY in algebra relational ?

Apocopate

Answer 1

Neither is possible in relational algebra but people have been creating some "extensions" for these operations (Note: in the original text, part of the text is written as subscript).

GROUP BY, According to the book Fundamentals of Database Systems (Elmasri, Navathe 2011 6th ed):

Another type of request that cannot be expressed in the basic relational algebra is to specify mathematical aggregate functions on collections of values from the database.
...
We can define an AGGREGATE FUNCTION operation, using the symbol ℑ (pronounced script F)7, to specify these types of requests as follows:

<grouping attributes> ℑ <function list> (R)

where <grouping attributes> is a list of attributes of the relation specified in R, and <function list> is a list of (<function> <attribute>) pairs. In each such pair, <function> is one of the allowed functions—such as SUM, AVERAGE, MAXIMUM, MINIMUM,COUNT—and <attribute> is an attribute of the relation specified by R. The resulting relation has the grouping attributes plus one attribute for each element in the function list.

ORDER BY (SORT BY), John L. Donaldson's lecture notes.

Since a relation is a set (or a bag), there is no ordering defined for a relation. That is, two relations are the same if they contain the same tuples, irrespective of ordering. However, a user frequently wants the output of a query to be listed in some particular order. We can define an additional operator τ which sorts a relation if we are willing to allow an operator whose output is not a relation, but an ordered list of tuples.

For example, the expression

τLastName,FirstName(Student)

generates a list of all the Student tuples, ordered by LastName (as the primary sort key) then FirstName (as a secondary sort key). (The secondary sort key is used only if two tuples agree on the primary sort key. A sorting operation can list any number of sort keys, from most significant to least significant.)

Ephedrine

Answer 2

You can use projection π for the columns that you want group the table by them without aggregating (The PROJECT operation removes any duplicate tuples) as following:

π c1,c2,c3 (R)

where c1,c2,c3 are columns(attributes) and R is the table(the relation)

Camisado

Answer 3

According to this SQL to relational algebra converter tool, we have:

SELECT agents.agent_code, agents.agent_name, SUM(orders.advance_amount)
FROM agents, orders
WHERE agents.agent_code = orders.agent_code
GROUP BY agents.agent_code, agents.agent_name
ORDER BY agents.agent_code

Written in functions sort of like:

τ agents.agent_code
 γ agent_code, agent_name, SUM(advance_amount)
  σ agents.agent_code = orders.agent_code (agents × orders)

With a diagram like:

Sciamachy