RE: interesting sql question

>----- ------- Original Message ------- -----
>From: <rgaffuri_at_cox.net>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Mon, 29 Sep 2003 05:19:39
>
>Im taking a database theory class(no I dont need
>help with my homework). There is an interesting
>query in the book that I have never seen posed
>before. The solution would be hideously slow if
>there was even a moderate amount of data in the
>tables. How would you write it?
>
>Given 3 tables: and columns in the tables:
>
>TABLE: Person
>Primary Key: SID
>COLUMN: NAME
>
>TABLE: BIDS
>Primary Key: BID
>Foreign Key: SID
>FOREIGN KEYT: BOAT_ID
>Column: Date
>
>Boat:
>Primary Key: BOAT_ID
>Column: Color
>
>Find any person who has reserved all the boats. The
>
>
>I dont have the solution with me, but there is a
>'NOT EXISTS', then in the subquery there is a minus
>and a correlated 'where' clause.'. That query
>wouldnt move.
>
>How would you solve this?
>
>Also, according to the 'SQL Standard', SQL is
>supposed to support op codes such as 'ALL' or 'ANY'
>So you can say:
>
>Find all people who are older than any person with
>blue eyes. Or find all the people who are older
>than 'ALL' the people with blue eyes.
>
>Just to reiterate. Not looking for help with my
>homework. My professor isnt an Oracle guy so he
>doesnt know.
>

I would run an uncorrelated subquery on BOATS to count how many of them we have (mot likely to be a multimillion row table, and it's just a PK scan), which you can feed into the HAVING clause of a GROUP BY on BIDS. By playing with in line views, and supposing (which is often the case) that your FK is indexed it doesn't require anything but another index scan. Which can of course take *some* time if BIDS is really big but I don't see how to escape a group by here (or anything worse).

Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).