Oracle-L: RE: interesting sql question

From: Chelur, Jayadas {PBSG} <Jayadas.Chelur_at_pepsi.com>
Date: Mon, 29 Sep 2003 06:59:40 -0800
Message-ID: <F001.005D15EA.20030929065940@fatcity.com>

This would eliminate duplicate bids on the same boat by the same person

SELECT p.*
FROM PERSON p,

        (
        SELECT  COUNT(*) boat_count
        FROM    BOAT
        ) c,
        (
        SELECT  sid, COUNT(DISTINCT boat_id) bid_count 
        FROM    BIDS 
        GROUP   BY sid
        ) b
WHERE   p.sid       = b.sid
AND     b.bid_count = c.boat_count;

-----Original Message-----
Sent: Monday, September 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L

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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <rgaffuri_at_cox.net
INET: rgaffuri_at_cox.net

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Chelur, Jayadas {PBSG}
INET: Jayadas.Chelur_at_pepsi.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------