Re: SQL Fun Challenge #2

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 03 Mar 2004 17:54:53 -0800
Message-ID: <1078365261.567197@yasure>

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_at_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_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Wed Mar 03 2004 - 19:54:53 CST