## Stuff about computing…mostly

### Solving a relational query: part 3

Image via Wikipedia

This is starting to depress me, now, because I thought I was full on top of all this material until I tried this question.

So, going back to the database specified in part 1 – I now need to find the relational algebra (not the SQL, I have done that) for: “find the name and email of any user who has made multiple bids for a lot including a bid exceeding £100″.

Various ideas have crossed my mind and some bits are easy eg $USER \Join \sigma _{AMOUNT > 100} \rho (BIDDER\_ID -> USER\_ID,\ BID)$ gets a relation from which we coul;d extract users who bid more than £100 for anything, but how can I get it all to string together.

I think the SQL that works is:

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 L.LOT_ID IN (
SELECT COUNT(*) > 1 FROM
LOT, BID WHERE
LOT.LOT_ID = L.LOT_ID AND BID.LOT_ID = LOT.LOT_ID
AND BID.BIDDER_ID = U.USER_ID)

About these ads

#### Comments on: "Solving a relational query: part 3" (5)

1. Methinks the “> 1″ part of the inner query should be in a WHERE clause and not a SELECT field.

My SQL is rusty, but I believe grouping is your friend here. I think you can select BIDDER_ID, COUNT(BID_ID) and MAX(AMOUNT) in a single SELECT of the appropriate join, with GROUP BY BIDDER_ID and maybe put the count > 1 and max > 100 in the WHERE clause of that query. Then you need to connect the BIDDER_ID field of that query to the other stuff.

2. yes, you are right. I sort of thought that as I was typing it…

3. [...] Paul Rubin pointed out the SQL in the last post was broken – here’s some that, I think (please do not let it [...]

4. [...] Solving a relational query: part 3 (cartesianproduct.wordpress.com) [...]

5. [...] Solving a relational query: part 3 (cartesianproduct.wordpress.com) [...]

Comments are closed.