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).
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). 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).
RE: interesting sql question
select pn.name from (select /*+ no_merge */ count(*) boat_cnt from boat) bt, bid bd, person pn where bd.sid = pn.sid group by pn.name, boat_cnt having count(bd.boat_id) = boat_cnt Waleed -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: Khedr, Waleed 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
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).
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: interesting sql question
This would eliminate duplicate bids on the same boat by the same person
SELECT p.*
FROMPERSON p,
(
SELECT COUNT(*) boat_count
FROMBOAT
) c,
(
SELECT sid, COUNT(DISTINCT boat_id) bid_count
FROMBIDS
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: [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: Chelur, Jayadas {PBSG}
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
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 Hey ... the question wasn't complete ... give us the full statement of the question ... g 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
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
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 ...
g
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 ...
g 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 !
