RE: RE: interesting sql question
no there are examples in the book using where 'not exists'. the query was horrible. Ill post it later if you want to see how bad it is. no its not homework. Id get the answer wrong if i did it this way, since Id have to follow the model in the book. Which is terrible. > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon PM 12:29:40 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: RE: interesting sql question > > yeah! I think it *is* homework :) > > Tom > > > -Original Message- > Sent: Monday, September 29, 2003 12:10 PM > To: Multiple recipients of list ORACLE-L > > > > Hey ... the question wasn't complete ... > > give us the full statement of the question ... > > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > > > -Original Message- > > Sent: Monday, September 29, 2003 11:55 AM > To: Multiple recipients of list ORACLE-L > > > a user may request the same boat more than once. not sure that work. > > > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > > Date: 2003/09/29 Mon AM 10:34:53 EDT > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: RE: RE: interesting sql question > > > > Here is an attempt ... > > > > select p.* > > from persons p > > where sid in > > (select sid, count(bid) > > from bids > >group by sid > > having count(sid) = (select count(boad_id) from boats)) > > / > > > > You wanted to find all persons who have booked all boats ... add criteria > > for booked in the first sub-query. > > > > Raj > > > -------- > > > > > Rajendra dot Jamadagni at nospamespn dot com > > All Views expressed in this email are strictly personal. > > QOTD: Any clod can have facts, having an opinion is an art ! > > > > > > > Title: RE: RE: interesting sql question yeah! I think it *is* homework :) Tom -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 12:10 PMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: interesting sql question Hey ... the question wasn't complete ... give us the full statement of the question ... Raj ---- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: interesting sql question a user may request the same boat more than once. not sure that work. > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon AM 10:34:53 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: RE: interesting sql question > > Here is an attempt ... > > select p.* > from persons p > where sid in > (select sid, count(bid) > from bids > group by sid > having count(sid) = (select count(boad_id) from boats)) > / > > You wanted to find all persons who have booked all boats ... add criteria > for booked in the first sub-query. > > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > >
RE: RE: interesting sql question
Title: RE: RE: interesting sql question yeah! I think it *is* homework :) Tom -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 12:10 PMTo: Multiple recipients of list ORACLE-LSubject: RE: RE: interesting sql question Hey ... the question wasn't complete ... give us the full statement of the question ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: interesting sql question a user may request the same boat more than once. not sure that work. > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon AM 10:34:53 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: RE: interesting sql question > > Here is an attempt ... > > select p.* > from persons p > where sid in > (select sid, count(bid) > from bids > group by sid > having count(sid) = (select count(boad_id) from boats)) > / > > You wanted to find all persons who have booked all boats ... add criteria > for booked in the first sub-query. > > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > >
Re: RE: interesting sql question
you could do this, but i would have concerns over the indexing strategy. select name from person, (select distinct sid, count(*) bid_count from bids group by sid HAVING count(*) = (SELECT COUNT(BOAT_ID FROM BOATS)) bids where person.sid = bids.sid; Now yours bids table is an intersect table and would have the most records of all three tables. I would create an extra field that never gets update and just put a default value in it. Then I would put a bitmap index on it. since they aer VERY faster on counts. my problem is with the group by. SID could be huge. That could lead to a massive slow down and alot of LIOs dont think there is a faster a solution though. No correlated sub-queries which are LIO intensive. > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon AM 09:34:38 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: interesting sql question > > Ralph, > > Assuming that there is no history in the BIDS table (meaning that there are > no "old" records indicating a bid recorded last year), I think the following > would work just fine. > > > select name > from person, > (select distinct sid, count(*) bid_count > from bids > group by sid) bids > where person.sid = bids.sid > and bid_count = 3 > > > Tom Mercadante > Oracle Certified Professional > > > -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: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Mercadante, Thomas F > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: RE: interesting sql question
Title: RE: RE: interesting sql question Hey ... the question wasn't complete ... give us the full statement of the question ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: interesting sql question a user may request the same boat more than once. not sure that work. > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon AM 10:34:53 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: RE: interesting sql question > > Here is an attempt ... > > select p.* > from persons p > where sid in > (select sid, count(bid) > from bids > group by sid > having count(sid) = (select count(boad_id) from boats)) > / > > You wanted to find all persons who have booked all boats ... add criteria > for booked in the first sub-query. > > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > > This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: RE: interesting sql question
a user may request the same boat more than once. not sure that work. > > From: "Jamadagni, Rajendra" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon AM 10:34:53 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: RE: interesting sql question > > Here is an attempt ... > > select p.* > from persons p > where sid in > (select sid, count(bid) > from bids >group by sid > having count(sid) = (select count(boad_id) from boats)) > / > > You wanted to find all persons who have booked all boats ... add criteria > for booked in the first sub-query. > > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > > Title: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: RE: interesting sql question
Title: RE: RE: interesting sql question Here is an attempt ... select p.* from persons p where sid in (select sid, count(bid) from bids group by sid having count(sid) = (select count(boad_id) from boats)) / You wanted to find all persons who have booked all boats ... add criteria for booked in the first sub-query. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
Re: RE: interesting sql question
> > From: "Stephane Faroult" <[EMAIL PROTECTED]> > Date: 2003/09/29 Mon AM 09:59:39 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: interesting sql question > > > > >- --- Original Message --- - > >From: <[EMAIL PROTECTED]> > >To: Multiple recipients of list ORACLE-L > ><[EMAIL PROTECTED]> > >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). Bitmap scan would be the fastest. Ive noticed that counts on those are incredibly fast. So your saying something like: how would you write the query? I dont quite see it. > > Regards, > > Stephane Faroult > Oriole > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephane Faroult > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
