Re: SQL Fun Challenge #2

From: Tony <andrewst_at_onetel.net.uk>
Date: 4 Mar 2004 05:54:37 -0800
Message-ID: <c0e3f26e.0403040554.7d858bd2@posting.google.com>

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1078359740.735908_at_yasure>...
> 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!

Here is my attempt:

SQL> create table poss_ages( age integer );

Table created.

SQL> insert into poss_ages select rownum-1 from all_objects where rownum <= 37;

37 rows created.

SQL> select c1.age, c2.age, c3.age, c1.age+c2.age+c3.age tot_age   2 from poss_ages c1, poss_ages c2, poss_ages c3

  3  where  c1.age*c2.age*c3.age = 36
  4  and    c1.age >= c2.age
  5  and    c2.age >= c3.age

  6 order by tot_age;

       AGE AGE AGE TOT_AGE ---------- ---------- ---------- ----------

         4          3          3         10
         6          3          2         11
         6          6          1         13
         9          2          2         13
         9          4          1         14
        12          3          1         16
        18          2          1         21
        36          1          1         38

From this I can see that the total age must be 13 since that is the only ambiguous value, and that since there is one eldest child, the ages must be 9, 2 and 2 and NOT 6, 6 and 1.

I could even deduce the answer via a more complex query:

SQL> select age1, age2, age3
  2 from
  3 (
  4 select c1.age age1, c2.age age2, c3.age age3, c1.age+c2.age+c3.age tot_age,
  5 count(*) over (partition by c1.age+c2.age+c3.age) cnt   6 from poss_ages c1, poss_ages c2, poss_ages c3

  7  where  c1.age*c2.age*c3.age = 36
  8  and    c1.age >= c2.age
  9  and    c2.age >= c3.age

 10 )
 11 where age1 > age2
 12 and cnt = 2;

      AGE1 AGE2 AGE3
---------- ---------- ----------

         9 2 2 Received on Thu Mar 04 2004 - 07:54:37 CST