Tag Archives: Database

Solving a relational query: part 4


As Paul Rubin pointed out the SQL in the last post was broken – here’s some that, I think (please do not let it be wrong a second time!), by relying on SQL’s coercion of a tuple to a scalar, would work (Paul proposed an even more ambitions way of doing this in a single join):

SELECT DISTINCT U.NAME, U.EMAIL
FROM USER U, BID B, LOT L
WHERE U.USER_ID = B.BIDDER_ID AND
B.AMOUNT > 100 AND B.LOT_ID = L.LOT_ID
AND (SELECT COUNT(*) FROM
BID, LOT WHERE
BID.LOT_ID = B.LOT_ID AND BID.BIDDER_ID = U.USER_ID
AND BID.LOT_ID = LOT.LOT_ID) > 1

Still no clue as to how to do this with relational algebra though.

Looks like a great book


Relational model Data items organized as a set...
Image via Wikipedia

My copy of C. J. Date‘s SQL and Relational Theory: How to Write Accurate SQL Code turned up today – I have only read one chapter so far but already I have a feeling that it is going to have been worth every penny.

Date is concerned not to write another SQL primer but to give those with some SQL experience a good grounding in the relational model and the set theory on which it is based. It just feels more like a proper scientific/mathematical text book and it also seems to be well written.

Very pleased with the purchase and will keep you all posted on how it goes: at the moment I am just wishing I had bought it months ago.