Re: SQL Fun Challenge #2

From: Brian Peasland <dba_at_remove_spam.peasland.com>
Date: Thu, 4 Mar 2004 01:16:33 GMT
Message-ID: <40468371.FA8F2E72@remove_spam.peasland.com>

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 Received on Wed Mar 03 2004 - 19:16:33 CST