CODEWITHSUNDEEP
relational-algebra
user251249
user251249

Reputation:

Relational algebra - what is the proper way to represent a 'having' clause?

This is a small part of a homework question so I can understand the whole.

SQL query to list car prices that occur more than once:

select car_price from cars
group by car_price
having count (car_price) > 1;

The general form of this in relational algebra is Y (gl, al) R where Y is the greek symbol, gl is list of attributes to group, and al is a list of aggregations.

The relational algebra:

 Y (count(car_price)) cars

How is the having clause written in that statement? Is there a shorthand? If not, do I just need to select from that relation? Like this?

SELECT (count(car_price) > 1) [Y (count(car_price)) cars]

Upvotes: 6

Views: 4566

Answers (2)

Erwin Smout
Erwin Smout

Reputation: 18408

For a more or less precise answer to the actual question asked, "Relational algebra - what is the proper way to represent a ‘having’ clause?", it needs to be stated first that the question itself seems to suggest, or presume, that there exists such a thing as "THE" relational algebra, but that presumption is simply untrue !

An algebra is a set of operators, and anyone can define any set of operators he likes, meaning anyone can define any algebra he likes ! In his most recent publication, Hugh Darwen mentions that RESTRICT is not a fundamental operator of the algebra, though lots of others do consider it as such.

Especially with respect to aggregations and summaries, there is little consensus as to how those should be incorporated in a relational algebra. Defining operators such as COUNT() (that take a relation as an argument value and return an integer) as part of the algebra, might be problematic wrt the closure property of the algebra, precisely because such operators do not return a relation ...

So the sorry, but nevertheless most appropriate, answer here seems to be that a conclusive answer to this question is almost impossible to give ...

Upvotes: 0

Simon Bertrang
Simon Bertrang

Reputation: 29

select count(*) from (select * from cars where price > 1) as cars;

also known as relational closure.

Upvotes: 2

Related Questions