Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!news-FFM2.ecrc.net!newsfeed.stueberl.de!proxad.net!freenix!sn-xit-02!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Daniel Morgan Newsgroups: comp.databases.oracle.server Subject: Re: SQL Fun Challenge #2 Date: Wed, 03 Mar 2004 17:54:53 -0800 Organization: ATS Message-ID: <1078365261.567197@yasure> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 References: <1078359740.735908@yasure> <40468371.FA8F2E72@remove_spam.peasland.com> In-Reply-To: <40468371.FA8F2E72@remove_spam.peasland.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Cache-Post-Path: yasure!unknown@oracle.advtechserv.com X-Cache: nntpcache 2.4.0b5 (see http://www.nntpcache.org/) X-Complaints-To: abuse@supernews.com Lines: 112 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:256251 Brian Peasland wrote: > Daniel Morgan wrote: > >>Two mathematicians (Boris and Vladimir) met accidently for the first >>time in 20 years. >> >>They greet each other and begin catching up on their respective lives. >> >>Boris asks Vladi >>"Do you have any children?" "Yes" replies Vladimir, "I have three." "How >>old are they?", asks Boris. >> >>"The product of their ages is 36 and the sum of their ages is equal to >>the number of windows on that building across the street." Boris looks >>at the building, counts the windows then says "Vladi, that still doesn't >>tell me the ages." "Ah, says Vladi, then I must tell you that the eldest >>has red hair." "Oh", says Boris, "now I know their ages." What are the >>ages of Boris' children? >> >>Create a table, load it with data, and write a single SQL statement to >>produce the data set required to deduce the answer ... then deduce away! >> >>-- >>Daniel Morgan >>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp >>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp >>damorgan@x.washington.edu >>(replace 'x' with a 'u' to reply) > > > Old puzzle..... > > The only three numbers that multiply to 36 are the following: > > 1x1x36, 1x2x18, 1x3x12, 1x4x9, 1x6x6, 2x2x9, 2x3x6 and 3x3x4 > > The second clue is mostly a red herring. The above numbers all have > different sums. If you knew the number of windows in the building, then > the answer would be obvious (for the most part). > > 1+1+36 = 38 > 1+2+18 = 21 > 1+3+12 = 16 > 1+4+9 = 14 > 1+6+6 = 13 > 2+2+9 = 13 > 2+3+6 = 11 > 3+3+4 = 10 > > If the number of windows were 38,21,16,14,11, or 10, then you wouldn't > need the final clue. Since you need the final clue, that means you need > to be able to tell between two identical answers. Therefore the number > of windows is 13. > > Variations of this puzzle would have one boy and two girls (or vice > versa. In that case, the final clue mentions the "oldest boy" (or oldest > girl). Out of (1,6,6) and (2,2,9), you know that there are twins. There > is only one combination that has a true "oldest", that being (2,2,9). > The other combination has two "oldest" kids, unless you really want to > get nitpicky and say one twin was born before the other...but I digress. > > So a SQL solution might look as follows: > > CREATE TABLE kids ( > age1 NUMBER, > age2 NUMBER, > age3 NUMBER); > > INSERT INTO kids VALUES (1,1,36); > INSERT INTO kids VALUES (1,2,18); > INSERT INTO kids VALUES (1,3,12); > INSERT INTO kids VALUES (1,4,9); > INSERT INTO kids VALUES (1,6,6); > INSERT INTO kids VALUES (2,2,9); > INSERT INTO kids VALUES (2,3,6); > INSERT INTO kids VALUES (3,3,4); > > SELECT age1,age2,age3,age1+age2+age3 AS total > FROM ( SELECT age1+age2+age3 AS total,count(*) > FROM kids > GROUP BY age1+age2+age3 > HAVING count(*) > 1) x, > kids k > WHERE k.age1+k.age2+k.age3 = x.total > AND k.age3 > k.age2; > > > I'm sure it could be cleaned up... > > Cheers, > Brian Actually the second clue is essential to solving the problem. But lets give everyone a shot at it before disclosing the correct result. Remember this is a SQL challenge ... just looking up the answer in a book or working it out on paper doesn't count. Your insert statements would not have gotten you a passing grade in class last night in that you manually produced the possible ages for your insert statement by hand. BTW: One of your possible answer is excluded given the wording of the puzzle. A correct solution would have avoided it. ;-) -- Daniel Morgan http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp damorgan@x.washington.edu (replace 'x' with a 'u' to reply)